import { Injectable, HttpException, HttpStatus, Res } from "@nestjs/common"
import { InjectRepository } from "@nestjs/typeorm"
import { Repository, Like, Not, In, DataSource, Brackets } from "typeorm"
import { classToPlain } from "class-transformer"
import { responseMessages } from "../../messages/response-messages"
import { emailMessages } from "../../messages/email-messages"
import { BrowseInfluencerRepository } from "./browse-influencer.repository"
import * as bcrypt from "bcryptjs"
import { HttpService } from "@nestjs/axios"
import { Constants } from "../../common/constants"
import { JwtService } from "@nestjs/jwt"
const moment = require("moment-timezone")
import { SendMailService } from "../../common/config/send-mail.service"

// Entities Files
import { UserRoles, Users } from "../../entities/users.entity"
import { BrandIndustries } from "../../entities/brand-industries.entity"
import { KeyType, Settings, SettingType } from "../../entities/settings.entity"

// DTO Files

import { SocialMedias } from "src/entities/social-medias.entity"
import { Categories } from "src/entities/categories.entity"
import { InfluencePostDto } from "./dtos/influencer-post-search.dto"
import { SocialMediaPosts } from "src/entities/social-media-posts.entity"
import { BrandFavorite } from "src/entities/brand-favorit.entity"
import { CampaignInfluenceDto } from "./dtos/campaign-influencer-search.dto"
import { CampaignInfluencer, InfluencerStatus } from "src/entities/campaign_influencer.entity"
import { CampaignRepository } from "../campaign/campaign.repository"
import { Campaign } from "src/entities/campaign.entity"
import { UsersServices } from "src/common/services/users/users.service"
import * as ExcelJS from 'exceljs';
import { ResponseStatus, SearchFrom, SearchLog } from "src/entities/search-logs.entity"
import { filter } from "rxjs"

@Injectable()
export class BrowseInfluencerService {
    constructor(
        private readonly dataSource: DataSource,
        private readonly CampaignRepository: CampaignRepository,
        private readonly UsersServices: UsersServices,

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

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

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

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

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

        @InjectRepository(SearchLog)
        private SearchLog: Repository<SearchLog>,
    ) { }

    /**
     * @description Gets all influencer categories for the specified user.
     *              Throws an error if the user does not exist or if no influencer categories are found.
     */
    async getInfluencer1(authUser: any) {
        const lang = authUser.language || "en"

        const influencerCategories = await this.Users.createQueryBuilder("user")
            .leftJoinAndSelect("user.brandIndustries", "brandIndustry")
            .leftJoinAndSelect("brandIndustry.category", "category", "category.is_active = true")
            .leftJoinAndSelect("category.influencerCategories", "influencerCategory")
            .leftJoinAndSelect("influencerCategory.users", "influencerUser")
            .leftJoinAndSelect("influencerUser.influencerCategories", "influencerCategoryOne")
            .leftJoinAndSelect("influencerCategoryOne.category", "categoryOne", "categoryOne.is_active = true")
            .leftJoinAndSelect('influencerUser.userKeywords', 'userKeywords')
            .leftJoinAndSelect('userKeywords.keywords', 'keywords')
            .leftJoin('keywords.translations', 'keyword_translation_req', 'keyword_translation_req.language_code = :lang AND keyword_translation_req.deleted_at IS NULL', { lang: authUser.language || 'en' })
            .leftJoin('keywords.translations', 'keyword_translation_en', 'keyword_translation_en.language_code = \'en\' AND keyword_translation_en.deleted_at IS NULL')
            .leftJoinAndSelect('keywords.translations', 'keyword_translation',
                '(keyword_translation.language_code = :lang OR (keyword_translation.language_code = \'en\' AND keyword_translation_req.id IS NULL)) AND keyword_translation.deleted_at IS NULL',
                { lang: authUser.language || 'en' })
            .loadRelationCountAndMap(
                "influencerUser.favoritesCount", // The virtual property on the entity
                "influencerUser.favoritedBy", // The relation to count
                "favorites", // Alias for the relation
                (qb) => qb.andWhere("favorites.brand = :brandId", { brandId: authUser.id })
            )
            .select(["user.id", "user.first_name", "user.last_name", "user.display_name", "user.image", "brandIndustry", "category.id", "category.slug", "category.title",
                "influencerCategory.id", "userKeywords", "keywords", "keyword_translation",
                "influencerUser.id", "influencerUser.first_name", "influencerUser.image", "influencerUser.last_name",
                "influencerUser.slug", "influencerUser.platforms", "influencerUser.total_followers",
                "influencerUser.total_following", "influencerUser.engagement_ratio",
                "influencerUser.country", "influencerCategoryOne.id", "categoryOne.id", "categoryOne.slug", "categoryOne.title"])
            .where("user.id = :userId", { userId: authUser.id })
            .andWhere("influencerUser.is_active = true")
            .getOne()

        if (!influencerCategories.brandIndustries) {
            throw new Error("No influencer categories found for the specified user.")
        }

        return { data: influencerCategories.brandIndustries }
    }

    async getInfluencer(query: any, authUser: any) {
        const lang = authUser.language || "en"
        const languageCode = lang
        const brandIndustries = await this.Users.createQueryBuilder("user")
            .leftJoinAndSelect("user.brandIndustries", "brandIndustry")
            .leftJoinAndSelect("brandIndustry.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 })
            .select(["user.id", "user.first_name", "user.last_name", "user.display_name", "user.image", "brandIndustry", "category.id", "category.slug", "category.title", "translation.title", "translation.description", "translation.slug"])
            .where("user.id = :userId", { userId: authUser.id })
            // .andWhere("category.is_active = true")
            .getOne()

        for (let brandIndustry of brandIndustries.brandIndustries) {
            const categoryId = brandIndustry?.category?.id;

            if (!categoryId) continue;

            brandIndustry['category']['influencerCategories1'] = await this.Users.createQueryBuilder("user")
                .leftJoinAndSelect("user.influencerCategories", "influencerCategory")
                .leftJoinAndSelect("influencerCategory.category", "category", "category.is_active = true")
                .leftJoin("category.translations", "translation_req1", "translation_req1.language_code = :languageCode AND translation_req1.deleted_at IS NULL", { languageCode })
                .leftJoinAndSelect("category.translations", "translation",
                    "(translation.language_code = :languageCode OR (translation.language_code = 'en' AND translation_req1.id IS NULL)) AND translation.deleted_at IS NULL",
                    { languageCode })
                .leftJoinAndSelect('user.userKeywords', 'userKeywords')
                .leftJoin('user.social_medias', 'social_medias')
                .leftJoinAndSelect('userKeywords.keywords', 'keywords')
                .leftJoin('keywords.translations', 'keyword_translation_req1', 'keyword_translation_req1.language_code = :languageCode AND keyword_translation_req1.deleted_at IS NULL', { languageCode })
                .leftJoin('keywords.translations', 'keyword_translation_en1', 'keyword_translation_en1.language_code = \'en\' AND keyword_translation_en1.deleted_at IS NULL')
                .leftJoinAndSelect('keywords.translations', 'keyword_translation1',
                    '(keyword_translation1.language_code = :languageCode OR (keyword_translation1.language_code = \'en\' AND keyword_translation_req1.id IS NULL)) AND keyword_translation1.deleted_at IS NULL',
                    { languageCode })
                .select(["user.id", "user.first_name", "user.image", "user.last_name",
                    "user.slug", "user.platforms", "user.total_followers", "user.display_name",
                    "user.total_following", "user.engagement_ratio", "user.country",
                    "influencerCategory.id", "category.id", "category.slug", "category.title", "translation.title", "translation.description", "translation.slug",
                    "userKeywords", "keywords", "keyword_translation1",
                    "social_medias.id", "social_medias.social_user_name", "social_medias.social_type"])
                .loadRelationCountAndMap(
                    "user.favoritesCount", // The virtual property on the entity
                    "user.favoritedBy", // The relation to count
                    "favorites", // Alias for the relation
                    (qb) => qb.andWhere("favorites.brand = :brandId", { brandId: authUser.id })
                )
                .where('influencerCategory.category = :category_id', { category_id: categoryId })
                .andWhere("user.is_active = true")
                .andWhere("user.role = 'influencer'")
                .orderBy("user.engagement_ratio", 'DESC')
                .take(10)
                .getMany()
        }

        if (!brandIndustries.brandIndustries) {
            throw new Error("No influencer categories found for the specified user.")
        }

        return { data: brandIndustries.brandIndustries }
    }

    async risingFast(queryParam, authUser: any) {
        const lang = authUser.language || "en"
        const page = queryParam.page - 1
        let pageSize = queryParam.per_page ? queryParam.per_page : Constants.categories_list
        const offset = page * pageSize
        const nextPage = page + 1
        const nextOffset = nextPage * pageSize
        let limitReached = 1
        const languageCode = lang
        const permission = await this.UsersServices.getUserPermission(authUser.id, 'browse_influencers');
        //  if (permission.subscription.plan.plan_type === 'free') {
        const requestedLimit = (page + 1) * pageSize;
        const remainingLimit = permission.limit - offset;
        if (requestedLimit >= permission.limit) {
            pageSize = Math.max(0, remainingLimit);
            limitReached = 0;
        }
        //  }

        const settings = await this.Settings.find({ where: { setting_type: SettingType.TRENDING_INFLUENCER } });

        const trendingFollowerCount = parseInt(settings.filter(setting => setting.type === KeyType.TRENDING_FOLLOWER_COUNT)[0].value);
        const trendingEngagementCount = parseFloat(settings.filter(setting => setting.type === KeyType.TRENDING_ENGAGEMENT_COUNT)[0].value);
        const trendingPercentage = parseFloat(settings.filter(setting => setting.type === KeyType.TRENDING_PERCENTAGE)[0].value);

        let query = this.Users.createQueryBuilder("user")
            .select(["user.id", "user.first_name", "user.image", "user.last_name",
                "user.slug", "user.platforms", "user.total_followers", "user.engagement_rate_percentage",
                "user.total_following", "user.engagement_ratio", "user.country", "user.display_name",
                "influencerCategory.id", "category.id", "category.slug", "category.title", "translation.title", "translation.description", "translation.slug",
                "social_medias.id", "social_medias.social_user_name", "social_medias.social_type", "keyword_translation2"])
            .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.userKeywords', 'userKeywords')
            .leftJoin('user.social_medias', 'social_medias')
            .leftJoinAndSelect('userKeywords.keywords', 'keywords')
            .leftJoin('keywords.translations', 'keyword_translation_req2', 'keyword_translation_req2.language_code = :languageCode AND keyword_translation_req2.deleted_at IS NULL', { languageCode })
            .leftJoin('keywords.translations', 'keyword_translation_en2', 'keyword_translation_en2.language_code = \'en\' AND keyword_translation_en2.deleted_at IS NULL')
            .leftJoinAndSelect('keywords.translations', 'keyword_translation2',
                '(keyword_translation2.language_code = :languageCode OR (keyword_translation2.language_code = \'en\' AND keyword_translation_req2.id IS NULL)) AND keyword_translation2.deleted_at IS NULL',
                { languageCode })
            .loadRelationCountAndMap(
                "user.favoritesCount", // The virtual property on the entity
                "user.favoritedBy", // The relation to count
                "favorites", // Alias for the relation
                (qb) => qb.andWhere("favorites.brand = :brandId", { brandId: authUser.id })
            ).where("user.is_active = true")
            .andWhere("user.role = 'influencer'")
            // .andWhere("category.is_active = true")
            .andWhere("user.total_followers >= :trendingFollowerCount", { trendingFollowerCount })
            .andWhere("user.engagement_ratio >= :trendingEngagementCount", { trendingEngagementCount })
            // .andWhere("user.engagement_rate_percentage >= :trendingPercentage", { trendingPercentage })
            .orderBy("user.engagement_rate_percentage", queryParam.sort);

        const [results, total] = await query.skip(offset).take(pageSize).getManyAndCount();
        // GET THE USER THREE LATEST POST IF LIST VIEW
        if (queryParam.is_list_view) {
            for (const user of results) {
                user.socialMediaPosts = await this.SocialMediaPosts.createQueryBuilder("socialMediaPosts").where("socialMediaPosts.users.id = :userId", { userId: user.id }).andWhere("socialMediaPosts.created_time <> ''").orderBy("DATE(socialMediaPosts.created_time)", "DESC").limit(3).getMany()
            }
        }
        return {
            data: results,
            total: total,
            hasMoreResults: total > nextOffset ? 1 : 0,
            limit: permission.limit,
            limitReached: limitReached
        }

    }

    async chatgptSearch(queryParam) {
        const lang = "en"
        const page = queryParam.page - 1
        const pageSize = queryParam.per_page ? queryParam.per_page : Constants.categories_list
        const offset = page * pageSize
        const nextPage = page + 1
        const nextOffset = nextPage * pageSize
        const languageCode = lang
        // CHECK PERMISSION
        //  await this.dataSource.query(`SELECT set_limit(0.40)`)
        let query = await this.commonQuery(languageCode);
        if (queryParam.search) {
            query = query.andWhere(`(translation_req.title ILIKE :search
                    OR CONCAT(user.first_name, ' ', user.last_name) ILIKE :search
                    OR user.display_name ILIKE :search
                    OR user.about_me ILIKE :search
                    OR keyword_translation_req.keyword ILIKE :search
                    OR social_medias.social_user_name ILIKE :search 
                    OR social_medias.display_name ILIKE :search 
                    OR social_medias.city_name ILIKE :search
                    OR social_medias.description ILIKE :search)`, { search: `%${queryParam.search}%` })
        }

        if (queryParam.category) {
            query = query.andWhere(`translation.title ILIKE :category`, { category: `%${queryParam.category}%` })
        }

        if (queryParam?.platforms?.length) {
            const platformArr = queryParam.platforms.map((item) => item.value)
            query = query.andWhere(`user.platforms ?| array[:...platformArr]`, { platformArr: platformArr })
        }

        if (queryParam?.country) {
            query = query.andWhere(`user.country ILIKE :country`, { country: `%${queryParam.country}%` })
        }
        if (queryParam?.gender) {
            query = query.andWhere(`user.gender ILIKE :gender`, { gender: `%${queryParam.gender}%` })
        }
        if (queryParam.min_followers && queryParam.max_followers) {
            query = query.andWhere(new Brackets((qb) => {
                qb.orWhere(`user.total_followers BETWEEN :from1 AND :to1`, {
                    [`from1`]: queryParam.min_followers,
                    [`to1`]: queryParam.max_followers,
                })
            }))
        }
        if (queryParam.min_engagement && queryParam.max_engagement) {
            query = query.andWhere(new Brackets((qb) => {
                qb.orWhere(`user.engagement_ratio BETWEEN :from2 AND :to2`, {
                    [`from2`]: queryParam.min_engagement,
                    [`to2`]: queryParam.max_engagement,
                })
            }))
        }
        const sortByMap = {
            total_followers: "user.total_followers",
            engagement_ratio: "user.engagement_ratio",
            created_at: "user.created_at",
            display_name: "user.display_name",
        };

        const requestedSortBy = String(queryParam?.sort_by || "total_followers");
        const sortBy = sortByMap[requestedSortBy] || sortByMap.total_followers;
        const sortType = String(queryParam?.sort || "DESC").toUpperCase() === "ASC" ? "ASC" : "DESC";

        const results = await query.orderBy(sortBy, sortType).skip(offset).take(pageSize).getMany()
        return results
    }

    /**
     * Top social posts for an influencer: rows linked by `social_media_posts.users` OR by
     * `social_media_posts.socialMedia` → `social_medias.users` (covers legacy / partial FKs).
     * Sorted by likes + comments in application code to avoid DB type cast issues.
     */
    async getTopSocialPostsForUser(userId: string, limit = 10) {
        const cap = Math.min(500, Math.max(limit * 50, 100))
        const rows = await this.SocialMediaPosts.createQueryBuilder("socialMediaPosts")
            .leftJoin("socialMediaPosts.users", "postUser")
            .leftJoin("socialMediaPosts.socialMedia", "postSm")
            .leftJoin("postSm.users", "smOwner")
            .where(
                new Brackets((qb) => {
                    qb.where("postUser.id = :userId", { userId }).orWhere(
                        "smOwner.id = :userId",
                        { userId }
                    )
                })
            )
            .andWhere("socialMediaPosts.deleted_at IS NULL")
            .orderBy("socialMediaPosts.created_at", "DESC")
            .limit(5)
            .getMany()

        const score = (p: SocialMediaPosts) =>
            Number((p as any).like_count ?? 0) + Number((p as any).comment_count ?? 0)
        rows.sort((a, b) => score(b) - score(a))
        return rows.slice(0, limit)
    }

    /**
     * Single influencer profile for MCP / ChatGPT apps, with top social posts by engagement (likes + comments).
     */
    async chatgptInfluencerDetail(params: { slug?: string | null; id?: string | null }) {
        const languageCode = "en"
        const slug = typeof params.slug === "string" ? params.slug.trim() : ""
        const id = typeof params.id === "string" ? params.id.trim() : ""
        if (!slug && !id) return null

        let query = await this.commonQuery(languageCode)
        query = query.addSelect("user.about_me")
        if (slug) {
            query = query.andWhere("user.slug = :slug", { slug })
        } else {
            query = query.andWhere("user.id = :id", { id })
        }

        const influencer = await query.getOne()
        if (!influencer) return null

        const topPosts = await this.getTopSocialPostsForUser(influencer.id, 10)

        return { influencer, topPosts }
    }

    async getInfluencerSearchBar(queryParam, authUser: any) {
        const lang = authUser.language || "en"
        const page = queryParam.page - 1
        const pageSize = queryParam.per_page ? queryParam.per_page : Constants.categories_list
        const offset = page * pageSize
        const nextPage = page + 1
        const nextOffset = nextPage * pageSize
        const languageCode = lang
        this.saveSearchLog({ search: queryParam.search }, authUser.id, 'search_by_name');
        // CHECK PERMISSION
        await this.checkPermission(authUser.id);
        //  await this.dataSource.query(`SELECT set_limit(0.40)`)
        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.userKeywords", "userKeywords")
            .leftJoinAndSelect("userKeywords.keywords", "keyword")
            .leftJoin("keyword.translations", "keyword_translation_req", "keyword_translation_req.language_code = :languageCode AND keyword_translation_req.deleted_at IS NULL", { languageCode })
            .leftJoin("keyword.translations", "keyword_translation_en", "keyword_translation_en.language_code = 'en' AND keyword_translation_en.deleted_at IS NULL")
            .leftJoinAndSelect("keyword.translations", "keyword_translation",
                "(keyword_translation.language_code = :languageCode OR (keyword_translation.language_code = 'en' AND keyword_translation_req.id IS NULL)) AND keyword_translation.deleted_at IS NULL",
                { languageCode })
            .leftJoinAndSelect("user.social_medias", "social_medias")
            .where("user.role = :role", { role: "influencer" })
            .andWhere("user.is_active = true")
            .andWhere("user.total_followers > 0")
            // .andWhere("category.is_active = true")
            .select(["user.id", "user.first_name", "user.last_name", "user.display_name", "user.slug", "user.image", "user.total_followers", "user.engagement_ratio",
                "influencerCategory", "category.id", "category.slug", "category.title", "translation.title", "translation.description", "translation.slug",
                "userKeywords", "keyword", "keyword_translation"])
            .orderBy("user.engagement_ratio", "DESC")
        if (queryParam.search) {
            query = query.andWhere(`(translation.title ILIKE :search 
                OR CONCAT(user.first_name, ' ', user.last_name) ILIKE :search 
                OR user.display_name ILIKE :search
                OR COALESCE(keyword_translation_req.keyword, keyword_translation_en.keyword, '') ILIKE :search 
                OR social_medias.social_user_name ILIKE :search 
                OR social_medias.display_name ILIKE :search 
                OR social_medias.city_name ILIKE :search
                OR social_medias.description ILIKE :search )`,
                { search: `%${queryParam.search}%` })
        }


        const results = await query.skip(offset).take(pageSize).getMany()
        return {
            data: results,
            // total: total,
            // hasMoreResults: total > nextOffset ? 1 : 0
        }
    }

    async checkPermission(userId: string, type: string = 'browse_influencers_time_period') {
        //try {
        const isBrowseInfluencerTime = await this.UsersServices.checkPermissionWithTimeLimit(userId, type);
        if (!isBrowseInfluencerTime) {
            const message = type === 'export_influencer_time_limit_time_period' ? responseMessages.en.permission.export_limit_reached : responseMessages.en.permission.browse_influencers_time_period;
            throw new HttpException(message, HttpStatus.BAD_REQUEST);
        }
        // } catch (error) {
        //     throw new HttpException(responseMessages.en.permission.permission_not_exists, HttpStatus.BAD_REQUEST);
        // }

        // const isBrowseInfluencer = await this.UsersServices.checkPermission(userId, 'browse_influencers');
        // if (!isBrowseInfluencer) {
        //     throw new HttpException(responseMessages.en.permission.browse_influencers, HttpStatus.BAD_REQUEST);
        // }
    }

    async favorite(body: any, authUser: any) {
        const { influencer_id } = body
        const lang = authUser.language || "en"

        const influencer = await this.Users.findOne({ where: { id: influencer_id } })
        if (!influencer) {
            throw new HttpException(responseMessages[lang].user.user_not_exists, HttpStatus.NOT_FOUND)
        }

        const favoriteCondition = {
            brand: { id: authUser.id },
            influencer: { id: influencer.id }
        }

        const isFavorited = await this.BrandFavorite.count({ where: favoriteCondition })
        if (isFavorited) {
            await this.BrandFavorite.delete(favoriteCondition)
            return
        }

        this.BrandFavorite.save({
            brand: authUser,
            influencer: influencer
        })
        return
    }

    async getFavorite(params: any, authUser: any) {
        const lang = authUser.language || "en"
        const page = params.page - 1
        const pageSize = params.per_page ? params.per_page : Constants.categories_list
        const offset = page * pageSize
        const nextPage = page + 1
        const nextOffset = nextPage * pageSize
        const languageCode = authUser.language || "en"

        let query = await this.BrandFavorite.createQueryBuilder("favorite")
            .leftJoinAndSelect("favorite.influencer", "influencer")
            .leftJoinAndSelect("influencer.influencerCategories", "categories")
            .leftJoin('influencer.social_medias', 'social_medias')
            .leftJoinAndSelect("categories.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 })
            .loadRelationCountAndMap("influencer.favoritesCount", "influencer.favoritedBy", "favorite", (qb) => qb.andWhere("favorite.brand = :brandId", { brandId: authUser.id }))
            .select(["favorite.id", "favorite.createdAt", "influencer.id", "influencer.slug", "influencer.display_name", "influencer.total_followers", "influencer.total_following", "influencer.first_name", "influencer.last_name", "influencer.image", "influencer.country", "influencer.platforms", "influencer.engagement_ratio", "categories.id", "category.id", "category.slug", "category.title", "translation.title", "translation.description", "translation.slug", "social_medias.id", "social_medias.social_user_name", "social_medias.social_type"])
            .where("favorite.brand = :brandId", { brandId: authUser.id })
            .andWhere("influencer.is_active = true")
            //.andWhere("category.is_active = true")
            .orderBy("favorite.createdAt", "DESC")
        if (params.search) {
            query = query.andWhere(`(CONCAT(influencer.first_name, ' ', influencer.last_name) ILIKE :search OR influencer.display_name ILIKE :search)`, { search: `%${params.search}%` })
        }
        if (params.campaign_id) {

            query = query.loadRelationCountAndMap("influencer.isCampaignAdded",
                "influencer.campaignInfluencer",
                "campaignInf",
                (qb) => qb.andWhere("(campaignInf.campaign_id = :campaignId OR campaignInf.campaign_id IS NULL)", { campaignId: params.campaign_id }));

            query = query.loadRelationCountAndMap("influencer.isBookmarkAdded",
                "influencer.campaignBookmarks",
                "campaignBookmarks",
                (qb) => qb.andWhere("(campaignBookmarks.campaign_id = :campaignId OR campaignBookmarks.campaign_id IS NULL)", { campaignId: params.campaign_id }));
            query = query.leftJoinAndSelect('influencer.campaignBookmarks', 'campaignBookmarks', 'campaignBookmarks.campaign_id = :campaignId', { campaignId: params.campaign_id });

            query.loadRelationCountAndMap(
                "influencer.isArchived", // The virtual property on the entity
                "influencer.campaignInfluencer", // The relation to count
                "campaignInfluencer", // Alias for the relation
                (qb) => qb.andWhere("campaignInfluencer.campaign_id = :campaignId", { campaignId: params.campaign_id }).andWhere("campaignInfluencer.is_archived = true")
            )
        }

        const [results, total] = await query.skip(offset).take(pageSize).getManyAndCount();
        if (params.is_list_view) {
            for (const user of results) {
                user.influencer.socialMediaPosts = await this.SocialMediaPosts.createQueryBuilder("socialMediaPosts").where("socialMediaPosts.users.id = :userId", { userId: user.influencer.id }).andWhere("socialMediaPosts.created_time <> ''").orderBy("DATE(socialMediaPosts.created_time)", "DESC").limit(3).getMany()
            }
        }
        return {
            data: results,
            total: total,
            hasMoreResults: total > nextOffset ? 1 : 0
        }
    }

    async exportFavorite(authUser: any, @Res() response: any) {
        await this.checkPermission(authUser.id, 'export_influencer_time_limit_time_period');
        const permission = await this.UsersServices.getUserPermission(authUser.id, 'export_influencer_limit');
        if (permission.limit <= 0) {
            throw new HttpException(responseMessages.en.permission.permission_not_exists, HttpStatus.BAD_REQUEST);
        }
        const lang = authUser.language || "en"
        const languageCode = lang
        const workbook = new ExcelJS.Workbook();
        const worksheet = workbook.addWorksheet('Favorite Influencer');
        // 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 = ["Name", "Profile Engagement", "Category", "Bio", "Profile URL",
            "Instagram URL", "Facebook URL", "Youtube URL", "TikTok URL", "Xiaohongshu URL", "Profile Picture", "Keywords",
            "Instagram User Name", "Instagram Follower Count", "Instagram Engagement Rate", "Instagram Post Count",
            "Facebook User Name", "Facebook Follower Count", "Facebook Engagement Rate", "Facebook Post Count",
            "Youtube User Name", "Youtube Follower Count", "Youtube Engagement Rate", "Youtube Post Count",
            "TikTok User Name", "TikTok Follower Count", "TikTok Engagement Rate", "TikTok Post Count",
            "Xiaohongshu User Name", "Xiaohongshu Follower Count", "Xiaohongshu Following Count", "Xiaohongshu Engagement Rate", "Xiaohongshu Post Count",
        ];

        worksheet.addRow(headers); // Add the headers as the first row

        let query = await this.BrandFavorite.createQueryBuilder("favorite")
            .innerJoinAndSelect("favorite.influencer", "influencer")
            .leftJoinAndSelect("influencer.influencerCategories", "categories")
            .leftJoinAndSelect('influencer.social_medias', 'social_medias')
            .leftJoinAndSelect("categories.category", "category")
            .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('influencer.facebookPages', 'facebookPages')
            .leftJoinAndSelect('influencer.instagramProfiles', 'instagramProfiles')
            .leftJoinAndSelect('influencer.youtubeChannels', 'youtubeChannels')
            .leftJoinAndSelect('influencer.tikTokProfiles', 'tikTokProfiles')
            .leftJoinAndSelect('influencer.xiaohongshuProfiles', 'xiaohongshuProfiles')
            .leftJoinAndSelect('influencer.userKeywords', 'userKeywords')
            .leftJoinAndSelect('userKeywords.keywords', 'keywords')
            .leftJoin('keywords.translations', 'keyword_translation_req3', 'keyword_translation_req3.language_code = :languageCode AND keyword_translation_req3.deleted_at IS NULL', { languageCode })
            .leftJoin('keywords.translations', 'keyword_translation_en3', 'keyword_translation_en3.language_code = \'en\' AND keyword_translation_en3.deleted_at IS NULL')
            .leftJoinAndSelect('keywords.translations', 'keyword_translation3',
                '(keyword_translation3.language_code = :languageCode OR (keyword_translation3.language_code = \'en\' AND keyword_translation_req3.id IS NULL)) AND keyword_translation3.deleted_at IS NULL',
                { languageCode })
            .where("favorite.brand = :brandId", { brandId: authUser.id })

        const influencerArr = await query
            .take(permission.limit)
            .orderBy('favorite.createdAt', 'ASC')
            .getMany();

        if (influencerArr.length > 0) {
            for (let data of influencerArr) {
                await this.mergeAllProfile(data.influencer, worksheet);

            }
            worksheet.getRow(1).font = { bold: true }; // Bold headers
            worksheet.columns.forEach((column) => {
                column.width = 20; // Adjust column width
            });
            const fileName = 'favorite-influencer-' + 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();
            return;
        }
        throw new HttpException(responseMessages.en.file_manager.no_favorite_influencer, HttpStatus.NOT_FOUND);
    }
    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(media.engagement_rate);

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

        return engagementMap;
    }

    async mergeAllProfile(influencer, worksheet) {
        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;
                return translation ? translation.title : '';
            })
            .filter(title => title !== '')
            .join(", ")

        const keywords = influencer.userKeywords
            .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(", ")
        const engagementMap = await this.getSimpleEngagementMap(influencer.social_medias);

        let row = {
            firstName: influencer.display_name,
            engagementRate: influencer.engagement_ratio,
            category: categoryTitles,
            bio: influencer.about_me,
            profileUrl: process.env.FRONTEND_URL + 'influencers/' + influencer.slug,
            InstagramUrl: influencer?.instagramProfiles?.[0]?.url || '',
            FacebookUrl: influencer?.facebookPages?.[0]?.url || '',
            YoutubeUrl: influencer?.youtubeChannels?.[0]?.url || '',
            TikTokUrl: influencer?.tikTokProfiles?.[0]?.url || '',
            xiaohongshuUrl: influencer?.xiaohongshuProfiles?.[0]?.url || '',
            ProfileImage: influencer.image,
            keywords: keywords,
            // INSTAGRAM 

            IG_User_Name: influencer?.instagramProfiles?.[0]?.user_name || '',
            IG_Followers: influencer?.instagramProfiles?.[0]?.followers_count || '',
            IG_Engagement_Rate: engagementMap['instagram'] || '', //influencer?.instagramProfiles?.[0]?.engagement_rate || '',
            IG_POST_COUNT: influencer?.instagramProfiles?.[0]?.media_count || '',

            // FACEBOOK

            FB_USER_NAME: influencer?.facebookPages?.[0]?.user_name || '',
            FB_FOLLOWER_COUNT: influencer?.facebookPages?.[0]?.followers_count || '',
            FB_ENGAGEMENT_RATE: engagementMap['facebook'] || '',
            FB_POST_COUNT: influencer?.facebookPages?.[0]?.post_count || '',

            // YouTube Data

            YT_USER_NAME: influencer?.youtubeChannels?.[0]?.channel_name || '',
            YT_FOLLOWER_COUNT: influencer?.youtubeChannels?.[0]?.subscriber_count || '',
            YT_ENGAGEMENT_RATE: engagementMap['youtube'] || '',
            YT_POST_COUNT: influencer?.youtubeChannels?.[0]?.video_count || '',

            // TikTok Data
            TK_USER_NAME: influencer?.tikTokProfiles?.[0]?.username || '',
            TK_FOLLOWER_COUNT: influencer?.tikTokProfiles?.[0]?.follower_count || '',
            TK_ENGAGEMENT_RATE: engagementMap['tiktok'] || '',
            TK_POST_COUNT: influencer?.tikTokProfiles?.[0]?.video_count || '',

            // XiaohongshuProfiles Data

            XHS_USER_NAME: influencer?.xiaohongshuProfiles?.[0]?.user_name || '',
            XHS_FOLLOWER_COUNT: influencer?.xiaohongshuProfiles?.[0]?.follower_count || '',
            XHS_ENGAGEMENT_COUNT_RATE: engagementMap['xiaohongshu'] || '',
            XHS_POST_COUNT: influencer?.xiaohongshuProfiles?.[0]?.post_count || '',

        };
        worksheet.addRow(Object.values(row)); // Add the row to the worksheet
    }


    async search(request: InfluencePostDto, authUser) {
        const lang = authUser.language || "en"
        const page = request.page - 1
        let pageSize = request.per_page ? request.per_page : Constants.categories_list
        let offset = page * pageSize
        const nextPage = page + 1
        const nextOffset = nextPage * pageSize
        let limitReached = 1
        // CREATE COMMON QUERY
        const languageCode = lang
        let query = await this.commonQuery(languageCode)

        const permission = await this.UsersServices.getUserPermission(authUser.id, 'browse_influencers');
        //   if (permission.subscription.plan.plan_type === 'free') {
        const requestedLimit = (page + 1) * pageSize;
        const remainingLimit = permission.limit - offset;
        if (requestedLimit >= permission.limit) {
            pageSize = Math.max(0, remainingLimit);
            limitReached = 0;
        }
        //  }

        query.loadRelationCountAndMap(
            "user.favoritesCount", // The virtual property on the entity
            "user.favoritedBy", // The relation to count
            "favorites", // Alias for the relation
            (qb) => qb.andWhere("favorites.brand = :brandId", { brandId: authUser.id })
        )

        let sortByArr = {
            created_at: "user.created_at",
            engagement_ratio: "user.engagement_ratio",
            total_followers: "user.total_followers",
            name: "user.display_name"
        }

        const sort_by = sortByArr[request.sort_by] || sortByArr.engagement_ratio
        const sort_type = request.sort === "ASC" || request.sort === "DESC" ? request.sort : "ASC"

        query = query.orderBy(sort_by, sort_type)

        // CREATE COMMON FILTER
        query = await this.commonFilter(query, request, authUser.id)

        const [results, total] = await Promise.all([
            query.skip(offset).take(pageSize).getMany(),
            query.getCount() // Or optimized separate count query
        ]);
        //   const [results, total] = await query.skip(offset).take(pageSize).getManyAndCount()
        // GET THE USER THREE LATEST POST IF LIST VIEW
        if (request.is_list_view) {
            for (const user of results) {
                user.socialMediaPosts = await this.SocialMediaPosts.createQueryBuilder("socialMediaPosts").where("socialMediaPosts.users.id = :userId", { userId: user.id }).andWhere("socialMediaPosts.created_time <> ''").orderBy("DATE(socialMediaPosts.created_time)", "DESC").limit(3).getMany()
            }
        }
        return {
            data: results,
            total: total,
            hasMoreResults: total > nextOffset ? 1 : 0,
            limit: permission.limit,
            limitReached: limitReached
        }
    }

    async commonQuery(languageCode) {
        return 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.userKeywords", "userKeywords")
            .leftJoinAndSelect("userKeywords.keywords", "keywords")
            .leftJoin("keywords.translations", "keyword_translation_req", "keyword_translation_req.language_code = :languageCode AND keyword_translation_req.deleted_at IS NULL", { languageCode })
            .leftJoin("keywords.translations", "keyword_translation_en", "keyword_translation_en.language_code = 'en' AND keyword_translation_en.deleted_at IS NULL")
            .leftJoinAndSelect("keywords.translations", "keyword_translation",
                "(keyword_translation.language_code = :languageCode OR (keyword_translation.language_code = 'en' AND keyword_translation_req.id IS NULL)) AND keyword_translation.deleted_at IS NULL",
                { languageCode })
            .leftJoinAndSelect("user.social_medias", "social_medias")
            .where("user.role = :role", { role: "influencer" })
            .andWhere("user.is_active = true")
            // .andWhere("user.total_followers > 0")
            //.andWhere("category.is_active = true")
            .select(["user.id", "user.first_name", "user.last_name", "user.platforms", "user.created_at", "user.display_name",
                "user.image", "user.slug", "user.total_followers", "user.total_following", "user.engagement_ratio",
                "user.country", "influencerCategory",
                "category.id", "category.slug", "category.title", "translation.title", "translation.description", "translation.slug", "social_medias.id", "social_medias.social_user_name", "social_medias.social_type"])
    }

    async saveSearchLog(request, userId, action) {
        let isBrowseInfluencerTime = false;
        const permission = await this.UsersServices.getUserPermission(userId, 'browse_influencers_time_period');

        if (permission.expires_at === null || moment().isAfter(permission.expires_at)) {
            isBrowseInfluencerTime = true;
        }

        if (permission.remaining) {
            isBrowseInfluencerTime = true;
        }


        const save = {
            user: { id: userId },
            search: request.search,
            filters: request,
            action: action,
            search_from: request?.campaign_id ? SearchFrom.FROM_CAMPAIGN : SearchFrom.FROM_BROWSE_INFLUENCER,
            response_status: isBrowseInfluencerTime ? ResponseStatus.FETCHED : ResponseStatus.FETCH_LIMIT_EXCEEDED
        }

        await this.SearchLog.save(save)
    }

    async commonFilter(query, request, userId) {
        const action = request?.page === 1 ? 'search' : 'load_more';
        this.saveSearchLog(request, userId, action)
        let isFiltered = false;
        if (request.search) {
            query = query.andWhere(`(category.title ILIKE :search
                    OR CONCAT(user.first_name, ' ', user.last_name) ILIKE :search
                    OR user.display_name ILIKE :search
                    OR user.about_me ILIKE :search
                    OR keyword_translation_req.keyword ILIKE :search
                    OR social_medias.social_user_name ILIKE :search 
                    OR social_medias.display_name ILIKE :search 
                    OR social_medias.city_name ILIKE :search
                    OR social_medias.description ILIKE :search)`, { search: `%${request.search}%` })
            isFiltered = true
        }

        if (request.category_id) {
            query = query.andWhere(`category.id = :category_id`, { category_id: `${request.category_id}` })
        }

        if (request.filteredValue) {
            const filteredValue = request.filteredValue
            if (filteredValue?.platforms?.length) {
                const platformArr = filteredValue.platforms.map((item) => item.value)
                query = query.andWhere(`user.platforms ?| array[:...platformArr]`, { platformArr: platformArr })
                isFiltered = true
            }

            if (filteredValue?.skills?.length) {
                const skillsArr = filteredValue.skills.map((item) => item.value)
                // query = query.andWhere(`category.id IN(:...skillsArr)`, { skillsArr });
                // query = query.andWhere(
                //     `EXISTS (SELECT 1 
                //                 FROM influencer_categories ic
                //                 WHERE ic.users = "user".id
                //                 AND ic.category IN (:...skillsArr))`,
                //     { skillsArr }
                // )

                for (let i = 0; i < skillsArr.length; i++) {
                    query = query.andWhere(
                        `EXISTS (
                                SELECT 1 
                                FROM influencer_categories ic
                                WHERE ic.users = "user".id
                                AND ic.category = :skill${i}
                            )`,
                        { [`skill${i}`]: skillsArr[i] }
                    );
                }
                isFiltered = true
            }
            if (filteredValue?.keywords?.length) {
                const keywordsArr = filteredValue.keywords.map((item) => item.value)
                // query = query.andWhere(`keywords.id IN(:...keywordsArr)`, { keywordsArr })
                for (let i = 0; i < keywordsArr.length; i++) {
                    query = query.andWhere(
                        `EXISTS (
                            SELECT 1 
                            FROM user_keywords uk
                            WHERE uk."usersId" = "user".id
                            AND uk."keywordsId" = :keyword${i}
                        )`,
                        { [`keyword${i}`]: keywordsArr[i] }
                    );
                }
                isFiltered = true
            }

            if (filteredValue?.gender?.length) {
                const genderArr = filteredValue.gender;
                query = query.andWhere(`user.gender IN(:...genderArr)`, { genderArr })
                isFiltered = true
            }

            if (filteredValue?.country?.length) {
                const countryArr = filteredValue.country.map((item) => item.value)
                query = query.andWhere(`user.country IN(:...countryArr)`, { countryArr })
                isFiltered = true
            }

            if (filteredValue?.followers?.length) {
                query = query.andWhere(new Brackets((qb) => {
                    let index = 1;
                    for (const follower of filteredValue.followers) {
                        qb.orWhere(`user.total_followers BETWEEN :from${index} AND :to${index}`, {
                            [`from${index}`]: follower.from,
                            [`to${index}`]: follower.to,
                        })
                        index++
                    }
                }))
                // query = query.andWhere(`user.total_followers BETWEEN :from AND :to`, { from, to })
                isFiltered = true
            }

            if (filteredValue?.engagement?.length) {
                query = query.andWhere(new Brackets((qb) => {
                    let index = 1;
                    for (const engagement of filteredValue.engagement) {
                        qb.orWhere(`user.engagement_ratio BETWEEN :eng_from${index} AND :eng_to${index}`, {
                            [`eng_from${index}`]: engagement.from,
                            [`eng_to${index}`]: engagement.to,
                        })
                        index++
                    }
                }))
                isFiltered = true
            }
        }

        if (isFiltered) {
            await this.checkPermission(userId);
        }

        return query
    }

    async campaign(request: CampaignInfluenceDto, authUser) {
        const lang = authUser.language || "en"
        const page = request.page - 1
        let pageSize = Constants.browse_influencer_list
        const offset = page * pageSize
        const nextPage = page + 1
        const nextOffset = nextPage * pageSize
        let limitReached = 1
        const languageCode = lang
        let query = await this.commonQuery(languageCode)

        const permission = await this.UsersServices.getUserPermission(authUser.id, 'browse_influencers');
        // if (permission.subscription.plan.plan_type === 'free') {
        const requestedLimit = (page + 1) * pageSize;
        const remainingLimit = permission.limit - offset;
        if (requestedLimit >= permission.limit) {
            pageSize = Math.max(0, remainingLimit);
            limitReached = 0;
        }
        // }

        // CHECK INFLUENCER ADDED OR NOT IN CAMPAIGN
        query.loadRelationCountAndMap(
            "user.isCampaignAdded", // The virtual property on the entity
            "user.campaignInfluencer", // The relation to count
            "campaignInfluencer", // Alias for the relation
            (qb) => qb.andWhere("campaignInfluencer.campaign_id = :campaignId", { campaignId: request.campaign_id })
        )

        query.loadRelationCountAndMap(
            "user.isArchived", // The virtual property on the entity
            "user.campaignInfluencer", // The relation to count
            "campaignInfluencer", // Alias for the relation
            (qb) => qb.andWhere("campaignInfluencer.campaign_id = :campaignId", { campaignId: request.campaign_id }).andWhere("campaignInfluencer.is_archived = true")
        )

        // CHECK INFLUENCER BOOKMAKR ADDED OR NOT IN CAMPAIGN
        query.loadRelationCountAndMap(
            "user.isBookmarkAdded", // The virtual property on the entity
            "user.campaignBookmarks", // The relation to count
            "campaignBookmarks", // Alias for the relation
            (qb) => qb.andWhere("campaignBookmarks.campaign_id = :campaignId", { campaignId: request.campaign_id })
        )
        query = query.leftJoinAndSelect('user.campaignBookmarks', 'campaignBookmarks', 'campaignBookmarks.campaign_id = :campaignId', { campaignId: request.campaign_id });//.addSelect('campaignBookmarks');

        if (request.is_campaign_ad) {
            query.leftJoinAndSelect('user.campaignInfluencer', 'campaignInfluencer')
                .andWhere('campaignInfluencer.status = :influencerStatus', { influencerStatus: InfluencerStatus.QUOTATION_AVAILABLE })
                .andWhere("campaignInfluencer.campaign_id = :campaignId", { campaignId: request.campaign_id })
        }

        let sortByArr = {
            created_at: "user.created_at",
            engagement_ratio: "user.engagement_ratio",
            total_followers: "user.total_followers",
            is_recommended: "user.total_followers",
            application_date: "campaignInfluencer.createdAt",
            name: "user.display_name",
            ai_score: "campaignInfluencer.ai_score"
        }

        const sort_by = sortByArr[request.sort_by] || sortByArr.engagement_ratio
        const sort_type = request.sort === "DESC" ? request.sort : "ASC"

        query = await this.commonFilter(query, request, authUser.id)
        if (request.is_bookmark) {
            // query.leftJoinAndSelect('user.campaignBookmarks', 'campaignBookmarks')
            query.andWhere('campaignBookmarks.campaign_id = :campaignId', { campaignId: request.campaign_id })
        }

        if (request.sort_by == "is_recommended") {
            const campaign = await this.Campaign.findOne({ where: { id: request.campaign_id } });

            if (moment(campaign.recommended_date_time).isBefore(moment())) {
                await this.CampaignRepository.saveRecommendInfluencer(request.campaign_id);
            }
            await query.leftJoin(
                'user.campaignRecommend', 'campaignRecommend', 'campaignRecommend.campaign_id = :campaignId', { campaignId: request.campaign_id }
            )
                .addSelect('campaignRecommend.score', 'campaignRecommend_score')
                .addSelect('campaignRecommend.is_admin_recommended')  // Add alias for clarity
                .orderBy('campaignRecommend.is_admin_recommended', 'DESC', 'NULLS LAST')
                .addOrderBy('campaignRecommend.score', 'DESC', 'NULLS LAST')
                .addOrderBy('user.engagement_ratio', 'DESC');
        } else {
            query = query.orderBy(sort_by, sort_type)
        }

        const [results, total] = await query.skip(offset).take(pageSize).getManyAndCount()
        // GET THE USER THREE LATEST POST IF LIST VIEW
        if (request.is_list_view) {
            for (const user of results) {
                user.socialMediaPosts = await this.SocialMediaPosts.createQueryBuilder("socialMediaPosts")
                    .where("socialMediaPosts.users.id = :userId", { userId: user.id })
                    .andWhere("socialMediaPosts.created_time <> ''")
                    .orderBy("DATE(socialMediaPosts.created_time)", "DESC").limit(3).getMany()
            }
        }
        const campaign = await this.Campaign.findOne({ where: { id: request.campaign_id } });
        return {
            campaign: campaign,
            data: results,
            total: total,
            hasMoreResults: (total > nextOffset ? 1 : 0),
            limit: permission.limit,
            limitReached: limitReached
        }
    }
}