import { Injectable, HttpException, HttpStatus, OnModuleInit } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { DataSource, In, Not, Repository } from 'typeorm';
import { Campaign, CampaignStatus } from 'src/entities/campaign.entity';
import { responseMessages } from 'src/messages/response-messages';
import { UserRoles, Users } from 'src/entities/users.entity';
import { CampaignInfluencer, InfluencerStatus } from 'src/entities/campaign_influencer.entity';
import { Draft } from 'src/entities/draft.entity';
import { DraftVersions } from 'src/entities/draft_versions.entity';
import { DraftComments } from 'src/entities/draft_comments.entity';
import { from } from 'rxjs';
import { Posts } from 'src/entities/posts.entity';
import { SocialMedias } from 'src/entities/social-medias.entity';
import axios from 'axios';
import { query } from 'express';
import { SocketGateway } from 'src/common/services/socket/socket.gateway';
import { Card } from 'src/entities/card.entity';
import { CampaignPayment, PAYMENT_STATUS, PaymentMode } from 'src/entities/campaign_payment.entity';
import { Constants } from 'src/common/constants';
import { CommonService } from '../common/common.services';
import { StripeServices } from 'src/common/services/payment-gateway/stripe/stripe.service';
import { KeyType, Settings, SettingType } from 'src/entities/settings.entity';
import { CampaignPaymentHistory, PAYMENT_STATUS as PaymentType } from 'src/entities/campaign_payment_history.entity';
import { NotificationService } from 'src/common/services/notification/notification.service';
import { notificationMessages } from 'src/messages/notifications-messages';
import { Notifications, RedirectType, Type } from 'src/entities/notifications.entity';
import { uploadFileToS3 } from 'src/common/config/upload-s3-file-using-link';
import { SendMailService } from "src/common/config/send-mail.service"

import * as ejs from 'ejs';
import * as puppeteer from 'puppeteer';
import * as fs from 'fs';
import * as path from 'path';
import { throwError } from "rxjs"
import { Categories } from 'src/entities/categories.entity';
import { CampaignCategory } from 'src/entities/campaign-caregories.entity';
import { CampaignRecommended } from 'src/entities/campaign_recommended.entity';
import { InfluencerCampaignRecommended } from 'src/entities/influencer_campaign_recommended.entity';
import { emailMessages } from 'src/messages/email-messages';
import { EncryptionService } from 'src/common/services/encryption/encryption.service';
import { InstagramServices } from 'src/common/services/instagram/instagram.service';
const moment = require('moment');
import { S3Client, PutObjectCommand } from '@aws-sdk/client-s3';
import { SocialType } from 'src/entities/social-media-posts.entity';
import { NotificationChannel } from 'src/entities/user-notification-preferences.entity';
import { WhatsAppServices } from 'src/common/services/whatsapp/whatsapp.service';
import { notificationSettings } from 'src/messages/notification-settings';
import { chatMessages } from 'src/messages/comet-chat';
import { CategoryTranslation } from 'src/entities/category-translations.entity';
import { CountryRecommendedCategory } from 'src/entities/country-recommended-categories.entity';
import { Countries } from 'src/entities/countries.entity';
import { CampaignCategoryCountryOrder } from 'src/entities/campaign-category-country-order.entity';
@Injectable()
export class CampaignRepository implements OnModuleInit {
    private s3: S3Client;
    public s3Config = {};
    constructor(
        private SocketGateway: SocketGateway,
        private CommonService: CommonService,
        private StripeService: StripeServices,
        private notificationService: NotificationService,
        private readonly dataSource: DataSource,
        private readonly SendMailService: SendMailService,
        private readonly encryptService: EncryptionService,
        private readonly instagramService: InstagramServices,
        private readonly whatsappService: WhatsAppServices,
        @InjectRepository(Campaign)
        private Campaign: Repository<Campaign>,
        @InjectRepository(Users)
        private Users: Repository<Users>,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    async onModuleInit() {
        const AWS_ACCESS_KEY_ID = await this.encryptService.decrypt(process.env.AWS_ACCESS_KEY_ID);
        const AWS_SECRET_ACCESS_KEY = await this.encryptService.decrypt(process.env.AWS_SECRET_ACCESS_KEY);
        this.s3 = new S3Client({
            region: process.env.AWS_DEFAULT_REGION,
            credentials: {
                accessKeyId: AWS_ACCESS_KEY_ID,
                secretAccessKey: AWS_SECRET_ACCESS_KEY,
            },
        });
        this.s3Config = {
            accessKeyId: AWS_ACCESS_KEY_ID,
            secretAccessKey: AWS_SECRET_ACCESS_KEY,
            region: process.env.AWS_DEFAULT_REGION,
        }
    }

    async createCampaign(request: any) {
        const result = await this.Campaign.save(request);
        if (!result) {
            throw new HttpException(responseMessages.en.campaign.campaign_not_created, HttpStatus.BAD_REQUEST);
        }

        await this.saveCampaignCategory(request, result.id);
        const adminMessage = notificationMessages.en.descriptions.new_campaign_created.replace(':userName', `${request.users.first_name} ${request.users.last_name}`).replace(':campaign_name', result.campaign_name);
        this.adminNotification(adminMessage, request.users, RedirectType.CAMPAIGN_LIST, result.id, result.slug);
        this.saveRecommendInfluencer(result.id);
        return;
    }

    async addHotCategory(campaign: Campaign) {
        const hotCategory = await this.CategoryTranslation.findOne({ relations: ['category'], where: { slug: "hot", language_code: "en" } });
        if (hotCategory) {
            const country = await this.Countries.findOne({ where: { two_digit_code: campaign.campaign_country } });
            const countryRecommendedCategory = await this.CountryRecommendedCategory.findOne({ where: { country: { id: country.id }, category: { id: hotCategory.category.id } } });
            if (countryRecommendedCategory.min_application == campaign.applied) {
                const savedCampaignCategory = await this.CampaignCategory.save({
                    category: hotCategory.category,
                    campaign: campaign,
                });

                const allCountries = await this.Countries.find();
                const campaignCountry = campaign.campaign_country
                    ? await this.Countries.findOne({ where: { two_digit_code: campaign.campaign_country } })
                    : null;
                savedCampaignCategory.category = hotCategory.category;
                const countryOrdersToSave: CampaignCategoryCountryOrder[] = [];
                for (const campaignCategory of [savedCampaignCategory]) {
                    const categoryId = campaignCategory.category?.id;
                    if (!categoryId) {
                        continue;
                    }

                    const targetCountries = this.resolveTargetCountries(
                        campaign,
                        campaignCountry,
                        allCountries,
                    );

                    for (const country of targetCountries) {
                        const record = new CampaignCategoryCountryOrder();
                        record.country = country;
                        record.campaignCategory = campaignCategory;
                        record.order_by = await this.getNextCountryCategoryOrder(country.id, categoryId);
                        record.setDefaults();
                        countryOrdersToSave.push(record);
                    }
                }

                if (countryOrdersToSave.length) {
                    await this.CampaignCategoryCountryOrder.save(countryOrdersToSave);
                }

            }
        }
    }

    private resolveTargetCountries(
        campaign: Campaign,
        campaignCountry: Countries | null,
        allCountries: Countries[],
    ): Countries[] {
        if (campaign.is_only_international_influencer) {
            return campaignCountry
                ? allCountries.filter((country) => country.id !== campaignCountry.id)
                : allCountries;
        }

        if (campaign.is_open_to_international_influencer) {
            return allCountries;
        }

        return campaignCountry ? [campaignCountry] : [];
    }

    applyInternationalOnlyVisibilityForInfluencer(query: any, influencerCountry?: string) {
        if (!influencerCountry) {
            return query;
        }

        return query.andWhere(
            '(campaign.is_only_international_influencer = false OR campaign.campaign_country IS DISTINCT FROM :influencerCountry)',
            { influencerCountry },
        );
    }

    async applyInternationalTabHomeCountryExclusion(
        query: any,
        categoryId: string,
        influencerCountry?: string,
    ): Promise<any> {
        if (!influencerCountry) {
            return query;
        }

        const internationalCategory = await this.CategoryTranslation.findOne({
            relations: ['category'],
            where: { slug: 'international', language_code: 'en' },
        });
        const internationalCategoryId = internationalCategory?.category?.id ?? null;

        if (!internationalCategoryId || categoryId !== internationalCategoryId) {
            return query;
        }

        // Home-country campaigns stay hidden on International, unless admin explicitly added the category
        return query.andWhere(
            '(campaignCategory.is_added_by_admin = true OR campaign.campaign_country IS DISTINCT FROM :influencerCountry)',
            { influencerCountry },
        );
    }

    private async deleteCampaignCategoryCountryOrdersForCampaign(
        campaignId: string,
        options?: { categoryId?: string; excludeCountryId?: string; excludeCategoryId?: string },
    ): Promise<void> {
        let subQuery = 'SELECT cc.id FROM campaign_categories cc WHERE cc.campaign_id = :campaignId';
        const parameters: Record<string, string> = { campaignId };

        if (options?.categoryId) {
            subQuery += ' AND cc.category_id = :categoryId';
            parameters.categoryId = options.categoryId;
        }
        if (options?.excludeCategoryId) {
            subQuery += ' AND cc.category_id != :excludeCategoryId';
            parameters.excludeCategoryId = options.excludeCategoryId;
        }

        let query = this.CampaignCategoryCountryOrder
            .createQueryBuilder()
            .delete()
            .from(CampaignCategoryCountryOrder)
            .where(`campaign_category_id IN (${subQuery})`, parameters);

        if (options?.excludeCountryId) {
            query = query.andWhere('country_id != :excludeCountryId', {
                excludeCountryId: options.excludeCountryId,
            });
        }

        await query.execute();
    }

    async getNextCountryCategoryOrder(countryId: string, categoryId: string): Promise<number> {
        const maxOrderResult = await this.CampaignCategoryCountryOrder.createQueryBuilder('cco')
            .innerJoin('cco.campaignCategory', 'cc')
            .select('MAX(cco.order_by)', 'max')
            .where('cco.country_id = :countryId', { countryId })
            .andWhere('cc.category_id = :categoryId', { categoryId })
            .getRawOne();

        return Number(maxOrderResult?.max ?? 0) + 1;
    }

    async getNonRecommendedCategoryIdsByCountry(countryId: string): Promise<string[]> {
        const results = await this.Categories.createQueryBuilder('categories')
            .select('categories.id', 'id')
            .where('categories.deleted_at IS NULL')
            .andWhere('categories.is_active = :is_active', { is_active: true })
            .andWhere((qb) => {
                const subQuery = qb
                    .subQuery()
                    .select('crc.category_id')
                    .from(CountryRecommendedCategory, 'crc')
                    .where('crc.country_id = :countryId', { countryId })
                    .andWhere('crc.deleted_at IS NULL')
                    .getQuery();
                return `categories.id NOT IN ${subQuery}`;
            })
            .setParameter('countryId', countryId)
            .getRawMany();

        return results.map((row) => row.id);
    }

    async saveCampaignCategoryCountryOrders(campaign: Campaign, savedCampaignCategories: CampaignCategory[]) {
        const internationalCategory = await this.CategoryTranslation.findOne({
            relations: ['category'],
            where: { slug: 'international', language_code: 'en' },
        });
        const internationalCategoryId = internationalCategory?.category?.id ?? null;

        const allCountries = await this.Countries.find();
        const campaignCountry = campaign.campaign_country
            ? await this.Countries.findOne({ where: { two_digit_code: campaign.campaign_country } })
            : null;

        if (!campaign.is_open_to_international_influencer && internationalCategoryId) {
            await this.deleteCampaignCategoryCountryOrdersForCampaign(campaign.id, {
                categoryId: internationalCategoryId,
            });
            await this.CampaignCategory.delete({ campaign: { id: campaign.id }, category: { id: internationalCategoryId } });
        }
        // Keep home-country order; remove other countries when campaign is local-only
        if (!campaign.is_open_to_international_influencer && !campaign.is_only_international_influencer && campaignCountry) {
            await this.deleteCampaignCategoryCountryOrdersForCampaign(campaign.id, {
                excludeCountryId: campaignCountry.id,
            });
        }

        if (!savedCampaignCategories.length) {
            return;
        }

        const countryOrdersToSave: CampaignCategoryCountryOrder[] = [];

        for (const campaignCategory of savedCampaignCategories) {
            const categoryId = campaignCategory.category?.id;
            if (!categoryId) {
                continue;
            }

            const targetCountries = this.resolveTargetCountries(
                campaign,
                campaignCountry,
                allCountries,
            );

            for (const country of targetCountries) {
                const record = new CampaignCategoryCountryOrder();
                record.country = country;
                record.campaignCategory = campaignCategory;
                record.order_by = await this.getNextCountryCategoryOrder(country.id, categoryId);
                record.setDefaults();
                countryOrdersToSave.push(record);
            }
        }

        if (countryOrdersToSave.length) {
            await this.CampaignCategoryCountryOrder.save(countryOrdersToSave);
        }
    }

    async saveCampaignCategory(request: any, campaign_id: string) {
        request.categories = request.categories || [];

        const hotCategory = await this.CategoryTranslation.findOne({
            relations: ['category'],
            where: { slug: 'hot', language_code: 'en' },
        });

        const hotCategoryId = hotCategory?.category?.id ?? null;
        const isHotExists = await this.CampaignCategory.findOne({ where: { campaign: { id: campaign_id }, category: { id: hotCategoryId } } });
        if (isHotExists) {
            request.categories.push(hotCategoryId);
        }

        // ADD ALL CATEGORY
        const allCategory = await this.CategoryTranslation.findOne({ relations: ['category'], where: { slug: "all", language_code: "en" } });
        const allCategoryId = allCategory?.category?.id ?? null;
        if (allCategoryId) {
            request.categories.push(allCategoryId);
        }

        // ADD INTERNATIONAL CATEGORY
        const internationalCategory = await this.CategoryTranslation.findOne({ relations: ['category'], where: { slug: "international", language_code: "en" } });
        const internationalCategoryId = internationalCategory?.category?.id ?? null;

        if (request.is_open_to_international_influencer) {
            if (internationalCategoryId && !request.categories.includes(internationalCategoryId)) {
                request.categories.push(internationalCategoryId);
            }
        } else if (internationalCategoryId) {
            request.categories = request.categories.filter((categoryId) => categoryId !== internationalCategoryId);
        }

        const desiredCategoryIds: any[] = [...new Set(request.categories.filter(Boolean))];
        const campaign = await this.Campaign.findOne({ where: { id: campaign_id } });
        const existingCampaignCategories = await this.CampaignCategory.find({
            where: { campaign: { id: campaign_id } },
            relations: ['category'],
        });
        const existingCategoryIds = existingCampaignCategories.map((cc) => cc.category.id);

        const categoryIdsToRemove = existingCategoryIds.filter((id) => !desiredCategoryIds.includes(id));
        const categoryIdsToAdd = desiredCategoryIds.filter((id) => !existingCategoryIds.includes(id));

        for (const categoryId of categoryIdsToRemove) {
            await this.deleteCampaignCategoryCountryOrdersForCampaign(campaign_id, { categoryId });
            await this.CampaignCategory.delete({ campaign: { id: campaign_id }, category: { id: categoryId } });
        }

        const campaignCategoriesToAdd = [];
        for (const categoryId of categoryIdsToAdd) {
            const categoryData = await this.Categories.findOne({ where: { id: categoryId } });
            campaignCategoriesToAdd.push({ category: categoryData, campaign: campaign });
        }

        let newlyAddedCategories: CampaignCategory[] = [];
        if (campaignCategoriesToAdd.length) {
            await this.CampaignCategory.save(campaignCategoriesToAdd);
            newlyAddedCategories = await this.CampaignCategory.find({
                where: {
                    campaign: { id: campaign_id },
                    category: { id: In(categoryIdsToAdd) },
                },
                relations: ['category'],
            });
        }

        await this.saveCampaignCategoryCountryOrders(campaign, newlyAddedCategories);
        return
    }

    async checkCampaignExists(id: string, authUser: any) {
        const result = await this.Campaign.findOne({ 'relations': ['users'], where: { id } });
        if (!result) {
            throw new HttpException(responseMessages.en.campaign.campaign_not_exists, HttpStatus.NOT_FOUND);
        }
        if (result.users.id != authUser?.id && authUser.role == UserRoles.BRAND) {
            throw new HttpException(responseMessages.en.campaign.campaign_not_allowed, HttpStatus.FORBIDDEN);
        }
        return result;
    }

    async checkUserExists(id: string) {
        const result = await this.Users.findOne({ where: { id } });
        if (!result) {
            throw new HttpException(responseMessages.en.user.user_not_exists, HttpStatus.NOT_FOUND);
        }
        return result;
    }



    async checkCampaignInfluencerExists(campaign_id: string, influencer_id: string) {
        const result = await this.CampaignInfluencer.findOne({ where: [{ campaign: { id: campaign_id }, influencer: { id: influencer_id } }] })
        if (!result) {
            throw new HttpException(responseMessages.en.campaign.campaign_influencer_not_exists, HttpStatus.NOT_FOUND)
        }
        return result
    }

    async addDraft(request: any, campaignInfluencer: any) {

        if (request.draft_id) {
            await this.Draft.update(request.draft_id, { is_draft_seen: false });
        }

        let draft = request.draft_id ? await this.Draft.findOne({ where: { id: request.draft_id } }) : null;
        if (!draft) {
            draft = new Draft();
            draft.campaign_influencer = campaignInfluencer;
            draft.campaign = campaignInfluencer.campaign;
            draft.influencer = campaignInfluencer.influencer;
            draft = await this.Draft.save(draft);
        }

        const draftVersions = {
            draft: draft,
            draft_name: request.draft_name,
            thumbnail: request.thumbnail,
            type: request.type,
            description: request.comment
        }
        const result = await this.DraftVersions.save(draftVersions);
        await this.addDraftComment(draft.id, request.comment, campaignInfluencer.influencer);
    }

    async checkDraftExists(draft_id: string) {
        const result = await this.Draft.findOne({ where: { id: draft_id } });
        if (!result) {
            throw new HttpException(responseMessages.en.draft.draft_not_exists, HttpStatus.NOT_FOUND);
        }
        return result;
    }

    async addDraftComment(draft_id: any, comment: any, authUser: any) {
        let draft = await this.Draft.findOne({ where: { id: draft_id } });
        const draftComments = {
            draft: draft,
            comment: comment,
            fromUser: authUser,
        }
        const result = await this.DraftComments.save(draftComments);
    }

    async addPosts(request: any, campaignInfluencer: any) {
        let postArr = [];
        let storedPost = 0;
        for (const post of request.posts) {
            const socialMedias = await this.SocialMedias.findOne({ where: { id: post.social_media_id } });
            const postExist = await this.Posts.findOne({ where: { social_post_id: post.social_post_id, campaign: { id: campaignInfluencer.campaign.id }, influencer: { id: campaignInfluencer.influencer.id } } });
            if (!postExist) {
                if (socialMedias.social_type == SocialType.INSTAGRAM) {
                    const insightsUrl = process.env.INSTAGRAM_BASE_URL + post.social_post_id + '/insights';
                    const insightsParams = {
                        metric: 'views',
                    };
                    const headers = {
                        Authorization: `Bearer ${socialMedias.social_access_token}`,
                    };
                    const instagramMediasResponse = await this.instagramService.getAxios(insightsUrl, headers, insightsParams);
                    if (instagramMediasResponse) {
                        post.view_counts = instagramMediasResponse.data[0]?.values[0]?.value || 0;
                    }
                }
                let division = Number(socialMedias.follower_count); // Avoid division by zero
                if (division == 0) {
                    division = 1;
                }
                const engagementRate = parseFloat((((Number(post.like_counts) + Number(post.comment_count) + Number(post.view_counts)) / division * 100).toFixed(4)));

                postArr.push({
                    campaign: campaignInfluencer.campaign,
                    influencer: campaignInfluencer.influencer,
                    post_link: post.post_link, image_link: post.image_link,
                    campaign_influencer: campaignInfluencer,
                    social_post_id: post.social_post_id,
                    comment_count: post.comment_count,
                    like_counts: post.like_counts,
                    view_counts: post.view_counts,
                    social_media: socialMedias,
                    platform: post.platform,
                    engagement_rate: engagementRate,
                });
                storedPost++;
            }
        }
        await this.Posts.save(postArr);

        if (storedPost > 0) {

        }

        // UPLOAD ALL POST IMAGES ON S3 BUCKET USING LINK
        this.uploadPostOnS3(campaignInfluencer.id)
        return storedPost;
    }
    async uploadPostOnS3(campaign_influencer_id) {
        const posts = await this.Posts.find({ where: { campaign_influencer: { id: campaign_influencer_id }, is_uploaded_on_s3: false } });
        for (const post of posts) {
            const s3Response = await uploadFileToS3(post.image_link, 'files/campaign/posts', this.s3Config);
            console.log(s3Response);
            post.image_link = s3Response.path;
            post.is_uploaded_on_s3 = true;
            await this.Posts.save(post);
        }
    }
    async commonQuery(languageCode, countryId?: string) {
        let query = this.Campaign.createQueryBuilder("campaign").withDeleted()
            .leftJoinAndSelect("campaign.users", "users")
            .leftJoinAndSelect("campaign.campaignCategory", "campaignCategory")
            .leftJoinAndSelect("campaignCategory.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 });

        if (countryId) {
            query = query
                .leftJoin(
                    'campaignCategory.countryOrders',
                    'countryOrder',
                    `countryOrder.deleted_at IS NULL AND countryOrder.country_id IN (
                        SELECT c.id FROM countries c WHERE c.id = :countryId AND c.deleted_at IS NULL
                    )`,
                    { countryId },
                );
        }

        query = query.select([
            "campaign.id",
            "campaign.campaign_name",
            "campaign.platforms",
            "campaign.slug",
            "campaign.currency",
            "campaign.budget_per_influencer",
            "campaign.number_of_influencers",
            "campaign.total_budget",
            "campaign.content_type",
            "campaign.job_privacy",
            "campaign.influencer_type",
            "campaign.job_requirement",
            "campaign.posting_start_date",
            "campaign.posting_end_date",
            "campaign.first_draft_date",
            "campaign.campaign_image",
            "campaign.profile_privacy",
            "campaign.status",
            "campaign.applied",
            "campaign.campaign_requirements",
            "campaign.attachment",
            "campaign.is_open_to_international_influencer",
            "campaign.is_only_international_influencer",
            "campaign.messages",
            "campaign.invited",
            "campaign.hired",
            "campaign.campaign_country",
            "campaign.created_at",
            "campaign.deleted_at",
            "campaign.is_barter",
            "campaign.product_value",
            "campaign.is_active",
            "campaign.ai_criteria",
            "campaign.ai_report_inprogress",
            "users.id",
            "users.slug",
            "users.first_name",
            "users.last_name",
            "users.email",
            "users.image",
            "users.country",
            "users.company_name",
            "users.about_me",
            "users.is_offline_payment_allowed",
            "users.offline_payment_allowed",
            "campaignCategory.id",
            "category.id",
            "category.slug",
            "category.title",
            "translation.title",
            "translation.description",
            "translation.slug"
        ]);

        if (countryId) {
            query = query.addSelect(['countryOrder.id', 'countryOrder.country_id', 'countryOrder.order_by']);
        }

        return query;
    }

    async draftCommonQuery(query) {
        return query.leftJoinAndSelect('campaignInfluencers.drafts', 'drafts', 'drafts.deleted_at IS NULL')
            .leftJoinAndSelect('campaignInfluencers.rejectCompletionRequest', 'rejectCompletionRequest')
            .leftJoinAndSelect('drafts.draftVersions', 'draftVersions')
            .leftJoinAndSelect('campaignInfluencers.posts', 'posts')
            .leftJoinAndSelect('campaignInfluencers.dispute', 'dispute')
            .leftJoinAndSelect('dispute.disputeHistory', 'disputeHistory')
            //   .addSelect('drafts','draftVersions','posts','rejectCompletionRequest')
            .addOrderBy('draftVersions.status', 'ASC')
            .addOrderBy('draftVersions.created_at', 'DESC')
            .addOrderBy('posts.created_at', 'DESC')
            .addOrderBy('rejectCompletionRequest.created_at', 'DESC');
    }
    async campaignInfluencerCommonQuery(languageCode: string) {
        return this.Campaign.createQueryBuilder("campaign")
            .leftJoinAndSelect("campaign.campaignInfluencers", "campaignInfluencer").withDeleted()
            .leftJoinAndSelect("campaignInfluencer.influencer", "influencer")
            .leftJoinAndSelect('influencer.userKeywords', 'userKeywords')
            .leftJoinAndSelect('userKeywords.keywords', 'keywords')
            .leftJoin('keywords.translations', 'keyword_translation_req_cr', 'keyword_translation_req_cr.language_code = :languageCode AND keyword_translation_req_cr.deleted_at IS NULL', { languageCode: 'en' })
            .leftJoin('keywords.translations', 'keyword_translation_en_cr', 'keyword_translation_en_cr.language_code = \'en\' AND keyword_translation_en_cr.deleted_at IS NULL')
            .leftJoinAndSelect('keywords.translations', 'keyword_translation_cr',
                '(keyword_translation_cr.language_code = :languageCode OR (keyword_translation_cr.language_code = \'en\' AND keyword_translation_req_cr.id IS NULL)) AND keyword_translation_cr.deleted_at IS NULL',
                { languageCode: languageCode })
            .leftJoin('influencer.campaignRecommend', 'campaignRecommend', 'campaignRecommend.campaign_id = campaign.id')
            .select([
                "campaign.id",
                "campaign.campaign_name",
                "campaign.is_barter",
                "campaign.is_active",
                "campaign.product_value",
                "campaign.ai_report_inprogress",
                "campaignInfluencer",
                "influencer.id",
                "influencer.slug",
                "influencer.first_name",
                "influencer.last_name",
                "influencer.image",
                "influencer.total_followers",
                "influencer.country",
                "influencer.platforms",
                "influencer.engagement_ratio",
                "influencer.about_me",
                "influencer.display_name",
                'userKeywords', 'keywords', 'keyword_translation_cr', 'campaignRecommend'
            ])
            // .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')
    }

    async commonQueryAllCampaignInfluencer(languageCode) {
        return this.CampaignInfluencer.createQueryBuilder('campaignInfluencer')
            .leftJoin('campaignInfluencer.campaign', 'campaign').withDeleted()
            .leftJoin("campaign.users", "users")
            .leftJoin("campaignInfluencer.influencer", "influencer")
            .leftJoinAndSelect('influencer.userKeywords', 'userKeywords')
            .leftJoinAndSelect('userKeywords.keywords', 'keywords')
            .leftJoin('keywords.translations', 'keyword_translation_req_cr2', 'keyword_translation_req_cr2.language_code = :languageCode AND keyword_translation_req_cr2.deleted_at IS NULL', { languageCode })
            .leftJoin('keywords.translations', 'keyword_translation_en_cr2', 'keyword_translation_en_cr2.language_code = \'en\' AND keyword_translation_en_cr2.deleted_at IS NULL')
            .leftJoinAndSelect('keywords.translations', 'keyword_translation_cr2',
                '(keyword_translation_cr2.language_code = :languageCode OR (keyword_translation_cr2.language_code = \'en\' AND keyword_translation_req_cr2.id IS NULL)) AND keyword_translation_cr2.deleted_at IS NULL',
                { languageCode })
            .leftJoin("influencer.influencerCategories", "influencerCategory")
            .leftJoin("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 })
            .select([
                "campaignInfluencer",
                "campaign.id", "campaign.campaign_name", "campaign.slug", "campaign.is_barter", "campaign.product_value", "campaign.is_active",
                "influencer.id", "influencer.slug", "influencer.first_name", "influencer.last_name", "influencer.display_name",
                "influencer.image", "influencer.total_followers", "influencer.country", "influencer.platforms",
                "influencer.engagement_ratio", "influencer.about_me",
                'userKeywords', 'keywords', 'keyword_translation_cr2',
                "users.id", "users.slug", "users.first_name", "users.last_name", "users.email", "users.image", "users.is_offline_payment_allowed", "users.offline_payment_allowed",
                "users.country", "users.about_me",
                "influencerCategory.id", "category.id", "category.slug", "category.title", "translation.title", "translation.description", "translation.slug"
            ]);
    }

    async socketCampaignInfluencerDetails(id) {
        // const campaignInfluencerDetails = await this.CampaignInfluencer.createQueryBuilder("campaignInfluencer")
        //     .leftJoinAndSelect("campaignInfluencer.influencer", "influencer")
        //     .leftJoinAndSelect('influencer.userKeywords', 'userKeywords')
        //     .leftJoinAndSelect('userKeywords.keywords', 'keywords')
        //     .leftJoinAndSelect('campaignInfluencer.drafts', 'drafts')
        //     .leftJoinAndSelect('campaignInfluencer.rejectCompletionRequest', 'rejectCompletionRequest')
        //     .leftJoinAndSelect('drafts.draftVersions', 'draftVersions')
        //     .leftJoinAndSelect('drafts.draftComments', 'draftComments')
        //     .leftJoinAndSelect('campaignInfluencer.posts', 'posts')
        //     .select([
        //         "campaignInfluencer",
        //         "influencer.id",
        //         "influencer.slug",
        //         "influencer.first_name",
        //         "influencer.last_name",
        //         "influencer.image",
        //         "influencer.total_followers",
        //         "influencer.country",
        //         "influencer.platforms",
        //         "influencer.engagement_ratio",
        //         "influencer.about_me",
        //         'userKeywords', 'keywords', 'drafts', 'draftVersions', 'draftComments', 'posts', 'rejectCompletionRequest'
        //     ])
        //     .addOrderBy('draftVersions.created_at', 'DESC')
        //     .addOrderBy('posts.created_at', 'DESC')
        //     .addOrderBy('rejectCompletionRequest.created_at', 'DESC')
        //     .where('campaignInfluencer.id = :id', { id })
        //     .withDeleted()
        //     .getOne();
        const CampaignInfluencer = await this.CampaignInfluencer.findOne({ select: ['id', 'campaign'], relations: ['campaign'], where: { id: id } });
        this.SocketGateway.handleMessage(true, CampaignInfluencer.campaign.id);
    }

    async getCampaignBrandDetails(id) {
        let brandDetails = await this.Users.findOne({ where: { id: id }, withDeleted: true });
        brandDetails['campaignCount'] = await this.Campaign.createQueryBuilder('camp')
            .withDeleted()
            .select(['COUNT(*) AS count', 'SUM(camp.total_budget) as total_budget'])
            .where('camp.users = :user_id', { user_id: id })
            .getRawMany();
        return brandDetails;
    }
    private applyPlaceholders(template: string, values: Record<string, string | number>) {
        if (!template) {
            return '';
        }
        return Object.entries(values).reduce((text, [key, value]) => {
            return text.split(key).join(String(value ?? ''));
        }, template);
    }

    async cometChatMessageCreate(authUser: any, type: string, metadata: any = {}) {
        const userName = authUser.display_name || authUser.first_name + ' ' + authUser.last_name;
        const replacements = {
            ':user_name': userName,
            ':new_price': String(metadata?.newPrice ?? metadata?.new_price ?? '').replace(/\.00\b/, ''),
            ':stage': metadata?.stage ?? '',
            ':campaign_name': metadata?.campaignName ?? metadata?.campaign_name ?? '',
        };
        const brandMessage = this.applyPlaceholders(chatMessages.en.brand[type], replacements);
        const influencerMessage = this.applyPlaceholders(chatMessages.en.influencer[type], replacements);
        const adminMessage = this.applyPlaceholders(chatMessages.en.admin[type], replacements);
        return { brandMessage, influencerMessage, adminMessage };
    }

    async sendCometMessage(authUser, template, groupId, userId, type = 'status', metadata = {}) {
        try {

            const { brandMessage, influencerMessage, adminMessage } = await this.cometChatMessageCreate(authUser, template, metadata);
            console.log("brandMessage", brandMessage);
            console.log("influencerMessage", influencerMessage);
            console.log("adminMessage", adminMessage);
            // type = status for the send custom messge,
            // type = text for the send system generated messge,    

            let data = type == 'status' ? { customData: { brandMessage, influencerMessage, adminMessage } } : { text: brandMessage }
            let category = type == 'status' ? 'custom' : 'message';
            const messagesObj = {
                //receiver: groupId,
                category: category,
                type: type,
                data: data,
                receiverType: 'group',
                "multipleReceivers": {
                    "guids": [groupId]
                },
            }
            const COMET_CHAT_API_KEY = await this.encryptService.decrypt(process.env.COMET_CHAT_API_KEY);
            const headers = { accept: 'application/json', 'content-type': 'application/json', apikey: COMET_CHAT_API_KEY };
            const response = await axios.post(process.env.COMET_CHAT_URL + 'messages', messagesObj, { headers });
        } catch (error) {
            console.log(error);
        }
    }

    /**
     * Updates CometChat group with a single tag and metadata.
     * Optionally also updates name, icon (avatar), and description.
     * A group always has exactly one tag (replaces any existing tags).
     */
    async updateCometChatGroupTags(
        groupId: string,
        tag: any[],
        metadata: Record<string, any> = {},
        groupDetails: { name?: string; icon?: string; description?: string } = {},
    ) {
        if (!groupId || !tag) {
            return;
        }
        try {
            const COMET_CHAT_API_KEY = await this.encryptService.decrypt(process.env.COMET_CHAT_API_KEY);
            const headers = { accept: 'application/json', 'content-type': 'application/json', apikey: COMET_CHAT_API_KEY };
            const body: Record<string, any> = {
                tags: tag
            };
            if (metadata) {
                body.metadata = metadata;
            }
            if (groupDetails.name) {
                body.name = groupDetails.name.substring(0, 100);
            }
            if (groupDetails.icon) {
                body.icon = groupDetails.icon;
            }
            if (groupDetails.description) {
                body.description = groupDetails.description.substring(0, 255);
            }
            await axios.put(`${process.env.COMET_CHAT_URL}groups/${groupId}`, body, { headers });
        } catch (error: any) {
            console.error('Error updating CometChat group tags:', error.message);
        }
    }


    async updateCometChatInfluencerGroup(influencerId) {
        const campaignInfluencer = await this.CampaignInfluencer.createQueryBuilder('campaignInfluencer')
            .leftJoin('campaignInfluencer.campaign', 'campaign')
            .leftJoin('campaignInfluencer.influencer', 'influencer')
            .where('campaignInfluencer.influencer = :influencerId', { influencerId })
            .andWhere('campaign.status = :status', { status: CampaignStatus.RUNNING })
            .andWhere('campaign.deleted_at IS NULL')
            .select(['campaignInfluencer.id', 'influencer.id', 'influencer.display_name', 'influencer.image'])
            .getMany();

        const COMET_CHAT_API_KEY = await this.encryptService.decrypt(process.env.COMET_CHAT_API_KEY);
        const headers = { accept: 'application/json', 'content-type': 'application/json', apikey: COMET_CHAT_API_KEY };
        for (const row of campaignInfluencer) {
            try {
                const body = {
                    name: row.influencer.display_name,
                    icon: row.influencer.image ?? process.env.AWS_FILE_PATH + 'images/profile/placeholder.jpg',
                }
                await axios.put(`${process.env.COMET_CHAT_URL}groups/${row.id}`, body, { headers });
            } catch (error: any) {
                console.error('Error updating CometChat influencer group:', error?.message);
            }
        }
        return true;
    }

    private resolveCampaignUpdateEmailStatusKey(type, status, receiverRole) {

        let statusKey = type;
        switch (type) {
            case 'chase_up':
                statusKey = `chase_up_${status}`;
                break;
            case 'add_posts':
                statusKey = receiverRole === UserRoles.BRAND ? 'add_posts_brand' : 'add_posts_influencer';
                break;
            case 'add_draft_comment':
                statusKey = receiverRole === UserRoles.BRAND ? 'add_draft_comment_brand' : 'add_draft_comment_influencer';
                break;
            case 'dispute':
                statusKey = receiverRole === UserRoles.BRAND ? 'dispute_brand' : 'dispute_influencer';
                break;
        }
        return statusKey;
    }
    async checkReceiverPreference(receiver) {
        const notificationPreferences = await this.Users.findOne({ relations: ['notificationPreferences'], where: { id: receiver.id } });
        const userPreferences = {
            whatsapp_notification: notificationPreferences.notificationPreferences.find(preference => preference.channel === NotificationChannel.WHATSAPP_NOTIFICATION)?.is_enabled
        }
        return userPreferences;
    }
    private buildCampaignNotificationFrontendLink(
        receiverRole: string,
        status: string | undefined,
        redirect_slug: string | null,
        redirect_id: string | null,
        metadata: any
    ): string {
        const base = process.env.FRONTEND_URL || '';
        if (receiverRole === UserRoles.INFLUENCER) {
            const jobsMeta = encodeURIComponent(JSON.stringify({ redirect_slug, redirect_id }));
            if (status === InfluencerStatus.WAITING_FOR_QUOTATIONS) {
                return `${base}influencer/jobs?tab=invited&metadata=${jobsMeta}`;
            }
            if (status === InfluencerStatus.QUOTATION_AVAILABLE) {
                return `${base}influencer/jobs?tab=applied&metadata=${jobsMeta}`;
            }
            return `${base}influencer/my-jobs?tab=inprogress&metadata=${jobsMeta}`;
        }
        return `${base}brand/my-campaigns/${redirect_slug}?tab=Campaign%20Management&id=${redirect_id}&metadata=${encodeURIComponent(JSON.stringify(metadata))}`;
    }

    private buildWhatsappNotificationLink(receiverRole: string, status: string | undefined, redirect_slug: string | null, redirect_id: string | null, metadata: any): string {
        const jobsMeta = encodeURIComponent(JSON.stringify({ redirect_slug, redirect_id }));
        if (receiverRole === UserRoles.INFLUENCER) {
            if (status == InfluencerStatus.WAITING_FOR_QUOTATIONS) {
                return `influencer/jobs?tab=invited&metadata=${jobsMeta}`;
            } else if (status == InfluencerStatus.QUOTATION_AVAILABLE) {
                return `influencer/jobs?tab=applied&metadata=${jobsMeta}`;
            } else {
                return `influencer/my-jobs?tab=inprogress&metadata=${jobsMeta}`;
            }
        }
        return `brand/my-campaigns/${redirect_slug}?tab=Campaign%20Management&id=${redirect_id}&metadata=${encodeURIComponent(JSON.stringify(metadata))}`;
    }

    async notification(receiver, description, sender = null, redirect_type = null, redirect_id = null, redirect_slug = null, metadata = { campaignName: "", status: "" }, type = Type.NOTIFICATION) {
        const notificationType = type == 'chase_up' ? Type.CHASE_UP : Type.NOTIFICATION;
        const notification = {
            sender,
            receiver,
            description,
            redirect_type,
            redirect_id,
            redirect_slug,
            metadata,
            type: notificationType
        };

        this.notificationService.save(notification);
    }

    async checkNotificationLimit(userId, type, metadata) {
        console.log("campaignInfluencerId", userId);
        if (!userId) {
            return false;
        }
        switch (type) {
            case 'chase_up':
                return this.checkChaseUpLimit(userId);
            case 'add_draft_comment':
                return this.checkAddDraftCommentLimit(userId);
            case 'add_posts':
                return this.checkAddPostsLimit(metadata);
            case 'add_draft':
                return this.checkAddDraftLimit(userId);
            default:
                return false;
        }
    }

    async checkAddDraftLimit(campaignInfluencerId) {
        const perDayLimit = notificationSettings.add_draft.per_day_limit;
        const todaysDraftCount = await this.Draft.createQueryBuilder('draft')
            .where('draft.campaign_influencer_id = :campaignInfluencerId', { campaignInfluencerId: campaignInfluencerId })
            .andWhere('DATE(draft.created_at) = DATE(NOW())')
            .getCount();
        console.log(todaysDraftCount);
        console.log(perDayLimit);
        if (todaysDraftCount > perDayLimit) {
            return true;
        }
        return false;
    }

    async checkChaseUpLimit(campaignInfluencerId) {

        const perDayLimit = notificationSettings.chase_up.per_day_limit;
        const todaysChaseUpCount = await this.Notifications.createQueryBuilder('notification')
            .where('notification.receiver_id = :receiverId', { receiverId: campaignInfluencerId })
            .andWhere('notification.type = :type', { type: Type.CHASE_UP })
            .andWhere('DATE(notification.created_at) = DATE(NOW())')
            .getCount();

        if (todaysChaseUpCount > perDayLimit) {
            return true;
        }
        return false;
    }

    async checkAddDraftCommentLimit(fromUserId) {
        const minutes = notificationSettings.add_draft_comment.after_minutes; //  (await (this.Settings.findOne({ where: { type: 'comet_chat_message_notification' } }))).value;
        const cutoff = moment.utc().subtract(minutes, 'minutes').toISOString();
        const lastCommentTime = await this.DraftComments.createQueryBuilder('draftComments')
            .where('draftComments.from_user_id = :fromUserId', { fromUserId: fromUserId })
            .andWhere('draftComments.created_at >= :cutoff::timestamptz', { cutoff })
            .andWhere('draftComments.deleted_at IS NULL')
            .orderBy('draftComments.created_at', 'DESC')
            .getCount();
        console.log(lastCommentTime);
        if (lastCommentTime == 1) {
            return false;
        }
        return true;
    }

    async checkAddPostsLimit(metadata) {
        const perDayLimit = notificationSettings.add_posts.per_day_limit;
        if (metadata?.today_post_count > perDayLimit) {
            return true;
        }
        return false;
    }

    async sendEmail(receiver, metadata, type, redirect_slug, redirect_id) {

        const name = `${receiver.first_name}`;
        const status = metadata?.status;
        const statusKey = this.resolveCampaignUpdateEmailStatusKey(type, status, receiver.role);
        console.log(statusKey);
        const emailTemplate = emailMessages.en.campaignUpdates[statusKey] || emailMessages.en.campaignUpdates.default;
        const campaignName = metadata?.campaignName ?? '';

        const frontendLink = this.buildCampaignNotificationFrontendLink(
            receiver.role,
            status,
            redirect_slug,
            redirect_id,
            metadata
        );
        const emailPlaceholders = {
            ':INSERT_LINK_HERE': frontendLink,
            ':campaignName': campaignName,
            ':newApplications': `${metadata?.newApplications ?? 0}`,
            ':pendingApplications': `${metadata?.pendingApplications ?? 0}`,
            ':influencerName': metadata?.influencerName ?? '',
            ':newPrice': String(metadata?.newPrice ?? '').replace(/\.00\b/, ''),
            ':stage': metadata?.stage ?? '',
            ':message': metadata?.message ?? '',
            ':brandName': metadata?.brandName ?? '',
        };
        const mailSubject = this.applyPlaceholders(emailTemplate.subject, emailPlaceholders);
        const mailTitle = this.applyPlaceholders(emailTemplate.title, emailPlaceholders);
        const mailDescription = this.applyPlaceholders(emailTemplate.description, emailPlaceholders);

        this.SendMailService.sendMailObjectWithTemplates(receiver.email, mailSubject, 'campaign_update_mail', {
            hello_user_name: emailMessages.en.hello_user_name.replace(':user_name', name),
            main_title: mailTitle,
            descriptions: '',
            frontend_link: mailDescription,
            copyright: emailMessages.en.copyright,
            all_rights_reserved: emailMessages.en.all_rights_reserved,
        });
    }

    async sendWhatsappNotification(receiver, metadata, description, redirect_slug, redirect_id) {

        const whatsappLink = this.buildWhatsappNotificationLink(
            receiver.role,
            metadata?.status,
            redirect_slug,
            redirect_id,
            metadata
        );

        const userPreferences = await this.checkReceiverPreference(receiver);
        if (userPreferences.whatsapp_notification) {
            const userName = receiver.first_name;
            const whatsappDescription = this.applyPlaceholders(description, {
                ':user_name': userName,
                ':influencer_name': metadata?.influencerName ?? metadata?.influencer_name ?? '',
                ':brand_name': metadata?.brandName ?? metadata?.brand_name ?? '',
                ':new_price': String(metadata?.newPrice ?? metadata?.new_price ?? '').replace(/\.00\b/, ''),
                ':campaign_name': metadata?.campaignName ?? metadata?.campaign_name ?? '',
                ':stage': metadata?.stage ?? '',
                ':link': whatsappLink,
            });
            const whatsappMetadata = {
                user_name: userName,
                campaign_name: metadata.campaignName,
                link: whatsappLink,
            }
            this.whatsappService.sendWhatsAppMessage(receiver.whatsapp_phone_number, whatsappDescription, whatsappMetadata);
        }
    }

    // async autoPaymentNotification(receiver, description, sender = null, redirect_type = null, redirect_id = null, redirect_slug = null, metadata = { payment_status: "" }) {
    //     const notification = {
    //         sender,
    //         receiver,
    //         description,
    //         redirect_type,
    //         redirect_id,
    //         redirect_slug,
    //         metadata
    //     };

    //     this.notificationService.save(notification);
    //     let template = emailMessages.en.auto_payment_success;
    //     if (metadata.payment_status === 'failed') {
    //         template = emailMessages.en.auto_payment_failed;
    //     }
    //     const name = receiver.first_name + ' ' + receiver.last_name;
    //     const mailSubject = template.subject;
    //     // const descriptions = sender.first_name + ' ' + sender.last_name + ' ' + description;
    //     let frontendLink = process.env.FRONTEND_URL + 'login';
    //     frontendLink = template.description.replace(':INSERT_LINK_HERE', frontendLink);
    //     const mailObj = {
    //         hello_user_name: emailMessages.en.hello_user_name.replace(':user_name', name),
    //         main_title: description,
    //         descriptions: '',
    //         frontend_link: frontendLink,
    //         copyright: emailMessages.en.copyright,
    //         all_rights_reserved: emailMessages.en.all_rights_reserved,
    //     }
    //     this.SendMailService.sendMailObjectWithTemplates(receiver.email, mailSubject, 'campaign_update_mail', mailObj);
    // }

    async weeklyCompletionRequestNotification(receiver, description, sender = null, redirect_type = null, redirect_id = null, redirect_slug = null, metadata = { campaignName: "", influencerName: "", daysAgo: 30 }, isEmail = true) {
        const notification = {
            sender,
            receiver,
            description,
            redirect_type,
            redirect_id,
            redirect_slug,
            metadata
        };

        this.notificationService.save(notification);
        if (isEmail) {
            const { influencerName, campaignName, daysAgo } = metadata || {};
            const receiverName = `${receiver.first_name}`;
            // const frontendUrl = `${process.env.FRONTEND_URL}login`;

            const frontendLink = this.buildCampaignNotificationFrontendLink(
                receiver.role,
                InfluencerStatus.COMPLETION_REQUEST,
                redirect_slug,
                redirect_id,
                metadata
            );
            // Base email template
            let template = emailMessages.en.campaignUpdates.completion_request_reminder_notification;
            let daysRemaining: string | null = null;

            // Check if job is closing soon
            if (daysAgo >= 28) {
                const remaining = 31 - daysAgo;
                daysRemaining = remaining === 1 ? '24 Hours' : `${remaining} days`;
                template = emailMessages.en.campaignUpdates.completion_request_reminder_notification_close_soon;
            }

            // Build mail content dynamically
            const mailSubject = template.subject
                .replace(':influencerName', influencerName || '')
                .replace(':daysRemaining', daysRemaining || '');

            const mainTitle = template.title
                .replace(':campaignName', campaignName || '')
                .replace(':influencerName', influencerName || '');

            const description = template.description
                .replace(':campaignName', campaignName || '')
                .replace(':influencerName', influencerName || '')
                .replace(':INSERT_LINK_HERE', frontendLink)
                .replace(':daysRemaining', daysRemaining || '');

            // Build final email object
            const mailObj = {
                hello_user_name: emailMessages.en.hello_user_name.replace(':user_name', receiverName),
                main_title: mainTitle,
                descriptions: '',
                frontend_link: description,
                copyright: emailMessages.en.copyright,
                all_rights_reserved: emailMessages.en.all_rights_reserved,
            };

            this.SendMailService.sendMailObjectWithTemplates(receiver.email, mailSubject, 'campaign_update_mail', mailObj);
        }
    }

    // async cometChatNotification(receiver, description, sender = null, redirect_type = null, redirect_id = null, redirect_slug = null, metadata) {
    //     const notification = {
    //         sender,
    //         receiver,
    //         description,
    //         redirect_type,
    //         redirect_id,
    //         redirect_slug,
    //         metadata
    //     };

    //     this.notificationService.save(notification);

    //     const name = receiver.first_name + ' ' + receiver.last_name;
    //     const mailSubject = emailMessages.en.messageNotification.subject.replace(':groupName', metadata?.campaignName);
    //     let frontendLink = this.buildCampaignNotificationFrontendLink(
    //         receiver.role,
    //         metadata?.status,
    //         redirect_slug,
    //         redirect_id,
    //         metadata
    //     );
    //     frontendLink = emailMessages.en.messageNotification.description.replace(':INSERT_LINK_HERE', frontendLink);
    //     const whatsappLink = this.buildWhatsappNotificationLink(
    //         receiver.role,
    //         metadata?.status,
    //         redirect_slug,
    //         redirect_id,
    //         metadata
    //     );
    //     const userPreferences = await this.checkReceiverPreference(receiver);
    //     if (userPreferences.whatsapp_notification && receiver.is_whatsapp_verified && Constants.NOTIFICATION_TYPES['comet_chat_message']) {
    //         const userName = receiver.first_name + ' ' + receiver.last_name;
    //         const whatsappMetadata = {
    //             user_name: userName,
    //             campaign_name: metadata.campaignName,
    //             link: whatsappLink,
    //         }
    //         this.whatsappService.sendWhatsAppMessage(receiver.whatsapp_phone_number, description, whatsappMetadata);
    //     }
    //     frontendLink = emailMessages.en.messageNotification.description.replace(':INSERT_LINK_HERE', frontendLink).replace(':campaignName', metadata?.campaignName);
    //     const mailObj = {
    //         hello_user_name: emailMessages.en.hello_user_name.replace(':user_name', name),
    //         main_title: description,
    //         descriptions: '',
    //         frontend_link: frontendLink,
    //         copyright: emailMessages.en.copyright,
    //         all_rights_reserved: emailMessages.en.all_rights_reserved,
    //     }
    //     this.SendMailService.sendMailObjectWithTemplates(receiver.email, mailSubject, 'campaign_update_mail', mailObj);
    // }

    async adminNotification(description, sender = null, redirect_type = null, redirect_id = null, redirect_slug = null) {
        const receivers = await this.Users.find({ where: { role: UserRoles.ADMIN, is_active: true } });
        for (const receiver of receivers) {
            const notification = {
                sender,
                receiver,
                description,
                redirect_type,
                redirect_id,
                redirect_slug
            };
            await this.notificationService.save(notification);
        }
    }

    async adminNotificationWithEmail(description, sender = null, redirect_type = null, redirect_id = null, redirect_slug = null) {
        const receivers = await this.Users.find({ where: { role: UserRoles.ADMIN, is_active: true } });
        for (const receiver of receivers) {
            const notification = {
                sender,
                receiver,
                description,
                redirect_type,
                redirect_id,
                redirect_slug
            };
            await this.notificationService.save(notification);
            const mailSubject = emailMessages.en.campaign_auto_payment_failed.subject;
            const mailObj = {
                hello_user_name: emailMessages.en.hello_user_name.replace(':user_name', receiver.first_name + ' ' + receiver.last_name),
                main_title: mailSubject,
                descriptions: description,
                copyright: emailMessages.en.copyright,
                all_rights_reserved: emailMessages.en.all_rights_reserved,
            }
            this.SendMailService.sendMailObjectWithTemplates(receiver.email, mailSubject, 'common_mail', mailObj);
        }
    }

    /**
     * Process the payment of influencer
     * @param id campaign_influencer id
     * @param authUser user brand is making the payment
     */
    async influencerPayment(id: string, authUser: any, card_id: string = null) {
        let payment = null;
        try {
            let campaignInfluencer = await this.CampaignInfluencer.findOne({ relations: ['influencer', 'campaign', 'campaign.users'], where: [{ id }] })
            if (campaignInfluencer) {
                if (!campaignInfluencer.influencer) {
                    return;
                }
                let amount = campaignInfluencer.influencer_budget;
                if (amount < 0) {
                    return;
                }
                const platform_charge = await this.Settings.findOne({ where: { type: KeyType.PLATFORM_CHARGE } });
                let platform_fees = (parseInt(platform_charge.value) * amount) / 100;
                let payable_amount = Number(amount) + Number(platform_fees);
                let currency = campaignInfluencer.currency;
                let transaction_id = "";
                let payment_response = {};
                let campaignPayment = {
                    influencer: campaignInfluencer.influencer,
                    campaign: campaignInfluencer.campaign,
                    brand: authUser,
                    currency: currency,
                    amount: payable_amount,
                    campaign_influencer: campaignInfluencer,
                    amount_usd: await this.CommonService.convertCurrencyRate(currency, payable_amount),
                    platform_fees: platform_fees,
                    platform_fees_usd: await this.CommonService.convertCurrencyRate(currency, platform_fees),
                    influencer_payable_amount: amount,
                    influencer_payable_amount_usd: await this.CommonService.convertCurrencyRate(currency, amount),
                    payment_mode: campaignInfluencer?.campaign?.users?.offline_payment_allowed == 'forced' ? PaymentMode.FORCE_OFFLINE : campaignInfluencer.payment_mode,
                    payment_status: PaymentMode.ONLINE == campaignInfluencer.payment_mode ? PAYMENT_STATUS.PENDING : PAYMENT_STATUS.COMPLETED,
                    //payment_status: PAYMENT_STATUS.PENDING,
                    is_admin_paid: PaymentMode.ONLINE == campaignInfluencer.payment_mode || campaignInfluencer?.campaign?.users?.offline_payment_allowed == 'forced' ? false : true
                }
                payment = await this.CampaignPayment.save(this.CampaignPayment.create(campaignPayment));
                if (campaignInfluencer.payment_mode == PaymentMode.ONLINE) {

                    const whereCondition = card_id ? { card_id: card_id } : { user_id: authUser.id };
                    const card = await this.Card.findOne({ where: whereCondition, order: { created_at: 'DESC' } });

                    const metadata = { "card_id": card.id, "user_id": authUser.id, 'campaign_influencer_id': campaignInfluencer.id };
                    const response = await this.StripeService.paymentIntents(payable_amount, currency, card.card_id, authUser.stripe_customer_id, metadata);
                    console.log(response);
                    if (response.response.status) {
                        payment.transaction_id = response.response.paymentIntent.id;
                        payment.payment_response = response.response;
                        await this.CampaignPayment.save(payment);
                        //this.checkPaymentStatus(payment.id);
                        return;
                    }
                    if (response.response?.paymentIntent?.id) {
                        payment.transaction_id = response.response.paymentIntent.id;
                        await this.CampaignPayment.save(payment);
                        response['payment_id'] = payment.id;
                        return response;
                    }
                    if (response.response?.error) {
                        await this.CampaignPayment.update(payment.id, { payment_status: PAYMENT_STATUS.FAILED, comment: response.response.error });
                        return { response: { paymentIntent: null, status: false, error: response.response.error } };
                    }
                }
            }
        } catch (error: any) {
            if (payment) {
                await this.CampaignPayment.update(payment.id, { payment_status: PAYMENT_STATUS.FAILED, comment: error.message });
            }
            return { response: { paymentIntent: null, status: false, error: error.message } };
        }
    }

    async checkPaymentStatus(id) {
        let count = 0;
        while (count < 3) {
            await this.sleep(1000);
            const isCompleted = await this.CampaignPayment.count({
                where: { id: id, payment_status: PAYMENT_STATUS.COMPLETED }
            });

            if (isCompleted) {
                const payment = await this.CampaignPayment.findOne({ where: { id } });
                const paymentPdf = {
                    name: "Connector Club",
                    amount: payment.influencer_payable_amount,
                    currency: payment.currency,
                    date: payment.created_at,
                    platform_fees: payment.platform_fees,
                    receipt_number: payment.receipt_number,
                    total_amount: Number(payment.influencer_payable_amount) + Number(payment.platform_fees)
                }
                const response = await this.createInvoiceAndUpload(paymentPdf);

                this.CampaignPayment.update({ id: payment.id }, { invoice_url: response.fileName });
                break;
            }
            count++;
        }
    }

    async sleep(ms) {
        return new Promise(resolve => setTimeout(resolve, ms));
    }

    async createInvoiceAndUpload(paymentPdf: any) {
        const currentDate = moment().format('MMMM DD, YYYY, h:mm A');

        // Path to the EJS template

        const templatePath = path.join(__dirname, '..', '..', '..', 'src', 'templates', 'reports', `campaign-receipt.ejs`);
        // Read the EJS template
        const template = fs.readFileSync(templatePath, 'utf-8');
        // Render the EJS template with dynamic data
        const htmlContent = ejs.render(template, { paymentPdf, currentDate });
        // Launch Puppeteer / executablePath: '/usr/bin/chromium-browser'
        const browser = await puppeteer.launch({
            headless: true,
            executablePath: '/usr/bin/google-chrome', // or '/usr/bin/google-chrome-stable'
            args: ['--no-sandbox', '--disable-setuid-sandbox']
        });
        const page = await browser.newPage();


        // Set the HTML content in Puppeteer
        await page.setContent(htmlContent, { waitUntil: 'networkidle0' });

        // Generate the PDF
        const pdfBuffer = await page.pdf({
            format: 'A4',
            printBackground: true
        });
        const bucketName = process.env.AWS_BUCKET;
        const fileName = `${paymentPdf.receipt_number}.pdf`;
        let key = `files/campaign/receipt/${fileName}`;
        await browser.close();
        const params = {
            Bucket: bucketName,
            Key: key,
            Body: pdfBuffer,
            ContentType: 'application/pdf',
            // ACL: 'public-read',
        };
        const data = await this.s3.send(new PutObjectCommand(params));
        return { fileName }
    }

    async refundAmount(id) {
        let campaignInfluencer = await this.CampaignInfluencer.findOne({ relations: ['campaignPayment'], where: [{ id }] });
        if (campaignInfluencer) {
            const chargeId = campaignInfluencer.campaignPayment.charge_id;

            if (chargeId) {
                const payment = await this.CampaignPayment.findOne({ relations: ['brand', 'campaign'], where: { id: campaignInfluencer.campaignPayment.id } });
                if (!payment) return
                this.StripeService.refundPayment(chargeId);

                const campaignPaymentHistory = {
                    users: payment.influencer,
                    campaign: payment.campaign,
                    campaign_payment: payment,
                    amount: payment.amount,
                    currency: payment.currency,
                    amount_usd: payment.amount_usd,
                    transaction_status: PAYMENT_STATUS.COMPLETED,
                    payment_status: PaymentType.REFUNDED
                }
                this.CampaignPaymentHistory.save(campaignPaymentHistory);
            }
        }
    }

    async saveInfluencerRecommendedScore(campaign_influencer_id: string, influencer_id: string) {
        const campaignInfluencer = await this.CampaignInfluencer.findOne({ relations: ['campaign'], where: { id: campaign_influencer_id } });
        if (campaignInfluencer) {
            const recommendedInfluencer = await this.getRecommendedInfluencer(campaignInfluencer.campaign.id, influencer_id);
            if (recommendedInfluencer.length > 0) {
                campaignInfluencer.recommended_score = recommendedInfluencer[0].score;
                await this.CampaignInfluencer.save(campaignInfluencer);
                return campaignInfluencer.recommended_score;
            }
        }
        return 0;
    }

    async getRecommendedInfluencer(campaignId: string, influencer_id: string = null, limit: number = Constants.RECOMMENDED_INFLUENCER, offset: number = 0) {

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

        const platformScore = parseInt(recommendedSettings.filter(setting => setting.type === KeyType.PLATFORM_SCORE)[0].value);
        const countryScore = parseInt(recommendedSettings.filter(setting => setting.type === KeyType.GEOGRAPHY_SCORE)[0].value);
        const currencyScore = parseInt(recommendedSettings.filter(setting => setting.type === KeyType.CURRENCY_SCORE)[0].value);
        const categoryScore = parseInt(recommendedSettings.filter(setting => setting.type === KeyType.JOB_CATEGORY_SCORE)[0].value);
        const influencerScore = parseInt(recommendedSettings.filter(setting => setting.type === KeyType.REGISTERED_INFLUENCER_SCORE)[0].value);

        let influencer_condition = '';
        if (influencer_id) {
            influencer_condition = ` AND i.id = $9`;
        }

        const query = `
      WITH campaign_data AS (
          SELECT 
              c.id AS campaign_id,
              array_agg(cc.category_id) AS campaign_categories,
              ARRAY(SELECT jsonb_array_elements_text(c.platforms)) AS campaign_platforms,
              c.campaign_country,
              c.currency
          FROM campaigns c
          LEFT JOIN campaign_categories cc ON c.id = cc.campaign_id  
          WHERE c.id = $1
          GROUP BY c.id, c.platforms, c.campaign_country, c.currency
      )

      SELECT 
          i.id AS influencer_id,
          i.first_name,
          array_agg(ic.category) AS influencer_categories,
          i.platforms,  
          i.country,
          i.is_imported_by_admin,
          i.total_followers,
          i.currency,
          ( 
              CASE 
                  WHEN i.country = cd.campaign_country THEN $4 ELSE 0 
              END + 
              CASE 
                  WHEN i.currency = cd.currency THEN $5 ELSE 0 
              END + 
              CASE
                WHEN i.is_imported_by_admin = false THEN $8 ELSE 0
              END +
              (SELECT COUNT(*) FROM unnest(array_agg(ic.category)) AS cat WHERE cat = ANY(cd.campaign_categories)) * $6 +
              (SELECT COUNT(*) FROM unnest(ARRAY(SELECT jsonb_array_elements_text(i.platforms))) AS plat WHERE plat = ANY(cd.campaign_platforms)) * $7  
          ) AS recommendation_score

      FROM users i
      JOIN influencer_categories ic ON i.id = ic.users  
      CROSS JOIN campaign_data cd
      WHERE i.role = 'influencer' AND i.is_active = true AND i.deleted_at IS NULL
      ${influencer_condition}
      GROUP BY i.id, i.first_name, i.platforms, i.country, i.currency,i.is_imported_by_admin,
               cd.campaign_categories, cd.currency, cd.campaign_country, cd.campaign_platforms
      ORDER BY recommendation_score DESC, total_followers DESC
      LIMIT $2 OFFSET $3;
    `;

        const params: any[] = [campaignId, limit, offset, countryScore, currencyScore, categoryScore, platformScore, influencerScore];
        if (influencer_id) {
            params.push(influencer_id);
        }
        const recommendedInfluencer = await this.dataSource.query(query, params);
        //return recommendedInfluencer.filter(item => item.recommendation_score > 0).map(item => item.influencer_id);
        return recommendedInfluencer
            .filter(item => item.recommendation_score > 0)
            .map(item => ({
                id: item.influencer_id,
                score: item.recommendation_score
            }));
    }

    async saveRecommendInfluencer(campaign_id: string) {

        const influencerArr = await this.getRecommendedInfluencer(campaign_id);
        if (influencerArr.length === 0) return

        await this.CampaignRecommended.delete({ campaign: { id: campaign_id }, is_admin_recommended: false });

        const campaign = await this.Campaign.findOne({ where: { id: campaign_id } });
        const campaignRecommended = [];
        if (campaign) {
            for (const influencerObj of influencerArr) {
                const influencer = await this.Users.findOne({ where: { id: influencerObj.id } });
                await this.CampaignRecommended.save({
                    campaign: campaign,
                    influencer: influencer,
                    score: influencerObj.score
                });
                // campaignRecommended.push({ 
                //     campaign: campaign,
                //     influencer: influencer
                // });
            }
        }
        campaign.recommended_date_time = moment().add(Constants.RECOMMENDED_UPDATE_HOURS, 'hours').format('YYYY-MM-DD HH:mm:ss');
        await this.Campaign.save(campaign);
        //await this.CampaignRecommended.save(campaignRecommended);
    }

    async getRecommendedCampaign(influencer_id: string, limit: number = Constants.RECOMMENDED_CAMPAIGN, offset: number = 0) {

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

        const platformScore = parseInt(recommendedSettings.filter(setting => setting.type === KeyType.INFLUENCER_PLATFORM_SCORE)[0].value);
        const countryScore = parseInt(recommendedSettings.filter(setting => setting.type === KeyType.INFLUENCER_GEOGRAPHY_SCORE)[0].value);
        const currencyScore = parseInt(recommendedSettings.filter(setting => setting.type === KeyType.INFLUENCER_CURRENCY_SCORE)[0].value);
        const categoryScore = parseInt(recommendedSettings.filter(setting => setting.type === KeyType.INFLUENCER_JOB_CATEGORY_SCORE)[0].value);
        const query = `
        WITH influencer_data AS (
            SELECT 
                u.id AS influencer_id,
                array_agg(ic.category) AS influencer_categories,
                ARRAY(SELECT jsonb_array_elements_text(u.platforms)) AS influencer_platforms,
                u.country AS influencer_country,
                u.currency AS influencer_currency
            FROM users u
            LEFT JOIN influencer_categories ic ON u.id = ic.users
            WHERE u.id = $1
            GROUP BY u.id, u.platforms, u.country, u.currency
        )

        SELECT 
            c.id AS campaign_id,
            c.campaign_name,
            array_agg(cc.category_id) AS campaign_categories,
            c.platforms,  
            c.campaign_country,
            c.currency,
            ( 
                CASE 
                    WHEN c.campaign_country = id.influencer_country THEN $4 ELSE 0 
                END + 
                CASE 
                    WHEN c.currency = id.influencer_currency THEN $5 ELSE 0 
                END + 
                (SELECT COUNT(*) FROM unnest(array_agg(cc.category_id)) AS cat WHERE cat = ANY(id.influencer_categories)) * $6 +
                (SELECT COUNT(*) FROM unnest(ARRAY(SELECT jsonb_array_elements_text(c.platforms))) AS plat WHERE plat = ANY(id.influencer_platforms)) * $7  
            ) AS recommendation_score
        FROM campaigns c
        LEFT JOIN campaign_categories cc ON c.id = cc.campaign_id  
        CROSS JOIN influencer_data id
        WHERE c.status = 'running' AND c.is_active = true AND c.profile_privacy = 'public' AND c.deleted_at IS NULL
        AND (c.is_only_international_influencer = false OR c.campaign_country IS DISTINCT FROM id.influencer_country)
        AND EXISTS (
        SELECT 1
            FROM unnest(ARRAY(SELECT jsonb_array_elements_text(c.platforms))) AS campaign_platform
            WHERE campaign_platform = ANY(id.influencer_platforms)
        )
        GROUP BY c.id, c.campaign_name, c.platforms, c.campaign_country, c.currency, 
                id.influencer_categories, id.influencer_currency, id.influencer_country, id.influencer_platforms
        ORDER BY recommendation_score DESC, c.id DESC
        LIMIT $2 OFFSET $3;`;
        const recommendedCampaign = await this.dataSource.query(query, [influencer_id, limit, offset, countryScore, currencyScore, categoryScore, platformScore]);
        // return recommendedCampaign.filter(item => item.recommendation_score > 0).map(item => item.campaign_id);
        return recommendedCampaign
            .filter(item => item.recommendation_score > 0)
            .map(item => ({
                id: item.campaign_id,
                score: item.recommendation_score
            }));
    }


    async saveRecommendedCampaign(influencer_id: string) {

        const campaignArr = await this.getRecommendedCampaign(influencer_id);
        if (campaignArr.length === 0) return

        await this.InfluencerCampaignRecommended.delete({ influencer: { id: influencer_id }, is_admin_recommended: false });

        const user = await this.Users.findOne({ where: { id: influencer_id } });
        if (user) {
            for (const campaignObj of campaignArr) {
                const campaign = await this.Campaign.findOne({ where: { id: campaignObj.id } });
                await this.InfluencerCampaignRecommended.save({
                    campaign: campaign,
                    influencer: user,
                    score: campaignObj.score
                });
            }
        }
        user.recommended_date_time = moment().add(Constants.RECOMMENDED_UPDATE_HOURS, 'hours').format('YYYY-MM-DD HH:mm:ss');
        await this.Users.save(user);
    }

    async getRelatedPublicCampaignsForPreview(
        userId: string,
        filters: {
            country?: string;
            currency?: string;
            categories?: string[];
            platforms?: string[];
            isBarter?: string;
            jobPrivacy?: string;
        },
        offset: number = 0,
        limit: number = 9,
    ): Promise<{ items: { id: string; relevance_score: number }[]; total: number }> {
        const { country, currency, categories = [], platforms = [], isBarter, jobPrivacy } = filters;

        const orConditions: string[] = [];
        const scoreParts: string[] = [];
        const params: any[] = [userId];
        let idx = 2;

        const addParam = (value: any): string => {
            params.push(value);
            return `$${idx++}`;
        };

        if (country) {
            const countryParam = addParam(country);
            orConditions.push(`c.campaign_country = ${countryParam}`);
            scoreParts.push(`CASE WHEN c.campaign_country = ${countryParam} THEN 1 ELSE 0 END`);
        }

        if (currency) {
            const currencyParam = addParam(currency);
            orConditions.push(`c.currency = ${currencyParam}`);
            scoreParts.push(`CASE WHEN c.currency = ${currencyParam} THEN 1 ELSE 0 END`);
        }

        if (categories.length > 0) {
            const categoriesParam = addParam(categories);
            orConditions.push(`EXISTS (
                SELECT 1 FROM campaign_categories cc
                WHERE cc.campaign_id = c.id
                AND cc.category_id = ANY(${categoriesParam}::uuid[])
                AND cc.deleted_at IS NULL
            )`);
            scoreParts.push(`(
                SELECT COUNT(*)::int FROM campaign_categories cc
                WHERE cc.campaign_id = c.id
                AND cc.category_id = ANY(${categoriesParam}::uuid[])
                AND cc.deleted_at IS NULL
            )`);
        }

        if (platforms.length > 0) {
            const platformsParam = addParam(platforms);
            orConditions.push(`c.platforms ?| ${platformsParam}::text[]`);
            scoreParts.push(`(
                SELECT COUNT(*)::int
                FROM jsonb_array_elements_text(c.platforms) AS p(platform)
                WHERE p.platform = ANY(${platformsParam}::text[])
            )`);
        }

        if (isBarter !== undefined && isBarter !== null) {
            const isBarterParam = addParam(isBarter);
            orConditions.push(`c.is_barter = ${isBarterParam}`);
            scoreParts.push(`CASE WHEN c.is_barter = ${isBarterParam} THEN 1 ELSE 0 END`);
        }

        if (jobPrivacy) {
            const jobPrivacyParam = addParam(jobPrivacy);
            orConditions.push(`c.job_privacy = ${jobPrivacyParam}`);
            scoreParts.push(`CASE WHEN c.job_privacy = ${jobPrivacyParam} THEN 1 ELSE 0 END`);
        }

        if (orConditions.length === 0) {
            return { items: [], total: 0 };
        }

        const relevanceScore = scoreParts.length > 0 ? scoreParts.join(' + ') : '0';
        const whereClause = `
            c.users != $1
            AND c.profile_privacy = 'public'
            AND c.status = 'running'
            AND c.is_active = true
            AND c.deleted_at IS NULL
            AND (${orConditions.join(' OR ')})
            AND ((${relevanceScore}) > 0)
        `;

        const countQuery = `
            SELECT COUNT(*)::int AS total
            FROM campaigns c
            WHERE ${whereClause}
        `;
        const [{ total }] = await this.dataSource.query(countQuery, params);

        const offsetParam = addParam(offset);
        const limitParam = addParam(limit);
        const dataQuery = `
            SELECT c.id,
                (${relevanceScore}) AS relevance_score
            FROM campaigns c
            WHERE ${whereClause}
            ORDER BY relevance_score DESC, c.created_at DESC
            OFFSET ${offsetParam}
            LIMIT ${limitParam}
        `;


        const items = await this.dataSource.query(dataQuery, params);

        return { items, total: total ?? 0 };
    }

    async commonCampaignStatusNotificationQuery(status: string) {
        return this.CampaignInfluencer.createQueryBuilder('campaignInfluencer')
            .leftJoin('campaignInfluencer.campaign', 'campaign')
            .leftJoin('campaign.users', 'users')
            .leftJoin('campaignInfluencer.influencer', 'influencer')
            .where('campaignInfluencer.influencer_status = :status', { status })
            .andWhere('campaignInfluencer.notification_reminder_at < :now', { now: moment.utc().format('YYYY-MM-DD HH:mm:ss') })
            .select(['campaignInfluencer.id',
                'campaign.id', 'campaign.slug', 'campaign.campaign_name', 'campaignInfluencer.status', 'campaignInfluencer.influencer_status',
                'influencer.id', 'influencer.slug', 'influencer.first_name', 'influencer.last_name', 'influencer.email',
                'users.id', 'users.slug', 'users.first_name', 'users.last_name', 'users.email'
            ]);
    }
    async commonCampaignBrandStatusNotificationQuery(status: string) {
        let queryBuilder = this.CampaignInfluencer.createQueryBuilder('campaignInfluencer')
            .leftJoin('campaignInfluencer.campaign', 'campaign')
            .leftJoin('campaign.users', 'users')
            .leftJoin('campaignInfluencer.influencer', 'influencer')
            .where('campaignInfluencer.status = :status', { status })
            .andWhere('campaignInfluencer.notification_reminder_at < :now', { now: moment.utc().format('YYYY-MM-DD HH:mm:ss') })
            .select(['campaignInfluencer.id', 'campaignInfluencer.status', 'campaignInfluencer.draft_notification_sent',
                'campaign.id', 'campaign.slug', 'campaign.campaign_name',
                'influencer.id', 'influencer.slug', 'influencer.first_name', 'influencer.last_name', 'influencer.email',
                'users.id', 'users.slug', 'users.first_name', 'users.last_name', 'users.email'
            ]);
        if (status == InfluencerStatus.DRAFT_AVAILABLE) {
            queryBuilder.andWhere('campaignInfluencer.draft_notification_sent < 3');
        }
        return queryBuilder;
    }



    async commonCampaignInfluencerCompletedQuery(campaignId: string) {
        const campaignInfluencer = await this.CampaignInfluencer.createQueryBuilder('campaign_influencer')
            .select(['COUNT(*) as count', "COALESCE(SUM(CASE WHEN campaign_influencer.status = 'closed' THEN 1 ELSE 0 END), 0) AS closedCount"])
            .where('campaign_influencer.campaign_id = :campaignId', { campaignId })
            .andWhere('campaign_influencer.status NOT IN(:...influencer_status)', { influencer_status: [InfluencerStatus.UNCONNTACTED, InfluencerStatus.WAITING_FOR_QUOTATIONS, InfluencerStatus.QUOTATION_AVAILABLE] })
            .getRawOne();
        if (!campaignInfluencer || (campaignInfluencer.count == campaignInfluencer.closedcount && campaignInfluencer.closedcount >= 0)) {
            return true;
        }
        return false;
    }
}