import { Injectable, HttpException, HttpStatus, Res } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, Like, Not } from 'typeorm';
import { classToPlain, Type } from 'class-transformer';
import { responseMessages } from '../../messages/response-messages';
import { emailMessages } from '../../messages/email-messages';
import { Constants } from '../../common/constants';
import { FileManagerRepository } from './file-manager.repository';
import { CommonService } from 'src/modules/common/common.services';
import * as ExcelJS from 'exceljs';
import * as validator from 'validator';

// Entities Files
import { UserRoles, Users } from "../../entities/users.entity";
import { FileManagers } from "../../entities/file-managers.entity";
import { FileLogs } from '../../entities/file-logs.entity';
import { SocialMedias } from '../../entities/social-medias.entity';
import { InstagramProfiles } from '../../entities/instagram-profile.entity';
import { FacebookPages } from '../../entities/facebook-pages.entity';
import { YoutubeChannels } from '../../entities/youtube-channel.entity';
import { TikTokProfiles } from '../../entities/tiktok-profile.entity';
import { XiaohongshuProfiles } from '../../entities/xiaohongshu-profile.entity';
import { Categories } from '../../entities/categories.entity';
import { CategoryTranslation } from '../../entities/category-translations.entity';
import { InfluencerCategory } from '../../entities/influencer-caregories.entity';
import { Countries } from '../../entities/countries.entity';

// DTO Files
import { FileManagerListQueryDto } from './dtos/file-manager-list-query.dto';
import { ErrorLogListQueryDto } from './dtos/error-log-list-query.dto';
import { ImportUsersDto } from './dtos/import-users.dto';
import { last } from 'rxjs';
import { Settings } from 'src/entities/settings.entity';
import { InfluencerRegisterRepository } from '../influencer-register/influencer-register.repository';
import { Keywords } from 'src/entities/keywords.entity';
import { Industries } from 'src/entities/industries.entity';
import { ApifyServices } from 'src/common/services/apify/apify.service';
import { YoutubeServices } from 'src/common/services/youtube/youtube.service';
const moment = require('moment');

@Injectable()
export class FileManagerService {

    constructor(
        private fileManagerRepository: FileManagerRepository,
        private commonService: CommonService,
        private influencerRegisterRepository: InfluencerRegisterRepository,
        private apifyService: ApifyServices,
        private youtubeServices: YoutubeServices,
        @InjectRepository(Users)
        private Users: Repository<Users>,

        @InjectRepository(FileManagers)
        private FileManagers: Repository<FileManagers>,

        @InjectRepository(FileLogs)
        private FileLogs: Repository<FileLogs>,

        @InjectRepository(SocialMedias)
        private SocialMedias: Repository<SocialMedias>,

        @InjectRepository(InstagramProfiles)
        private InstagramProfiles: Repository<InstagramProfiles>,

        @InjectRepository(FacebookPages)
        private FacebookPages: Repository<FacebookPages>,

        @InjectRepository(YoutubeChannels)
        private YoutubeChannels: Repository<YoutubeChannels>,

        @InjectRepository(TikTokProfiles)
        private TikTokProfiles: Repository<TikTokProfiles>,

        @InjectRepository(XiaohongshuProfiles)
        private XiaohongshuProfiles: Repository<XiaohongshuProfiles>,

        @InjectRepository(Categories)
        private Categories: Repository<Categories>,

        @InjectRepository(CategoryTranslation)
        private CategoryTranslation: Repository<CategoryTranslation>,

        @InjectRepository(InfluencerCategory)
        private InfluencerCategory: Repository<InfluencerCategory>,

        @InjectRepository(Countries)
        private Countries: Repository<Countries>,

        @InjectRepository(Settings)
        private Settings: Repository<Settings>,



    ) { }

    /**
     * Retrieves a list of file managers on query parameters.
     * @param queryParam - An instance of FileManagerListQueryDto containing.
     * @param response - The response object for sending the HTTP response.
     * @param req - The request object, containing the authenticated user information.
     * @returns 200 status with file manager list data is successful.
     * In case of an error, logs the error and responds with the respective error status and message.
    */
    async fileMangerList(queryParam: FileManagerListQueryDto, authUser: any) {
        const page = queryParam.page - 1;
        const pageSize = queryParam.per_page ? queryParam.per_page : Constants.file_manager_list;
        const offset = page * pageSize;
        const nextPage = page + 1;
        const nextOffset = nextPage * pageSize;

        let sortByArr = {
            created_at: 'file_managers.created_at',
            status: 'file_managers.status',
            total_row_count: 'file_managers.total_row_count',
            success_row_count: 'file_managers.success_row_count',
            failed_row_count: 'file_managers.failed_row_count',
            file_name: 'file_managers.file_name',
        };

        let sort_by = queryParam.sort_by || 'created_at';
        let sort_type = queryParam.sort || 'DESC';
        sort_by = sortByArr[sort_by];

        let query = await this.FileManagers
            .createQueryBuilder('file_managers')
            .where('file_managers.deleted_at is NULL')
            // .andWhere('(industries.is_active = :is_active)', { is_active: true })
            // .andWhere('(bops.users_id = :users_id)', { users_id : req_user_id })
            .select(['file_managers.id', 'file_managers.file_name', 'file_managers.extension', 'file_managers.source_from', 'file_managers.file_path', 'file_managers.total_row_count', 'file_managers.success_row_count', 'file_managers.failed_row_count', 'file_managers.status', 'file_managers.created_at'])
            .orderBy(sort_by, sort_type as 'ASC' | 'DESC')

        if (queryParam.search) {
            query = query.andWhere(`(file_managers.file_path LIKE :search)`, { search: `%${queryParam.search}%` });
        }

        const [results, total] = await query.skip(offset).take(pageSize).getManyAndCount();

        return {
            data: results,
            total: total,
            hasMoreResults: (total > nextOffset) ? 1 : 0,
            file_path: process.env.AWS_FILE_PATH,
        }
    }

    /**
     * Retrieves a list of error logs from file manager id on query parameters.
     * @param queryParam - An instance of ErrorLogListQueryDto containing.
     * @param response - The response object for sending the HTTP response.
     * @param req - The request object, containing the authenticated user information.
     * @throws HttpException - Throws a 404 error if the file manager does not exist.
     * @returns 200 status with error log list data is successful.
     * In case of an error, logs the error and responds with the respective error status and message.
    */
    async errorLogList(queryParam: ErrorLogListQueryDto, authUser: any) {
        const lang = authUser.language || 'en';
        const fileManager = await this.FileManagers.findOne({ where: { id: queryParam.file_manager_id } });
        if (!fileManager) {
            throw new HttpException(responseMessages[lang].file_manager.file_manager_not_exists, HttpStatus.NOT_FOUND);
        }

        const page = queryParam.page - 1;
        const pageSize = queryParam.per_page ? queryParam.per_page : Constants.error_log_list;
        const offset = page * pageSize;
        const nextPage = page + 1;
        const nextOffset = nextPage * pageSize;

        let sortByArr = {
            created_at: 'file_logs.created_at',
            row_number: 'file_logs.row_number',
        };

        let sort_by = queryParam.sort_by || 'created_at';
        let sort_type = queryParam.sort ? queryParam.sort : 'DESC';
        sort_by = sortByArr[sort_by];

        let query = await this.FileLogs
            .createQueryBuilder('file_logs')
            .where('file_logs.deleted_at is NULL')
            // .andWhere('file_logs.deleted_at is NULL')
            .andWhere('(file_logs.file_manager_id = :file_manager_id)', { file_manager_id: queryParam.file_manager_id })
            // .andWhere('(bops.users_id = :users_id)', { users_id : req_user_id })
            .select(['file_logs.id', 'file_logs.row_number', 'file_logs.error_message', 'file_logs.created_at'])
            .orderBy(sort_by, sort_type as 'ASC' | 'DESC')

        if (queryParam.search) {
            query = query.andWhere(`(file_logs.error_message LIKE :search)`, { search: `%${queryParam.search}%` });
        }

        const [results, total] = await query.skip(offset).take(pageSize).getManyAndCount();

        return {
            data: results,
            total: total,
            hasMoreResults: (total > nextOffset) ? 1 : 0,
            file_manager: fileManager,
        }
    }

    /**
    * Excel file stroting data based on the request.
    * @param request - An instance of ImportUsersDto containing the file name to process of storing data.
    * @param response - The response object for sending the HTTP response.
    * @param req - The request object, containing the authenticated user information.
    * @throws HttpException - Throws a 404 error if requested file is not exists in AWS Bucket.
    * @returns 201 status if the update is successful.
    * In case of an error, logs the error and responds with the respective error status and message.
    */
    async importUsers(request: ImportUsersDto, authUser: any) {
        const fileBuffer = await this.commonService.getAWSFile(`files/user-excel/${request.file_name}`);

        if (!fileBuffer) {
            throw new HttpException(responseMessages.en.user.file_not_exists, HttpStatus.NOT_FOUND);
        }

        const fileManager = await this.storeFileManager(request.file_name);

        // this.saveImportedData(fileBuffer);
        this.saveImportedUsersData(fileBuffer, request.file_name, fileManager);

        return;
    }

    /**
     * Common function : Request excel file buffer data and store to database.
     * @param request - Request of file buffer data.
     * @param response - Return : Successfull store.
     * In case of an error, logs the error and responds with the respective error status and message.
    */
    async saveImportedUsersData(fileBuffer: any, fileName: string, fileManager: any) {
        try {
            const workbook = new ExcelJS.Workbook();
            // Load the buffer into the workbook
            await workbook.xlsx.load(fileBuffer);

            const worksheet = workbook.getWorksheet(1);
            const sheetArray = [];
            const headers = [];

            worksheet.getRow(1).eachCell((cell, colNumber) => {
                headers[colNumber] = cell.value || '';
            });

            // Loop through each row after the header
            worksheet.eachRow((row, rowNumber) => {
                if (rowNumber === 1) return; // Skip header row
                const rowData = {};
                headers.forEach((header, colNumber) => {
                    const cell = row.getCell(colNumber);
                    if (header === 'EMAIL' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header == 'IG_PROFILE_PICTURE' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header === 'FB_PROFILE_PICTURE' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header === 'YT_PROFILE_PICTURE' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header === 'TK_PROFILE_PICTURE' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header === 'XHS_PROFILE_PICTURE' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header == 'IG_URL' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header === 'FB_URL' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header === 'YT_URL' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header === 'TK_URL' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header === 'XHS_URL' && cell.hyperlink) {
                        rowData[header] = cell.text;
                    } else if (header === 'IG_ENGAGEMENT_RATE' && typeof cell.value === "string") {
                        rowData[header] = cell.value;
                        if (cell.value.includes('%')) {
                            rowData[header] = parseFloat(cell.value.replace('%', '')) / 100;
                        }
                    }
                    else if (header === 'FB_ENGAGEMENT_RATE' && typeof cell.value === "string") {
                        rowData[header] = cell.value;
                        if (cell.value.includes('%')) {
                            rowData[header] = parseFloat(cell.value.replace('%', '')) / 100;
                        }
                    }
                    else if (header === 'TK_ENGAGEMENT_RATE' && typeof cell.value === "string") {
                        rowData[header] = cell.value;
                        if (cell.value.includes('%')) {
                            rowData[header] = parseFloat(cell.value.replace('%', '')) / 100;
                        }
                    }
                    else if (header === 'YT_ENGAGEMENT_RATE' && typeof cell.value === "string") {
                        rowData[header] = cell.value;
                        if (cell.value.includes('%')) {
                            rowData[header] = parseFloat(cell.value.replace('%', '')) / 100;
                        }
                    } else if (header === 'XHS_ENGAGEMENT_RATE' && typeof cell.value === "string") {
                        rowData[header] = cell.value;
                        if (cell.value.includes('%')) {
                            rowData[header] = parseFloat(cell.value.replace('%', '')) / 100;
                        }
                    } else if (header === 'YT_USER_NAME' && typeof cell.value === "string") {
                        if (!cell.value.includes('@') && cell.value != "") {
                            rowData[header] = '@' + cell.value;
                        } else {
                            rowData[header] = cell.value;
                        }
                    } else {
                        rowData[header] = cell.value !== undefined && cell.value !== null ? cell.value : '';
                    }
                });
                sheetArray.push(rowData);
            });

            if (sheetArray.length > 0) {
                await this.storingUserProcess(sheetArray, fileManager);
                return;
            }

            fileManager['status'] = 'completed';
            await this.FileManagers.save(fileManager);

            return;
        } catch (error) {
            console.log(error)
            fileManager['status'] = 'failed';
            await this.FileManagers.save(fileManager);
            let errorLog = {
                file_manager_id: fileManager,
                row_number: 0,
                error_message: 'Wrong formated file uploaded, Please try another file',
            }
            await this.fileManagerRepository.createFileLogs(errorLog);
        }
    }


    /**
    * Store file details on Filemanager based on request.
    * @param fileName - The request object, containing the file name.
    * @returns File manager object.
    */
    async storeFileManager(fileName: string) {
        let fileMangerCreate = {
            file_name: fileName,
            extension: fileName.split('.').pop(),
            source_from: 'Modash',
            file_path: `files/user-excel/${fileName}`,
        }

        const fileManeger = await this.fileManagerRepository.createFileManagerwithObject(fileMangerCreate);

        return fileManeger;
    }

    /**
    * Store users details on user based on request.
    * @param sheetArray - The request object of excel sheet.
    * @param fileManager - The request object of file manager object.
    */
    async storingUserProcess(sheetArray: any, fileManager: any) {

        const chunkSize = 250;
        const chunks = [];

        for (let i = 0; i < sheetArray.length; i += chunkSize) {
            chunks.push(sheetArray.slice(i, i + chunkSize));
        }

        let index = 0;
        let successCount = 0;
        let failedCount = 0;
        let country = "";
        let gender = null;
        // for (const chunk of chunks) {
        for (let x = 0; x < chunks.length; x++) {
            const innerArray = chunks[x];
            for (let y = 0; y < innerArray.length; y++) {

                innerArray[y].index = index;
                let rawNumber = index + 2;

                let socialMediaSuccessCount = 0;
                let followingCount = 0;
                let followersCount = 0;
                let engagementCount = 0;
                let platforms = [];
                // console.log(rawNumber);
                // console.log(chunks[x][y]);
                // console.log(typeof innerArray[y].IG_Followers);
                const isValid = await this.checkValidation(innerArray[y], fileManager, rawNumber);
                if (!isValid.status) { index++; failedCount = failedCount + 1; continue }

                const userData = await this.userDetailsProcess(isValid.userData, innerArray[y]);

                // Instagram Data Create or Update as per IG_User_Name
                const isInstaValid = await this.checkSocialMediaValidation(userData, innerArray[y], fileManager, rawNumber, 'instagram');
                // if(!isSocialValid.status){index++;continue}
                const instaData = await this.userInstaDetailsProcess(userData, isInstaValid, innerArray[y]);

                if (instaData) {
                    country = innerArray[y].IG_COUNTRY || "";
                    gender = innerArray[y].IG_GENDER ? innerArray[y].IG_GENDER.toLowerCase() : null;
                    followersCount = Number(innerArray[y].IG_FOLLOWER_COUNT) || 0;
                    followingCount = Number(innerArray[y].IG_FOLLOWING_COUNT) || 0;
                    engagementCount = engagementCount + innerArray[y].IG_ENGAGEMENT_RATE || 0;
                    socialMediaSuccessCount = 1;
                    successCount = successCount + 1;
                    if (!platforms.includes('instagram')) {
                        platforms.push('instagram');
                    }
                }

                // Facebook Data Create or Update as per FB_USER_NAME
                const isFacebookValid = await this.checkSocialMediaValidation(userData, innerArray[y], fileManager, rawNumber, 'facebook');
                // if(!isSocialValid.status){index++;continue}

                const faceBookData = await this.userFacebookDetailsProcess(userData, isFacebookValid, innerArray[y]);
                if (faceBookData) {
                    country = country || innerArray[y].FB_COUNTRY;
                    if (!gender) {
                        gender = innerArray[y].FB_GENDER ? innerArray[y].FB_GENDER.toLowerCase() : null;
                    }
                    followersCount += Number(innerArray[y].FB_FOLLOWER_COUNT) || 0;
                    followingCount += Number(innerArray[y].FB_FOLLOWING_COUNT) || 0;
                    engagementCount = engagementCount + innerArray[y].FB_ENGAGEMENT_RATE || 0;
                    if (socialMediaSuccessCount == 0) {
                        socialMediaSuccessCount = 1;
                        successCount = successCount + 1;
                    }
                    if (!platforms.includes('facebook')) {
                        platforms.push('facebook');
                    }
                }

                // Youtube Data Create or Update as per YT_USER_NAME
                const isYoutubeValid = await this.checkSocialMediaValidation(userData, innerArray[y], fileManager, rawNumber, 'youtube');
                // if(!isSocialValid.status){index++;continue}
                const youtubeData = await this.userYoutubeDetailsProcess(userData, isYoutubeValid, innerArray[y]);
                if (youtubeData) {
                    country = country || innerArray[y].YT_COUNTRY;
                    if (!gender) {
                        gender = innerArray[y].YT_GENDER ? innerArray[y].YT_GENDER.toLowerCase() : null;
                    }
                    followersCount += Number(innerArray[y].YT_FOLLOWER_COUNT) || 0;
                    followingCount += Number(innerArray[y].YT_FOLLOWING_COUNT) || 0;
                    engagementCount = engagementCount + innerArray[y].YT_ENGAGEMENT_RATE || 0;
                    if (socialMediaSuccessCount == 0) {
                        socialMediaSuccessCount = 1;
                        successCount = successCount + 1;
                    }
                    if (!platforms.includes('youtube')) {
                        platforms.push('youtube');
                    }
                }

                // TikTok Data Create or Update as per TK_USER_NAME
                const isTiktokValid = await this.checkSocialMediaValidation(userData, innerArray[y], fileManager, rawNumber, 'tiktok');
                // if(!isSocialValid.status){index++;continue}

                const tiktokData = await this.userTikTokDetailsProcess(userData, isTiktokValid, innerArray[y]);
                if (tiktokData) {
                    country = country || innerArray[y].TK_COUNTRY;
                    if (!gender) {
                        gender = innerArray[y].TK_GENDER ? innerArray[y].TK_GENDER.toLowerCase() : null;
                    }
                    followersCount += Number(innerArray[y].TK_FOLLOWER_COUNT) || 0;
                    followingCount += Number(innerArray[y].TK_FOLLOWING_COUNT) || 0;
                    engagementCount = engagementCount + innerArray[y].TK_ENGAGEMENT_RATE || 0;
                    if (socialMediaSuccessCount == 0) {
                        socialMediaSuccessCount = 1;
                        successCount = successCount + 1;
                    }
                    if (!platforms.includes('tiktok')) {
                        platforms.push('tiktok');
                    }
                }

                // Xiaohongshu Data Create or Update as per XHS_USER_NAME
                const isXiValid = await this.checkSocialMediaValidation(userData, innerArray[y], fileManager, rawNumber, 'xiaohongshu');
                // if(!isSocialValid.status){index++;continue}

                const xiData = await this.userXiDetailsProcess(userData, isXiValid, innerArray[y]);
                if (xiData) {
                    country = country || innerArray[y].XHS_COUNTRY;
                    if (!gender) {
                        gender = innerArray[y].XHS_GENDER ? innerArray[y].XHS_GENDER.toLowerCase() : null;
                    }
                    followersCount += Number(innerArray[y].XHS_FOLLOWER_COUNT) || 0;
                    followingCount += Number(innerArray[y].XHS_FOLLOWING_COUNT) || 0;
                    engagementCount += innerArray[y].XHS_ENGAGEMENT_RATE || 0;

                    if (socialMediaSuccessCount == 0) {
                        socialMediaSuccessCount = 1;
                        successCount = successCount + 1;
                    }
                    if (!platforms.includes('xiaohongshu')) {
                        platforms.push('xiaohongshu');
                    }
                }
                failedCount = socialMediaSuccessCount == 0 ? failedCount + 1 : failedCount;
                if (socialMediaSuccessCount == 0 && innerArray[y].UUID == "") {
                    await this.Users.softDelete({ id: userData.id });
                    index++;
                    this.FileManagers.update({ id: fileManager.id }, { failed_row_count: failedCount })
                    continue
                }

                //   await this.storeCategoriesProcess(userData, innerArray[y]);
                userData['country'] = country;
                userData['gender'] = gender;
                userData['total_followers'] = followersCount;
                userData['total_following'] = followingCount;
                //userData['engagement_ratio'] = engagementCount;
                userData['platforms'] = platforms;
                //this.Users.update({ id: userData.id }, { country: country, gender: gender, platforms : platforms});
                await this.Users.save(userData);
                //await this.SocialMedias.update({ users: userData.id }, { access_token_expire_at: moment().format('YYYY-MM-DD HH:mm:ss') });
                if (innerArray[y].KEYWORDS) {
                    const keywords = { keywords: innerArray[y].KEYWORDS.split(',').map(word => word.trim()) };
                    this.influencerRegisterRepository.updateInflucnerKeywords(keywords, userData);
                }
                if (innerArray[y].CATEGORY) {
                    const categoryFields = [
                        innerArray[y].IG_CATEGORIES,
                        innerArray[y].FB_CATEGORIES,
                        innerArray[y].YT_CATEGORIES,
                        innerArray[y].TK_CATEGORIES,
                        innerArray[y].XHS_CATEGORIES,
                        innerArray[y].CATEGORY,
                    ];

                    let allCategories: string[] = [];

                    // Process each field
                    for (const field of categoryFields) {
                        if (field) {
                            const categories = field.split(',').map(word => word.trim());
                            allCategories.push(...categories);
                        }
                    }

                    // Remove duplicates
                    allCategories = [...new Set(allCategories)];

                    const categories = { categories: allCategories };
                    this.influencerRegisterRepository.updateOrCreateInfluencerCategory(categories, userData);
                }
                this.FileManagers.update({ id: fileManager.id }, { success_row_count: successCount, failed_row_count: failedCount })
                console.log("*******");
                console.log(successCount);
                console.log(failedCount);
                console.log(rawNumber);

                index++;

            }
        }

        fileManager['total_row_count'] = sheetArray.length;
        fileManager['success_row_count'] = successCount;
        fileManager['failed_row_count'] = failedCount;
        fileManager['status'] = 'completed';
        await this.FileManagers.save(fileManager);
        if (process.env.NODE_ENV === 'production') {
            this.scrapProfiles();
        }
        console.log("File Manager Data Saved Successfully");
        return;
    }

    async scrapProfiles() {
        console.log('Scraping profiles...');

        this.apifyService.facebookScraper(100);
        this.apifyService.tiktokScraper(100);
        await this.youtubeServices.getChannelIdFromName();
        this.youtubeServices.youtubeScraper(100);
        this.apifyService.instagramScraper(100);
        console.log('Scraping profiles completed...');
    }


    /**
    * Common Function for check user validation.
    * @param excelUser - The request object of excel sheet row.
    * @param fileManager - The request object of file manager object.
    * @param rawNumber - The request raw number of excel sheet.
    */
    async checkValidation(excelUser: any, fileManeger: any, rawNumber: any) {
        if (excelUser.UUID && !validator.isUUID(excelUser.UUID)) {

            let errorLog = {
                file_manager_id: fileManeger,
                row_number: rawNumber,
                error_message: responseMessages.en.import_file.uuid_invalid.replace(':uuid', excelUser.UUID),
            }
            await this.fileManagerRepository.createFileLogs(errorLog);

            return { status: false, userData: null };
        }

        let existingUuid = null;

        // Check if UUID is provided and fetch user by ID
        if (excelUser.UUID) {
            existingUuid = await this.Users.findOne({ where: { id: excelUser.UUID } });
        }
        if (excelUser.EMAIL) {
            // Prepare email condition: if UUID exists, exclude it from email check
            const emailCondition = {
                email: excelUser.EMAIL.toLowerCase(),
                ...(existingUuid && { id: Not(excelUser.UUID) }),
            };

            // Check if email already exists (excluding current UUID if provided)
            const existingEmail = await this.Users.findOne({ where: emailCondition });

            if (existingEmail) {
                await this.fileManagerRepository.createFileLogs({
                    file_manager_id: fileManeger,
                    row_number: rawNumber,
                    error_message: responseMessages.en.import_file.email_exists.replace(':email', excelUser.EMAIL),
                });

                return { status: false, userData: null };
            }
        }

        return { status: true, userData: existingUuid };
    }

    /**
    * Common Function for user create or update after validation is success.
    * @param user - The request object of exists user or null.
    * @param excelUser - The request object of excel sheet row.
    * @return UserObject - existing user or created new user.
    */
    async userDetailsProcess(user: any, excelUser: any) {
        let userDetails = user ? user : null;
        if (userDetails) {
            console.log("old");
            userDetails['first_name'] = excelUser.FIRST_NAME || excelUser.IG_DISPLAY_NAME;
            userDetails['last_name'] = excelUser.LAST_NAME || "";
            userDetails['display_name'] = excelUser.DISPLAY_NAME.trim() || ((excelUser.FIRST_NAME) + ' ' + (excelUser.LAST_NAME || "")).trim();
            userDetails['email'] = excelUser.EMAIL.toLowerCase() || "";
            userDetails['ai_summary'] = excelUser.AI_SUMMARY;
            userDetails['role'] = 'influencer';
            userDetails['is_updated_by_cron'] = excelUser.IS_CRON_OVERRIDE == 'Yes' ? true : false
            await this.Users.save(userDetails);
        } else {
            const userSlug = excelUser.IG_USER_NAME || excelUser.FB_USER_NAME || excelUser.YT_USER_NAME || excelUser.TK_USER_NAME || excelUser.XHS_USER_NAME || excelUser.FIRST_NAME;
            console.log("new");
            const firstName = excelUser.FIRST_NAME || excelUser.IG_DISPLAY_NAME
            const userCreate = {
                first_name: firstName.trim(),
                last_name: (excelUser.LAST_NAME || "").trim(),
                display_name: excelUser.DISPLAY_NAME.trim() || ((excelUser.FIRST_NAME) + ' ' + (excelUser.LAST_NAME || "")).trim(),
                email: excelUser.EMAIL.toLowerCase() || "",
                ai_summary: excelUser.AI_SUMMARY,
                role: 'influencer',
                is_imported_by_admin: true,
                is_updated_by_cron: excelUser.IS_CRON_OVERRIDE == 'Yes' ? true : false,
                slug: await this.fileManagerRepository.generateSlug(userSlug),
            }
            console.log(userCreate);
            userDetails = await this.fileManagerRepository.createUserwithObject(userCreate);

        }

        return userDetails;
    }

    /**
    * Common Function for check validation for social media.
    * @param userDetails - The request object of exists user or created user.
    * @param excelUser - The request object of excel sheet row.
    * @param fileManager - The request object of file manager object.
    * @param rawNumber - The request raw number of excel sheet.
    * @param type - The request of social media type like : instagram,facebook,youtube,tiktok,xiaohongshu.
    * @return status - if validation success then true otherwise false.
    * @return socialData - if exists socialMedia then return object otherwise null.
    */
    async checkSocialMediaValidation(userDetails: any, excelUser: any, fileManeger: any, rawNumber: any, type: any) {
        //    console.log(excelUser);
        switch (type) {
            case 'instagram':
                if (excelUser.IG_USER_NAME) {

                    const instaValidation = await this.isValidSocialValidation(fileManeger, rawNumber, type, excelUser.IG_PROFILE_PICTURE, excelUser.IG_FOLLOWER_COUNT, excelUser.IG_FOLLOWING_COUNT, excelUser.IG_POST_COUNT, excelUser.IG_TOTAL_VIEWS, excelUser.IG_ENGAGEMENT_COUNT, excelUser.IG_GENDER, excelUser.IG_COUNTRY, excelUser.IG_ENGAGEMENT_RATE)
                    if (!instaValidation) {
                        return { status: false, socialData: null };
                    }

                    const instaStatus = await this.handleSocialMediaValidation(excelUser.IG_USER_NAME, userDetails, 'instagram', fileManeger, rawNumber);
                    return instaStatus;
                }
                break;

            case 'facebook':
                if (excelUser.FB_USER_NAME) {

                    const facebookValidation = await this.isValidSocialValidation(fileManeger, rawNumber, type, excelUser.FB_PROFILE_PICTURE, excelUser.FB_FOLLOWER_COUNT, excelUser.FB_FOLLOWING_COUNT, excelUser.FB_POST_COUNT, excelUser.FB_TOTAL_VIEWS, excelUser.FB_ENGAGEMENT_COUNT, excelUser.FB_GENDER, excelUser.FB_COUNTRY, excelUser.FB_ENGAGEMENT_RATE)
                    if (!facebookValidation) {
                        return { status: false, socialData: null };
                    }

                    const facebookStatus = await this.handleSocialMediaValidation(excelUser.FB_USER_NAME, userDetails, 'facebook', fileManeger, rawNumber);
                    return facebookStatus;
                }
                break;

            case 'youtube':
                if (excelUser.YT_USER_NAME) {
                    console.log(excelUser.YT_USER_NAME);
                    const youtubeValidation = await this.isValidSocialValidation(fileManeger, rawNumber, type, excelUser.YT_PROFILE_PICTURE, excelUser.YT_FOLLOWER_COUNT, excelUser.YT_FOLLOWING_COUNT, excelUser.YT_POST_COUNT, excelUser.YT_TOTAL_VIEWS, excelUser.YT_ENGAGEMENT_COUNT, excelUser.YT_GENDER, excelUser.YT_COUNTRY, excelUser.YT_ENGAGEMENT_RATE)
                    if (!youtubeValidation) {
                        return { status: false, socialData: null };
                    }

                    const youtubeStatus = await this.handleSocialMediaValidation(excelUser.YT_USER_NAME, userDetails, 'youtube', fileManeger, rawNumber);
                    return youtubeStatus;
                }
                break;

            case 'tiktok':
                if (excelUser.TK_USER_NAME) {

                    const tiktokValidation = await this.isValidSocialValidation(fileManeger, rawNumber, type, excelUser.TK_PROFILE_PICTURE, excelUser.TK_FOLLOWER_COUNT, excelUser.TK_FOLLOWING_COUNT, excelUser.TK_POST_COUNT, excelUser.TK_TOTAL_VIEWS, excelUser.TK_ENGAGEMENT_COUNT, excelUser.TK_GENDER, excelUser.TK_COUNTRY, excelUser.TK_ENGAGEMENT_RATE)
                    console.log(tiktokValidation);
                    if (!tiktokValidation) {
                        return { status: false, socialData: null };
                    }

                    const tiktokStatus = await this.handleSocialMediaValidation(excelUser.TK_USER_NAME, userDetails, 'tiktok', fileManeger, rawNumber);
                    return tiktokStatus;
                }
                break;

            case 'xiaohongshu':
                if (excelUser.XHS_USER_NAME) {
                    const xiValidation = await this.isValidSocialValidation(fileManeger, rawNumber, type, excelUser.XHS_PROFILE_PICTURE, excelUser.XHS_FOLLOWER_COUNT, excelUser.XHS_FOLLOWING_COUNT, excelUser.XHS_POST_COUNT, excelUser.XHS_TOTAL_VIEWS, excelUser.XHS_ENGAGEMENT_COUNT, excelUser.XHS_GENDER, excelUser.XHS_COUNTRY, excelUser.XHS_ENGAGEMENT_COUNT_RATE)
                    if (!xiValidation) {
                        return { status: false, socialData: null };
                    }

                    const xiStatus = await this.handleSocialMediaValidation(excelUser.XHS_USER_NAME, userDetails, 'xiaohongshu', fileManeger, rawNumber);
                    return xiStatus;
                }
                break;

            default:

                break;
        }

        return { status: false, socialData: null };
    }

    /**
    * Common Function for check validation for social media's data from excel sheet request.
    * @param fileManager - The request object of file manager object.
    * @param rawNumber - The request raw number of excel sheet.
    * @param socialType - The request of social media type like : instagram,facebook,youtube,tiktok,xiaohongshu.
    * @param imageUrl - The request of image url from excel sheet.
    * @param followers_count - The request of followers count from excel sheet.
    * @param following_count - The request of following count from excel sheet.
    * @param post_counts - The request of post counts from excel sheet.
    * @param total_views_count - The request of total views count from excel sheet.
    * @param engagement_count - The request of engagement count from excel sheet.
    * @param gender - The request of gender from excel sheet.
    * @param country - The request of country from excel sheet.
    * @return true or false.
    */
    async isValidSocialValidation(fileManeger: any, rawNumber: any, socialType: any, imageUrl: any, followers_count: any, following_count: any, post_counts: any, total_views_count: any, engagement_count: any, gender: any, country: any, engagement_rate: any) {

        if (imageUrl) {
            if (!imageUrl || !validator.isURL(imageUrl)) {
                const errorLog = {
                    file_manager_id: fileManeger,
                    row_number: rawNumber,
                    error_message: responseMessages.en.import_file.image_url_invalid.replace(':platform_name', socialType),
                };

                await this.fileManagerRepository.createFileLogs(errorLog);
                return false;
            }
        }

        if (followers_count) {
            const checkFollowersCount = await this.checkNmberAndPositiveValue(fileManeger, rawNumber, followers_count, 'Followers count', socialType);
            if (!checkFollowersCount) { return false };

            const checkFollowingCountSetting = await this.checkFollowerCountSetting(fileManeger, rawNumber, followers_count, socialType);
            if (!checkFollowingCountSetting) { return false };
        }

        if (following_count) {
            if (socialType != 'youtube') {
                const checkFollowingCount = await this.checkNmberAndPositiveValue(fileManeger, rawNumber, following_count, 'Following count', socialType);
                if (!checkFollowingCount) { return false };
            }
        }

        if (post_counts) {
            const checkPostCount = await this.checkNmberAndPositiveValue(fileManeger, rawNumber, post_counts, 'Post count', socialType);
            if (!checkPostCount) { return false };
        }

        if (total_views_count) {
            const checkViewCount = await this.checkNmberAndPositiveValue(fileManeger, rawNumber, total_views_count, 'Total view count', socialType);
            if (!checkViewCount) { return false };
        }
        if (engagement_count) {
            const checkEngagementCount = await this.checkNmberAndPositiveValue(fileManeger, rawNumber, engagement_count, 'Engagement count', socialType);
            if (!checkEngagementCount) { return false };
        }
        if (engagement_rate) {
            const checkEngagementRate = await this.checkEngementRateValue(fileManeger, rawNumber, engagement_rate, 'Engagement rate', socialType);
            console.log("checkEngagementRate", checkEngagementRate)
            if (!checkEngagementRate) { return false };
        }

        if (gender) {
            const validGenders = ['Male', 'Female', 'male', 'female'];
            if (!validGenders.includes(gender)) {
                const errorLog = {
                    file_manager_id: fileManeger,
                    row_number: rawNumber,
                    error_message: responseMessages.en.import_file.gender_invalid.replace(':platform_name', socialType),
                };
                await this.fileManagerRepository.createFileLogs(errorLog);
                return false;
            }
        }

        if (country) {
            const existCountry = await this.Countries.findOne({ where: { two_digit_code: country } });
            if (!existCountry) {
                const errorLog = {
                    file_manager_id: fileManeger,
                    row_number: rawNumber,
                    error_message: responseMessages.en.import_file.country_invalid.replace(':platform_name', socialType),
                };
                await this.fileManagerRepository.createFileLogs(errorLog);
                return false;
            }
        }
        return true;
    }

    async checkEngementRateValue(fileManeger: any, rawNumber: any, count: any, countName: any, socialType: any) {
        if (count) {
            console.log(countName + " " + count)
            count = await this.extractNumber(count);

            if (typeof count !== 'number') {
                const errorLog = {
                    file_manager_id: fileManeger,
                    row_number: rawNumber,
                    error_message: responseMessages.en.import_file.count_string.replace(':count_name', countName).replace(':platform_name', socialType),
                };
                await this.fileManagerRepository.createFileLogs(errorLog);
                return false;
            }
            if (count < 0) {
                const errorLog = {
                    file_manager_id: fileManeger,
                    row_number: rawNumber,
                    error_message: responseMessages.en.import_file.count_negative.replace(':count_name', countName).replace(':platform_name', socialType),
                };
                await this.fileManagerRepository.createFileLogs(errorLog);
                return false;
            }
            if (count > 99.9999) {
                const errorLog = {
                    file_manager_id: fileManeger,
                    row_number: rawNumber,
                    error_message: responseMessages.en.import_file.enegement_rate_invalid.replace(':platform_name', socialType),
                };
                await this.fileManagerRepository.createFileLogs(errorLog);
                return false;
            }
        }
        return true;
    }

    /**
    * Common Function for check validation of request count.
    * @param fileManager - The request object of file manager object.
    * @param rawNumber - The request raw number of excel sheet.
    * @param count - The request of image url from excel sheet.
    * @param countName - The request of count type like : Followers count,Following count,Post count,Total view count,Engagement count.
    * @param socialType - The request of social media type like : instagram,facebook,youtube,tiktok,xiaohongshu.
    * @return true or false.
    */
    async checkNmberAndPositiveValue(fileManeger: any, rawNumber: any, count: any, countName: any, socialType: any) {

        if (!count) {
            console.log(countName + " " + count)
            count = await this.extractNumber(count);

            if (typeof count !== 'number') {
                const errorLog = {
                    file_manager_id: fileManeger,
                    row_number: rawNumber,
                    error_message: responseMessages.en.import_file.count_string.replace(':count_name', countName).replace(':platform_name', socialType),
                };
                await this.fileManagerRepository.createFileLogs(errorLog);
                return false;
            }
            if (count < 0) {
                const errorLog = {
                    file_manager_id: fileManeger,
                    row_number: rawNumber,
                    error_message: responseMessages.en.import_file.count_negative.replace(':count_name', countName).replace(':platform_name', socialType),
                };
                await this.fileManagerRepository.createFileLogs(errorLog);
                return false;
            }
        }
        return true;
    }

    async extractNumber(input) {
        console.log("input", input)
        if (typeof input === "number") return input; // If already a number, return as is

        if (typeof input === "string") {
            let num = parseInt(input, 10);
            if (!isNaN(num) && /^\d+$/.test(input)) return num; // If pure number string, convert

            let match = input.match(/\d+/g); // Extract numeric part
            return match ? parseInt(match.join(""), 10) : null;
        }

        return null; // Handle cases where input is neither string nor number
    }

    async checkFollowerCountSetting(fileManeger: any, rawNumber: any, followerCount: any, socialType: any) {
        followerCount = await this.extractNumber(followerCount);
        console.log("SETTING " + followerCount)
        const socialCountField = { facebook: "facebook_followers_count", instagram: "instagram_followers_count", tiktok: "tiktok_followers_count", youtube: "youtube_subscriber_count" };

        const setting = await this.Settings.findOne({ where: { setting_type: 'platform', type: socialCountField[socialType] } });
        const count = Number(setting.value);
        if (followerCount < count || typeof followerCount !== 'number') {
            const errorLog = {
                file_manager_id: fileManeger,
                row_number: rawNumber,
                error_message: responseMessages.en.import_file.follower_count_issue.replace(':platform_name', socialType),
            };
            await this.fileManagerRepository.createFileLogs(errorLog);
            return false;
        }
        return true;
    }

    /**
    * Common Function for check user's social media exists or not.
    * @param socialUserName - The request of social user name from excel sheet.
    * @param userDetails - The request object of exists user or created user.
    * @param socialType - The request of social media type like : instagram,facebook,youtube,tiktok,xiaohongshu.
    * @param fileManager - The request object of file manager object.
    * @param rawNumber - The request raw number of excel sheet.
    * @return status - true or false.
    * @return socialData - if exists social media return object otherwise null.
    */
    async handleSocialMediaValidation(socialUserName: any, userDetails: any, socialType: any, fileManeger: any, rawNumber: any) {

        const existsDuplicate = await this.SocialMedias.findOne({ where: { social_user_name: socialUserName, users: { id: Not(userDetails.id) }, social_type: socialType } });
        if (existsDuplicate) {
            const errorLog = {
                file_manager_id: fileManeger,
                row_number: rawNumber,
                error_message: responseMessages.en.import_file.social_user_name_exists.replace(':social_user_name', socialUserName).replace(':platform_name', socialType),
            };
            await this.fileManagerRepository.createFileLogs(errorLog);

            return { status: false, socialData: null };
        }

        const existsSocial = await this.SocialMedias.findOne({ where: { users: { id: userDetails.id }, social_type: socialType } });
        if (existsSocial) {
            if (existsSocial.social_user_name == socialUserName) {
                return { status: true, socialData: existsSocial };
            }

            const errorLog = {
                file_manager_id: fileManeger,
                row_number: rawNumber,
                error_message: responseMessages.en.import_file.allow_one_social_media.replace(':social_user_name', socialUserName).replace(':platform_name', socialType),
            };
            await this.fileManagerRepository.createFileLogs(errorLog);

            return { status: false, socialData: null };
        }

        return { status: true, socialData: null };
    }

    /**
    * Common Function for social media data create or update.
    * @param isSocialValid - The request of object that validate or not.
    * @param createObj - The request object of social media object that need to create or update.
    * @return Social media object.
    */
    async createOrUpdateSocialMedias(isSocialValid: any, createObj: any) {
        let socialMediaDetails = isSocialValid.socialData ? isSocialValid.socialData : null;

        if (socialMediaDetails) {
            Object.assign(socialMediaDetails, createObj);
            await this.SocialMedias.save(socialMediaDetails);
        } else {
            socialMediaDetails = await this.fileManagerRepository.ceateSocialMediaWithObject(createObj);
        }

        return socialMediaDetails;
    }

    /**
    * Common Function for user instagram social media data create or update.
    * @param userData - The request object of exists user or created user.
    * @param isSocialValid - The request of object that validate or not.
    * @param excelUser - The request object of excel sheet row.
    * @return true or false.
    */
    async userInstaDetailsProcess(userData: any, isSocialValid: any, excelUser: any) {
        if (isSocialValid.status) {
            // let socialMediaDetails = isSocialValid.socialData ? isSocialValid.socialData : null;

            const createObj = {
                users: userData,
                social_id: excelUser.IG_SOCIAL_ID || "",
                social_type: 'instagram',
                social_user_name: (excelUser.IG_USER_NAME || "").trim(),
                follower_count: excelUser.IG_FOLLOWER_COUNT || 0,
                following_count: excelUser.IG_FOLLOWING_COUNT || 0,
                post_count: excelUser.IG_POST_COUNT || 0,
                engagement_count: excelUser.IG_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.IG_ENGAGEMENT_RATE || 0) * 100,
                is_token_expire: true,
                country: excelUser.IG_COUNTRY || "",
                country_name: excelUser.IG_COUNTRY || "",
                city_name: excelUser.IG_CITY || "",
                display_name: excelUser.IG_DISPLAY_NAME || "",
                description: excelUser.IG_BIO || "",
            };

            const socialMediaDetails = await this.createOrUpdateSocialMedias(isSocialValid, createObj);

            const instaProfile = await this.InstagramProfiles.findOne({ where: { socialMedia: { id: socialMediaDetails.id } } });

            const instaProfileObj = {
                users: userData,
                socialMedia: socialMediaDetails,
                user_name: (excelUser.IG_USER_NAME || "").trim(),
                description: excelUser.IG_BIO || "",
                name: excelUser.IG_DISPLAY_NAME || "",
                instagram_user_id: excelUser.IG_SOCIAL_ID || "",
                profile_picture_url: excelUser.IG_PROFILE_PICTURE || "",
                biography: excelUser.IG_BIO || "",
                follows_count: excelUser.IG_FOLLOWING_COUNT || 0,
                followers_count: excelUser.IG_FOLLOWER_COUNT || 0,
                media_count: excelUser.IG_POST_COUNT || 0,
                engagement_count: excelUser.IG_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.IG_ENGAGEMENT_RATE || 0) * 100,
                total_views: excelUser.IG_TOTAL_VIEWS || 0,
                country_name: excelUser.IG_COUNTRY || "",
                city_name: excelUser.IG_CITY || "",
                gender: excelUser.IG_GENDER || "",
                url: excelUser.IG_URL || "",
            };

            if (instaProfile) {
                Object.assign(instaProfile, instaProfileObj);
                await this.InstagramProfiles.save(instaProfile);
            } else {
                await this.fileManagerRepository.ceateInstaProfile(instaProfileObj);
            }
            return true;
        }

        return false;
    }

    /**
    * Common Function for user facebook social media data create or update.
    * @param userData - The request object of exists user or created user.
    * @param isSocialValid - The request of object that validate or not.
    * @param excelUser - The request object of excel sheet row.
    * @return true or false.
    */
    async userFacebookDetailsProcess(userData: any, isSocialValid: any, excelUser: any) {
        if (isSocialValid.status) {
            // let socialMediaDetails = isSocialValid.socialData ? isSocialValid.socialData : null;

            const createObj = {
                users: userData,
                social_id: excelUser.FB_SOCIAL_ID || "",
                social_type: 'facebook',
                social_user_name: (excelUser.FB_USER_NAME || "").trim(),
                follower_count: excelUser.FB_FOLLOWER_COUNT || 0,
                following_count: excelUser.FB_FOLLOWING_COUNT || 0,
                post_count: excelUser.FB_POST_COUNT || 0,
                engagement_count: excelUser.FB_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.FB_ENGAGEMENT_RATE || 0) * 100,
                is_token_expire: true,
                country: excelUser.FB_COUNTRY || "",
                country_name: excelUser.FB_COUNTRY || "",
                city_name: excelUser.FB_CITY || "",
                display_name: excelUser.FB_NAME || "",
                description: excelUser.FB_BIO || "",
            };

            const socialMediaDetails = await this.createOrUpdateSocialMedias(isSocialValid, createObj);

            const facebookPage = await this.FacebookPages.findOne({ where: { socialMedia: { id: socialMediaDetails.id } } });

            const facebookPageObj = {
                users: userData,
                socialMedia: socialMediaDetails,
                user_name: (excelUser.FB_USER_NAME || "").trim(),
                description: excelUser.FB_BIO || "",
                name: excelUser.FB_NAME || "",
                page_logo: excelUser.FB_PROFILE_PICTURE || "",
                facebook_page_id: excelUser.FB_SOCIAL_ID || "",
                followers_count: excelUser.FB_FOLLOWER_COUNT || 0,
                engagement_count: excelUser.FB_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.FB_ENGAGEMENT_RATE || 0) * 100,
                post_count: excelUser.FB_POST_COUNT || 0,
                total_views: excelUser.FB_TOTAL_VIEWS || 0,
                country: excelUser.FB_COUNTRY || "",
                gender: excelUser.FB_GENDER || "",
                city: excelUser.FB_CITY || "",
                url: excelUser.FB_URL || "",
            };

            if (facebookPage) {
                Object.assign(facebookPage, facebookPageObj);
                await this.FacebookPages.save(facebookPage);
            } else {
                await this.fileManagerRepository.ceateFacebookPage(facebookPageObj);
            }

            return true;

        }

        return false;
    }

    /**
    * Common Function for user youtube social media data create or update.
    * @param userData - The request object of exists user or created user.
    * @param isSocialValid - The request of object that validate or not.
    * @param excelUser - The request object of excel sheet row.
    * @return true or false.
    */
    async userYoutubeDetailsProcess(userData: any, isSocialValid: any, excelUser: any) {
        if (isSocialValid.status) {
            // let socialMediaDetails = isSocialValid.socialData ? isSocialValid.socialData : null;

            const createObj = {
                users: userData,
                social_id: excelUser.YT_SOCIAL_ID || "",
                social_type: 'youtube',
                social_user_name: (excelUser.YT_USER_NAME || "").trim(),
                follower_count: excelUser.YT_FOLLOWER_COUNT || 0,
                following_count: excelUser.YT_FOLLOWING_COUNT || 0,
                post_count: excelUser.YT_POST_COUNT || 0,
                engagement_count: excelUser.YT_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.YT_ENGAGEMENT_RATE || 0) * 100,
                country: excelUser.YT_COUNTRY || "",
                country_name: excelUser.YT_COUNTRY || "",
                city_name: excelUser.YT_CITY || "",
                display_name: excelUser.YT_USER_NAME || "",
                description: excelUser.YT_BIO || "",
            };

            const socialMediaDetails = await this.createOrUpdateSocialMedias(isSocialValid, createObj);

            const youtubeChannel = await this.YoutubeChannels.findOne({ where: { socialMedia: { id: socialMediaDetails.id } } });

            const youtubeChannelObj = {
                users: userData,
                socialMedia: socialMediaDetails,
                title: excelUser.YT_DISPLAY_NAME || "",
                description: excelUser.YT_BIO || "",
                channel_name: (excelUser.YT_USER_NAME || "").trim(),
                thumbnails: excelUser.YT_PROFILE_PICTURE || "",
                view_counts: excelUser.YT_TOTAL_VIEWS || 0,
                subscriber_count: excelUser.YT_FOLLOWER_COUNT || 0,
                video_count: excelUser.YT_POST_COUNT || 0,
                engagement_count: excelUser.YT_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.YT_ENGAGEMENT_RATE || 0) * 100,
                country_name: excelUser.YT_COUNTRY || "",
                city_name: excelUser.YT_CITY || "",
                gender: excelUser.YT_GENDER || "",
                url: excelUser.YT_URL || "",
            };

            if (youtubeChannel) {
                Object.assign(youtubeChannel, youtubeChannelObj);
                await this.YoutubeChannels.save(youtubeChannel);
            } else {
                await this.fileManagerRepository.ceateYoutubeChannel(youtubeChannelObj);
            }

            return true;

        }

        return false;
    }

    /**
    * Common Function for user tiktok social media data create or update.
    * @param userData - The request object of exists user or created user.
    * @param isSocialValid - The request of object that validate or not.
    * @param excelUser - The request object of excel sheet row.
    * @return true or false.
    */
    async userTikTokDetailsProcess(userData: any, isSocialValid: any, excelUser: any) {
        if (isSocialValid.status) {
            // let socialMediaDetails = isSocialValid.socialData ? isSocialValid.socialData : null;

            const createObj = {
                users: userData,
                social_id: excelUser.TK_SOCIAL_ID || "",
                social_type: 'tiktok',
                social_user_name: (excelUser.TK_USER_NAME || "").trim(),
                follower_count: excelUser.TK_FOLLOWER_COUNT || 0,
                following_count: excelUser.TK_FOLLOWING_COUNT || 0,
                post_count: excelUser.TK_POST_COUNT || 0,
                engagement_count: excelUser.TK_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.TK_ENGAGEMENT_RATE || 0) * 100,
                is_token_expire: true,
                country: excelUser.TK_COUNTRY || "",
                country_name: excelUser.TK_COUNTRY || "",
                city_name: excelUser.TK_CITY || "",
                display_name: excelUser.TK_DISPLAY_NAME || "",
                description: excelUser.TK_BIO || "",
            };

            const socialMediaDetails = await this.createOrUpdateSocialMedias(isSocialValid, createObj);

            const tiktokProfile = await this.TikTokProfiles.findOne({ where: { socialMedia: { id: socialMediaDetails.id } } });

            const tiktokProfileObj = {
                users: userData,
                socialMedia: socialMediaDetails,
                avatar_url: excelUser.TK_PROFILE_PICTURE || "",
                bio_description: excelUser.TK_BIO || "",
                open_id: excelUser.TK_SOCIAL_ID || "",
                username: (excelUser.TK_USER_NAME || "").trim(),
                display_name: excelUser.TK_DISPLAY_NAME || "",
                video_count: excelUser.TK_POST_COUNT || 0,
                following_count: excelUser.TK_FOLLOWER_COUNT || 0,
                follower_count: excelUser.TK_FOLLOWING_COUNT || 0,
                engagement_count: excelUser.TK_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.TK_ENGAGEMENT_RATE || 0) * 100,
                total_views: excelUser.TK_TOTAL_VIEWS || 0,
                country_name: excelUser.TK_COUNTRY || "",
                city_name: excelUser.TK_CITY || "",
                gender: excelUser.TK_GENDER || "",
                url: excelUser.TK_URL || "",
            };

            if (tiktokProfile) {
                Object.assign(tiktokProfile, tiktokProfileObj);
                await this.TikTokProfiles.save(tiktokProfile);
            } else {
                await this.fileManagerRepository.ceateTikTokProfile(tiktokProfileObj);
            }

            return true;

        }

        return false;
    }

    /**
    * Common Function for user xiaohongshu social media data create or update.
    * @param userData - The request object of exists user or created user.
    * @param isSocialValid - The request of object that validate or not.
    * @param excelUser - The request object of excel sheet row.
    * @return true or false.
    */
    async userXiDetailsProcess(userData: any, isSocialValid: any, excelUser: any) {
        if (isSocialValid.status) {
            // let socialMediaDetails = isSocialValid.socialData ? isSocialValid.socialData : null;

            const createObj = {
                users: userData,
                social_id: excelUser.XHS_SOCIAL_ID || "",
                social_type: 'xiaohongshu',
                social_user_name: excelUser.XHS_USER_NAME || "",
                follower_count: excelUser.XHS_FOLLOWER_COUNT || 0,
                following_count: excelUser.XHS_FOLLOWING_COUNT || 0,
                post_count: excelUser.XHS_POST_COUNT || 0,
                engagement_count: excelUser.XHS_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.XHS_ENGAGEMENT_COUNT_RATE || 0) * 100,
                is_token_expire: true,
                is_verified: true,
                country: excelUser.XHS_COUNTRY || "",
                country_name: excelUser.XHS_COUNTRY || "",
                city_name: excelUser.XHS_CITY || "",
                display_name: excelUser.XHS_DISPLAY_NAME || "",
                description: excelUser.XHS_BIO || "",
            };

            const socialMediaDetails = await this.createOrUpdateSocialMedias(isSocialValid, createObj);

            const xiProfile = await this.XiaohongshuProfiles.findOne({ where: { socialMedia: { id: socialMediaDetails.id } } });

            const xiProfileObj = {
                users: userData,
                socialMedia: socialMediaDetails,
                name: excelUser.XHS_DISPLAY_NAME || "",
                social_id: excelUser.XHS_SOCIAL_ID || "",
                user_name: excelUser.XHS_USER_NAME || "",
                profile_picture_url: excelUser.XHS_PROFILE_PICTURE || "",
                follower_count: excelUser.XHS_FOLLOWER_COUNT || 0,
                following_count: excelUser.XHS_FOLLOWING_COUNT || 0,
                engagement_count: excelUser.XHS_ENGAGEMENT_COUNT || 0,
                engagement_rate: (excelUser.XHS_ENGAGEMENT_COUNT_RATE || 0) * 100,
                post_count: excelUser.XHS_POST_COUNT || 0,
                total_views: excelUser.XHS_TOTAL_VIEWS || 0,
                country_name: excelUser.XHS_COUNTRY || "",
                description: excelUser.XHS_BIO || "",
                city_name: excelUser.XHS_CITY || "",
                gender: excelUser.XHS_GENDER || "",
                url: excelUser.XHS_URL || "",
                engagement_count_like: excelUser.XHS_ENGAGEMENT_COUNT_LIKE || 0,
                engagement_count_bookmark: excelUser.XHS_ENGAGEMENT_COUNT_BOOKMARK || 0,
            };

            if (xiProfile) {
                Object.assign(xiProfile, xiProfileObj);
                await this.XiaohongshuProfiles.save(xiProfile);
            } else {
                await this.fileManagerRepository.ceateXiProfile(xiProfileObj);
            }

            return true;

        }

        return false;
    }

    /**
    * Common Function for store user category data create or update.
    * @param userDetails - The request object of exists user or created user.
    * @param excelUser - The request object of excel sheet row.
    * @return
    */
    async storeCategoriesProcess(userDetails: any, excelUser: any) {
        if (excelUser.IG_CATEGORIES) {
            await this.handleCategories(excelUser.IG_CATEGORIES, userDetails);
        }

        if (excelUser.FB_CATEGORIES) {
            await this.handleCategories(excelUser.FB_CATEGORIES, userDetails);
        }

        if (excelUser.YT_CATEGORIES) {
            await this.handleCategories(excelUser.YT_CATEGORIES, userDetails);
        }

        if (excelUser.TK_CATEGORIES) {
            await this.handleCategories(excelUser.TK_CATEGORIES, userDetails);
        }

        if (excelUser.XHS_CATEGORIES) {
            await this.handleCategories(excelUser.XHS_CATEGORIES, userDetails);
        }

        return;
    }

    /**
    * Common Function for Influencer user category data create.
    * @param categoriesString - The request string of category.
    * @param userDetails - The request object of exists user or created user.
    * @return
    */
    async handleCategories(categoriesString: any, userDetails: any) {
        const excelCats = categoriesString.split(',').map(word => word.trim());
        let storeCategoryArray = [];

        for await (const cat of excelCats) {
            // Find category by title in translations (default to 'en')
            const translation = await this.CategoryTranslation.findOne({
                where: { title: cat, language_code: 'en' },
                relations: ['category']
            });
            let category = translation ? translation.category : null;
            if (category) {
                let existCat = await this.InfluencerCategory.findOne({ where: { users: { id: userDetails.id }, category: category } });
                if (!existCat) {
                    storeCategoryArray.push({
                        users: userDetails,
                        category: category,
                    });
                }
            }
        }

        if (storeCategoryArray.length > 0) {
            await this.InfluencerCategory.save(storeCategoryArray);
        }

        return;
    }


    async exportUsers(request, authUser, response) {
        const { start_date, end_date, status, search, category_id } = request;
        const batchSize = 4000;
        let lastCreatedAt = null;
        let offset = 0;
        let hasMore = true;
        let fileArr = [];
        while (hasMore) {
            const workbook = new ExcelJS.Workbook();
            const worksheet = workbook.addWorksheet('Influencers');
            // Define headers (matching your import file)
            //const headers = ['UUID', 'First_Name', 'Last_Name', 'Email', 'IG_Name', 'IG_Social_ID', 'IG_User_Name', 'IG_Followers', 'IG_Following', 'IG_Engagement', 'IG_Engagement_Rate', 'IG_POST_COUNT', 'IG_TOTAL_VIEWS', 'IG_PROFILE_PICTURE', 'IG_COUNTRY', 'IG_BIO', 'IG_GENDER', 'IG_CITY', 'IG_CATEGORIES', 'FB_NAME', 'FB_SOCIAL_ID', 'FB_USER_NAME', 'FB_FOLLOWER_COUNT', 'FB_FOLLOWING_COUNT', 'FB_Engagement', 'FB_ENGAGEMENT_RATE', 'FB_POST_COUNT', 'FB_TOTAL_VIEWS', 'FB_PROFILE_PICTURE', 'FB_COUNTRY', 'FB_BIO', 'FB_GENDER', 'FB_CITY', 'FB_CATEGORIES', 'YT_DISPLAY_NAME', 'YT_SOCIAL_ID', 'YT_USER_NAME', 'YT_FOLLOWER_COUNT', 'YT_FOLLOWING_COUNT', 'YT_Engagement', 'YT_ENGAGEMENT_RATE', 'YT_POST_COUNT', 'YT_TOTAL_VIEWS', 'YT_PROFILE_PICTURE', 'YT_COUNTRY', 'YT_BIO', 'YT_GENDER', 'YT_CITY', 'YT_CATEGORIES', 'TK_DISPLAY_NAME', 'TK_SOCIAL_ID', 'TK_USER_NAME', 'TK_FOLLOWER_COUNT', 'TK_FOLLOWING_COUNT', 'TK_Engagement', 'TK_ENGAGEMENT_RATE', 'TK_POST_COUNT', 'TK_TOTAL_VIEWS', 'TK_PROFILE_PICTURE', 'TK_COUNTRY', 'TK_BIO', 'TK_GENDER', 'TK_CITY', 'TK_CATEGORIES', 'XHS_DISPLAY_NAME', 'XHS_SOCIAL_ID', 'XHS_USER_NAME', 'XHS_FOLLOWER_COUNT', 'XHS_FOLLOWING_COUNT', 'XHS_ENGAGEMENT_COUNT', 'XHS_ENGAGEMENT_COUNT_RATE', 'XHS_POST_COUNT', 'XHS_TOTAL_VIEWS', 'XHS_PROFILE_PICTURE', 'XHS_COUNTRY', 'XHS_BIO', 'XHS_GENDER', 'XHS_CITY', 'XHS_CATEGORIES','Keywords'];
            const headers = ["UUID", "FIRST_NAME", "LAST_NAME", "DISPLAY_NAME", "EMAIL",
                "IG_DISPLAY_NAME", "IG_SOCIAL_ID", "IG_USER_NAME", "IG_URL", "IG_FOLLOWER_COUNT", "IG_FOLLOWING_COUNT",
                "IG_ENGAGEMENT_COUNT", "IG_ENGAGEMENT_RATE", "IG_POST_COUNT", "IG_TOTAL_VIEWS", "IG_PROFILE_PICTURE", "IG_COUNTRY",
                "IG_BIO", "IG_GENDER", "IG_CITY", "IG_CATEGORIES",
                "FB_NAME", "FB_SOCIAL_ID", "FB_USER_NAME", "FB_URL", "FB_FOLLOWER_COUNT", "FB_FOLLOWING_COUNT",
                "FB_ENGAGEMENT_COUNT", "FB_ENGAGEMENT_RATE", "FB_POST_COUNT", "FB_TOTAL_VIEWS", "FB_PROFILE_PICTURE",
                "FB_COUNTRY", "FB_BIO", "FB_GENDER", "FB_CITY", "FB_CATEGORIES",
                "YT_DISPLAY_NAME", "YT_SOCIAL_ID", "YT_USER_NAME", "YT_URL", "YT_FOLLOWER_COUNT", "YT_FOLLOWING_COUNT",
                "YT_ENGAGEMENT_COUNT", "YT_ENGAGEMENT_RATE", "YT_POST_COUNT", "YT_TOTAL_VIEWS", "YT_PROFILE_PICTURE",
                "YT_COUNTRY", "YT_BIO", "YT_GENDER", "YT_CITY", "YT_CATEGORIES",
                "TK_DISPLAY_NAME", "TK_SOCIAL_ID", "TK_USER_NAME", "TK_URL", "TK_FOLLOWER_COUNT", "TK_FOLLOWING_COUNT",
                "TK_ENGAGEMENT_COUNT", "TK_ENGAGEMENT_RATE", "TK_POST_COUNT", "TK_TOTAL_VIEWS", "TK_PROFILE_PICTURE", "TK_COUNTRY",
                "TK_BIO", "TK_GENDER", "TK_CITY", "TK_CATEGORIES",
                "XHS_DISPLAY_NAME", "XHS_SOCIAL_ID", "XHS_USER_NAME", "XHS_URL", "XHS_FOLLOWER_COUNT", "XHS_FOLLOWING_COUNT",
                "XHS_ENGAGEMENT_COUNT", "XHS_ENGAGEMENT_COUNT_LIKE", "XHS_ENGAGEMENT_COUNT_BOOKMARK", "XHS_ENGAGEMENT_COUNT_RATE",
                "XHS_POST_COUNT", "XHS_TOTAL_VIEWS", "XHS_PROFILE_PICTURE", "XHS_COUNTRY", "XHS_BIO", "XHS_GENDER", "XHS_CITY",
                "XHS_CATEGORIES", "XHS_CATEGORIES2", "KEYWORDS", "CATEGORY", "AI_SUMMARY"];

            worksheet.addRow(headers); // Add the headers as the first row
            const languageCode = 'en';
            let query = await this.Users.createQueryBuilder('user')
                .leftJoinAndSelect('user.influencerCategories', 'influencerCategory')
                .leftJoinAndSelect('influencerCategory.category', 'category', 'category.is_active = true')
                .leftJoin("category.translations", "translation_req", "translation_req.language_code = :languageCode AND translation_req.deleted_at IS NULL", { languageCode })
                .leftJoinAndSelect("category.translations", "translation",
                    "(translation.language_code = :languageCode OR (translation.language_code = 'en' AND translation_req.id IS NULL)) AND translation.deleted_at IS NULL",
                    { languageCode })
                .leftJoinAndSelect('user.social_medias', 'social_medias')
                .leftJoinAndSelect('user.facebookPages', 'facebookPages')
                .leftJoinAndSelect('user.instagramProfiles', 'instagramProfiles')
                .leftJoinAndSelect('user.youtubeChannels', 'youtubeChannels')
                .leftJoinAndSelect('user.tikTokProfiles', 'tikTokProfiles')
                .leftJoinAndSelect('user.xiaohongshuProfiles', 'xiaohongshuProfiles')
                .leftJoinAndSelect('user.userKeywords', 'userKeywords')
                .leftJoinAndSelect('userKeywords.keywords', 'keywords')
                .leftJoin('keywords.translations', 'keyword_translation_req_fm', 'keyword_translation_req_fm.language_code = :languageCode AND keyword_translation_req_fm.deleted_at IS NULL', { languageCode })
                .leftJoin('keywords.translations', 'keyword_translation_en_fm', 'keyword_translation_en_fm.language_code = \'en\' AND keyword_translation_en_fm.deleted_at IS NULL')
                .leftJoinAndSelect('keywords.translations', 'keyword_translation_fm',
                    '(keyword_translation_fm.language_code = :languageCode OR (keyword_translation_fm.language_code = \'en\' AND keyword_translation_req_fm.id IS NULL)) AND keyword_translation_fm.deleted_at IS NULL',
                    { languageCode })
                .where('user.role = :role', { role: 'influencer' })
                .andWhere('DATE(user.created_at) BETWEEN :start_date AND :end_date', { start_date, end_date })
                .select([
                    'user.id', 'user.first_name', 'user.last_name', "user.display_name", 'user.email', 'user.about_me', 'user.created_at', 'social_medias',
                    'facebookPages', 'instagramProfiles', 'youtubeChannels', 'tikTokProfiles', 'xiaohongshuProfiles', 'influencerCategory', 'category.id', 'category.slug', 'category.title',
                    'translation.id', 'translation.title', 'userKeywords', 'keywords', 'keyword_translation_fm',
                ])
                .orderBy('translation.title', 'ASC');

            if (search) {
                query = query.andWhere(`(CONCAT(user.first_name, ' ', user.last_name) ILIKE :search OR user.display_name ILIKE :search)`, { search: `%${search}%` });
            }
            if (status) {
                query = query.andWhere(`user.is_active = :status`, { status });
            }
            if (category_id) {
                query = query.andWhere(`category.id = :category_id`, { category_id });
            }
            if (lastCreatedAt) {
                query = query.andWhere('user.created_at < :lastCreatedAt', { lastCreatedAt });
            }

            const influencerArr = await query
                .take(batchSize)
                .orderBy('user.created_at', 'DESC')
                .getMany();

            if (influencerArr.length == 0) {
                console.log("offset", offset)
                if (offset == 0) {
                    console.log("IN-offset")
                    throw new HttpException(responseMessages.en.file_manager.no_record_found, HttpStatus.BAD_REQUEST);
                }
                hasMore = false;
                console.log("IN-fileArr", fileArr)
                return fileArr;
            }
            offset += influencerArr.length;
            console.log("influencerArr.length", influencerArr)
            // MERGE ALL PROFILE INTO SINGLE ROW AND ADDED INTO WORKSHEET 
            await this.mergeAllProfile(influencerArr, worksheet);
            // offset += batchSize;
            const lastInfluencer = influencerArr[influencerArr.length - 1];
            lastCreatedAt = lastInfluencer.created_at;
            worksheet.getRow(1).font = { bold: true }; // Bold headers

            const fileName = 'files/export/influencer_' + moment().format('YYYY-MM-DD-HH:mm:ss') + '.xlsx';
            const buffer = await workbook.xlsx.writeBuffer();

            const s3Params = {
                Bucket: 'connector-club',
                Key: fileName, // S3 path
                Body: buffer,
                ContentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            };
            await this.commonService.uploadFile(s3Params);
            fileArr.push(process.env.AWS_FILE_PATH + fileName);
            console.log(fileArr);
        }
        return fileArr;
    }

    async getInfluencer(request) {

    }

    async getSimpleEngagementMap(social_medias) {
        const engagementMap = {};

        if (social_medias && Array.isArray(social_medias)) {
            social_medias.forEach((media) => {
                const platform = media.social_type;
                const rate = parseFloat((parseFloat(media.engagement_rate) / 100).toFixed(1)); // Convert percentage to decimal

                if (!isNaN(rate)) {
                    engagementMap[platform] = rate;
                }
            });
        }

        return engagementMap;
    }
    async mergeAllProfile(influencerArr, worksheet) {
        const influencerRows = [];
        const firstName = [];
        for (let influencer of influencerArr) {

            const categoryTitles = (influencer.influencerCategories || [])
                // .filter(item => item.category && item.category.is_active)
                .map((item) => {
                    const translation = item.category?.translations && item.category.translations.length > 0 ? item.category.translations[0] : null;
                    console.log("translation", translation);
                    return translation ? translation.title : '';
                })
                .filter(title => title !== '')
                .join(", ")
            const keywords = (influencer.userKeywords || [])
                .filter(item => item.keywords)
                .map((item) => {
                    const translation = item.keywords?.translations && item.keywords.translations.length > 0
                        ? item.keywords.translations[0]
                        : null;
                    return translation ? translation.keyword : (item.keywords?.keyword || '');
                })
                .join(", ")
            firstName.push(influencer.first_name);
            const engagementMap = await this.getSimpleEngagementMap(influencer.social_medias);
            let row = {
                uuid: influencer.id,
                firstName: influencer.first_name,
                lastName: influencer.last_name,
                displayName: influencer.display_name,
                email: influencer.email,

                // INSTAGRAM 
                IG_Name: influencer?.instagramProfiles?.[0]?.name || '',
                IG_Social_ID: influencer?.instagramProfiles?.[0]?.instagram_user_id || '',
                IG_User_Name: influencer?.instagramProfiles?.[0]?.user_name || '',
                IG_URL: influencer?.instagramProfiles?.[0]?.url || '',
                IG_Followers: influencer?.instagramProfiles?.[0]?.followers_count || '',
                IG_Following: influencer?.instagramProfiles?.[0]?.follows_count || '',
                IG_Engagement: influencer?.instagramProfiles?.[0]?.engagement_count || '',
                IG_Engagement_Rate: engagementMap['instagram'] || 0,
                IG_POST_COUNT: influencer?.instagramProfiles?.[0]?.media_count || '',
                IG_TOTAL_VIEWS: influencer?.instagramProfiles?.[0]?.total_views || '',
                IG_PROFILE_PICTURE: influencer?.instagramProfiles?.[0]?.profile_picture_url || '',
                IG_COUNTRY: influencer?.instagramProfiles?.[0]?.country_name || '',
                IG_BIO: influencer?.instagramProfiles?.[0]?.biography || '',
                IG_GENDER: influencer?.instagramProfiles?.[0]?.gender || '',
                IG_CITY: influencer?.instagramProfiles?.[0]?.city_name || '',
                IG_CATEGORIES: categoryTitles,
                // FACEBOOK
                FB_NAME: influencer?.facebookPages?.[0]?.name || '',
                FB_SOCIAL_ID: influencer?.facebookPages?.[0]?.facebook_page_id || '',
                FB_USER_NAME: influencer?.facebookPages?.[0]?.user_name || '',
                FB_URL: influencer?.facebookPages?.[0]?.url || '',
                FB_FOLLOWER_COUNT: influencer?.facebookPages?.[0]?.followers_count || 0,
                FB_FOLLOWING_COUNT: 0,
                FB_Engagement: influencer?.facebookPages?.[0]?.engagement_count || 0,
                FB_ENGAGEMENT_RATE: engagementMap['facebook'] || '',
                FB_POST_COUNT: influencer?.facebookPages?.[0]?.post_count || 0,
                FB_TOTAL_VIEWS: influencer?.facebookPages?.[0]?.total_views || 0,
                FB_PROFILE_PICTURE: influencer?.facebookPages?.[0]?.page_logo || '',
                FB_COUNTRY: influencer?.facebookPages?.[0]?.country || '',
                FB_BIO: influencer?.facebookPages?.[0]?.description || '',
                FB_GENDER: influencer?.facebookPages?.[0]?.gender || '',
                FB_CITY: influencer?.facebookPages?.[0]?.city || '',
                FB_CATEGORIES: categoryTitles,
                // YouTube Data
                YT_DISPLAY_NAME: influencer?.youtubeChannels?.[0]?.title || '',
                YT_SOCIAL_ID: influencer?.youtubeChannels?.[0]?.content_id || '',
                YT_USER_NAME: influencer?.youtubeChannels?.[0]?.channel_name || '',
                YT_URL: influencer?.youtubeChannels?.[0]?.url || '',
                YT_FOLLOWER_COUNT: influencer?.youtubeChannels?.[0]?.subscriber_count || 0,
                YT_FOLLOWING_COUNT: 0,
                YT_ENGAGEMENT_COUNT: influencer?.youtubeChannels?.[0]?.engagement_count || 0,
                YT_ENGAGEMENT_RATE: engagementMap['youtube'] || '',
                YT_POST_COUNT: influencer?.youtubeChannels?.[0]?.video_count || 0,
                YT_TOTAL_VIEWS: influencer?.youtubeChannels?.[0]?.view_counts || 0,
                YT_PROFILE_PICTURE: influencer?.youtubeChannels?.[0]?.thumbnails || '',
                YT_COUNTRY: influencer?.youtubeChannels?.[0]?.country_name || '',
                YT_BIO: influencer?.youtubeChannels?.[0]?.description || '',
                YT_GENDER: influencer?.youtubeChannels?.[0]?.gender || '',
                YT_CITY: influencer?.youtubeChannels?.[0]?.city_name || '',
                YT_CATEGORIES: categoryTitles,

                // TikTok Data
                TK_DISPLAY_NAME: influencer?.tikTokProfiles?.[0]?.display_name || '',
                TK_SOCIAL_ID: influencer?.tikTokProfiles?.[0]?.open_id || '',
                TK_USER_NAME: influencer?.tikTokProfiles?.[0]?.username || '',
                TK_URL: influencer?.tikTokProfiles?.[0]?.url || '',
                TK_FOLLOWER_COUNT: influencer?.tikTokProfiles?.[0]?.follower_count || 0,
                TK_FOLLOWING_COUNT: influencer?.tikTokProfiles?.[0]?.following_count || 0,
                TK_ENGAGEMENT_COUNT: influencer?.tikTokProfiles?.[0]?.engagement_count || 0,
                TK_ENGAGEMENT_RATE: engagementMap['tiktok'] || '',
                TK_POST_COUNT: influencer?.tikTokProfiles?.[0]?.video_count || 0,
                TK_TOTAL_VIEWS: influencer?.tikTokProfiles?.[0]?.total_views || 0,
                TK_PROFILE_PICTURE: influencer?.tikTokProfiles?.[0]?.avatar_url || '',
                TK_COUNTRY: influencer?.tikTokProfiles?.[0]?.country_name || '',
                TK_BIO: influencer?.tikTokProfiles?.[0]?.bio_description || '',
                TK_GENDER: influencer?.tikTokProfiles?.[0]?.gender || '',
                TK_CITY: influencer?.tikTokProfiles?.[0]?.city_name || '',
                TK_CATEGORIES: categoryTitles,

                // XiaohongshuProfiles Data
                XHS_DISPLAY_NAME: influencer?.xiaohongshuProfiles?.[0]?.name || '',
                XHS_SOCIAL_ID: influencer?.xiaohongshuProfiles?.[0]?.social_id || '',
                XHS_USER_NAME: influencer?.xiaohongshuProfiles?.[0]?.user_name || '',
                XHS_URL: influencer?.xiaohongshuProfiles?.[0]?.url || '',
                XHS_FOLLOWER_COUNT: influencer?.xiaohongshuProfiles?.[0]?.follower_count || 0,
                XHS_FOLLOWING_COUNT: influencer?.xiaohongshuProfiles?.[0]?.following_count || 0,
                XHS_ENGAGEMENT_COUNT: influencer?.xiaohongshuProfiles?.[0]?.engagement_count || 0,
                XHS_ENGAGEMENT_COUNT_LIKE: influencer?.xiaohongshuProfiles?.[0]?.engagement_like_count || 0,
                XHS_ENGAGEMENT_COUNT_BOOKMARK: influencer?.xiaohongshuProfiles?.[0]?.engagement_bookmark_count || 0,
                XHS_ENGAGEMENT_COUNT_RATE: influencer?.xiaohongshuProfiles?.[0]?.engagement_rate || '',
                XHS_POST_COUNT: influencer?.xiaohongshuProfiles?.[0]?.post_count || 0,
                XHS_TOTAL_VIEWS: influencer?.xiaohongshuProfiles?.[0]?.total_views || 0,
                XHS_PROFILE_PICTURE: influencer?.xiaohongshuProfiles?.[0]?.profile_picture_url || '',
                XHS_COUNTRY: influencer?.xiaohongshuProfiles?.[0]?.country_name || '',
                XHS_BIO: influencer?.xiaohongshuProfiles?.[0]?.description || '',
                XHS_GENDER: influencer?.xiaohongshuProfiles?.[0]?.gender || '',
                XHS_CITY: influencer?.xiaohongshuProfiles?.[0]?.city_name || '',
                XHS_CATEGORIES: categoryTitles,
                XHS_CATEGORIES2: "",
                Keywords: keywords,
                CATEGORY: categoryTitles,
                AI_SUMMARY: influencer?.ai_summary || '',
            };
            influencerRows.push(Object.values(row));
        }

        await worksheet.addRows(influencerRows);
    }

    async exportBrand(request, authUser, response) {
        const workbook = new ExcelJS.Workbook();
        const worksheet = workbook.addWorksheet('Brands');
        const { start_date, end_date, status, search, category_id } = request;
        // Define headers (matching your import file)
        //const headers = ['UUID', 'First_Name', 'Last_Name', 'Email', 'IG_Name', 'IG_Social_ID', 'IG_User_Name', 'IG_Followers', 'IG_Following', 'IG_Engagement', 'IG_Engagement_Rate', 'IG_POST_COUNT', 'IG_TOTAL_VIEWS', 'IG_PROFILE_PICTURE', 'IG_COUNTRY', 'IG_BIO', 'IG_GENDER', 'IG_CITY', 'IG_CATEGORIES', 'FB_NAME', 'FB_SOCIAL_ID', 'FB_USER_NAME', 'FB_FOLLOWER_COUNT', 'FB_FOLLOWING_COUNT', 'FB_Engagement', 'FB_ENGAGEMENT_RATE', 'FB_POST_COUNT', 'FB_TOTAL_VIEWS', 'FB_PROFILE_PICTURE', 'FB_COUNTRY', 'FB_BIO', 'FB_GENDER', 'FB_CITY', 'FB_CATEGORIES', 'YT_DISPLAY_NAME', 'YT_SOCIAL_ID', 'YT_USER_NAME', 'YT_FOLLOWER_COUNT', 'YT_FOLLOWING_COUNT', 'YT_Engagement', 'YT_ENGAGEMENT_RATE', 'YT_POST_COUNT', 'YT_TOTAL_VIEWS', 'YT_PROFILE_PICTURE', 'YT_COUNTRY', 'YT_BIO', 'YT_GENDER', 'YT_CITY', 'YT_CATEGORIES', 'TK_DISPLAY_NAME', 'TK_SOCIAL_ID', 'TK_USER_NAME', 'TK_FOLLOWER_COUNT', 'TK_FOLLOWING_COUNT', 'TK_Engagement', 'TK_ENGAGEMENT_RATE', 'TK_POST_COUNT', 'TK_TOTAL_VIEWS', 'TK_PROFILE_PICTURE', 'TK_COUNTRY', 'TK_BIO', 'TK_GENDER', 'TK_CITY', 'TK_CATEGORIES', 'XHS_DISPLAY_NAME', 'XHS_SOCIAL_ID', 'XHS_USER_NAME', 'XHS_FOLLOWER_COUNT', 'XHS_FOLLOWING_COUNT', 'XHS_ENGAGEMENT_COUNT', 'XHS_ENGAGEMENT_COUNT_RATE', 'XHS_POST_COUNT', 'XHS_TOTAL_VIEWS', 'XHS_PROFILE_PICTURE', 'XHS_COUNTRY', 'XHS_BIO', 'XHS_GENDER', 'XHS_CITY', 'XHS_CATEGORIES','Keywords'];

        let query = await this.Users.createQueryBuilder('user')
            .leftJoinAndSelect('user.brandIndustries', 'brandIndustries')
            .leftJoinAndSelect('brandIndustries.category', 'category')
            .leftJoinAndSelect('category.translations', 'categoryTranslation', 'categoryTranslation.language_code = :languageCode AND categoryTranslation.deleted_at IS NULL', { languageCode: 'en' })
            .where('user.role = :role', { role: 'brand' })
            //.andWhere('user.id = :id', { id:'5d776101-6e74-4380-adb7-b607c9394c30'})
            .andWhere('DATE(user.created_at) BETWEEN :start_date AND :end_date', { start_date, end_date })
            .select([
                'user.id', 'user.first_name', 'user.last_name', 'user.email', 'brandIndustries', 'category.id', 'category.slug', 'category.title', 'category.is_active',
                'categoryTranslation.id', 'categoryTranslation.title', 'user.company_name', 'user.gender', 'user.business_type', 'user.working_people', 'user.is_subscribed', 'user.is_offline_payment_allowed', 'user.offline_payment_allowed'
            ]);

        if (search) {
            query = query.andWhere(`(CONCAT(user.first_name, ' ', user.last_name) ILIKE :search)`, { search: `%${search}%` });
        }
        if (status) {
            query = query.andWhere(`user.is_active = :status`, { status });
        }
        if (category_id) {
            query = query.andWhere(`category.id = :category_id`, { category_id });
        }

        const brands = await query.orderBy('user.created_at', 'DESC').getMany();
        if (brands.length == 0) {
            throw new HttpException(responseMessages.en.file_manager.no_brand, HttpStatus.BAD_REQUEST);
        }
        // MERGE ALL PROFILE INTO SINGLE ROW AND ADDED INTO WORKSHEET 
        const headers = ["UUID", "FIRST_NAME", "LAST_NAME", "EMAIL", "COUNTRY", "PHONE_NUMBER", "COMPANY_NAME", "GENDER", "BUSINESS_TYPE", "WORKING_PEOPLE", "IS_SUBSCRIBED", "IS_OFFLINE_PAYMENT_ALLOWED", "INDUSTRIES"];
        worksheet.addRow(headers); // Add the headers as the first row
        for (const brand of brands) {
            const categoryTitles = (brand.brandIndustries || [])
                .filter(item => item.category && item.category.is_active)
                .filter(item => item.category.is_active)
                .map((item) => {
                    const translation = item.category.translations && item.category.translations.length > 0 ? item.category.translations[0] : null;
                    return translation ? translation.title : '';
                })
                .filter(title => title !== '')
                .join(", ")

            let row = {
                uuid: brand.id,
                firstName: brand.first_name,
                lastName: brand.last_name,
                email: brand.email,
                country: brand.country,
                phone_number: brand.phone_number,
                company_name: brand.company_name,
                gender: brand.gender,
                business_type: brand.business_type,
                working_people: brand.working_people,
                is_subscribed: brand.is_subscribed ? 'YES' : 'NO',
                is_offline_payment_allowed: brand.offline_payment_allowed,
                Industries: categoryTitles
            }
            worksheet.addRow(Object.values(row));
        }

        // Styling (optional)
        worksheet.getRow(1).font = { bold: true }; // Bold headers
        worksheet.columns.forEach((column) => {
            column.width = 20; // Adjust column width
        });
        const fileName = 'brands-' + moment().format('YYYY-MM-DD');

        // Set response headers
        response.setHeader(
            'Content-Type',
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        );
        response.setHeader('Content-Disposition', `attachment; filename=${fileName}.xlsx`);

        // Write the file to the response
        await workbook.xlsx.write(response);
        response.end();
    }

}