import { Injectable, HttpException, HttpStatus, Res } from "@nestjs/common"
import { InjectRepository } from "@nestjs/typeorm"
import { Repository, Like, Not, In } from "typeorm"
import { classToPlain } from "class-transformer"
import { responseMessages } from "../../messages/response-messages"
import { emailMessages } from "../../messages/email-messages"
import { CategoriesRepository } from "./categories.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")

// Entities Files
import { UserRoles, Users } from "../../entities/users.entity"
import { Categories } from "../../entities/categories.entity"
import { CategoryTranslation } from "../../entities/category-translations.entity"
import { KeywordTranslation } from "../../entities/keyword-translations.entity"
import { UpdateDto } from "./dtos/update.dto"
import { CategoryIdParamDto } from "./dtos/category-id-param.dto"

// DTO Files
import { CreateDto } from "./dtos/create.dto"
import { ListQueryDto } from "./dtos/list-query.dto"
import { InfluencerCategory } from "src/entities/influencer-caregories.entity"
import { BrandIndustries } from "src/entities/brand-industries.entity"
import { redisClient } from "src/common/services/redis/redis.provider"
import { Keywords } from "src/entities/keywords.entity"

@Injectable()
export class CategoriesService {
    constructor(
        private categoriesRepository: CategoriesRepository,

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

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

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

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

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

        @InjectRepository(KeywordTranslation)
        private KeywordTranslation: Repository<KeywordTranslation>
    ) { }

    /**
     * Create category based on request parameters.
     * @param request - An instance of CreateDto containing the category data with translations to process of create data.
     * @param response - The response object for sending the HTTP response.
     * @returns Responds with a 201 status if successful.
     * In case of an error, logs the error and responds with the respective error status and message.
     */
    async create(request: CreateDto) {
        // Validate translations array
        if (!request.translations || request.translations.length === 0) {
            throw new HttpException(responseMessages.en.categories.translations_required, HttpStatus.BAD_REQUEST)
        }

        // Validate each translation and check for duplicates
        for (const translation of request.translations) {
            const languageCode = translation.language_code;
            const title = translation.title.trim();
            const slug = title.toLowerCase().replace(/\s+/g, "-");
            if (!title) {
                continue
            }
            // Check if title exists in translations for this language
            const existsTitle = await this.CategoryTranslation.findOne({
                where: { title: title, language_code: languageCode }
            });

            if (existsTitle) {
                throw new HttpException(responseMessages.en.categories.already_exists, HttpStatus.NOT_FOUND)
            }

            // Check if slug exists in translations for this language
            const existsSlug = await this.CategoryTranslation.findOne({
                where: { slug: slug, language_code: languageCode }
            });
            if (existsSlug) {
                throw new HttpException(responseMessages.en.categories.already_exists, HttpStatus.NOT_FOUND)
            }
        }

        // Create category entity
        const categoryData: any = {
            is_active: true,
            image: request.image || null,
            tags: request.tags || null
        };

        // Process keywords/tags for all languages
        const keywords = request.tags ? request.tags.split(",").map((tag) => tag.trim()).filter((tag) => tag !== "") : [];
        const processedLanguages = new Set<string>();

        for (const translation of request.translations) {
            const languageCode = translation.language_code;

            // Process keywords for each language (avoid duplicate processing)
            if (!processedLanguages.has(languageCode)) {
                processedLanguages.add(languageCode);

                for (const keywd of keywords) {
                    // Find keyword by translation text for this language
                    const keywordTranslation = await this.KeywordTranslation.findOne({
                        where: { keyword: keywd.trim(), language_code: languageCode },
                        relations: ['keywordRef']
                    });
                    let keyword = keywordTranslation ? keywordTranslation.keywordRef : null;

                    if (!keyword) {
                        // Check if keyword exists in English translation
                        const enTranslation = await this.KeywordTranslation.findOne({
                            where: { keyword: keywd.trim(), language_code: 'en' },
                            relations: ['keywordRef']
                        });
                        keyword = enTranslation ? enTranslation.keywordRef : null;

                        if (!keyword) {
                            // CREATE NEW KEYWORD
                            keyword = await this.Keywords.save({ keyword: keywd.trim() });
                        }

                        // Create translation for the keyword in this language
                        const newTranslation = new KeywordTranslation();
                        newTranslation.keywordRef = keyword;
                        newTranslation.language_code = languageCode;
                        newTranslation.keyword = keywd.trim();
                        newTranslation.setDefaults();
                        await this.KeywordTranslation.save(newTranslation);
                    }
                }
            }
        }

        // Create category
        const categoryResult = await this.categoriesRepository.createData(categoryData);
        const categoryId = categoryResult.id;

        // Create translations for all languages
        const translations = request.translations.map(translation => {
            const title = translation.title.trim();
            const slug = title.toLowerCase().replace(/\s+/g, "-");
            return {
                language_code: translation.language_code,
                display_title: translation.display_title?.trim() || title,
                title: title,
                slug: slug,
                description: translation.description || null,
                tags: request.tags || null
            };
        });

        await this.categoriesRepository.createTranslations(categoryId, translations);

        // Clear cache
        const cacheKey = `${process.env.NODE_ENV}:sitemap:categories`;
        await redisClient.del(cacheKey);
        const cacheKeyJson = `${process.env.NODE_ENV}:sitemap:categories-json:en`;
        await redisClient.del(cacheKeyJson);
        const cacheKeyJsonCn = `${process.env.NODE_ENV}:sitemap:categories-json:cn`;
        await redisClient.del(cacheKeyJsonCn);
        return
    }

    /**
     * Update category based on request parameters.
     * @param params - An instance of CategoryIdParamDto containing the category id to process of update data.
     * @param response - The response object for sending the HTTP response.
     * @param request - An instance of UpdateDto containing the category data with translations to process of update data.
     * @throws HttpException - Throws a 404 error if the category does not exist.
     * @throws HttpException - Throws a 404 error if the category duplicate exist.
     * @returns Responds with a 201 status if successful.
     * In case of an error, logs the error and responds with the respective error status and message.
     */
    async update(params: CategoryIdParamDto, request: UpdateDto) {
        const existsCat = await this.Categories.findOne({ where: { id: params.category_id } })
        if (!existsCat) {
            throw new HttpException(responseMessages.en.categories.not_exists, HttpStatus.NOT_FOUND)
        }

        // Validate translations array
        if (!request.translations || request.translations.length === 0) {
            throw new HttpException(responseMessages.en.categories.translations_required, HttpStatus.BAD_REQUEST)
        }

        // Update category image if provided
        if (request.image !== undefined) {
            existsCat.image = request.image || null;
            existsCat.tags = request.tags || null;
            await this.Categories.save(existsCat);
        }

        // Validate each translation and check for duplicates (excluding current category)
        for (const translation of request.translations) {
            const languageCode = translation.language_code;
            const title = translation.title.trim();
            const slug = title.toLowerCase().replace(/\s+/g, "-");
            if (!title) {
                continue
            }
            // Check if title exists in translations for this language (excluding current category)
            const existsTitle = await this.CategoryTranslation.findOne({
                where: {
                    title: title,
                    language_code: languageCode
                },
                relations: ['category']
            });
            if (existsTitle && existsTitle.category.id !== params.category_id) {
                throw new HttpException(responseMessages.en.categories.already_exists, HttpStatus.NOT_FOUND)
            }

            // Check if slug exists in translations for this language (excluding current category)
            const existsSlug = await this.CategoryTranslation.findOne({
                where: {
                    slug: slug,
                    language_code: languageCode
                },
                relations: ['category']
            });
            if (existsSlug && existsSlug.category.id !== params.category_id) {
                throw new HttpException(responseMessages.en.categories.already_exists, HttpStatus.NOT_FOUND)
            }
        }

        // Process keywords/tags for all languages
        const keywords = request.tags ? request.tags.split(",").map((tag) => tag.trim()).filter((tag) => tag !== "") : [];
        const processedLanguages = new Set<string>();


        for (const keywd of keywords) {
            // Find keyword by translation text for this language
            const keywordTranslation = await this.KeywordTranslation.findOne({
                where: { keyword: keywd.trim(), language_code: 'en' },
                relations: ['keywordRef']
            });
            let keyword = keywordTranslation ? keywordTranslation.keywordRef : null;

            if (!keyword) {

                // CREATE NEW KEYWORD
                keyword = await this.Keywords.save({ keyword: keywd.trim() });

                // Create translation for the keyword in this language
                const newTranslation = new KeywordTranslation();
                newTranslation.keywordRef = keyword;
                newTranslation.language_code = 'en';
                newTranslation.keyword = keywd.trim();
                newTranslation.setDefaults();
                await this.KeywordTranslation.save(newTranslation);
            }
        }


        // Process each translation - update if exists, create if not
        for (const translation of request.translations) {
            const languageCode = translation.language_code;
            const title = translation.title.trim();
            if (!title) {
                continue;
            }
            const slug = title.toLowerCase().replace(/\s+/g, "-");
            const displayTitle = translation.display_title?.trim() || title;

            // Check if translation exists for this language
            const existingTranslation = await this.CategoryTranslation.findOne({
                where: {
                    category: { id: params.category_id },
                    language_code: languageCode
                }
            });

            if (existingTranslation) {
                // Update existing translation
                existingTranslation.title = title;
                existingTranslation.slug = slug;
                existingTranslation.display_title = displayTitle;
                existingTranslation.description = translation.description || null;
                existingTranslation.tags = request.tags || null;
                await this.CategoryTranslation.save(existingTranslation);
            } else {
                // Create new translation
                const newTranslation = {
                    language_code: languageCode,
                    title: title,
                    slug: slug,
                    display_title: displayTitle,
                    description: translation.description || null,
                    tags: request.tags || null
                };
                await this.categoriesRepository.createTranslations(params.category_id, [newTranslation]);
            }
        }

        // Clear cache
        const cacheKey = `${process.env.NODE_ENV}:sitemap:categories`;
        await redisClient.del(cacheKey);
        const cacheKeyJson = `${process.env.NODE_ENV}:sitemap:categories-json:en`;
        await redisClient.del(cacheKeyJson);
        const cacheKeyJsonCn = `${process.env.NODE_ENV}:sitemap:categories-json:cn`;
        await redisClient.del(cacheKeyJsonCn);

        return
    }

    /** JP-
     * Retrieves a list of category for admin with two languages (en as default and requested language).
     *
     * @param queryParam - Query parameters for listing category list, including pagination, sorting options, and language_code.
     * @returns Category list with translations in two languages: English (en) as default and requested language.
     */
    async adminList(queryParam: any) {
        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

        // Get language code from query, default to 'en'
        const requestedLanguageCode = queryParam.language_code || "en"

        // If requested language is 'en', we only need English translations
        const languageCodes = requestedLanguageCode === "en" ? ["en"] : ["en", requestedLanguageCode]

        let sortByArr = {
            created_at: "categories.created_at",
            title: "sort_title",
            is_active: "categories.is_active"
        }

        let sort_by = queryParam.sort_by || "title"
        let sort_type = queryParam.sort || "ASC"
        sort_by = sortByArr[sort_by] || "COALESCE(translation_en.title, translation_req.title)"

        // Build query to load categories with translations in both languages
        let query = this.Categories.createQueryBuilder("categories")
            .leftJoin("categories.translations", "translation_en", "translation_en.language_code = 'en' AND translation_en.deleted_at IS NULL")
            .leftJoin("categories.translations", "translation_req", "translation_req.language_code = :requestedLanguageCode AND translation_req.deleted_at IS NULL", { requestedLanguageCode })
            // Load only English and requested language translations
            .leftJoinAndSelect("categories.translations", "translation",
                `translation.language_code IN (:...languageCodes) AND translation.deleted_at IS NULL`,
                { languageCodes })
            .addSelect(
                "COALESCE(translation_en.title, translation_req.title)",
                "sort_title"
            )
            .where("categories.deleted_at IS NULL")

            // Ensure category has at least English translation
            .andWhere("translation_en.id IS NOT NULL")

        if (queryParam.search) {
            query = query.andWhere(`(
                COALESCE(translation_en.title, '') ILIKE :search 
                OR COALESCE(translation_req.title, '') ILIKE :search
                OR COALESCE(translation_en.description, '') ILIKE :search
                OR COALESCE(translation_req.description, '') ILIKE :search
            )`, {
                search: `%${queryParam.search}%`
            })
        }
        if (queryParam.is_recommended) {
            query = query.andWhere("categories.is_recommended = :is_recommended", { is_recommended: true })
        }

        // Order by English title first, then requested language title
        query = query.orderBy(sort_by, sort_type)

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

        // Filter translations to only include English and requested language, with English always at index 0
        const filteredResults = results.map(category => {
            const filteredTranslations = category.translations
                .filter(translation => languageCodes.includes(translation.language_code))
                .sort((a, b) => {
                    // English ('en') always comes first
                    if (a.language_code === 'en') return -1;
                    if (b.language_code === 'en') return 1;
                    // Other languages maintain their order
                    return 0;
                });
            return {
                ...category,
                translations: filteredTranslations
            }
        })

        return {
            data: filteredResults,
            total: total,
            hasMoreResults: total > nextOffset ? 1 : 0
        }
    }

    /** JP-
     * Retrieves a list of category on query parameters.
     *
     * @param queryParam - Query parameters for listing category list, including pagination and sorting options.
     * @param req - The request object, containing the authenticated user information.
     * @param response - The response object for sending the JSON response.
     *
     * Responds with a 200 status and the list of category if successful.
     * Logs the error and responds with the error status and message if an exception occurs.
     */
    async list(queryParam: any, authUser: any) {
        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

        // Get language code from query or user preference, default to 'en'
        const languageCode = authUser.language || "en"

        let sortByArr = {
            created_at: "categories.created_at",
            title: "COALESCE(translation_req.title, translation_en.title)"
        }

        let sort_by = queryParam.sort_by || "title"
        let sort_type = queryParam.sort || "ASC"
        sort_by = sortByArr[sort_by] || "translation_req.title"

        // Build query with fallback to English - load only requested language or English translation
        let query = this.Categories.createQueryBuilder("categories")
            .leftJoin("categories.translations", "translation_req", "translation_req.language_code = :languageCode AND translation_req.deleted_at IS NULL", { languageCode })
            .leftJoin("categories.translations", "translation_en", "translation_en.language_code = 'en' AND translation_en.deleted_at IS NULL")
            // Load only the requested language translation, or English if requested language doesn't exist
            .leftJoinAndSelect("categories.translations", "translation",
                `(translation.language_code = :languageCode OR (translation.language_code = 'en' AND translation_req.id IS NULL)) AND translation.deleted_at IS NULL`,
                { languageCode })
            .where("categories.deleted_at IS NULL")
            //.andWhere("categories.is_hidden = :is_hidden", { is_hidden: false })
            // Ensure category has at least requested language or English translation
            .andWhere("(translation_req.id IS NOT NULL OR translation_en.id IS NOT NULL)")

        if (authUser.role != "admin") {
            query = query.andWhere("(categories.is_active = :is_active)", { is_active: true })
        }

        if (queryParam.search) {
            query = query.andWhere(`(
                COALESCE(translation_req.title, translation_en.title, '') ILIKE :search 
                OR COALESCE(translation_req.description, translation_en.description, '') ILIKE :search
            )`, {
                search: `%${queryParam.search}%`
            })
        }

        // Order by preferred language title, fallback to English
        // For COALESCE expressions, we need to add the expression as a select field so TypeORM can use it in ORDER BY
        if (sort_by.includes("COALESCE")) {
            // Add the COALESCE expression as a select field with an alias
            query = query.addSelect(sort_by, "sort_field")
            query = query.addOrderBy("sort_field", sort_type)
        } else {
            query = query.orderBy(sort_by, sort_type)
        }

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

        return {
            data: results,
            total: total,
            hasMoreResults: total > nextOffset ? 1 : 0
        }
    }

    async categoryKeywordJson(authUser: any) {
        try {
            const languageCode = authUser?.language || "en";
            const fallbackLanguage = "en";
            const languageCodes = languageCode === "en"
                ? ["en"]
                : ["en", languageCode];

            /* ------------------ CATEGORIES ------------------ */

            const categories = await this.Categories
                .createQueryBuilder("categories")
                .leftJoin(
                    "categories.translations",
                    "translation_req",
                    "translation_req.language_code = :languageCode AND translation_req.deleted_at IS NULL",
                    { languageCode }
                )
                .leftJoin(
                    "categories.translations",
                    "translation_en",
                    "translation_en.language_code = 'en' AND translation_en.deleted_at IS NULL"
                )
                .leftJoinAndSelect(
                    "categories.translations",
                    "translation",
                    `
                translation.language_code IN (:...languageCodes)
                AND translation.deleted_at IS NULL
                `,
                    { languageCodes }
                )
                .where("categories.is_active = true")
                .andWhere("(translation_req.id IS NOT NULL OR translation_en.id IS NOT NULL)")
                .orderBy("COALESCE(translation_req.title, translation_en.title)", "ASC")
                .getMany();

            /* ------------------ KEYWORDS ------------------ */

            const keywords = await this.Keywords
                .createQueryBuilder("keywords")
                .leftJoin(
                    "keywords.translations",
                    "translation_en",
                    "translation_en.language_code = 'en' AND translation_en.deleted_at IS NULL"
                )
                .leftJoin(
                    "keywords.translations",
                    "translation_req",
                    "translation_req.language_code = :languageCode AND translation_req.deleted_at IS NULL",
                    { languageCode }
                )
                .leftJoinAndSelect(
                    "keywords.translations",
                    "translation",
                    `
                translation.language_code IN (:...languageCodes)
                AND translation.deleted_at IS NULL
                `,
                    { languageCodes }
                )
                .where("keywords.deleted_at IS NULL")
                .andWhere("translation_en.id IS NOT NULL") // English must exist
                .getMany();

            /* ------------------ BUILD KEYWORD MAP ------------------ */

            const keywordMap = new Map<string, { id: string; keyword: string }>();

            for (const kw of keywords) {
                if (!kw?.translations?.length) continue;

                const reqTranslation = kw.translations.find(
                    t => t.language_code === languageCode
                );
                const enTranslation = kw.translations.find(
                    t => t.language_code === fallbackLanguage
                );

                const matchText = (enTranslation?.keyword)?.toLowerCase();
                const outputText = reqTranslation?.keyword || enTranslation?.keyword;

                if (!matchText || !outputText) continue;

                keywordMap.set(matchText, {
                    id: kw.id,
                    keyword: outputText
                });
            }

            /* ------------------ BUILD CATEGORY JSON ------------------ */

            const categoryKeywordJson: Record<string, any[]> = {};

            for (const category of categories) {
                if (!category?.translations?.length || !category?.tags) continue;

                const reqTranslation = category.translations.find(
                    t => t.language_code === languageCode
                );
                const enTranslation = category.translations.find(
                    t => t.language_code === fallbackLanguage
                );

                const categoryTitle = reqTranslation?.title || enTranslation?.title;
                if (!categoryTitle) continue;

                const tags = category.tags
                    .split(",")
                    .map(t => t.trim().toLowerCase())
                    .filter(Boolean);

                categoryKeywordJson[categoryTitle] = [];

                for (const tag of tags) {
                    const matchedKeyword = keywordMap.get(tag);
                    if (matchedKeyword) {
                        categoryKeywordJson[categoryTitle].push(matchedKeyword);
                    }
                }
            }

            return { categories: categoryKeywordJson };

        } catch (error) {
            console.error("categoryKeywordJson error:", error);
            throw new Error("Failed to generate category keyword JSON");
        }
    }


    // async categoryKeywordJson(authUser: any) {
    //     // Get language code from user preference, default to 'en'
    //     const languageCode = authUser.language || "en";
    //     console.log("Requested language code for categories-json:", languageCode);
    //     // const cacheKey = `${process.env.NODE_ENV}:sitemap:categories-json:${languageCode}`;

    //     // // 1️⃣ Check Redis cache
    //     // const cachedData = await redisClient.get(cacheKey);
    //     // if (cachedData) {
    //     //     return { categories: JSON.parse(cachedData) };
    //     // }
    //     console.log("CACHE MISS - categories-json");

    //     let query = this.Categories.createQueryBuilder("categories")
    //         .leftJoin("categories.translations", "translation_req", "translation_req.language_code = :languageCode AND translation_req.deleted_at IS NULL", { languageCode })
    //         .leftJoin("categories.translations", "translation_en", "translation_en.language_code = 'en' AND translation_en.deleted_at IS NULL")
    //         .leftJoinAndSelect("categories.translations", "translation",
    //             "(translation.language_code = :languageCode OR (translation.language_code = 'en' AND translation_req.id IS NULL)) AND translation.deleted_at IS NULL",
    //             { languageCode })
    //         .where('(categories.is_active = :is_active)', { is_active: true })
    //         .andWhere("(translation_req.id IS NOT NULL OR translation_en.id IS NOT NULL)")
    //         .select([
    //             "categories",
    //             "translation.id",
    //             "translation.title",
    //             "translation.tags"
    //         ])
    //         .orderBy("COALESCE(translation_req.title, translation_en.title)", "ASC")

    //     const results = await query.getMany();

    //     let categoryKeywordJson: Record<string, any[]> = {};
    //     let languageCodeKeyword = "en";
    //     // Get keywords with translations for the requested language, fallback to English
    //     // const keywordsQuery = this.Keywords.createQueryBuilder("keywords")
    //     //     .leftJoin("keywords.translations", "translation_req", "translation_req.language_code = :languageCodeKeyword AND translation_req.deleted_at IS NULL", { languageCodeKeyword })
    //     //     .leftJoin("keywords.translations", "translation_en", "translation_en.language_code = 'en' AND translation_en.deleted_at IS NULL")
    //     //     .leftJoinAndSelect("keywords.translations", "translation",
    //     //         `(translation.language_code = :languageCodeKeyword OR (translation.language_code = 'en' AND translation_req.id IS NULL)) AND translation.deleted_at IS NULL`,
    //     //         { languageCodeKeyword })
    //     //     .where("(translation_req.id IS NOT NULL OR translation_en.id IS NOT NULL)");

    //     // const keywords = await keywordsQuery.getMany();
    //     const languageCodes = ['en', languageCode];
    //     const keywords = await this.Keywords.createQueryBuilder("keywords")
    //         // Required joins to ensure existence
    //         .leftJoin(
    //             "keywords.translations",
    //             "translation_en",
    //             "translation_en.language_code = 'en' AND translation_en.deleted_at IS NULL"
    //         )
    //         .leftJoin(
    //             "keywords.translations",
    //             "translation_req",
    //             "translation_req.language_code = :languageCodeKeyword AND translation_req.deleted_at IS NULL",
    //             { languageCodeKeyword }
    //         )

    //         // ✅ Load ONLY English + requested language
    //         .leftJoinAndSelect(
    //             "keywords.translations",
    //             "translation",
    //             "translation.language_code IN (:...languageCodes) AND translation.deleted_at IS NULL",
    //             { languageCodes }
    //         )

    //         .where("keywords.deleted_at IS NULL")

    //         // ✅ Must have English translation
    //         .andWhere("translation_en.id IS NOT NULL")

    //         .getMany();

    //     const keywordIndex = languageCode == "en" ? 0 : 1;

    //     for (const cat of results) {
    //         const translation = cat.translations && cat.translations.length > 0 ? cat.translations[0] : null;
    //         if (translation && cat.tags) {
    //             categoryKeywordJson[translation.title] = [];
    //             let tags = cat.tags.split(",").map(tag => tag.trim()).filter(tag => tag !== "");
    //             for (const tag of tags) {
    //                 // Find keyword by translation text - check both requested language and English
    //                 for (const kw of keywords) {
    //                     const kwTranslation = kw.translations && kw.translations.length > 0 ? kw.translations[0] : null;
    //                     if (kwTranslation) {
    //                         // Check if the tag matches the translation (either requested language or English fallback)
    //                         const translationText = kwTranslation.keyword.toLowerCase();
    //                         if (translationText === tag.toLowerCase()) {
    //                             categoryKeywordJson[translation.title].push({
    //                                 id: kw.id,
    //                                 keyword: kw?.translations[keywordIndex]?.keyword
    //                             });
    //                             break;
    //                         }
    //                     }
    //                 }
    //             }
    //         }
    //     }

    //     //     await redisClient.setex(cacheKey, 7 * 24 * 60 * 60, JSON.stringify(categoryKeywordJson));
    //     return { categories: categoryKeywordJson }
    // }

    /**
     * Update category activation status based on request parameters.
     * @param params - An instance of CategoryIdParamDto containing the category id to process of update data.
     * The request, if category activation status is active than its inactive or if category activation status is inactive then its make active.
     * @param response - The response object for sending the HTTP response.
     * @throws HttpException - Throws a 404 error if the category does not exist.
     * @returns 201 status if the update is successful.
     * In case of an error, logs the error and responds with the respective error status and message.
     */
    async activationStatusUpdate(params: CategoryIdParamDto) {
        const existsCat = await this.Categories.findOne({ where: { id: params.category_id } })
        if (!existsCat) {
            throw new HttpException(responseMessages.en.categories.not_exists, HttpStatus.NOT_FOUND)
        }
        const activeCategoryCnt = await this.Categories.count({ where: { is_active: true } })

        if (activeCategoryCnt == 1 && existsCat.is_active) {
            throw new HttpException(responseMessages.en.categories.atleast_one_active, HttpStatus.BAD_GATEWAY)
        }

        existsCat["is_active"] = existsCat.is_active == true ? false : true
        await this.Categories.save(existsCat)

        return
    }

    /**
     * Toggle category recommended flag (recommended ↔ not recommended).
     * @param params - Category id from route params.
     * @throws HttpException - 404 if the category does not exist.
     */
    async recommendedStatusUpdate(params: CategoryIdParamDto) {
        const existsCat = await this.Categories.findOne({ where: { id: params.category_id } })

        if (!existsCat) {
            throw new HttpException(responseMessages.en.categories.not_exists, HttpStatus.NOT_FOUND)
        }


        existsCat["is_recommended"] = existsCat.is_recommended == true ? false : true
        await this.Categories.save(existsCat)

        return
    }
    async influencerCount(params: CategoryIdParamDto) {
        const { category_id } = params
        const existsCat = await this.Categories.findOne({ where: { id: category_id } })
        const count = await this.InfluencerCategory.createQueryBuilder("influencerCategory")
            .innerJoin("influencerCategory.users", "user")
            .where("influencerCategory.category = :category_id", { category_id })
            .andWhere("user.role = :role", { role: UserRoles.INFLUENCER })
            .getCount()

        return { count }
    }

    async delete(params: CategoryIdParamDto) {
        const { category_id } = params

        const protectedCategory = await this.CategoryTranslation.findOne({
            where: {
                category: { id: category_id },
                slug: In(["hot", "international", 'all']),
                language_code: "en",
            },
        })
        if (protectedCategory) {
            throw new HttpException(responseMessages.en.categories.category_not_deletable, HttpStatus.BAD_REQUEST)
        }

        const whereCondition = { where: { category: { id: category_id } } }

        // let isCategoryUserd = (await this.InfluencerCategory.count(whereCondition)) || (await this.BrandIndustries.count(whereCondition))
        // if (isCategoryUserd) {
        //     throw new HttpException(responseMessages.en.categories.category_in_use, HttpStatus.BAD_GATEWAY)
        // }
        await this.Categories.softDelete({ id: category_id })
        await this.CategoryTranslation.softDelete({ category: { id: category_id } })
        return
    }

    async categoryInfluencer(param: any, query: any) {
        // Get language code from param or default to 'en'
        const languageCode = query.language_code || "en";

        // Find category by slug in translations
        const category = await this.Categories.createQueryBuilder("categories")
            .leftJoin("categories.translations", "translation_req", "translation_req.language_code = :languageCode AND translation_req.deleted_at IS NULL", { languageCode })
            .leftJoinAndSelect("categories.translations", "translation",
                "(translation.language_code = :languageCode OR (translation.language_code = 'en' AND translation_req.id IS NULL)) AND translation.deleted_at IS NULL",
                { languageCode })
            .where("categories.is_active = :is_active", { is_active: true })
            .andWhere("categories.deleted_at IS NULL")
            .andWhere("translation.slug = :slug", { slug: param.slug })
            .getOne();

        if (!category) {
            throw new HttpException(responseMessages.en.categories.not_exists, HttpStatus.NOT_FOUND)
        }

        const influencer = await this.Users.createQueryBuilder("user")
            .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.slug", "social_medias.id", "social_medias.social_user_name", "social_medias.social_type"])
            .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_req_cat', 'keyword_translation_req_cat.language_code = :languageCode AND keyword_translation_req_cat.deleted_at IS NULL', { languageCode })
            .leftJoin('keywords.translations', 'keyword_translation_en_cat', 'keyword_translation_en_cat.language_code = \'en\' AND keyword_translation_en_cat.deleted_at IS NULL')
            .leftJoinAndSelect('keywords.translations', 'keyword_translation_cat',
                '(keyword_translation_cat.language_code = :languageCode OR (keyword_translation_cat.language_code = \'en\' AND keyword_translation_req_cat.id IS NULL)) AND keyword_translation_cat.deleted_at IS NULL',
                { languageCode })
            .where('influencerCategory.category = :category_id', { category_id: category.id })
            .andWhere("user.is_active = true")
            .andWhere("user.role = 'influencer'")
            .orderBy("user.engagement_ratio", 'DESC')
            .take(10)
            .getMany();


        return { data: influencer, category: category }
    }

    /**
     * Get category details by ID with translation support.
     * @param params - An instance of CategoryIdParamDto containing the category id.
     * @param languageCode - Optional language code for translation (defaults to 'en').
     * @param authUser - Optional authenticated user for role-based access.
     * @returns Category details with translation for the specified language.
     * @throws HttpException - Throws a 404 error if the category does not exist.
     */
    async detail(params: CategoryIdParamDto, authUser?: any) {
        const category = await this.Categories.createQueryBuilder("categories")
            .leftJoinAndSelect("categories.translations", "translation", "translation.deleted_at IS NULL")
            .where("categories.id = :categoryId", { categoryId: params.category_id })
            .andWhere("categories.deleted_at IS NULL")
            .getOne();

        if (!category) {
            throw new HttpException(responseMessages.en.categories.not_exists, HttpStatus.NOT_FOUND)
        }

        // Check if user is admin, if not, only return active categories
        if (authUser?.role !== "admin" && !category.is_active) {
            throw new HttpException(responseMessages.en.categories.not_exists, HttpStatus.NOT_FOUND)
        }

        return { category: category }
    }
}