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());

        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;

            }

        }

        ticket.setUpdateTimestamp(LocalDateTime.now());

        TicketSubCategory ticketSubCategory = ticketSubCategoryRepository.selectById(ticket.getSubCategoryId());

        TicketCategory ticketCategory = ticketCategoryRepository.selectById(ticketSubCategory.getCategoryId());

        // One-step escalation: pick the current-level assignee (with fallback to next level if

        // the current level has no one configured), then pick one more level up as the manager.

        // History is preserved in ticket_assigned; Ticket only carries current state.

        EscalationAssignmentResult assigneeResult =

                this.getAuthUserWithFallback(ticketCategory.getId(), newEscalationType, ticket.getFofoId());

        int assigneeAuthId;

        EscalationType assigneeEscalationType;

        int managerAuthId;

        if (assigneeResult.getAuthUserId() == 0) {

            assigneeAuthId = ProfitMandiConstants.FINAL_AUTH_ID;

            assigneeEscalationType = EscalationType.Final;

            managerAuthId = ProfitMandiConstants.FINAL_AUTH_ID;

        } else {

            assigneeAuthId = assigneeResult.getAuthUserId();

            assigneeEscalationType = assigneeResult.getActualEscalationType();

            EscalationType managerLevel = assigneeEscalationType.next();

            if (managerLevel == null || managerLevel.equals(EscalationType.Final)) {

                managerAuthId = ProfitMandiConstants.FINAL_AUTH_ID;

            } else {

                EscalationAssignmentResult managerResult =

                        this.getAuthUserWithFallback(ticketCategory.getId(), managerLevel, ticket.getFofoId());

                managerAuthId = managerResult.getAuthUserId() > 0

                        ? managerResult.getAuthUserId()

                        : ProfitMandiConstants.FINAL_AUTH_ID;

            }

        }

        ticket.setCurrentAssigneeId(assigneeAuthId);

        ticket.setCurrentManagerId(managerAuthId);

        ticket.setCurrentEscalationLevel(assigneeEscalationType);

        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));

        }

    }

    @Override

    public boolean isInEscalationChain(int ticketId, int authId) {

        if (ticketId <= 0 || authId <= 0) {

            return false;

        }

        return ticketAssignedRepository.selectByAssigneeIdAndTicketId(authId, ticketId) != null;

    }

    @Override

    public Set<Integer> ticketsInEscalationChain(int authId, List<Integer> ticketIds) {

        if (authId <= 0 || ticketIds == null || ticketIds.isEmpty()) {

            return Collections.emptySet();

        }

        return ticketAssignedRepository.selectByTicketIds(ticketIds).stream()

                .filter(ta -> ta.getAssineeId() == authId)

                .map(TicketAssigned::getTicketId)

                .collect(Collectors.toSet());

    }

    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();

        // One-step model: the manual assignment records the current assignee and the

        // configured next-level manager. Downstream escalations still walk one level per

        // cron cycle in assignTicket(). History remains in ticket_assigned.

        int nextLevelManagerId = 0;

        if (escalationType != null) {

            EscalationType nextLevel = escalationType.next();

            if (nextLevel != null && !nextLevel.equals(EscalationType.Final)) {

                TicketSubCategory ticketSubCategory = ticketSubCategoryRepository.selectById(ticket.getSubCategoryId());

                TicketCategory ticketCategory = ticketCategoryRepository.selectById(ticketSubCategory.getCategoryId());

                EscalationAssignmentResult mgr =

                        this.getAuthUserWithFallback(ticketCategory.getId(), nextLevel, ticket.getFofoId());

                nextLevelManagerId = mgr.getAuthUserId();

            }

        }

        if (nextLevelManagerId <= 0) {

            nextLevelManagerId = managerAuthId > 0 ? managerAuthId : ProfitMandiConstants.FINAL_AUTH_ID;

        }

        ticket.setCurrentAssigneeId(authUserId);

        ticket.setCurrentManagerId(nextLevelManagerId);

        ticket.setCurrentEscalationLevel(escalationType);

        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<>();

        }

        // Batch fetch AuthUsers by current_assignee_id (one owner per ticket in the new model).

        List<Integer> authUserIds = tickets.stream()

                .map(Ticket::getCurrentAssigneeId)

                .filter(id -> 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.getCurrentAssigneeId()));

        }

        return authUserIdAndAuthUserMap;

    }

    @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<>();