Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.repository.cs;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
import com.spice.profitmandi.common.model.CustomRetailer;
import com.spice.profitmandi.common.model.ProfitMandiConstants;
import com.spice.profitmandi.dao.entity.auth.AuthUser;
import com.spice.profitmandi.dao.entity.cs.*;
import com.spice.profitmandi.dao.entity.fofo.ActivityType;
import com.spice.profitmandi.dao.entity.fofo.FofoStore;
import com.spice.profitmandi.dao.enumuration.auth.CollectionRemark;
import com.spice.profitmandi.dao.enumuration.cs.EscalationType;
import com.spice.profitmandi.dao.model.*;
import com.spice.profitmandi.dao.repository.auth.AuthRepository;
import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;
import com.spice.profitmandi.service.EmailService;
import com.spice.profitmandi.service.mail.MailOutboxService;
import com.spice.profitmandi.service.user.RetailerService;
import org.apache.commons.collections4.map.HashedMap;
import org.apache.commons.lang.RandomStringUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.stereotype.Component;

import javax.persistence.TypedQuery;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

@Component
public class CsServiceImpl implements CsService {

    private static final int ALL_PARTNERS_REGION = 5;

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

    private static final String ASSIGNED_TICKET = "Dear %s, New ticket #%s #%s created by %s has been assigned to you. Regards\nSmartdukaan";
    private static final String ESCALATED_TICKET = "Dear %s, Ticket #%s #%s created by %s has been escalated to you. Regards\nSmartdukaan";
    private static final String CATEGORY_CHANGED_TICKET = "Dear team, Category of Ticket #%s created by %s has been changed to %s. Regards\nSmartdukaan";
    private static final String ASSINMENT_SUBJECT = "Assignment Ticket";

    @Autowired
    TicketRepository ticketRepository;

    @Autowired
    JavaMailSender gmailRelaySender;

    @Autowired
    MailOutboxService mailOutboxService;

    @Autowired
    TicketCategoryRepository ticketCategoryRepository;

    @Autowired
    TicketSubCategoryRepository ticketSubCategoryRepository;

    @Autowired
    ActivityRepository activityRepository;

    @Autowired
    TicketActivityMediaRepository activityMediaRepository;

    @Autowired
    PartnerRegionRepository partnerRegionRepository;

    @Autowired
    private PositionRepository positionRepository;

    @Autowired
    private AuthRepository authRepository;

    @Autowired
    private RetailerService retailerService;

    @Autowired
    private RegionRepository regionRepository;

    @Autowired
    private TicketAssignedRepository ticketAssignedRepository;

    @Autowired
    private PartnerPositionRepository partnerPositionRepository;

    @Autowired
    private FofoStoreRepository fofoStoreRepository;

    @Autowired
    private SessionFactory sessionFactory;

    @Autowired
    EmailService emailService;

    @Autowired
    TicketReadStatusRepository ticketReadStatusRepository;


    @Override
    public void createTicket(int fofoId, int categoryId, int subcategoryId, String message) throws ProfitMandiBusinessException {
        this.createTicket(fofoId, categoryId, subcategoryId, message, 0);
    }


    @Override
    public void createTicket(int fofoId, int categoryId, int subcategoryId, String message, int createdByAuthId) throws ProfitMandiBusinessException {

        ActivityType type = ActivityType.OPENED;
        Ticket ticket = new Ticket();
        ticket.setSubCategoryId(subcategoryId);
        ticket.setFofoId(fofoId);
        ticket.setCreateTimestamp(LocalDateTime.now());
        ticket.setUpdateTimestamp(LocalDateTime.now());
        ticket.setHappyCode(getRandomString());
        ticketRepository.persist(ticket);
        this.addActivity(ticket, this.createActivity(type, message, createdByAuthId));
        ticket.setFirstActivityId(ticket.getLastActivityId());
        if (createdByAuthId != 0) {
            int l2AuthUser = this.getAuthUserId(categoryId, EscalationType.L2, fofoId);
            this.updateTicket(categoryId, subcategoryId, ticket, l2AuthUser, EscalationType.L2);
        } else {
            this.assignTicket(ticket);
        }

    }

    @Override
    public void createTicketWithMedia(int fofoId, int categoryId, int subcategoryId, String message, int createdByAuthId, ActivityMediaModel selectedRecording) throws ProfitMandiBusinessException {
        ActivityType type = ActivityType.OPENED;
        Ticket ticket = new Ticket();
        ticket.setSubCategoryId(subcategoryId);
        ticket.setFofoId(fofoId);
        ticket.setCreateTimestamp(LocalDateTime.now());
        ticket.setUpdateTimestamp(LocalDateTime.now());
        ticket.setHappyCode(getRandomString());
        ticketRepository.persist(ticket);
        this.addActivity(ticket, this.createActivity(type, message, createdByAuthId));
        this.addMediaActivity(ticket, this.createMediaActivity(selectedRecording));
        ticket.setFirstActivityId(ticket.getLastActivityId());
        if (createdByAuthId != 0) {
            int l2AuthUser = this.getAuthUserId(categoryId, EscalationType.L2, fofoId);
            this.updateTicket(categoryId, subcategoryId, ticket, l2AuthUser, EscalationType.L2);
        } else {
            this.assignTicket(ticket);
        }
    }

    @Override
    public void createTicket(int fofoId, int categoryId, int subcategoryId, String message, int createdByAuthId, EscalationType targetEscalation) throws ProfitMandiBusinessException {
        ActivityType type = ActivityType.OPENED;
        Ticket ticket = new Ticket();
        ticket.setSubCategoryId(subcategoryId);
        ticket.setFofoId(fofoId);
        ticket.setCreateTimestamp(LocalDateTime.now());
        ticket.setUpdateTimestamp(LocalDateTime.now());
        ticket.setHappyCode(getRandomString());
        ticketRepository.persist(ticket);
        this.addActivity(ticket, this.createActivity(type, message, createdByAuthId));
        ticket.setFirstActivityId(ticket.getLastActivityId());
        if (createdByAuthId != 0) {
            int authUser = this.getAuthUserIdWithFallback(categoryId, targetEscalation, fofoId);
            this.updateTicket(categoryId, subcategoryId, ticket, authUser, targetEscalation);
        } else {
            this.assignTicket(ticket);
        }
    }

    @Override
    public void createTicketWithMedia(int fofoId, int categoryId, int subcategoryId, String message, int createdByAuthId, ActivityMediaModel selectedRecording, EscalationType targetEscalation) throws ProfitMandiBusinessException {
        ActivityType type = ActivityType.OPENED;
        Ticket ticket = new Ticket();
        ticket.setSubCategoryId(subcategoryId);
        ticket.setFofoId(fofoId);
        ticket.setCreateTimestamp(LocalDateTime.now());
        ticket.setUpdateTimestamp(LocalDateTime.now());
        ticket.setHappyCode(getRandomString());
        ticketRepository.persist(ticket);
        this.addActivity(ticket, this.createActivity(type, message, createdByAuthId));
        this.addMediaActivity(ticket, this.createMediaActivity(selectedRecording));
        ticket.setFirstActivityId(ticket.getLastActivityId());
        if (createdByAuthId != 0) {
            int authUser = this.getAuthUserIdWithFallback(categoryId, targetEscalation, fofoId);
            this.updateTicket(categoryId, subcategoryId, ticket, authUser, targetEscalation);
        } else {
            this.assignTicket(ticket);
        }
    }

    /**
     * Gets auth user ID for given escalation level. If not found, falls back to next level (L1 -> L2 -> L3 -> L4 -> L5)
     * Returns both the auth user ID and the actual escalation level that was used.
     */
    @Override
    public EscalationAssignmentResult getAuthUserWithFallback(int categoryId, EscalationType targetEscalation, int fofoId) throws ProfitMandiBusinessException {
        EscalationType currentEscalation = targetEscalation;
        while (currentEscalation != null && !currentEscalation.equals(EscalationType.Final)) {
            try {
                int authUserId = this.getAuthUserId(categoryId, currentEscalation, fofoId);
                if (authUserId > 0) {
                    LOGGER.info("Found auth user {} for escalation level {} and fofoId {}", authUserId, currentEscalation, fofoId);
                    return new EscalationAssignmentResult(authUserId, currentEscalation);
                }
            } catch (Exception e) {
                LOGGER.warn("No auth user found for escalation level {} and fofoId {}, trying next level", currentEscalation, fofoId);
            }
            currentEscalation = currentEscalation.next();
        }
        // If no user found at any level, return 0 with the target escalation
        LOGGER.warn("No auth user found for any escalation level for categoryId {} and fofoId {}", categoryId, fofoId);
        return new EscalationAssignmentResult(0, targetEscalation);
    }

    /**
     * Gets auth user ID with fallback (for backward compatibility)
     */
    private int getAuthUserIdWithFallback(int categoryId, EscalationType targetEscalation, int fofoId) throws ProfitMandiBusinessException {
        return getAuthUserWithFallback(categoryId, targetEscalation, fofoId).getAuthUserId();
    }

    @Override
    public TicketActivityMedia createMediaActivity(ActivityMediaModel selectedRecording) {
        TicketActivityMedia activity = new TicketActivityMedia();
        activity.setMobile(selectedRecording.getMobile());
        // add a prefix endpoint of mediaUrl
        // String customValue = "https://ccs1.smartpingcc.io/v2/recording/direct/71287091";
        activity.setMediaUrl(selectedRecording.getMediaUrl());
        activity.setDurationInSec(selectedRecording.getDurationInSec());
        activity.setCallTime(selectedRecording.getCallConnectTime());
        activity.setCreatedBy(0);
        activity.setCreateTimestamp(LocalDateTime.now());
        activityMediaRepository.persist(activity);
        return activity;
    }

    @Override
    public void addMediaActivity(Ticket ticket, TicketActivityMedia activity) {
        activity.setActivityId(ticket.getLastActivityId());
    }

    @Override
    public void createTicketRemarkEscalation(int fofoId, int authId, int categoryId, int subcategoryId, String message, CollectionRemark remark) throws ProfitMandiBusinessException {

        ActivityType type = ActivityType.OPENED;
        Ticket ticket = new Ticket();
        ticket.setSubCategoryId(subcategoryId);
        ticket.setFofoId(fofoId);
        ticket.setCreateTimestamp(LocalDateTime.now());
        ticket.setUpdateTimestamp(LocalDateTime.now());
        ticket.setHappyCode(getRandomString());
        ticketRepository.persist(ticket);
        this.addActivity(ticket, this.createActivity(type, String.valueOf(remark), authId));
        ticket.setFirstActivityId(ticket.getLastActivityId());
        this.assignTicketByAuthId(ticket, authId, EscalationType.L2);

    }


    @Override
    public void assignTicket(Ticket ticket) throws ProfitMandiBusinessException {
        this.assignTicket(ticket,0);
    }

    @Override
    public void assignTicket(Ticket ticket,int currentUserId) throws ProfitMandiBusinessException {
        TicketAssigned ticketAssigned = null;
        EscalationType newEscalationType = EscalationType.L1;
        if (ticket.getAssignmentId() > 0) {
            ticketAssigned = ticketAssignedRepository.selectById(ticket.getAssignmentId());
            newEscalationType = ticketAssigned.getEscalationType().next();
            if (newEscalationType == null || newEscalationType.equals(EscalationType.Final)) {
                LOGGER.info("Cant escalate further");
                return;
            }
        } else {
            ticket.setL1AuthUser(0);
            ticket.setL2AuthUser(0);
            ticket.setL3AuthUser(0);
            ticket.setL4AuthUser(0);
            ticket.setL5AuthUser(0);
        }
        ticket.setUpdateTimestamp(LocalDateTime.now());


        TicketSubCategory ticketSubCategory = ticketSubCategoryRepository.selectById(ticket.getSubCategoryId());
        TicketCategory ticketCategory = ticketCategoryRepository.selectById(ticketSubCategory.getCategoryId());
        Map<Integer, EscalationType> escalationUserMap = new HashedMap<>();
        Set<Integer> assigneeUserIds = new LinkedHashSet<>();
        EscalationType escalationTypeNext = newEscalationType;
        do {
            int assigneeUserId = this.getAuthUserId(ticketCategory.getId(), escalationTypeNext, ticket.getFofoId());
            if (assigneeUserId == 0) {
                escalationTypeNext = escalationTypeNext.next();
                LOGGER.info("Escalation type next {}", escalationTypeNext);
                continue;
            }
            assigneeUserIds.add(assigneeUserId);
            escalationUserMap.put(assigneeUserId, escalationTypeNext);
            switch (escalationTypeNext) {
                case L1:
                    ticket.setL1AuthUser(assigneeUserId);
                    break;
                case L2:
                    ticket.setL2AuthUser(assigneeUserId);
                    break;
                case L3:
                    ticket.setL3AuthUser(assigneeUserId);
                    break;
                case L4:
                    ticket.setL4AuthUser(assigneeUserId);
                    break;
                case L5:
                    ticket.setL5AuthUser(assigneeUserId);
                    break;

                default:
                    break;
            }
            escalationTypeNext = escalationTypeNext.next();
            // LOGGER.info("Escalation type next {}", escalationTypeNext);
        } while (!escalationTypeNext.equals(EscalationType.Final));

        int assigneeAuthId = assigneeUserIds.stream().findFirst().orElse(0);

        int managerAuthId = 0;
        EscalationType assigneeEscalationType = null;
        if (assigneeAuthId == 0) {
            assigneeAuthId = ProfitMandiConstants.FINAL_AUTH_ID;
            assigneeEscalationType = EscalationType.Final;
            managerAuthId = ProfitMandiConstants.FINAL_AUTH_ID;
        } else {
            assigneeEscalationType = escalationUserMap.get(assigneeAuthId);
            managerAuthId = assigneeUserIds.stream().skip(1).findFirst().orElse(0);
            if (managerAuthId == 0) {
                managerAuthId = ProfitMandiConstants.FINAL_AUTH_ID;
            }
        }

        TicketAssigned ticketAssignedNew = new TicketAssigned();
        ticketAssignedNew.setTicketId(ticket.getId());
        ticketAssignedNew.setAssineeId(assigneeAuthId);
        ticketAssignedNew.setEscalationType(assigneeEscalationType);
        ticketAssignedNew.setManagerId(managerAuthId);
        ticketAssignedNew.setAssignedBy(currentUserId);
        ticketAssignedNew.setCreateTimestamp(LocalDateTime.now());
        ticketAssignedRepository.persist(ticketAssignedNew);
        LOGGER.info(ticketAssigned);

        AuthUser authUser = authRepository.selectById(ticketAssignedNew.getAssineeId());
        AuthUser authUserManager = null;

        // Dont send cc mail if both are same
        if (managerAuthId != assigneeAuthId) {
            authUserManager = authRepository.selectById(managerAuthId);
        }
        ticket.setAssignmentId(ticketAssignedNew.getId());

        List<Activity> activities = activityRepository.selectAll(ticket.getId());
        this.sendAssignedTicketMail(authUser, Arrays.asList(authUserManager), ticket, ticketAssigned != null, activities);
        if (!ticketAssignedNew.getEscalationType().equals(EscalationType.L1)) {
            this.addActivity(ticket, this.createActivity(ActivityType.ESCALATED, "Ticket ecalated and assigned to " + authUser.getName(), currentUserId));
        } else {
            this.addActivity(ticket, this.createActivity(ActivityType.ASSIGNED, "Ticket assigned to " + authUser.getName(), currentUserId));
        }
    }

    public void updateTicket(int categoryId, int subCategoryId, Ticket ticket, int authUserId, EscalationType escalationType) throws ProfitMandiBusinessException {
        this.updateTicket(categoryId, subCategoryId, ticket, authUserId, escalationType, null,0);
    }

    @Override
    public void updateTicket(int categoryId, int subCategoryId, Ticket ticket, int authUserId, EscalationType escalationType, ActivityMediaModel recording,int currentUserId) throws ProfitMandiBusinessException {
        LOGGER.info("subCategoryId {}",subCategoryId);
        TicketSubCategory ticketSubCategory = ticketSubCategoryRepository.getTicketSubCategoryMap().get(subCategoryId);
        if (ticketSubCategory == null) {
            throw new ProfitMandiBusinessException("Could not find subCategoryId " + subCategoryId + "- " + categoryId, "Problem", "Problem");
        }
        String storeName = retailerService.getAllFofoRetailers().get(ticket.getFofoId()).getBusinessName();
        List<TicketAssigned> oldTicketAssignedList = ticketAssignedRepository.selectByTicketIds(Arrays.asList(ticket.getId()));
        for (TicketAssigned oldTicketAssigned : oldTicketAssignedList) {
            ticketAssignedRepository.delete(oldTicketAssigned);
        }

        if (ticket.getSubCategoryId() != ticketSubCategory.getId()) {
            ticket.setSubCategoryId(ticketSubCategory.getId());
            List<Integer> oldAssignedAuthUserIds = oldTicketAssignedList.stream().map(x -> x.getAssineeId()).collect(Collectors.toList());
            List<String> oldAssignedEmailIds = authRepository.selectByIds(oldAssignedAuthUserIds).stream().map(x -> x.getEmailId()).collect(Collectors.toList());
            String mailTo = oldAssignedEmailIds.remove(0);
            String message = String.format(CATEGORY_CHANGED_TICKET, ticket.getId(), storeName, ticketSubCategory.getTicketCategory().getName() + " - " + ticketSubCategory.getName());
            try {
                mailOutboxService.queueMail(mailTo, oldAssignedEmailIds.toArray(new String[0]), "Ticket Category/Subcategory Changed", message, "CsServiceImpl.updateTicket");
            } catch (Exception e) {
                LOGGER.info("Could not send mail for ticket {}", ticket.toString());
            }

            Activity categoryChangedActivity = this.createActivity(ActivityType.CATEGORY_CHANGED, "Category changed to " + ticketSubCategory.getTicketCategory().getName() + " - " + ticketSubCategory.getName(), 0);
            this.addActivity(ticket, categoryChangedActivity);
            if (recording != null) {
                this.addMediaActivity(ticket, this.createMediaActivity(recording));
            }


        }

        if (authUserId > 0) {
            LOGGER.info("authUserId--1111444 {}",authUserId);
            this.assignTicketByAuthId(ticket, authUserId, escalationType);

        } else {
            LOGGER.info("authUserId--11113355 {}",authUserId);
            ticket.setAssignmentId(0);
            this.assignTicket(ticket,currentUserId);
        }

    }

    private void assignTicketByAuthId(Ticket ticket, int authUserId, EscalationType escalationType) throws ProfitMandiBusinessException {

        AuthUser authUser = authRepository.selectById(authUserId);

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

        int managerAuthId = authUser.getManagerId();

        TicketSubCategory ticketSubCategory = ticketSubCategoryRepository.selectById(ticket.getSubCategoryId());
        TicketCategory ticketCategory = ticketCategoryRepository.selectById(ticketSubCategory.getCategoryId());

        int authUserL2 = this.getAuthUserId(ticketCategory.getId(), EscalationType.L2, ticket.getFofoId());
        int authUserL3 = this.getAuthUserId(ticketCategory.getId(), EscalationType.L3, ticket.getFofoId());
        int authUserL4 = this.getAuthUserId(ticketCategory.getId(), EscalationType.L4, ticket.getFofoId());
        int authUserL5 = this.getAuthUserId(ticketCategory.getId(), EscalationType.L5, ticket.getFofoId());

        if (escalationType.equals(EscalationType.L1)) {
            ticket.setL1AuthUser(authUserId);
            ticket.setL2AuthUser(authUserL2);
            ticket.setL3AuthUser(authUserL3);
            ticket.setL4AuthUser(authUserL4);
            ticket.setL5AuthUser(authUserL5);
        } else if (escalationType.equals(EscalationType.L2)) {
            ticket.setL2AuthUser(authUserId);
            ticket.setL3AuthUser(authUserL3);
            ticket.setL4AuthUser(authUserL4);
            ticket.setL5AuthUser(authUserL5);
        }
        if (escalationType.equals(EscalationType.L3)) {
            ticket.setL3AuthUser(authUserId);
            ticket.setL4AuthUser(authUserL4);
            ticket.setL5AuthUser(authUserL5);
        }
        if (escalationType.equals(EscalationType.L4)) {

            ticket.setL4AuthUser(authUserId);
            ticket.setL5AuthUser(authUserL5);
        }
        if (escalationType.equals(EscalationType.L5)) {
            ticket.setL5AuthUser(authUserId);
        }


        TicketAssigned ticketAssignedNew = new TicketAssigned();
        ticketAssignedNew.setTicketId(ticket.getId());
        ticketAssignedNew.setAssineeId(authUserId);
        ticketAssignedNew.setEscalationType(escalationType);
        ticketAssignedNew.setManagerId(managerAuthId);
        ticketAssignedNew.setCreateTimestamp(LocalDateTime.now());
        ticketAssignedRepository.persist(ticketAssignedNew);

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


        AuthUser authUserManager = null;


        // Dont send cc mail if both are same
        if (managerAuthId != authUserId) {
            authUserManager = authRepository.selectById(managerAuthId);
        }
        ticket.setAssignmentId(ticketAssignedNew.getId());


        List<Activity> activities = activityRepository.selectAll(ticket.getId());

        this.sendAssignedTicketMail(authUser, Arrays.asList(authUserManager), ticket, false, activities);
        if (!ticketAssignedNew.getEscalationType().equals(EscalationType.L1)) {
            this.addActivity(ticket, this.createActivity(ActivityType.ESCALATED, "Ticket ecalated and assigned to " + authUser.getName(), 0));
        } else {
            this.addActivity(ticket, this.createActivity(ActivityType.ASSIGNED, "Ticket assigned to " + authUser.getName(), 0));
        }

    }

    @Override
    public Activity createActivity(ActivityType activityType, String message, int createdBy) {
        Activity activity = new Activity();
        activity.setMessage(message);
        activity.setCreatedBy(createdBy);
        activity.setType(activityType);
        activity.setCreateTimestamp(LocalDateTime.now());
        activityRepository.persist(activity);
        return activity;
    }

    @Override
    public int getAuthUserId(int categoryId, EscalationType escalationType, int fofoId) throws ProfitMandiBusinessException {
        int authUserId = 0;
        List<Position> positions = positionRepository.selectPositionbyCategoryIdAndEscalationType(categoryId, escalationType);
        List<Integer> regionIds = partnerRegionRepository.selectByfofoId(fofoId).stream().map(x -> x.getRegionId()).collect(Collectors.toList());
        // Add all Partner regions id
        regionIds.add(ALL_PARTNERS_REGION);
        LOGGER.info("Escalation Type {}, Region Ids {}", escalationType, regionIds);
        List<Integer> partnerPositionsIds = partnerPositionRepository.selectByRegionIdAndPartnerId(regionIds, Arrays.asList(0, fofoId)).stream().map(x -> x.getPositionId()).collect(Collectors.toList());

        List<Position> positionAssignee = positions.stream().filter(x -> x.isTicketAssignee() && partnerPositionsIds.contains(x.getId())).collect(Collectors.toList());
        LOGGER.info("User List List {}", positions.stream().map(x -> x.getAuthUserId()).collect(Collectors.toList()));
        LOGGER.info("positionAssignee List {}", positionAssignee);
        if (positionAssignee.size() > 0) {

            List<AuthUser> authUsers = authRepository.selectByIds(
                    positionAssignee.stream().map(x -> x.getAuthUserId()).collect(Collectors.toList()));
            authUsers = authUsers.stream().filter(x -> x.isActive()).collect(Collectors.toList());
            LOGGER.info("Auth User List {}", authUsers);
            if (authUsers.size() > 0) {
                authUserId = authUsers.get(0).getId();
                /*
                 * Random rand = new Random(); authUserId =
                 * authUsers.get(rand.nextInt(authUsers.size())).getId();
                 */
            }
        }
        return authUserId;

    }

    @Override
    public int getAuthUserIdWithoutTicketAssignee(int categoryId, EscalationType escalationType, int fofoId) throws ProfitMandiBusinessException {
        int authUserId = 0;
        List<Position> positions = positionRepository.selectPositionbyCategoryIdAndEscalationType(categoryId, escalationType);
        List<Integer> regionIds = partnerRegionRepository.selectByfofoId(fofoId).stream().map(x -> x.getRegionId()).collect(Collectors.toList());
        // Add all Partner regions id
        regionIds.add(ALL_PARTNERS_REGION);
        LOGGER.info("Escalation Type {}, Region Ids {}", escalationType, regionIds);
        List<Integer> partnerPositionsIds = partnerPositionRepository.selectByRegionIdAndPartnerId(regionIds, Arrays.asList(0, fofoId)).stream().map(x -> x.getPositionId()).collect(Collectors.toList());

        List<Position> positionAssignee = positions.stream().filter(x -> partnerPositionsIds.contains(x.getId())).collect(Collectors.toList());
        LOGGER.info("User List List {}", positions.stream().map(x -> x.getAuthUserId()).collect(Collectors.toList()));
        LOGGER.info("positionAssignee List {}", positionAssignee);
        if (positionAssignee.size() > 0) {

            List<AuthUser> authUsers = authRepository.selectByIds(
                    positionAssignee.stream().map(x -> x.getAuthUserId()).collect(Collectors.toList()));
            authUsers = authUsers.stream().filter(x -> x.isActive()).collect(Collectors.toList());
            LOGGER.info("Auth User List {}", authUsers);
            if (authUsers.size() > 0) {
                authUserId = authUsers.get(0).getId();
                /*
                 * Random rand = new Random(); authUserId =
                 * authUsers.get(rand.nextInt(authUsers.size())).getId();
                 */
            }
        }
        return authUserId;

    }


    @Override
    public void addActivity(Ticket ticket, Activity activity) {
        activity.setTicketId(ticket.getId());
        ticket.setLastActivity(activity.getType());
        ticket.setLastActivityId(activity.getId());
    }

    private String getRandomString() {
        int length = 4;
        boolean useLetters = false;
        boolean useNumbers = true;
        String generatedString = RandomStringUtils.random(length, useLetters, useNumbers);
        return generatedString;
    }

    @Override
    public void addPartnerToRegion(int regionId, List<Integer> fofoIds) {

        for (int fofoId : fofoIds) {
            PartnerRegion partnerRegion = new PartnerRegion();
            partnerRegion.setFofoId(fofoId);
            partnerRegion.setRegionId(regionId);
            partnerRegionRepository.persist(partnerRegion);
        }
    }

    @Override
    public Map<Integer, AuthUser> getAuthUserIdAndAuthUserMap(List<TicketAssigned> ticketAssigneds) {
        if (ticketAssigneds == null || ticketAssigneds.isEmpty()) {
            return new HashMap<>();
        }
        // OPTIMIZED: Batch fetch all AuthUsers instead of N+1 queries
        List<Integer> assigneeIds = ticketAssigneds.stream()
                .map(TicketAssigned::getAssineeId)
                .distinct()
                .collect(Collectors.toList());
        Map<Integer, AuthUser> authUserById = authRepository.selectByIds(assigneeIds).stream()
                .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

        Map<Integer, AuthUser> authUserIdAndAuthUserMap = new HashMap<>();
        for (TicketAssigned ticketAssigned : ticketAssigneds) {
            authUserIdAndAuthUserMap.put(ticketAssigned.getTicketId(), authUserById.get(ticketAssigned.getAssineeId()));
        }
        return authUserIdAndAuthUserMap;
    }

    @Override
    public Map<Integer, AuthUser> getTicketIdAndAuthUserMapUsingTickets(List<Ticket> tickets) {
        if (tickets == null || tickets.isEmpty()) {
            return new HashMap<>();
        }
        // OPTIMIZED: Batch fetch all AuthUsers instead of N+1 queries
        List<Integer> authUserIds = tickets.stream()
                .map(this::getEffectiveAuthUserId)
                .filter(id -> id != null && id > 0)
                .distinct()
                .collect(Collectors.toList());
        Map<Integer, AuthUser> authUserById = authRepository.selectByIds(authUserIds).stream()
                .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

        Map<Integer, AuthUser> authUserIdAndAuthUserMap = new HashMap<>();
        for (Ticket ticket : tickets) {
            authUserIdAndAuthUserMap.put(ticket.getId(), authUserById.get(ticket.getL1AuthUser()));
        }
        return authUserIdAndAuthUserMap;
    }

    private Integer getEffectiveAuthUserId(Ticket ticket) {

        if (ticket.getL1AuthUser() > 0) {
            return ticket.getL1AuthUser();
        }
        if (ticket.getL2AuthUser() > 0) {
            return ticket.getL2AuthUser();
        }
        if (ticket.getL3AuthUser() > 0) {
            return ticket.getL3AuthUser();
        }
        if (ticket.getL4AuthUser() > 0) {
            return ticket.getL4AuthUser();
        }
        return null;
    }


    @Override
    public Map<Integer, TicketSubCategory> getSubCategoryIdAndSubCategoryMap(List<Integer> subCategoryIds) {
        Map<Integer, TicketSubCategory> subCategoryIdAndSubCategoryMap = new HashMap<>();
        subCategoryIdAndSubCategoryMap = ticketSubCategoryRepository.selectByIds(subCategoryIds).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        return subCategoryIdAndSubCategoryMap;
    }

    @Override
    public Map<Integer, CustomRetailer> getPartnerByFofoIds(List<Ticket> tickets) throws ProfitMandiBusinessException {
        List<Integer> fofoIds = new ArrayList<>();
        LOGGER.info(tickets);
        if (tickets == null) {
            return null;
        }
        for (Ticket ticket : tickets) {
            fofoIds.add(ticket.getFofoId());
        }

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

        Map<Integer, CustomRetailer> fofoIdsAndCustomRetailer = fofoIds.stream().distinct().map(x -> customRetailerMap.get(x)).filter(x -> x != null).collect(Collectors.toList()).stream().collect(Collectors.toMap(x -> x.getPartnerId(), x -> x));
        return fofoIdsAndCustomRetailer;
    }

    @Override
    public List<TicketCategory> getAllTicketCategotyFromSubCategory() {

        Collection<TicketSubCategory> ticketSubCategories = ticketSubCategoryRepository.getTicketSubCategoryMap().values();
        return new ArrayList<>(ticketSubCategories.stream().map(x -> x.getTicketCategory()).collect(Collectors.toSet()));
    }

    @Override
    public Map<Integer, AuthUser> getAuthUserIdAndAuthUserMapUsingPositions(List<Position> positions) {
        if (positions == null || positions.isEmpty()) {
            return new HashMap<>();
        }
        // OPTIMIZED: Batch fetch all AuthUsers instead of N+1 queries
        List<Integer> authUserIds = positions.stream()
                .map(Position::getAuthUserId)
                .filter(id -> id != null && id > 0)
                .distinct()
                .collect(Collectors.toList());
        return authRepository.selectByIds(authUserIds).stream()
                .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));
    }

    @Override
    public Map<Integer, TicketCategory> getCategoryIdAndCategoryUsingPositions(List<Position> positions) {
        if (positions == null || positions.isEmpty()) {
            return new HashMap<>();
        }
        // OPTIMIZED: Batch fetch all categories instead of N+1 queries
        List<Integer> categoryIds = positions.stream()
                .map(Position::getCategoryId)
                .filter(id -> id != null && id > 0)
                .distinct()
                .collect(Collectors.toList());
        return ticketCategoryRepository.selectAll(categoryIds).stream()
                .collect(Collectors.toMap(TicketCategory::getId, tc -> tc, (a, b) -> a));
    }

    @Override
    public Map<Integer, Region> getRegionIdAndRegionMap(List<Position> positions) {
        if (positions == null || positions.isEmpty()) {
            return new HashMap<>();
        }
        // OPTIMIZED: Batch fetch all regions instead of N+1 queries
        Set<Integer> regionIds = positions.stream()
                .map(Position::getRegionId)
                .filter(id -> id != null && id > 0)
                .collect(Collectors.toSet());
        return regionRepository.selectAll().stream()
                .filter(r -> regionIds.contains(r.getId()))
                .collect(Collectors.toMap(Region::getId, r -> r, (a, b) -> a));
    }

    private void sendAssignedTicketMail(AuthUser authUserTo, List<AuthUser> authUsersCc, Ticket ticket, boolean isEscalated, List<Activity> activities) throws ProfitMandiBusinessException {
        try {
            String[] ccTo = authUsersCc.stream().filter(x -> x != null).map(x -> x.getEmailId()).toArray(String[]::new);

            String messageFormat = null;
            if (isEscalated) {
                messageFormat = ESCALATED_TICKET;
            } else {
                messageFormat = ASSIGNED_TICKET;
            }
            String message = String.format(messageFormat, authUserTo.getName(), ticket.getId(), activities.get(0).getMessage(), retailerService.getFofoRetailer(ticket.getFofoId()).getBusinessName());
            mailOutboxService.queueMail(authUserTo.getEmailId(), ccTo, ASSINMENT_SUBJECT, message, "CsServiceImpl.sendAssignedTicketMail");
        } catch (Exception e) {
            e.printStackTrace();
            throw new ProfitMandiBusinessException("Ticket Assingment", authUserTo.getEmailId(), "Could not send ticket assignment mail");
        }
    }

    @Override
    public Map<Integer, List<CustomRetailer>> getPositionCustomRetailerMap(List<Position> positions) throws ProfitMandiBusinessException {
        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();
        Map<Integer, List<CustomRetailer>> positionRetailerMap = new HashMap<>();

        // Batch fetch all PartnerPositions upfront to avoid N+1 queries
        List<Integer> positionIds = positions.stream().map(Position::getId).collect(Collectors.toList());
        Map<Integer, List<Integer>> positionIdToFofoIdsMap = partnerPositionRepository.selectByPositionIds(positionIds)
                .stream()
                .collect(Collectors.groupingBy(
                        pp -> pp.getPositionId(),
                        Collectors.mapping(pp -> pp.getFofoId(), Collectors.toList())
                ));

        // Collect all region IDs that might need lookup (positions with fofoId=0)
        Set<Integer> regionIdsNeedingLookup = new HashSet<>();
        for (Position position : positions) {
            List<Integer> fofoIds = positionIdToFofoIdsMap.get(position.getId());
            if (fofoIds != null && fofoIds.contains(0)) {
                regionIdsNeedingLookup.add(position.getRegionId());
            }
        }

        // Batch fetch all PartnerRegions for regions needing lookup
        Map<Integer, List<Integer>> regionIdToFofoIdsMap = new HashMap<>();
        if (!regionIdsNeedingLookup.isEmpty()) {
            regionIdToFofoIdsMap = partnerRegionRepository.selectAllByRegionIds(new ArrayList<>(regionIdsNeedingLookup))
                    .stream()
                    .collect(Collectors.groupingBy(
                            pr -> pr.getRegionId(),
                            Collectors.mapping(pr -> pr.getFofoId(), Collectors.toList())
                    ));
        }

        // Build the result map using pre-fetched data
        for (Position position : positions) {
            List<Integer> fofoIds = positionIdToFofoIdsMap.get(position.getId());
            if (fofoIds == null || fofoIds.isEmpty()) {
                continue;
            }

            if (fofoIds.contains(0)) {
                // Need region-based lookup
                fofoIds = regionIdToFofoIdsMap.get(position.getRegionId());
                if (fofoIds == null || fofoIds.contains(0)) {
                    fofoIds = new ArrayList<>(customRetailerMap.keySet());
                }
            }

            positionRetailerMap.put(position.getId(),
                    fofoIds.stream()
                            .map(customRetailerMap::get)
                            .filter(x -> x != null)
                            .collect(Collectors.toList()));
        }
        return positionRetailerMap;
    }

    @Override
    public Map<Integer, List<CustomRetailer>> getRegionPartners(List<Position> positions) throws ProfitMandiBusinessException {
        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();
        Map<Integer, List<CustomRetailer>> positionIdAndpartnerRegionMap = new HashedMap<>();

        // Batch fetch all PartnerRegions upfront to avoid N+1 queries
        List<Integer> regionIds = positions.stream().map(Position::getRegionId).distinct().collect(Collectors.toList());
        Map<Integer, List<Integer>> regionIdToFofoIdsMap = partnerRegionRepository.selectAllByRegionIds(regionIds)
                .stream()
                .collect(Collectors.groupingBy(
                        pr -> pr.getRegionId(),
                        Collectors.mapping(pr -> pr.getFofoId(), Collectors.toList())
                ));

        // Fetch all store IDs once in case any region has fofoId=0
        List<Integer> allStoreIds = null;

        for (Position position : positions) {
            List<Integer> fofoIds = regionIdToFofoIdsMap.get(position.getRegionId());
            if (fofoIds == null || fofoIds.isEmpty()) {
                continue;
            }

            if (fofoIds.contains(0)) {
                if (allStoreIds == null) {
                    allStoreIds = fofoStoreRepository.selectAll().stream().map(x -> x.getId()).collect(Collectors.toList());
                }
                fofoIds = allStoreIds;
            }

            Map<Integer, CustomRetailer> fofoRetailers = fofoIds.stream()
                    .map(customRetailerMap::get)
                    .filter(x -> x != null)
                    .collect(Collectors.toMap(x -> x.getPartnerId(), x -> x, (a, b) -> a));
            positionIdAndpartnerRegionMap.put(position.getRegionId(), new ArrayList<>(fofoRetailers.values()));
        }

        return positionIdAndpartnerRegionMap;
    }

    @Override
    @Cacheable(value = "authUserEmailMapping", cacheManager = "thirtyMinsTimeOutCacheManager")
    public Map<String, Set<String>> getAuthUserPartnerEmailMapping() throws ProfitMandiBusinessException {
        Map<String, Set<String>> storeGuyMap = new HashMap<>();
        Set<Integer> activeFofoIds = fofoStoreRepository.selectActiveStores().stream().map(x -> x.getId()).collect(Collectors.toSet());

        // Batch fetch positions by category IDs instead of multiple calls
        List<Position> categoryPositions = positionRepository.selectPositionByCategoryIds(Arrays.asList(
                ProfitMandiConstants.TICKET_CATEGORY_SALES,
                ProfitMandiConstants.TICKET_CATEGORY_RBM,
                ProfitMandiConstants.TICKET_CATEGORY_ABM));
        Map<Integer, Position> positionsMap = categoryPositions.stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        // Batch fetch all AuthUsers upfront to avoid N+1 queries
        Set<Integer> authUserIds = categoryPositions.stream().map(Position::getAuthUserId).collect(Collectors.toSet());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(new ArrayList<>(authUserIds)).stream()
                .collect(Collectors.toMap(AuthUser::getId, x -> x));

        Map<Integer, List<CustomRetailer>> positonPartnerMap = this.getPositionCustomRetailerMap(categoryPositions);
        for (Map.Entry<Integer, List<CustomRetailer>> positionPartnerEntry : positonPartnerMap.entrySet()) {
            int authUserId = positionsMap.get(positionPartnerEntry.getKey()).getAuthUserId();
            Set<String> partnerEmails = positionPartnerEntry.getValue().stream().filter(x -> activeFofoIds.contains(x.getPartnerId())).map(x -> x.getEmail()).collect(Collectors.toSet());
            AuthUser authUser = authUserMap.get(authUserId);
            if (authUser != null && authUser.isActive()) {
                if (!storeGuyMap.containsKey(authUser.getEmailId())) {
                    storeGuyMap.put(authUser.getEmailId(), partnerEmails);
                } else {
                    storeGuyMap.get(authUser.getEmailId()).addAll(partnerEmails);
                }
            }
        }
        return storeGuyMap;
    }

    @Override
    @Cacheable(value = "authUserEmailPartnerIdMapping", cacheManager = "thirtyMinsTimeOutCacheManager")
    public Map<String, Set<Integer>> getAuthUserPartnerIdMapping() throws ProfitMandiBusinessException {
        Map<String, Set<Integer>> storeGuyMap = new HashMap<>();
        Set<Integer> activeFofoIds = fofoStoreRepository.selectActiveStores().stream().map(x -> x.getId()).collect(Collectors.toSet());

        // Batch fetch positions by category IDs instead of multiple calls
        List<Position> categoryPositions = positionRepository.selectPositionByCategoryIds(Arrays.asList(
                ProfitMandiConstants.TICKET_CATEGORY_SALES,
                ProfitMandiConstants.TICKET_CATEGORY_RBM,
                ProfitMandiConstants.TICKET_CATEGORY_ABM,
                ProfitMandiConstants.TICKET_CATEGORY_BUSINESSINTELLIGENT,
                ProfitMandiConstants.TICKET_CATEGORY_TRAINING));
        Map<Integer, Position> positionsMap = categoryPositions.stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        // Batch fetch all AuthUsers upfront to avoid N+1 queries
        Set<Integer> authUserIds = categoryPositions.stream().map(Position::getAuthUserId).collect(Collectors.toSet());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(new ArrayList<>(authUserIds)).stream()
                .collect(Collectors.toMap(AuthUser::getId, x -> x));

        Map<Integer, List<CustomRetailer>> positonPartnerMap = this.getPositionCustomRetailerMap(categoryPositions);
        for (Map.Entry<Integer, List<CustomRetailer>> positionPartnerEntry : positonPartnerMap.entrySet()) {
            int authUserId = positionsMap.get(positionPartnerEntry.getKey()).getAuthUserId();
            Set<Integer> partnerIds = positionPartnerEntry.getValue().stream().filter(x -> activeFofoIds.contains(x.getPartnerId())).map(x -> x.getPartnerId()).collect(Collectors.toSet());
            AuthUser authUser = authUserMap.get(authUserId);
            if (authUser != null && authUser.isActive()) {
                if (!storeGuyMap.containsKey(authUser.getEmailId())) {
                    storeGuyMap.put(authUser.getEmailId(), partnerIds);
                } else {
                    storeGuyMap.get(authUser.getEmailId()).addAll(partnerIds);
                }
            }
        }
        return storeGuyMap;
    }

    @Override
    @Cacheable(value = "authUserEmailInactivePartnerIdMapping", cacheManager = "thirtyMinsTimeOutCacheManager")
    public Map<String, Set<Integer>> getAuthUserInactivePartnerIdMapping() throws ProfitMandiBusinessException {
        Map<String, Set<Integer>> storeGuyMap = new HashMap<>();
        Set<Integer> activeFofoIds = fofoStoreRepository.selectInActiveStore().stream().map(x -> x.getId()).collect(Collectors.toSet());

        // Batch fetch positions by category IDs instead of multiple calls
        List<Position> categoryPositions = positionRepository.selectPositionByCategoryIds(Arrays.asList(
                ProfitMandiConstants.TICKET_CATEGORY_SALES,
                ProfitMandiConstants.TICKET_CATEGORY_RBM,
                ProfitMandiConstants.TICKET_CATEGORY_ABM,
                ProfitMandiConstants.TICKET_CATEGORY_BUSINESSINTELLIGENT,
                ProfitMandiConstants.TICKET_CATEGORY_TRAINING));
        Map<Integer, Position> positionsMap = categoryPositions.stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        // Batch fetch all AuthUsers upfront to avoid N+1 queries
        Set<Integer> authUserIds = categoryPositions.stream().map(Position::getAuthUserId).collect(Collectors.toSet());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(new ArrayList<>(authUserIds)).stream()
                .collect(Collectors.toMap(AuthUser::getId, x -> x));

        Map<Integer, List<CustomRetailer>> positonPartnerMap = this.getPositionCustomRetailerMap(categoryPositions);
        for (Map.Entry<Integer, List<CustomRetailer>> positionPartnerEntry : positonPartnerMap.entrySet()) {
            int authUserId = positionsMap.get(positionPartnerEntry.getKey()).getAuthUserId();
            Set<Integer> partnerIds = positionPartnerEntry.getValue().stream().filter(x -> activeFofoIds.contains(x.getPartnerId())).map(x -> x.getPartnerId()).collect(Collectors.toSet());
            AuthUser authUser = authUserMap.get(authUserId);
            if (authUser != null && authUser.isActive()) {
                if (!storeGuyMap.containsKey(authUser.getEmailId())) {
                    storeGuyMap.put(authUser.getEmailId(), partnerIds);
                } else {
                    storeGuyMap.get(authUser.getEmailId()).addAll(partnerIds);
                }
            }
        }
        return storeGuyMap;
    }

    @Override
    @Cacheable(value = "authUserPartnerEmailPartnerIdMapping", cacheManager = "thirtyMinsTimeOutCacheManager")
    public Map<String, Set<Integer>> getAuthUserPartnerIdMappingByCategoryIds(List<Integer> categoryIds, boolean activeOnly) throws ProfitMandiBusinessException {
        Map<String, Set<Integer>> authUserPartnerMap = new HashMap<>();
        Set<Integer> activeFofoIds;
        if (activeOnly) {
            activeFofoIds = fofoStoreRepository.selectActiveStores().stream().map(x -> x.getId()).collect(Collectors.toSet());

        } else {
            activeFofoIds = fofoStoreRepository.selectAll().stream().map(x -> x.getId()).collect(Collectors.toSet());

        }

        List<Position> categoryPositions = positionRepository.selectPositionByCategoryIds(categoryIds);
        Map<Integer, Position> positionsMap = categoryPositions.stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        // OPTIMIZED: Batch fetch all AuthUsers instead of N+1 queries
        List<Integer> authUserIds = categoryPositions.stream()
                .map(Position::getAuthUserId)
                .filter(id -> id != null && id > 0)
                .distinct()
                .collect(Collectors.toList());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(authUserIds).stream()
                .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

        Map<Integer, List<CustomRetailer>> positonPartnerMap = this.getPositionCustomRetailerMap(categoryPositions);
        for (Map.Entry<Integer, List<CustomRetailer>> positionPartnerEntry : positonPartnerMap.entrySet()) {
            int authUserId = positionsMap.get(positionPartnerEntry.getKey()).getAuthUserId();
            Set<Integer> partnerIds = positionPartnerEntry.getValue().stream().filter(x -> activeFofoIds.contains(x.getPartnerId())).map(x -> x.getPartnerId()).collect(Collectors.toSet());
            AuthUser authUser = authUserMap.get(authUserId);
            if (authUser != null && authUser.isActive()) {
                if (!authUserPartnerMap.containsKey(authUser.getEmailId())) {
                    authUserPartnerMap.put(authUser.getEmailId(), partnerIds);
                } else {
                    authUserPartnerMap.get(authUser.getEmailId()).addAll(partnerIds);
                }
            }
        }
        return authUserPartnerMap;
    }

    @Override
    public Map<Integer, List<Integer>> authUserpartnerIdMap(int authId, int categoryId) {
        Session session = sessionFactory.getCurrentSession();

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

        List<Position> authPositions = positionRepository.selectPositionbyCategoryIdAndAuthId(categoryId, authId);

        // OPTIMIZED: Batch fetch all PartnerPositions instead of N+1 queries
        List<Integer> positionIds = authPositions.stream()
                .map(Position::getId)
                .collect(Collectors.toList());
        List<PartnerPosition> partnerPositions = partnerPositionRepository.selectByPositionIds(positionIds);

        if (partnerPositions.stream().anyMatch(partnerPosition -> partnerPosition.getFofoId() == 0)) {
            List<FofoStore> activeFofoStores = fofoStoreRepository.selectActiveStores();

            List<Integer> fofoStoreIds = activeFofoStores.stream()
                    .map(FofoStore::getId) // Extract the IDs of FofoStores
                    .collect(Collectors.toList());

            authUserpartnerIdMap.put(authId, fofoStoreIds);

        } else {
            final TypedQuery<AuthUserPartnerMapModel> typedQuerySimilar = session
                    .createNamedQuery("Position.Auth_User_Partner_Maping", AuthUserPartnerMapModel.class);

            typedQuerySimilar.setParameter("authId", authId);
            typedQuerySimilar.setParameter("categoryId", categoryId);

            List<AuthUserPartnerMapModel> authUserPartnerMapModels = typedQuerySimilar.getResultList();

            authUserpartnerIdMap = authUserPartnerMapModels.stream()
                    .collect(Collectors.groupingBy(
                            AuthUserPartnerMapModel::getAuthId,
                            Collectors.mapping(AuthUserPartnerMapModel::getFofoId, Collectors.toList())
                    ));

        }

        LOGGER.info("partnerusermap {}", authUserpartnerIdMap);
        return authUserpartnerIdMap;
    }

    @Override
    public List<String> getAuthUserByPartnerId(int fofoId) throws ProfitMandiBusinessException {

        List<String> emails = new ArrayList<>();
        List<Integer> regionIds = partnerRegionRepository.selectByfofoId(fofoId).stream().map(x -> x.getRegionId()).collect(Collectors.toList());

        regionIds.add(5);// All partners Id;

        List<Integer> partnerPositionIds = partnerPositionRepository.selectByRegionIdAndPartnerId(regionIds, Arrays.asList(fofoId, 0)).stream().map(x -> x.getPositionId()).collect(Collectors.toList());

        LOGGER.info("partnerPositionIds" + partnerPositionIds);

        List<Position> positions = positionRepository.selectByIds(partnerPositionIds);

        positions = positions.stream().filter(x -> (x.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_SALES || x.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_RBM || x.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_ABM)).collect(Collectors.toList());

        if (!positions.isEmpty()) {
            // OPTIMIZED: Batch fetch all AuthUsers instead of N+1 queries
            List<Integer> authUserIds = positions.stream()
                    .map(Position::getAuthUserId)
                    .filter(id -> id != null && id > 0)
                    .distinct()
                    .collect(Collectors.toList());
            Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(authUserIds).stream()
                    .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

            for (Position partnerPostionId : positions) {
                AuthUser authUser = authUserMap.get(partnerPostionId.getAuthUserId());
                LOGGER.info("authUser" + authUser);
                if (authUser != null) {
                    emails.add(authUser.getEmailId());
                }
            }
        }

        return emails;
    }

    @Override
    public Map<EscalationType, String> getAuthUserAndEsclationTypeByPartnerId(int fofoId) throws ProfitMandiBusinessException {

        List<String> emails = new ArrayList<>();
        Map<EscalationType, String> emailEsclationTypeMap = new HashMap<>();
        List<Integer> regionIds = partnerRegionRepository.selectByfofoId(fofoId).stream().map(x -> x.getRegionId()).collect(Collectors.toList());

        regionIds.add(5);// All partners Id;
        List<Integer> fofoIds = new ArrayList<>();
        fofoIds.add(fofoId);
        fofoIds.add(0);

        //LOGGER.info("fofoIds" + fofoIds);
        List<Integer> partnerPositionIds = partnerPositionRepository.selectByRegionIdAndPartnerId(regionIds, fofoIds).stream().map(x -> x.getPositionId()).collect(Collectors.toList());

        //LOGGER.info("partnerPositionIds" + partnerPositionIds);

        // OPTIMIZED: Batch fetch all positions instead of N+1 queries
        List<Position> positions = positionRepository.selectByIds(partnerPositionIds).stream()
                .filter(p -> p.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_SALES)
                .collect(Collectors.toList());

        if (!positions.isEmpty()) {
            // OPTIMIZED: Batch fetch all AuthUsers instead of N+1 queries
            List<Integer> authUserIds = positions.stream()
                    .map(Position::getAuthUserId)
                    .filter(id -> id != null && id > 0)
                    .distinct()
                    .collect(Collectors.toList());
            Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(authUserIds).stream()
                    .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

            for (Position position : positions) {
                AuthUser authUser = authUserMap.get(position.getAuthUserId());
                //LOGGER.info("authUser" + authUser);
                if (authUser != null) {
                    emailEsclationTypeMap.put(position.getEscalationType(), authUser.getEmailId());
                }
            }
        }

        //LOGGER.info("emailEsclationTypeMap" + emailEsclationTypeMap);

        return emailEsclationTypeMap;
    }

    @Override
    @Cacheable(value = "authUserIdPartnerIdMapping", cacheManager = "thirtyMinsTimeOutCacheManager")
    public Map<Integer, List<Integer>> getAuthUserIdPartnerIdMapping() throws ProfitMandiBusinessException {
        Map<Integer, List<Integer>> storeGuyMap = new HashMap<>();
        Set<Integer> activeFofoIds = fofoStoreRepository.selectActiveStores().stream().map(x -> x.getId()).collect(Collectors.toSet());

        List<Position> categoryPositions = positionRepository.selectAllPosition();

        Map<Integer, Position> positionsMap = categoryPositions.stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

        // OPTIMIZED: Batch fetch all AuthUsers instead of N+1 queries
        List<Integer> authUserIds = categoryPositions.stream()
                .map(Position::getAuthUserId)
                .filter(id -> id != null && id > 0)
                .distinct()
                .collect(Collectors.toList());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(authUserIds).stream()
                .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

        Map<Integer, List<CustomRetailer>> positonPartnerMap = this.getPositionCustomRetailerMap(categoryPositions);
        for (Map.Entry<Integer, List<CustomRetailer>> positionPartnerEntry : positonPartnerMap.entrySet()) {
            int authUserId = positionsMap.get(positionPartnerEntry.getKey()).getAuthUserId();
            List<Integer> partnerIds = positionPartnerEntry.getValue().stream().filter(x -> activeFofoIds.contains(x.getPartnerId())).map(x -> x.getPartnerId()).collect(Collectors.toList());
            AuthUser authUser = authUserMap.get(authUserId);
            if (authUser != null && authUser.isActive()) {
                if (!storeGuyMap.containsKey(authUserId)) {
                    storeGuyMap.put(authUserId, partnerIds);
                } else {
                    storeGuyMap.get(authUserId).addAll(partnerIds);
                }
            }
        }
        storeGuyMap.keySet().stream().forEach(x -> {
            List<Integer> fofoIds = storeGuyMap.get(x);
            storeGuyMap.put(x, fofoIds.stream().distinct().collect(Collectors.toList()));
        });
        return storeGuyMap;
    }

    @Override
    @Cacheable(value = "L1L2Mapping", cacheManager = "thirtyMinsTimeOutCacheManager")
    public Map<Integer, List<Integer>> getL2L1Mapping() throws ProfitMandiBusinessException {
        List<Position> l1SalesPositions = positionRepository.selectPositionbyCategoryIdAndEscalationType(
                ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L1);
        List<Position> l2SalesPositions = positionRepository.selectPositionbyCategoryIdAndEscalationType(
                ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L2);

        //LOGGER.info("position" + l1SalesPositions);
        Map<Integer, List<Integer>> authUserPartnerListMap = this.getAuthUserIdPartnerIdMapping();
        //LOGGER.info("map" + authUserPartnerListMap);

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

        for (Position l2SalePosition : l2SalesPositions) {
            int l2AuthUserId = l2SalePosition.getAuthUserId();
            if (authUserPartnerListMap.containsKey(l2SalePosition.getAuthUserId())) {
                List<Integer> mappedL1AuthUsers = new ArrayList<>();
                l2l1ListMap.put(l2AuthUserId, mappedL1AuthUsers);
                List<Integer> l2FofoIds = authUserPartnerListMap.get(l2AuthUserId);
                for (Position l1SalePosition : l1SalesPositions) {
                    int l1AuthUserId = l1SalePosition.getAuthUserId();
                    if (authUserPartnerListMap.containsKey(l1AuthUserId)) {
                        List<Integer> l1FofoIds = authUserPartnerListMap.get(l1SalePosition.getAuthUserId());
                        if (l2FofoIds.containsAll(l1FofoIds)) {
                            mappedL1AuthUsers.add(l1AuthUserId);
                        }
                    }

                }
            }

        }

        return l2l1ListMap;
    }

    @Override
    public Map<Integer, List<AuthUser>> getAssignedAuthList(List<Ticket> tickets) {
        if (tickets.size() == 0) {
            return new HashMap<>();
        }
        List<TicketAssigned> ticketAssignedList = ticketAssignedRepository.selectByTicketIds(tickets.stream().map(x -> x.getId()).collect(Collectors.toList()));
        List<Integer> authUserIds = ticketAssignedList.stream().map(x -> x.getAssineeId()).distinct().collect(Collectors.toList());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(authUserIds).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        return ticketAssignedList.stream().collect(Collectors.groupingBy(x -> x.getTicketId(), Collectors.mapping(x -> authUserMap.get(x.getAssineeId()), Collectors.toList())));
    }

    @Override
    public Map<EscalationType, AuthUser> getAuthUserAndEsclationByPartnerId(int fofoId) throws ProfitMandiBusinessException {
        Map<EscalationType, AuthUser> authuserEsclationTypeMap = new HashMap<>();
        List<Integer> regionIds = partnerRegionRepository.selectByfofoId(fofoId).stream().map(x -> x.getRegionId()).collect(Collectors.toList());

        regionIds.add(ALL_PARTNERS_REGION);// All partners Id;
        List<Integer> fofoIds = new ArrayList<>();
        fofoIds.add(fofoId);
        fofoIds.add(0);

        List<Integer> partnerPositionIds = partnerPositionRepository.selectByRegionIdAndPartnerId(regionIds, fofoIds).stream().map(x -> x.getPositionId()).collect(Collectors.toList());

        if (partnerPositionIds.isEmpty()) {
            return authuserEsclationTypeMap;
        }

        // OPTIMIZED: Batch fetch all positions instead of N+1 queries
        List<Position> salesPositions = positionRepository.selectByIds(partnerPositionIds).stream()
                .filter(p -> p.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_SALES)
                .collect(Collectors.toList());

        if (salesPositions.isEmpty()) {
            return authuserEsclationTypeMap;
        }

        // OPTIMIZED: Batch fetch all AuthUsers instead of N+1 queries
        List<Integer> authUserIds = salesPositions.stream()
                .map(Position::getAuthUserId)
                .filter(id -> id != null && id > 0)
                .distinct()
                .collect(Collectors.toList());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(authUserIds).stream()
                .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

        for (Position position : salesPositions) {
            AuthUser authUser = authUserMap.get(position.getAuthUserId());
            if (authUser != null) {
                authuserEsclationTypeMap.put(position.getEscalationType(), authUser);
            }
        }

        return authuserEsclationTypeMap;
    }

    @Override
    public Map<Integer, Map<EscalationType, AuthUser>> getAuthUserAndEsclationByPartnerIds(Set<Integer> fofoIds) throws ProfitMandiBusinessException {
        Map<Integer, Map<EscalationType, AuthUser>> result = new HashMap<>();

        if (fofoIds == null || fofoIds.isEmpty()) {
            return result;
        }

        // Initialize result map
        for (Integer fofoId : fofoIds) {
            result.put(fofoId, new HashMap<>());
        }

        // Batch fetch all partner regions
        List<Integer> fofoIdListWithZero = new ArrayList<>(fofoIds);
        fofoIdListWithZero.add(0);
        List<PartnerRegion> allPartnerRegions = partnerRegionRepository.selectByfofoIds(fofoIdListWithZero);

        // Build fofoId -> regionIds map
        Map<Integer, Set<Integer>> fofoRegionMap = new HashMap<>();
        for (Integer fofoId : fofoIds) {
            fofoRegionMap.put(fofoId, new HashSet<>());
            fofoRegionMap.get(fofoId).add(ALL_PARTNERS_REGION);
        }
        for (PartnerRegion pr : allPartnerRegions) {
            int prFofoId = pr.getFofoId();
            if (prFofoId == 0) {
                for (Integer fid : fofoIds) {
                    fofoRegionMap.get(fid).add(pr.getRegionId());
                }
            } else if (fofoIds.contains(prFofoId)) {
                fofoRegionMap.get(prFofoId).add(pr.getRegionId());
            }
        }

        // Collect all region IDs
        Set<Integer> allRegionIds = fofoRegionMap.values().stream()
                .flatMap(Set::stream).collect(Collectors.toSet());

        // Batch fetch all partner positions
        List<PartnerPosition> allPartnerPositions = partnerPositionRepository.selectByRegionIdAndPartnerId(
                new ArrayList<>(allRegionIds), fofoIdListWithZero);

        // Collect all position IDs
        Set<Integer> allPositionIds = allPartnerPositions.stream()
                .map(PartnerPosition::getPositionId).collect(Collectors.toSet());

        // Batch fetch all positions for SALES category
        List<Position> salesPositions = positionRepository.selectPositionbyCategoryIdAndEscalationType(
                ProfitMandiConstants.TICKET_CATEGORY_SALES, null);
        Map<Integer, Position> positionMap = salesPositions.stream()
                .filter(p -> allPositionIds.contains(p.getId()))
                .collect(Collectors.toMap(Position::getId, p -> p, (a, b) -> a));

        // Collect all auth user IDs and batch fetch
        Set<Integer> authUserIds = positionMap.values().stream()
                .map(Position::getAuthUserId).collect(Collectors.toSet());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(new ArrayList<>(authUserIds))
                .stream().collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

        // For each fofoId, find matching positions and build result
        for (Integer fofoId : fofoIds) {
            Set<Integer> regionIds = fofoRegionMap.get(fofoId);
            Map<EscalationType, AuthUser> escalationMap = result.get(fofoId);

            for (PartnerPosition pp : allPartnerPositions) {
                if (regionIds.contains(pp.getRegionId()) &&
                    (pp.getFofoId() == 0 || pp.getFofoId() == fofoId)) {
                    Position position = positionMap.get(pp.getPositionId());
                    if (position != null) {
                        AuthUser authUser = authUserMap.get(position.getAuthUserId());
                        if (authUser != null) {
                            escalationMap.put(position.getEscalationType(), authUser);
                        }
                    }
                }
            }
        }

        return result;
    }

    @Override
    public List<AuthUser> getAuthUserIdByPartnerId(int fofoId) throws ProfitMandiBusinessException {
        return this.getAuthUserIdByPartnerId(fofoId, EscalationType.escalations.toArray(new EscalationType[0]));
    }

    @Override
    public List<AuthUser> getAuthUserIdByPartnerId(int fofoId, EscalationType... escalationTypes) throws ProfitMandiBusinessException {
        List<AuthUser> authUsers = new ArrayList<>();
        List<Integer> regionIds = partnerRegionRepository.selectByfofoId(fofoId).stream().map(x -> x.getRegionId()).collect(Collectors.toList());

        regionIds.add(5);// All partners Id;
        List<Integer> fofoIds = new ArrayList<>();
        fofoIds.add(fofoId);
        fofoIds.add(0);

        //LOGGER.info("fofoIds" + fofoIds);
        List<Integer> partnerPositionIds = partnerPositionRepository.selectByRegionIdAndPartnerId(regionIds, fofoIds).stream().map(x -> x.getPositionId()).collect(Collectors.toList());

        //LOGGER.info("partnerPositionIds" + partnerPositionIds);
        List<Integer> allowedTicketCategoryIds = Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_SALES, ProfitMandiConstants.TICKET_CATEGORY_RBM,ProfitMandiConstants.TICKET_CATEGORY_ABM);


        List<Position> escalationBasedPositions = positionRepository.selectByIds(partnerPositionIds).stream().filter(x -> allowedTicketCategoryIds.contains(x.getCategoryId()) &&
                Arrays.stream(escalationTypes)
                        .anyMatch(y -> y.equals(x.getEscalationType()))).collect(Collectors.toList());

        List<Integer> authUserIds = escalationBasedPositions.stream().map(x -> x.getAuthUserId()).collect(Collectors.toList());

        return authRepository.selectByIds(authUserIds);

    }

    @Override
    public List<AuthUser> getAuthUserIds(int categoryId, List<EscalationType> escalationType) {

        List<Position> positions = positionRepository.selectPositionbyCategoryIdAndEscalationTypes(categoryId, escalationType);
        List<Integer> authIds = positions.stream().map(x -> x.getAuthUserId()).distinct().collect(Collectors.toList());

        //LOGGER.info("authIds" + authIds);
        List<AuthUser> authUsers = null;
        if (authIds.size() > 0) {
            authUsers = authRepository.selectByIds(authIds);
        } else {
            authUsers = new ArrayList<>();
        }

        LOGGER.info("authUsers" + authUsers);
        return authUsers;
    }

    @Override
    public List<AuthUser> getAuthUserByCategoryId(int categoryId, EscalationType escalationType) {

        List<Position> positions = positionRepository.selectPositionbyCategoryIdAndEscalationType(categoryId, escalationType);
        List<Integer> authIds = positions.stream().map(x -> x.getAuthUserId()).distinct().collect(Collectors.toList());

        LOGGER.info("authIds" + authIds);
        List<AuthUser> authUsers = new ArrayList<>();
        if (!authIds.isEmpty()) {
            authUsers = authRepository.selectByIds(authIds);
        }

        LOGGER.info("authUsers" + authUsers);
        return authUsers;
    }

    @Override
    @Cacheable(value = "authUserByCategoryId", cacheManager = "thirtyMinsTimeOutCacheManager")
    public List<AuthUser> getAuthUserByCategoryId(int categoryId) {
        List<Position> positions = positionRepository.selectPositionByCategoryId(categoryId);
        List<Integer> authIds = positions.stream().map(x -> x.getAuthUserId()).distinct().collect(Collectors.toList());

        LOGGER.info("authIds" + authIds);

        List<AuthUser> authUsers = authRepository.selectByIds(authIds).stream().filter(x -> x.getActive()).collect(Collectors.toList());

        LOGGER.info("authUsers" + authUsers);
        return authUsers;
    }

    private class SaleRoles1 {

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

        public SaleRoles1() {
            l1 = new ArrayList<>();
            l2 = new ArrayList<>();
            l3 = new ArrayList<>();
            l4 = new ArrayList<>();
        }

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

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

        public List<String> getL3() {
            return l3;
        }

        public List<String> getL4() {
            return l4;
        }

        @Override
        public String toString() {
            return "SaleRoles [l1=" + l1 + ", l2=" + l2 + ", l3=" + l3 + ", l4=" + l4 + "]";
        }

    }


    @Override
    @Cacheable(value = "partnerSaleHeader", cacheManager = "oneDayCacheManager")
    public Map<Integer, FofoReportingModel> getPartnerIdSalesHeaders() throws ProfitMandiBusinessException {
        Map<String, SaleRoles1> partnerEmailSalesMap = new HashMap<>();

        List<Position> positions = positionRepository.selectPositionByCategoryId(ProfitMandiConstants.TICKET_CATEGORY_SALES);
        Map<Integer, AuthUser> authUsersMap = authRepository.selectAllActiveUser().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        Map<Integer, List<CustomRetailer>> positionIdRetailerMap = this.getPositionCustomRetailerMap(positions);
        for (Position position : positions) {
            List<CustomRetailer> crList = positionIdRetailerMap.get(position.getId());
            if (crList == null)
                continue;
            for (CustomRetailer cr : crList) {
                if (!partnerEmailSalesMap.containsKey(cr.getEmail())) {
                    partnerEmailSalesMap.put(cr.getEmail(), new SaleRoles1());
                }
                SaleRoles1 saleRoles1 = 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)) {
                    saleRoles1.getL1().add(name);
                } else if (position.getEscalationType().equals(EscalationType.L2)) {
                    saleRoles1.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<>();

        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();
        for (Map.Entry<Integer, CustomRetailer> customRetailerEntry : customRetailerMap.entrySet()) {

            CustomRetailer customRetailer = customRetailerEntry.getValue();

            String code = customRetailer.getCode();
            // String storeName = "SmartDukaan-" +
            // fofoStore.getCode().replaceAll("[a-zA-Z]", "");
            String businessName = customRetailer.getBusinessName();
            try {
                String stateManager = StringUtils.join(new HashSet(partnerEmailSalesMap.get(customRetailer.getEmail()).getL2()), ", ");
                String territoryManager = StringUtils.join(new HashSet(partnerEmailSalesMap.get(customRetailer.getEmail()).getL1()), ", ");

                FofoReportingModel reportingModel = new FofoReportingModel();
                reportingModel.setBusinessName(businessName);
                reportingModel.setCode(code);
                reportingModel.setFofoId(customRetailer.getPartnerId());
                reportingModel.setRegionalManager(stateManager);
                reportingModel.setTerritoryManager(territoryManager);
                partnerIdSalesHeadersMap.put(customRetailer.getPartnerId(), reportingModel);
            } catch (Exception e) {
                LOGGER.warn("Could not find partner with email - {}", customRetailer.getEmail());
            }
        }
        return partnerIdSalesHeadersMap;

    }


    private class ABMRoles {

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

        public ABMRoles() {
            l1 = new ArrayList<>();
            l2 = new ArrayList<>();
            l3 = new ArrayList<>();
            l4 = new ArrayList<>();
        }

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

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

        public List<String> getL3() {
            return l3;
        }

        public List<String> getL4() {
            return l4;
        }

        @Override
        public String toString() {
            return "SaleRoles [l1=" + l1 + ", l2=" + l2 + ", l3=" + l3 + ", l4=" + l4 + "]";
        }

    }


    @Override
    @Cacheable(value = "partnerABMHeader", cacheManager = "oneDayCacheManager")
    public Map<Integer, FofoAbmReportingModel> getPartnerIdABMHeaders() throws ProfitMandiBusinessException {
        Map<String, ABMRoles> partnerEmailABMMap = new HashMap<>();

        List<Position> positions = positionRepository.selectPositionByCategoryId(ProfitMandiConstants.TICKET_CATEGORY_ABM);
        Map<Integer, AuthUser> authUsersMap = authRepository.selectAllActiveUser().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        Map<Integer, List<CustomRetailer>> positionIdRetailerMap = this.getPositionCustomRetailerMap(positions);
        for (Position position : positions) {
            List<CustomRetailer> crList = positionIdRetailerMap.get(position.getId());
            if (crList == null)
                continue;
            for (CustomRetailer cr : crList) {
                if (!partnerEmailABMMap.containsKey(cr.getEmail())) {
                    partnerEmailABMMap.put(cr.getEmail(), new ABMRoles());
                }
                ABMRoles abmRoles = 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)) {
                    abmRoles.getL1().add(name);
                } else if (position.getEscalationType().equals(EscalationType.L2)) {
                    abmRoles.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, FofoAbmReportingModel> partnerIdAbmHeadersMap = new HashMap<>();

        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();
        for (Map.Entry<Integer, CustomRetailer> customRetailerEntry : customRetailerMap.entrySet()) {

            CustomRetailer customRetailer = customRetailerEntry.getValue();

            String code = customRetailer.getCode();
            // String storeName = "SmartDukaan-" +
            // fofoStore.getCode().replaceAll("[a-zA-Z]", "");
            String businessName = customRetailer.getBusinessName();
            try {
                String l2Manager = StringUtils.join(new HashSet(partnerEmailABMMap.get(customRetailer.getEmail()).getL2()), ", ");
                String l1Manager = StringUtils.join(new HashSet(partnerEmailABMMap.get(customRetailer.getEmail()).getL1()), ", ");

                if (StringUtils.isEmpty(l1Manager)) {
                    l2Manager = StringUtils.join(new HashSet(partnerEmailABMMap.get(customRetailer.getEmail()).getL2()), ", ");
                }

                FofoAbmReportingModel reportingModel = new FofoAbmReportingModel();
                reportingModel.setBusinessName(businessName);
                reportingModel.setCode(code);
                reportingModel.setFofoId(customRetailer.getPartnerId());
                reportingModel.setL1Manager(l2Manager);
                reportingModel.setL2Manager(l1Manager);
                partnerIdAbmHeadersMap.put(customRetailer.getPartnerId(), reportingModel);
            } catch (Exception e) {
                LOGGER.warn("Could not find partner with email - {}", customRetailer.getEmail());
            }
        }
        return partnerIdAbmHeadersMap;

    }


    private class RBMRoles {

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

        public RBMRoles() {
            l1 = new ArrayList<>();
            l2 = new ArrayList<>();
            l3 = new ArrayList<>();
            l4 = new ArrayList<>();
        }

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

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

        public List<String> getL3() {
            return l3;
        }

        public List<String> getL4() {
            return l4;
        }

        @Override
        public String toString() {
            return "RBMRoles [l1=" + l1 + ", l2=" + l2 + ", l3=" + l3 + ", l4=" + l4 + "]";
        }

    }

    @Override
    @Cacheable(value = "partnerRBMHeader", cacheManager = "oneDayCacheManager")
    public Map<Integer, FofoRBMReportingModel> getPartnerIdRBMHeaders() throws ProfitMandiBusinessException {
        Map<String, RBMRoles> partnerEmailRbmMap = new HashMap<>();

        List<Position> positions = positionRepository.selectPositionByCategoryId(ProfitMandiConstants.TICKET_CATEGORY_RBM);
        Map<Integer, AuthUser> authUsersMap = authRepository.selectAllActiveUser().stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
        Map<Integer, List<CustomRetailer>> positionIdRetailerMap = this.getPositionCustomRetailerMap(positions);
        for (Position position : positions) {
            List<CustomRetailer> crList = positionIdRetailerMap.get(position.getId());
            if (crList == null)
                continue;
            for (CustomRetailer cr : crList) {
                if (!partnerEmailRbmMap.containsKey(cr.getEmail())) {
                    partnerEmailRbmMap.put(cr.getEmail(), new RBMRoles());
                }
                RBMRoles rbmRole = 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)) {
                    rbmRole.getL1().add(name);
                } else if (position.getEscalationType().equals(EscalationType.L2)) {
                    rbmRole.getL2().add(name);
                }

            }
        }

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


        Map<Integer, FofoRBMReportingModel> partnerIdRbmHeadersMap = new HashMap<>();

        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();
        for (Map.Entry<Integer, CustomRetailer> customRetailerEntry : customRetailerMap.entrySet()) {

            CustomRetailer customRetailer = customRetailerEntry.getValue();

            String code = customRetailer.getCode();
            // String storeName = "SmartDukaan-" +
            // fofoStore.getCode().replaceAll("[a-zA-Z]", "");
            String businessName = customRetailer.getBusinessName();
            try {
                String L2User = StringUtils.join(partnerEmailRbmMap.get(customRetailer.getEmail()).getL2(), ", ");
                String L1USer = StringUtils.join(partnerEmailRbmMap.get(customRetailer.getEmail()).getL1(), ", ");

                FofoRBMReportingModel reportingModel = new FofoRBMReportingModel();
                reportingModel.setBusinessName(businessName);
                reportingModel.setCode(code);
                reportingModel.setFofoId(customRetailer.getPartnerId());
                reportingModel.setL1Manager(L1USer);
                reportingModel.setL2Manager(L2User);
                partnerIdRbmHeadersMap.put(customRetailer.getPartnerId(), reportingModel);
            } catch (Exception e) {
                LOGGER.warn("Could not find partner with email - {}", customRetailer.getEmail());
            }
        }
        return partnerIdRbmHeadersMap;

    }

    @Override
    public Map<Integer, TicketCategory> getSubCategoryIdAndCategoryMap(List<Integer> subCategoryIds) {
        Map<Integer, TicketCategory> subCategoryIdAndCategoryMap = new HashMap<>();
        if (subCategoryIds == null || subCategoryIds.isEmpty()) {
            return subCategoryIdAndCategoryMap;
        }

        List<TicketSubCategory> ticketSubCategories = ticketSubCategoryRepository.selectByIds(subCategoryIds);

        // OPTIMIZED: Batch fetch all categories instead of N+1 queries
        List<Integer> categoryIds = ticketSubCategories.stream()
                .map(TicketSubCategory::getCategoryId)
                .distinct()
                .collect(Collectors.toList());
        Map<Integer, TicketCategory> categoryMap = ticketCategoryRepository.selectAll(categoryIds).stream()
                .collect(Collectors.toMap(TicketCategory::getId, tc -> tc, (a, b) -> a));

        for (TicketSubCategory ticketSubCategory : ticketSubCategories) {
            subCategoryIdAndCategoryMap.put(ticketSubCategory.getId(), categoryMap.get(ticketSubCategory.getCategoryId()));
        }
        return subCategoryIdAndCategoryMap;
    }

    @Override
    public Map<Integer, Map<String, Integer>> getBmAsmRbmAuthUserIdsByFofoIds(Set<Integer> fofoIds) throws ProfitMandiBusinessException {
        Map<Integer, Map<String, Integer>> result = new HashMap<>();

        if (fofoIds == null || fofoIds.isEmpty()) {
            return result;
        }

        // Initialize result map with empty values for each fofoId
        for (Integer fofoId : fofoIds) {
            Map<String, Integer> roleMap = new HashMap<>();
            roleMap.put("BM", 0);
            roleMap.put("ASM", 0);
            roleMap.put("RBM", 0);
            result.put(fofoId, roleMap);
        }

        // Fetch positions by category + escalation type (same for all fofoIds)
        List<Position> bmPositions = positionRepository.selectPositionbyCategoryIdAndEscalationType(
                ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L2);
        List<Position> asmPositions = positionRepository.selectPositionbyCategoryIdAndEscalationType(
                ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L1);
        List<Position> rbmPositions = positionRepository.selectPositionbyCategoryIdAndEscalationType(
                ProfitMandiConstants.TICKET_CATEGORY_RBM, EscalationType.L1);

        // Batch fetch all partner regions for all fofoIds
        List<Integer> fofoIdList = new ArrayList<>(fofoIds);
        fofoIdList.add(0); // Include global entries
        List<PartnerRegion> allPartnerRegions = partnerRegionRepository.selectByfofoIds(fofoIdList);

        // Build fofoId -> regionIds map
        Map<Integer, Set<Integer>> fofoRegionMap = new HashMap<>();
        for (Integer fofoId : fofoIds) {
            fofoRegionMap.put(fofoId, new HashSet<>());
            fofoRegionMap.get(fofoId).add(ALL_PARTNERS_REGION); // Add all partners region
        }
        for (PartnerRegion pr : allPartnerRegions) {
            int fofoId = pr.getFofoId();
            if (fofoId == 0) {
                // Global entry applies to all fofoIds
                for (Integer fid : fofoIds) {
                    fofoRegionMap.get(fid).add(pr.getRegionId());
                }
            } else if (fofoIds.contains(fofoId)) {
                fofoRegionMap.get(fofoId).add(pr.getRegionId());
            }
        }

        // Collect all unique region IDs and position IDs for batch query
        Set<Integer> allRegionIds = fofoRegionMap.values().stream()
                .flatMap(Set::stream).collect(Collectors.toSet());
        Set<Integer> allPositionIds = new HashSet<>();
        bmPositions.forEach(p -> allPositionIds.add(p.getId()));
        asmPositions.forEach(p -> allPositionIds.add(p.getId()));
        rbmPositions.forEach(p -> allPositionIds.add(p.getId()));

        // Batch fetch all partner positions
        List<PartnerPosition> allPartnerPositions = partnerPositionRepository.selectByRegionIdAndPostionId(
                new ArrayList<>(allRegionIds), new ArrayList<>(allPositionIds));

        // Build positionId -> set of (regionId, partnerId) for matching
        Map<Integer, List<PartnerPosition>> positionIdToPartnerPositions = allPartnerPositions.stream()
                .collect(Collectors.groupingBy(PartnerPosition::getPositionId));

        // Collect all auth user IDs we need
        Set<Integer> allAuthUserIds = new HashSet<>();
        bmPositions.forEach(p -> allAuthUserIds.add(p.getAuthUserId()));
        asmPositions.forEach(p -> allAuthUserIds.add(p.getAuthUserId()));
        rbmPositions.forEach(p -> allAuthUserIds.add(p.getAuthUserId()));

        // Batch fetch all auth users
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(new ArrayList<>(allAuthUserIds))
                .stream().filter(AuthUser::isActive)
                .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

        // Build position ID sets for fast lookup
        Set<Integer> bmPositionIds = bmPositions.stream().map(Position::getId).collect(Collectors.toSet());
        Set<Integer> asmPositionIds = asmPositions.stream().map(Position::getId).collect(Collectors.toSet());
        Set<Integer> rbmPositionIds = rbmPositions.stream().map(Position::getId).collect(Collectors.toSet());

        // Map position ID to auth user ID
        Map<Integer, Integer> positionToAuthUser = new HashMap<>();
        bmPositions.forEach(p -> positionToAuthUser.put(p.getId(), p.getAuthUserId()));
        asmPositions.forEach(p -> positionToAuthUser.put(p.getId(), p.getAuthUserId()));
        rbmPositions.forEach(p -> positionToAuthUser.put(p.getId(), p.getAuthUserId()));

        // For each fofoId, find matching positions
        for (Integer fofoId : fofoIds) {
            Set<Integer> regionIds = fofoRegionMap.get(fofoId);

            // Find position IDs that match this fofoId's regions (partnerId = 0 or fofoId)
            Set<Integer> matchingPositionIds = new HashSet<>();
            for (PartnerPosition pp : allPartnerPositions) {
                if (regionIds.contains(pp.getRegionId()) &&
                    (pp.getFofoId() == 0 || pp.getFofoId() == fofoId)) {
                    matchingPositionIds.add(pp.getPositionId());
                }
            }

            // Find first active auth user for each role
            Map<String, Integer> roleMap = result.get(fofoId);

            for (Integer positionId : matchingPositionIds) {
                Integer authUserId = positionToAuthUser.get(positionId);
                if (authUserId != null && authUserMap.containsKey(authUserId)) {
                    if (bmPositionIds.contains(positionId) && roleMap.get("BM") == 0) {
                        roleMap.put("BM", authUserId);
                    } else if (asmPositionIds.contains(positionId) && roleMap.get("ASM") == 0) {
                        roleMap.put("ASM", authUserId);
                    } else if (rbmPositionIds.contains(positionId) && roleMap.get("RBM") == 0) {
                        roleMap.put("RBM", authUserId);
                    }
                }
            }
        }

        return result;
    }

    @Override
    public Map<Integer, Boolean> getUnreadStatusForTickets(List<Ticket> tickets, int userId, TicketReadStatus.UserType userType) {
        Map<Integer, Boolean> unreadMap = new HashMap<>();
        if (tickets == null || tickets.isEmpty()) {
            return unreadMap;
        }

        List<Integer> ticketIds = tickets.stream().map(Ticket::getId).collect(Collectors.toList());
        List<TicketReadStatus> readStatuses = ticketReadStatusRepository.selectByTicketIdsAndUser(ticketIds, userId, userType);
        Map<Integer, Integer> ticketLastReadMap = readStatuses.stream()
                .collect(Collectors.toMap(TicketReadStatus::getTicketId, TicketReadStatus::getLastReadActivityId));

        // Get last activities for all tickets to check who created them
        Map<Integer, Activity> lastActivityMap = getLastActivitiesForTickets(ticketIds);

        for (Ticket ticket : tickets) {
            int lastActivityId = ticket.getLastActivityId();
            int lastReadActivityId = ticketLastReadMap.getOrDefault(ticket.getId(), 0);

            if (lastActivityId <= lastReadActivityId) {
                unreadMap.put(ticket.getId(), false);
                continue;
            }

            // Check if the current user created the last activity (exclude own updates)
            Activity lastActivity = lastActivityMap.get(ticket.getId());
            if (lastActivity != null) {
                if (userType == TicketReadStatus.UserType.AUTH_USER && lastActivity.getCreatedBy() == userId) {
                    unreadMap.put(ticket.getId(), false);
                    continue;
                }
                if (userType == TicketReadStatus.UserType.PARTNER && lastActivity.getCreatedBy() == 0
                        && lastActivity.getType() == ActivityType.COMMUNICATION_IN) {
                    unreadMap.put(ticket.getId(), false);
                    continue;
                }
                // For partners: only show as unread if activity type is external communication or resolved
                if (userType == TicketReadStatus.UserType.PARTNER) {
                    boolean isRelevantForPartner = ActivityType.PARTNER_ACTIVITIES.contains(lastActivity.getType());
                    unreadMap.put(ticket.getId(), isRelevantForPartner);
                    continue;
                }
            }

            unreadMap.put(ticket.getId(), true);
        }
        return unreadMap;
    }

    @Override
    public void markTicketAsRead(int ticketId, int userId, TicketReadStatus.UserType userType) {
        Ticket ticket = ticketRepository.selectById(ticketId);
        if (ticket == null) {
            return;
        }
        TicketReadStatus readStatus = new TicketReadStatus();
        readStatus.setTicketId(ticketId);
        readStatus.setUserId(userId);
        readStatus.setUserType(userType);
        readStatus.setLastReadActivityId(ticket.getLastActivityId());
        ticketReadStatusRepository.upsert(readStatus);
    }

    @Override
    public Map<Integer, Activity> getLastActivitiesForTickets(List<Integer> ticketIds) {
        Map<Integer, Activity> lastActivityMap = new HashMap<>();
        if (ticketIds == null || ticketIds.isEmpty()) {
            return lastActivityMap;
        }
        List<Activity> allActivities = activityRepository.selectAll(ticketIds);
        // Group by ticketId and get the last one (highest id)
        Map<Integer, List<Activity>> groupedByTicket = allActivities.stream()
                .collect(Collectors.groupingBy(Activity::getTicketId));
        for (Map.Entry<Integer, List<Activity>> entry : groupedByTicket.entrySet()) {
            Activity lastActivity = entry.getValue().stream()
                    .max(Comparator.comparingInt(Activity::getId))
                    .orElse(null);
            if (lastActivity != null) {
                lastActivityMap.put(entry.getKey(), lastActivity);
            }
        }
        return lastActivityMap;
    }

    @Override
    public Map<Integer, Activity> getLastMessageActivitiesForTickets(List<Integer> ticketIds) {
        Map<Integer, Activity> lastMessageMap = new HashMap<>();
        if (ticketIds == null || ticketIds.isEmpty()) {
            return lastMessageMap;
        }
        Set<ActivityType> messageTypes = EnumSet.of(
                ActivityType.COMMUNICATION_IN, ActivityType.COMMUNICATION_OUT,
                ActivityType.COMMUNICATION_INTERNAL, ActivityType.OPENED);
        List<Activity> allActivities = activityRepository.selectAll(ticketIds);
        Map<Integer, List<Activity>> groupedByTicket = allActivities.stream()
                .filter(a -> messageTypes.contains(a.getType()))
                .collect(Collectors.groupingBy(Activity::getTicketId));
        for (Map.Entry<Integer, List<Activity>> entry : groupedByTicket.entrySet()) {
            Activity lastMessage = entry.getValue().stream()
                    .max(Comparator.comparingInt(Activity::getId))
                    .orElse(null);
            if (lastMessage != null) {
                lastMessageMap.put(entry.getKey(), lastMessage);
            }
        }
        return lastMessageMap;
    }

    @Override
    public void notifyPartnerOfExternalCommunication(Ticket ticket, Activity activity) {
        try {
            CustomRetailer partner = retailerService.getFofoRetailer(ticket.getFofoId());
            if (partner == null || partner.getEmail() == null) {
                return;
            }

            TicketSubCategory ticketSubCategory = ticketSubCategoryRepository.selectById(ticket.getSubCategoryId());
            TicketCategory ticketCategory = ticketCategoryRepository.selectById(ticketSubCategory.getCategoryId());

            Map<String, Object> model = new HashMap<>();
            model.put("ticketId", ticket.getId());
            model.put("partnerName", partner.getBusinessName());
            model.put("categoryName", ticketCategory.getName());
            model.put("subCategoryName", ticketSubCategory.getName());
            model.put("activityMessage", activity.getMessage());

            emailService.sendMailWithAttachments(
                    "Update on Your Support Ticket #" + ticket.getId(),
                    "partner-ticket-update.vm",
                    model,
                    new String[]{partner.getEmail()},
                    null,
                    null);
        } catch (Exception e) {
            LOGGER.error("Failed to send partner notification email for ticket {}", ticket.getId(), e);
        }
    }

}