import { Sequelize, WhereOptions, literal, Op, IncludeOptions } from "sequelize";
import Models from "../models";
import { SeaQAQuestion, SeaQAQuestionAttributes, SeaQAQuestionCreationAttributes } from "../models/SeaQAQuestion";
import { SeaQAAnswer, SeaQAAnswerAttributes, SeaQAAnswerCreationAttributes } from "../models/SeaQAAnswer";
import { SeaQAComment, SeaQACommentAttributes, SeaQACommentCreationAttributes } from "../models/SeaQAComment";
import { AppError } from "../../utils/errors";
import { Common } from "../../utils/common";
import { SEA_QA } from "../config/constants";
import { LocalizedContent } from "../../utils/contentGenerator";
import _ from 'lodash';

export class SeaQADao {
    private accountId: number | null;
    private userId: number | null;
    private language: string;
    private followedAuthorCache = new Map<number, boolean>();
    private likedEntityCache = new Map<string, boolean>();
    private bookmarkedEntityCache = new Map<string, boolean>();

    constructor(
        private options: {
            language: string;
            scope: string[] | null;
            accountId: number | null;
            userId: number | null;
            config: userConfig | null;
        } = {
                language: process.env.DEFAULT_LANGUAGE_CODE!,
                scope: null,
                config: null,
                userId: null,
                accountId: null,
            }
    ) {
        this.language = options.language;
        this.userId = options.userId ?? null;
        this.accountId = options.accountId ?? null;
    }

    private async isAuthorFollowed(authorId: number): Promise<boolean> {
        if (!this.userId || !authorId || this.userId === authorId) {
            return false;
        }

        if (this.followedAuthorCache.has(authorId)) {
            return this.followedAuthorCache.get(authorId)!;
        }

        const followed = await Models.UserFollower.findOne({
            where: {
                followerId: this.userId,
                followingId: authorId
            }
        });

        const isFollowed = !!followed;
        this.followedAuthorCache.set(authorId, isFollowed);
        return isFollowed;
    }

    private async formatAuthor(author: any): Promise<any> {
        if (!author) return author;
        const profile = author.userProfile || {};

        let profileImage = null;
        if (profile.profileImage) {
            const img = profile.profileImage;
            profileImage = {
                id: Number(img.id),
                fileName: img.fileName,
                uniqueName: img.uniqueName,
                filePath: `${process.env.PROTOCOL}://${process.env.API_HOST}/attachment/${img.uniqueName}`,
                cdnUrl: `${process.env.CDN_PATH}/attachment/${img.uniqueName}`
            };
        }

        const rank = profile.rank
            ? {
                id: Number(profile.rank.id),
                name: profile.rank.content?.name || profile.rank.defaultContent?.name || null,
                category: profile.rank.parentCategory?.code || null
            }
            : null;

        return {
            id: Number(author.id),
            name: profile.name || '',
            profileImage: profileImage,
            isFollowed: await this.isAuthorFollowed(Number(author.id)),
            rank
        };
    }

    private authorInclude = (): IncludeOptions => ({
        model: Models.User,
        as: 'author',
        attributes: ['id', 'onlineStatus'],
        include: [
            {
                model: Models.UserProfile,
                as: 'userProfile',
                attributes: ['name', 'aboutMe', 'socialMediaLinks'],
                include: [
                    { model: Models.Attachment, as: 'profileImage' },
                    {
                        model: Models.Category,
                        as: 'rank',
                        attributes: ['id'],
                        include: [
                            { model: Models.CategoryContent, as: 'content', attributes: ['name'] },
                            { model: Models.CategoryContent, as: 'defaultContent', attributes: ['name'] },
                            { model: Models.Category, as: 'parentCategory', attributes: ['code'] }
                        ]
                    }
                ]
            }
        ]
    });

    private async formatQuestion(question: any): Promise<any> {
        if (!question) return question;

        // Localized title & description fallbacks
        if (question.content) {
            question.title = question.content.title || 'No Title Available';
            question.description = question.content.description || '';
        } else if (question.defaultContent) {
            question.title = question.defaultContent.title || 'No Title Available';
            question.description = question.defaultContent.description || '';
        } else {
            question.title = 'No Title Available';
            question.description = '';
        }
        delete question.content;
        delete question.defaultContent;
        delete question.questionContents;

        // 1. Transform categories
        let categories: any[] = [];
        if (question.categories && question.categories.length > 0) {
            categories = question.categories.map((cat: any) => ({
                id: Number(cat.id),
                name: cat.content?.name || cat.defaultContent?.name || '',
                code: cat.code
            }));
        }
        question.categories = categories;
        question.likesCount = Number(question.likesCount || 0);

        // 2. Transform author
        if (question.author) {
            question.author = await this.formatAuthor(question.author);
        }

        return question;
    }

    private async formatAnswer(answer: any): Promise<any> {
        if (!answer) return answer;

        // Localized body fallback
        if (answer.content) {
            answer.body = answer.content.body || 'No content available';
        } else if (answer.defaultContent) {
            answer.body = answer.defaultContent.body || 'No content available';
        } else {
            answer.body = 'No content available';
        }
        delete answer.content;
        delete answer.defaultContent;
        delete answer.answerContents;

        if (answer.author) {
            answer.author = await this.formatAuthor(answer.author);
        }

        if (answer.question) {
            const formattedQ = await this.formatQuestion(answer.question);
            answer.question = {
                id: formattedQ.id,
                title: formattedQ.title,
                code: formattedQ.code,
                status: formattedQ.status,
                categories: formattedQ.categories || []
            };
        }

        answer.likesCount = Number(answer.likesCount || 0);

        return answer;
    }

    private async formatComment(comment: any): Promise<any> {
        if (!comment) return comment;

        const formattedComment: any = {
            id: Number(comment.id),
            content: comment.content,
            createdAt: comment.createdAt,
            author: comment.author ? await this.formatAuthor(comment.author) : null
        };

        if (comment.replies && comment.replies.length > 0) {
            formattedComment.replies = await Promise.all(comment.replies.map((reply: any) => this.formatComment(reply)));
        } else {
            formattedComment.replies = [];
        }

        return formattedComment;
    }

    private async isEntityLiked(entityId: number, entityType: 'question' | 'answer'): Promise<boolean> {
        if (!this.userId || !entityId) return false;

        const cacheKey = `${entityType}:${entityId}`;

        if (this.likedEntityCache.has(cacheKey)) {
            return this.likedEntityCache.get(cacheKey)!;
        }

        const liked = await Models.SeaQALike.findOne({
            where: {
                userId: this.userId,
                entityId,
                entityType
            }
        });

        const isLiked = !!liked;
        this.likedEntityCache.set(cacheKey, isLiked);
        return isLiked;
    }

    private async isEntityBookmarked(entityId: number, entityType: 'question' | 'answer'): Promise<boolean> {
        if (!this.userId || !entityId) return false;

        const cacheKey = `${entityType}:${entityId}`;

        if (this.bookmarkedEntityCache.has(cacheKey)) {
            return this.bookmarkedEntityCache.get(cacheKey)!;
        }

        const bookmarked = await Models.SeaQABookmark.findOne({
            where: {
                userId: this.userId,
                entityId,
                entityType
            }
        });

        const isBookmarked = !!bookmarked;
        this.bookmarkedEntityCache.set(cacheKey, isBookmarked);
        return isBookmarked;
    }

    private normalizeIdList = (value: unknown): number[] => {
        const rawValues = Array.isArray(value)
            ? value
            : typeof value === 'string'
                ? value.split(',')
                : value == null
                    ? []
                    : [value];

        return rawValues
            .map((id) => Number(id))
            .filter((id) => Number.isFinite(id));
    }

    private buildCategoryFilterInclude = (categoryIds: unknown, withContent = true): IncludeOptions | null => {
        const normalizedCategoryIds = this.normalizeIdList(categoryIds);
        if (normalizedCategoryIds.length === 0) return null;

        const include: IncludeOptions = {
            model: Models.Category,
            as: 'categories',
            required: true,
            attributes: ['id', 'code'],
            through: {
                attributes: [],
                where: {
                    categoryId: {
                        [Op.in]: normalizedCategoryIds
                    }
                }
            }
        };

        if (withContent) {
            include.include = [
                {
                    model: Models.CategoryContent,
                    as: 'content',
                    required: false,
                    include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                },
                {
                    model: Models.CategoryContent,
                    as: 'defaultContent',
                    required: false,
                    include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                }
            ];
        }

        return include;
    }

    private async resolveQuestionIdsForCategories(categoryIds: unknown): Promise<number[] | null> {
        const normalizedCategoryIds = this.normalizeIdList(categoryIds);
        if (normalizedCategoryIds.length === 0) return null;

        const rows = await Models.SeaQAQuestionCategory.findAll({
            attributes: ['questionId'],
            where: {
                categoryId: {
                    [Op.in]: normalizedCategoryIds
                }
            },
            group: ['questionId'],
            raw: true
        });

        return rows
            .map((row: any) => Number(row.questionId))
            .filter((id: number) => Number.isFinite(id));
    }

    // --- Question Operations ---

    async createQuestion(data: Partial<SeaQAQuestionAttributes> & { title: string; description?: string; code: string; userId: number; accountId: number | null }, categoryIds: number[], languages: LanguageInfo, transaction?: any): Promise<SeaQAQuestion> {
        try {
            const { title, description, ...restData } = data;
            const descriptionText = description ? await Common.convertHtmlToText(description) : '';
            const questionContentObj = { title: title || '', description: description || '', descriptionText };
            const questionContents = LocalizedContent.generate(questionContentObj, languages);

            const question = await Models.SeaQAQuestion.create({
                ...restData,
                userId: this.userId,
                accountId: this.accountId,
                status: SEA_QA.QUESTION_STATUS.PENDING,
                questionContents
            }, {
                include: [{ model: Models.SeaQAQuestionContent, as: 'questionContents' }],
                transaction
            });

            if (categoryIds && categoryIds.length > 0) {
                await question.setCategories(categoryIds, { transaction });
            }

            return question;
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_CREATING_QUESTION', err);
        }
    }

    async updateQuestion(id: number, data: Partial<SeaQAQuestionAttributes> & { title?: string; description?: string }, languages?: LanguageInfo, categoryIds?: number[], transaction?: any): Promise<void> {
        try {
            const { title, description, ...restData } = data;
            if (Object.keys(restData).length > 0) {
                await Models.SeaQAQuestion.update(restData, { where: { id }, transaction });
            }
            if (languages && (typeof title !== 'undefined' || typeof description !== 'undefined')) {
                let verification = await Models.SeaQAQuestionContent.findOne({ where: { questionId: id, languageId: languages.requested.id }, transaction });
                const descriptionText = typeof description !== 'undefined' ? await Common.convertHtmlToText(description || '') : (verification ? verification.descriptionText : '');

                const questionContent: any = {
                    languageId: languages.requested.id,
                    questionId: id
                };

                if (typeof title !== 'undefined') {
                    questionContent.title = title || '';
                } else if (verification) {
                    questionContent.title = verification.title;
                } else {
                    questionContent.title = '';
                }

                if (typeof description !== 'undefined') {
                    questionContent.description = description || '';
                    questionContent.descriptionText = descriptionText;
                } else if (verification) {
                    questionContent.description = verification.description;
                    questionContent.descriptionText = verification.descriptionText;
                } else {
                    questionContent.description = '';
                    questionContent.descriptionText = '';
                }

                if (verification) {
                    await Models.SeaQAQuestionContent.update(questionContent, { where: { id: verification.id }, transaction });
                } else {
                    await Models.SeaQAQuestionContent.create(questionContent, { transaction });
                }
            }
            if (categoryIds) {
                const question = await Models.SeaQAQuestion.findByPk(id);
                if (question) {
                    await question.setCategories(categoryIds, { transaction });
                }
            }
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_UPDATING_QUESTION', err);
        }
    }

    async deleteQuestion(id: number, transaction?: any): Promise<void> {
        try {
            await Models.SeaQAQuestion.destroy({ where: { id }, transaction });
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_DELETING_QUESTION', err);
        }
    }

    async getQuestionById(id: number, transaction?: any): Promise<SeaQAQuestionResponse> {
        try {
            const question = await Models.SeaQAQuestion.findOne({
                where: { id },
                include: [
                    this.authorInclude(),
                    {
                        model: Models.Category,
                        as: 'categories',
                        include: [
                            {
                                model: Models.CategoryContent,
                                as: 'content',
                                required: false,
                                include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                            },
                            {
                                model: Models.CategoryContent,
                                as: 'defaultContent',
                                required: false,
                                include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                            }
                        ]
                    },
                    {
                        model: Models.SeaQAAnswer,
                        as: 'answers',
                        required: false,
                        where: { status: SEA_QA.ANSWER_STATUS.PUBLISHED },
                        include: [
                            this.authorInclude(),
                            {
                                model: Models.SeaQAAnswerContent,
                                as: 'content',
                                required: false,
                                include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                            },
                            {
                                model: Models.SeaQAAnswerContent,
                                as: 'defaultContent',
                                required: false,
                                include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                            }
                        ],
                        order: [['isFeatured', 'DESC'], ['createdAt', 'DESC']]
                    },
                    {
                        model: Models.SeaQAQuestionContent,
                        as: 'content',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                    },
                    {
                        model: Models.SeaQAQuestionContent,
                        as: 'defaultContent',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                    }
                ],
                transaction
            });

            if (!question) throw new AppError(404, 'QUESTION_NOT_FOUND', {});

            const result = JSON.parse(JSON.stringify(question));
            if (result.answers && result.answers.length > 0) {
                result.answers = await Promise.all(result.answers.map(async (answer: any) => {
                    const formattedAnswer = await this.formatAnswer(answer);
                    formattedAnswer.isLiked = await this.isEntityLiked(formattedAnswer.id, SEA_QA.LIKE_TYPE.ANSWER);
                    formattedAnswer.isBookmarked = await this.isEntityBookmarked(formattedAnswer.id, SEA_QA.LIKE_TYPE.ANSWER);
                    return formattedAnswer;
                }));
            } else {
                result.answers = [];
            }
            result.isLiked = await this.isEntityLiked(id, SEA_QA.LIKE_TYPE.QUESTION);
            result.isBookmarked = await this.isEntityBookmarked(id, SEA_QA.LIKE_TYPE.QUESTION);
            return await this.formatQuestion(result);
        } catch (err) {
            if (err instanceof AppError) throw err;
            throw new AppError(500, 'ERROR_WHILE_GETTING_QUESTION', err);
        }
    }

    async getQuestionList(filters: SeaQAQuestionListRequest): Promise<{ count: number; rows: SeaQAQuestionResponse[] }> {
        try {
            const { page, perPage, searchText, status, categoryIds, isTrending, isGoodQuestion, myQuestions, answerFilter, userId, bookmarkedByUserId } = filters;
            const offset = (page - 1) * perPage;
            const matchedQuestionIds = await this.resolveQuestionIdsForCategories(categoryIds);
            if (matchedQuestionIds !== null && matchedQuestionIds.length === 0) {
                return { count: 0, rows: [] };
            }

            const where: any = { accountId: this.accountId };
            if (status) where.status = status;

            if (matchedQuestionIds && matchedQuestionIds.length > 0) {
                where.id = { [Op.in]: matchedQuestionIds };
            }

            if (isGoodQuestion !== undefined && isGoodQuestion !== null) where.isGoodQuestion = isGoodQuestion;
            if (myQuestions) where.userId = this.userId;
            if (userId) where.userId = userId;

            if (searchText) {
                const escapedSearchText = searchText.replace(/'/g, "\\'");
                where[Op.and] = where[Op.and] || [];
                where[Op.and].push(
                    literal(`EXISTS (
                        SELECT 1 FROM sea_qa_question_content sqqc_search
                        WHERE sqqc_search.question_id = SeaQAQuestion.id
                        AND sqqc_search.deleted_at IS NULL
                        AND sqqc_search.title LIKE '%${escapedSearchText}%'
                    )`)
                );
            }

            if (bookmarkedByUserId) {
                where[Op.and] = where[Op.and] || [];
                where[Op.and].push(
                    literal(`EXISTS (
                        SELECT 1 FROM sea_qa_bookmarks sqb
                        WHERE sqb.entity_id = SeaQAQuestion.id
                        AND sqb.entity_type = 'question'
                        AND sqb.user_id = ${bookmarkedByUserId}
                    )`)
                );
            }

            // Applicability filtering
            if (!myQuestions && this.userId) {
                const userProfile = await Models.UserProfile.findOne({ where: { userId: this.userId }, attributes: ['rankId', 'shipTypeId'] });
                if (userProfile) {
                    const { rankId, shipTypeId } = userProfile;
                    // Filter questions where categories have no applicability OR user matches applicability
                    const userStatus = rankId ? 'Seafarer' : 'Ashore';
                    where[Op.and] = where[Op.and] || [];
                    where[Op.and].push(
                        literal(`((
                            SELECT COUNT(*) 
                            FROM sea_qa_question_categories sqqc
                            JOIN category_applicabilities ca ON sqqc.category_id = ca.category_id
                            WHERE sqqc.question_id = SeaQAQuestion.id
                            AND (
                                (ca.applicable_ranks IS NOT NULL AND JSON_CONTAINS(ca.applicable_ranks, '${rankId}'))
                                OR (ca.applicable_ship_types IS NOT NULL AND JSON_CONTAINS(ca.applicable_ship_types, '${shipTypeId}'))
                                OR (ca.applicable_to IS NOT NULL AND JSON_EXTRACT(ca.applicable_to, '$') = '${userStatus}')
                                OR (ca.applicable_to IS NOT NULL AND JSON_EXTRACT(ca.applicable_to, '$') = 'Others')
                            )
                        ) > 0 OR NOT EXISTS (
                            SELECT 1 
                            FROM sea_qa_question_categories sqqc2
                            JOIN category_applicabilities ca2 ON sqqc2.category_id = ca2.category_id
                            WHERE sqqc2.question_id = SeaQAQuestion.id
                        ))`)
                    );

                }
            }

            if (answerFilter) {
                where[Op.and] = where[Op.and] || [];
                if (answerFilter === 'short_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND status = 'published' 
                            AND word_count < 100
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'long_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND status = 'published' 
                            AND word_count >= 100
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'reviewed_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND status = 'published' 
                            AND requires_approval = 0
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'not_reviewed_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND (status = 'pending_approval' OR requires_approval = 1 OR status = 'draft')
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'featured_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND status = 'published' 
                            AND is_featured = 1
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'answered') {
                    where.answersCount = { [Op.gt]: 0 };
                } else if (answerFilter === 'unanswered') {
                    where.answersCount = 0;
                }
            }

            const include: any[] = [
                {
                    model: Models.User,
                    as: 'author',
                    attributes: ['id', 'onlineStatus'],
                    include: [
                        {
                            model: Models.UserProfile,
                            as: 'userProfile',
                            attributes: ['name', 'aboutMe', 'socialMediaLinks'],
                            include: [
                                {
                                    model: Models.Attachment,
                                    as: 'profileImage'
                                }
                            ]
                        }
                    ]
                },
                {
                    model: Models.Category,
                    as: 'categories',
                    through: { attributes: [] },
                    include: [
                        {
                            model: Models.CategoryContent,
                            as: 'content',
                            required: false,
                            include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                        },
                        {
                            model: Models.CategoryContent,
                            as: 'defaultContent',
                            required: false,
                            include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                        }
                    ]
                },
                {
                    model: Models.SeaQAQuestionContent,
                    as: 'content',
                    required: false,
                    include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                },
                {
                    model: Models.SeaQAQuestionContent,
                    as: 'defaultContent',
                    required: false,
                    include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                }
            ];


            const order: any[] = [];
            let mappedSortBy = filters.sortBy;
            if (mappedSortBy === 'sort-order' || mappedSortBy === 'created') mappedSortBy = 'createdAt';

            if (searchText) {
                const escapedSearchText = searchText.replace(/'/g, "\\'");
                order.push([literal(`(
                    SELECT COUNT(*) 
                    FROM sea_qa_question_content AS sqqc_order
                    WHERE sqqc_order.question_id = SeaQAQuestion.id
                    AND sqqc_order.deleted_at IS NULL
                    AND sqqc_order.title LIKE '%${escapedSearchText}%'
                )`), 'DESC']);
            }

            if (isTrending) order.push(['views', 'DESC']);
            else if (filters.bookmarkedByUserId && (mappedSortBy === 'id' || mappedSortBy === 'createdAt' || !mappedSortBy)) {
                order.push([literal(`(
                    SELECT sqb.id FROM sea_qa_bookmarks sqb
                    WHERE sqb.entity_id = SeaQAQuestion.id
                    AND sqb.entity_type = 'question'
                    AND sqb.user_id = ${filters.bookmarkedByUserId}
                    LIMIT 1
                )`), filters.sortDirection || 'DESC']);
            }
            else if (mappedSortBy && mappedSortBy !== 'createdAt') order.push([mappedSortBy, filters.sortDirection || 'DESC']);
            else if (filters.bookmarkedByUserId) {
                order.push([literal(`(
                    SELECT sqb.created_at FROM sea_qa_bookmarks sqb
                    WHERE sqb.entity_id = SeaQAQuestion.id
                    AND sqb.entity_type = 'question'
                    AND sqb.user_id = ${filters.bookmarkedByUserId}
                    LIMIT 1
                )`), filters.sortDirection || 'DESC']);
            }
            else order.push(['createdAt', 'DESC']);

            const { count, rows } = await Models.SeaQAQuestion.findAndCountAll({
                where,
                include,
                offset,
                limit: perPage,
                order,
                distinct: true
            });

            const formattedRows = await Promise.all(JSON.parse(JSON.stringify(rows)).map((row: any) => this.formatQuestion(row)));
            return { count, rows: formattedRows };
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_LISTING_QUESTIONS', err);
        }
    }

    // --- Answer Operations ---

    async createAnswer(data: Partial<SeaQAAnswerAttributes> & { body: string; questionId: number; userId: number; wordCount: number }, languages: LanguageInfo, transaction?: any): Promise<SeaQAAnswer> {
        try {
            const { body, ...restData } = data;
            const answerContents = LocalizedContent.generate({ body: body || '' }, languages);
            return await Models.SeaQAAnswer.create({
                ...restData,
                userId: this.userId,
                answerContents
            }, {
                include: [{ model: Models.SeaQAAnswerContent, as: 'answerContents' }],
                transaction
            });
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_CREATING_ANSWER', err);
        }
    }

    async deleteAnswer(id: number, transaction?: any): Promise<void> {
        try {
            await Models.SeaQAAnswer.destroy({ where: { id }, transaction });
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_DELETING_ANSWER', err);
        }
    }

    async getAnswerById(id: number, transaction?: any): Promise<SeaQAAnswerResponse> {
        try {
            const answer = await Models.SeaQAAnswer.findOne({
                where: { id },
                include: [
                    this.authorInclude(),
                    {
                        model: Models.SeaQAAnswerContent,
                        as: 'content',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                    },
                    {
                        model: Models.SeaQAAnswerContent,
                        as: 'defaultContent',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                    }
                ],
                transaction
            });

            if (!answer) throw new AppError(404, 'ANSWER_NOT_FOUND', {});

            const ans = JSON.parse(JSON.stringify(answer));
            ans.isLiked = await this.isEntityLiked(ans.id, SEA_QA.LIKE_TYPE.ANSWER);
            ans.isBookmarked = await this.isEntityBookmarked(ans.id, SEA_QA.LIKE_TYPE.ANSWER);
            const commentsCount = await Models.SeaQAComment.count({ where: { answerId: ans.id }, transaction });
            ans.commentsCount = commentsCount;
            return await this.formatAnswer(ans);
        } catch (err) {
            if (err instanceof AppError) throw err;
            throw new AppError(500, 'ERROR_WHILE_GETTING_ANSWER', err);
        }
    }

    async updateAnswer(id: number, data: Partial<SeaQAAnswerAttributes> & { body?: string }, languages?: LanguageInfo, transaction?: any): Promise<void> {
        try {
            const { body, ...restData } = data;
            if (Object.keys(restData).length > 0) {
                await Models.SeaQAAnswer.update(restData, { where: { id }, transaction });
            }
            if (languages && body) {
                let verification = await Models.SeaQAAnswerContent.findOne({ where: { answerId: id, languageId: languages.requested.id }, transaction });
                const answerContent = {
                    body: body,
                    languageId: languages.requested.id,
                    answerId: id
                };
                if (verification) {
                    await Models.SeaQAAnswerContent.update(answerContent, { where: { id: verification.id }, transaction });
                } else {
                    await Models.SeaQAAnswerContent.create(answerContent, { transaction });
                }
            }
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_UPDATING_ANSWER', err);
        }
    }

    async getAnswersByQuestionId(questionId: number, transaction?: any): Promise<SeaQAAnswerResponse[]> {
        try {
            const answers = await Models.SeaQAAnswer.findAll({
                where: { questionId, status: SEA_QA.ANSWER_STATUS.PUBLISHED },
                include: [
                    this.authorInclude(),
                    {
                        model: Models.SeaQAAnswerContent,
                        as: 'content',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                    },
                    {
                        model: Models.SeaQAAnswerContent,
                        as: 'defaultContent',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                    }
                ],
                order: [['isFeatured', 'DESC'], ['createdAt', 'DESC']],
                transaction
            });

            const results = JSON.parse(JSON.stringify(answers));
            const formattedResults = [];
            for (const ans of results) {
                ans.isLiked = await this.isEntityLiked(ans.id, SEA_QA.LIKE_TYPE.ANSWER);
                ans.isBookmarked = await this.isEntityBookmarked(ans.id, SEA_QA.LIKE_TYPE.ANSWER);
                const commentsCount = await Models.SeaQAComment.count({ where: { answerId: ans.id }, transaction });
                ans.commentsCount = commentsCount;
                formattedResults.push(await this.formatAnswer(ans));
            }

            return formattedResults;
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_GETTING_ANSWERS', err);
        }
    }

    async getUserAnswerForQuestion(questionId: number, userId: number, transaction?: any): Promise<SeaQAAnswerResponse | null> {
        try {
            const answer = await Models.SeaQAAnswer.findOne({
                where: { questionId, userId },
                include: [
                    this.authorInclude(),
                    {
                        model: Models.SeaQAAnswerContent,
                        as: 'content',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                    },
                    {
                        model: Models.SeaQAAnswerContent,
                        as: 'defaultContent',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                    }
                ],
                order: [['createdAt', 'DESC']],
                transaction
            });

            if (!answer) {
                return null;
            }

            const result = JSON.parse(JSON.stringify(answer));
            result.isLiked = await this.isEntityLiked(result.id, SEA_QA.LIKE_TYPE.ANSWER);
            result.isBookmarked = await this.isEntityBookmarked(result.id, SEA_QA.LIKE_TYPE.ANSWER);
            const commentsCount = await Models.SeaQAComment.count({ where: { answerId: result.id }, transaction });
            result.commentsCount = commentsCount;
            return await this.formatAnswer(result);
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_GETTING_USER_ANSWER', err);
        }
    }

    // --- Comment Operations ---

    async createComment(data: SeaQACommentCreationAttributes, transaction?: any): Promise<SeaQAComment> {
        try {
            return await Models.SeaQAComment.create({
                ...data,
                userId: this.userId
            }, { transaction });
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_CREATING_COMMENT', err);
        }
    }

    async getCommentById(id: number, transaction?: any): Promise<SeaQACommentResponse> {
        try {
            const comment = await Models.SeaQAComment.findOne({
                where: { id },
                include: [
                    this.authorInclude(),
                    {
                        model: Models.SeaQAComment, as: 'replies',
                        include: [
                            this.authorInclude()
                        ]
                    }
                ],
                transaction
            });

            if (!comment) throw new AppError(404, 'COMMENT_NOT_FOUND', {});

            const res = JSON.parse(JSON.stringify(comment));
            const formattedComment: any = {
                id: res.id,
                content: res.content,
                createdAt: res.createdAt,
                author: res.author ? await this.formatAuthor(res.author) : null
            };
            if (res.replies) {
                formattedComment.replies = await Promise.all(res.replies.map(async (reply: any) => ({
                    id: reply.id,
                    content: reply.content,
                    createdAt: reply.createdAt,
                    author: reply.author ? await this.formatAuthor(reply.author) : null
                })));
            } else {
                formattedComment.replies = [];
            }
            return formattedComment;
        } catch (err) {
            if (err instanceof AppError) throw err;
            throw new AppError(500, 'ERROR_WHILE_GETTING_COMMENT', err);
        }
    }

    async getCommentsByAnswerId(answerId: number, filters?: any, transaction?: any): Promise<SeaQACommentResponse[]> {
        try {
            const where: any = { answerId, parentId: null };
            if (filters?.userId) {
                where.userId = filters.userId;
            }
            const comments = await Models.SeaQAComment.findAll({
                where,
                include: [
                    this.authorInclude(),
                    {
                        model: Models.SeaQAComment, as: 'replies',
                        include: [
                            this.authorInclude()
                        ]
                    }
                ],
                order: [['createdAt', 'ASC']],
                transaction
            });
            const results = JSON.parse(JSON.stringify(comments));
            return Promise.all(results.map(async (comment: any) => {
                const formattedComment: any = {
                    id: comment.id,
                    content: comment.content,
                    createdAt: comment.createdAt,
                    author: comment.author ? await this.formatAuthor(comment.author) : null
                };
                if (comment.replies) {
                    formattedComment.replies = await Promise.all(comment.replies.map(async (reply: any) => ({
                        id: reply.id,
                        content: reply.content,
                        createdAt: reply.createdAt,
                        author: reply.author ? await this.formatAuthor(reply.author) : null
                    })));
                } else {
                    formattedComment.replies = [];
                }
                return formattedComment;
            }));
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_GETTING_COMMENTS', err);
        }
    }

    async getCommentList(filters: SeaQACommentGlobalListRequest): Promise<{ count: number, rows: SeaQACommentResponse[] }> {
        try {
            const { page, perPage, sortBy, sortDirection, userId, answerId } = filters;
            const offset = (page - 1) * perPage;
            const where: any = {};
            if (userId) where.userId = userId;
            if (answerId) where.answerId = answerId;

            const { count, rows } = await Models.SeaQAComment.findAndCountAll({
                where,
                include: [
                    this.authorInclude(),
                    {
                        model: Models.SeaQAAnswer,
                        as: 'answer',
                        attributes: ['id', 'questionId']
                    }
                ],
                order: [[(sortBy === 'sort-order' || sortBy === 'created') ? 'createdAt' : (sortBy || 'createdAt'), sortDirection || 'DESC']],
                limit: perPage,
                offset
            });

            const results = JSON.parse(JSON.stringify(rows));
            const formattedRows = await Promise.all(results.map(async (comment: any) => {
                const formattedComment: any = {
                    id: comment.id,
                    content: comment.content,
                    createdAt: comment.createdAt,
                    answerId: comment.answerId,
                    parentId: comment.parentId,
                    author: comment.author ? await this.formatAuthor(comment.author) : null
                };
                return formattedComment;
            }));

            return { count, rows: formattedRows };
        } catch (err) {
            if (err instanceof AppError) throw err;
            throw new AppError(500, 'ERROR_WHILE_LISTING_COMMENTS', err);
        }
    }

    // --- Bookmark Operations ---
    async toggleBookmark(entityId: number, entityType: 'question' | 'answer', transaction?: any): Promise<boolean> {
        try {
            const existing = await Models.SeaQABookmark.findOne({
                where: { userId: this.userId, entityId, entityType },
                transaction
            });

            if (existing) {
                await existing.destroy({ transaction });
                if (entityType === 'question') {
                    await Models.SeaQAQuestion.decrement('bookmarksCount', { where: { id: entityId }, transaction });
                } else {
                    await Models.SeaQAAnswer.decrement('bookmarksCount', { where: { id: entityId }, transaction });
                }
                if (this.userId) {
                    await Models.UserProfile.decrement('seaQABookmarksCount', { where: { userId: this.userId }, transaction });
                }
                this.bookmarkedEntityCache.set(`${entityType}:${entityId}`, false);
                return false;
            } else {
                await Models.SeaQABookmark.create({
                    userId: this.userId as number,
                    entityId,
                    entityType
                }, { transaction });
                if (entityType === 'question') {
                    await Models.SeaQAQuestion.increment('bookmarksCount', { where: { id: entityId }, transaction });
                } else {
                    await Models.SeaQAAnswer.increment('bookmarksCount', { where: { id: entityId }, transaction });
                }
                if (this.userId) {
                    await Models.UserProfile.increment('seaQABookmarksCount', { where: { userId: this.userId }, transaction });
                }
                this.bookmarkedEntityCache.set(`${entityType}:${entityId}`, true);
                return true;
            }
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_TOGGLING_BOOKMARK', err);
        }
    }

    // --- Like Operations ---

    async toggleLike(entityId: number, entityType: 'question' | 'answer', transaction?: any): Promise<boolean> {
        try {
            const existing = await Models.SeaQALike.findOne({
                where: { userId: this.userId, entityId, entityType },
                transaction
            });

            if (existing) {
                await existing.destroy({ transaction });
                if (entityType === 'question') {
                    await Models.SeaQAQuestion.decrement('likesCount', { where: { id: entityId }, transaction });
                } else {
                    await Models.SeaQAAnswer.decrement('likesCount', { where: { id: entityId }, transaction });
                }
                // Decrement the user's own seaQALikesCount
                if (this.userId) {
                    await Models.UserProfile.decrement('seaQALikesCount', { where: { userId: this.userId }, transaction });
                }
                this.likedEntityCache.set(`${entityType}:${entityId}`, false);
                return false;
            } else {
                await Models.SeaQALike.create({
                    userId: this.userId as number,
                    entityId,
                    entityType
                }, { transaction });
                if (entityType === 'question') {
                    await Models.SeaQAQuestion.increment('likesCount', { where: { id: entityId }, transaction });
                } else {
                    await Models.SeaQAAnswer.increment('likesCount', { where: { id: entityId }, transaction });
                }
                // Increment the user's own seaQALikesCount
                if (this.userId) {
                    await Models.UserProfile.increment('seaQALikesCount', { where: { userId: this.userId }, transaction });
                }
                this.likedEntityCache.set(`${entityType}:${entityId}`, true);
                return true;
            }
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_TOGGLING_LIKE', err);
        }
    }

    async unlike(entityId: number, entityType: 'question' | 'answer', transaction?: any): Promise<boolean> {
        try {
            const existing = await Models.SeaQALike.findOne({
                where: { userId: this.userId, entityId, entityType },
                transaction
            });

            if (!existing) {
                this.likedEntityCache.set(`${entityType}:${entityId}`, false);
                return false;
            }

            await existing.destroy({ transaction });
            if (entityType === 'question') {
                await Models.SeaQAQuestion.decrement('likesCount', { where: { id: entityId }, transaction });
            } else {
                await Models.SeaQAAnswer.decrement('likesCount', { where: { id: entityId }, transaction });
            }
            // Decrement the user's own seaQALikesCount
            if (this.userId) {
                await Models.UserProfile.decrement('seaQALikesCount', { where: { userId: this.userId }, transaction });
            }
            this.likedEntityCache.set(`${entityType}:${entityId}`, false);
            return false;
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_UNLIKING', err);
        }
    }

    // --- Follow/Topic Operations ---

    async followTopic(categoryId: number, transaction?: any): Promise<void> {
        try {
            await Models.UserFollowedTopic.findOrCreate({
                where: { userId: this.userId, categoryId },
                transaction
            });
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_FOLLOWING_TOPIC', err);
        }
    }

    async unfollowTopic(categoryId: number, transaction?: any): Promise<void> {
        try {
            await Models.UserFollowedTopic.destroy({
                where: { userId: this.userId, categoryId },
                transaction
            });
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_UNFOLLOWING_TOPIC', err);
        }
    }

    async getFollowedTopics(transaction?: any): Promise<CategoryObjectSummaryInteface[]> {
        try {
            const followed = await Models.UserFollowedTopic.findAll({
                where: { userId: this.userId },
                include: [{ model: Models.Category, as: 'category' }],
                transaction
            });
            return followed.map((f: any) => {
                if (!f.category) return null;
                const cat = JSON.parse(JSON.stringify(f.category));
                return {
                    id: cat.id,
                    name: cat.name,
                    code: cat.code
                };
            }).filter(Boolean) as any;
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_GETTING_FOLLOWED_TOPICS', err);
        }
    }

    // --- Applicability Operations ---

    async updateApplicability(categoryId: number, ranks: number[], shipTypes: number[], applicableTo: string, transaction?: any): Promise<void> {
        try {
            const [applicability] = await Models.CategoryApplicability.findOrBuild({
                where: { categoryId },
                transaction
            });
            applicability.applicableRanks = ranks;
            applicability.applicableShipTypes = shipTypes;
            applicability.applicableTo = applicableTo;
            await applicability.save({ transaction });
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_UPDATING_APPLICABILITY', err);
        }
    }

    async getApplicabilityByCategoryId(categoryId: number, transaction?: any): Promise<CategoryApplicabilityInterface | null> {
        try {
            const applicability = await Models.CategoryApplicability.findOne({
                where: { categoryId },
                transaction
            });
            return applicability ? JSON.parse(JSON.stringify(applicability)) : null;
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_GETTING_APPLICABILITY', err);
        }
    }

    async getAnswerList(filters: SeaQAAnswerListRequest): Promise<{ count: number; rows: SeaQAAnswerResponse[] }> {
        try {
            const { page, perPage, questionId, status, isFeatured, myAnswers, drafts, sortBy, sortDirection, includeQuestion, userId, bookmarkedByUserId } = filters;
            const offset = (page - 1) * perPage;

            const where: any = {};
            if (questionId) where.questionId = questionId;
            if (status) where.status = status;
            if (isFeatured !== undefined) where.isFeatured = isFeatured;
            if (myAnswers) where.userId = this.userId;
            if (userId) where.userId = userId;
            if (drafts) where.status = 'draft';

            if (bookmarkedByUserId) {
                where[Op.and] = where[Op.and] || [];
                where[Op.and].push(
                    literal(`EXISTS (
                        SELECT 1 FROM sea_qa_bookmarks sqb
                        WHERE sqb.entity_id = SeaQAAnswer.id
                        AND sqb.entity_type = 'answer'
                        AND sqb.user_id = ${bookmarkedByUserId}
                    )`)
                );
            }

            const order: any[] = [];
            let mappedSortBy = sortBy;
            if (mappedSortBy === 'sort-order' || mappedSortBy === 'created') mappedSortBy = 'createdAt';

            const dir = sortDirection?.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

            if (bookmarkedByUserId && (mappedSortBy === 'id' || mappedSortBy === 'createdAt' || !mappedSortBy)) {
                order.push([literal(`(
                    SELECT sqb.id FROM sea_qa_bookmarks sqb
                    WHERE sqb.entity_id = SeaQAAnswer.id
                    AND sqb.entity_type = 'answer'
                    AND sqb.user_id = ${bookmarkedByUserId}
                    LIMIT 1
                )`), dir]);
            } else if (mappedSortBy && mappedSortBy !== 'createdAt') {
                order.push([mappedSortBy, dir]);
            } else if (bookmarkedByUserId) {
                order.push([literal(`(
                    SELECT sqb.created_at FROM sea_qa_bookmarks sqb
                    WHERE sqb.entity_id = SeaQAAnswer.id
                    AND sqb.entity_type = 'answer'
                    AND sqb.user_id = ${bookmarkedByUserId}
                    LIMIT 1
                )`), dir]);
            } else {
                order.push(['id', 'DESC']);
            }

            const include: any[] = [
                {
                    model: Models.User,
                    as: 'author',
                    attributes: ['id', 'onlineStatus'],
                    include: [
                        {
                            model: Models.UserProfile,
                            as: 'userProfile',
                            attributes: ['name', 'aboutMe', 'socialMediaLinks'],
                            include: [
                                {
                                    model: Models.Attachment,
                                    as: 'profileImage'
                                }
                            ]
                        }
                    ]
                },
                {
                    model: Models.SeaQAAnswerContent,
                    as: 'content',
                    required: false,
                    include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                },
                {
                    model: Models.SeaQAAnswerContent,
                    as: 'defaultContent',
                    required: false,
                    include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                }
            ];

            if (includeQuestion) {
                include.push({
                    model: Models.SeaQAQuestion,
                    as: 'question',
                    include: [
                        {
                            model: Models.SeaQAQuestionContent,
                            as: 'content',
                            required: false,
                            include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                        },
                        {
                            model: Models.SeaQAQuestionContent,
                            as: 'defaultContent',
                            required: false,
                            include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                        }
                    ]
                });
            }

            const { count, rows } = await Models.SeaQAAnswer.findAndCountAll({
                where,
                offset,
                limit: perPage,
                order,
                include
            });

            // If includeQuestion is true, fetch categories for these questions in a separate query to avoid Sequelize nested subquery bugs
            const questionCategoriesMap: { [key: number]: any[] } = {};
            if (includeQuestion && rows.length > 0) {
                const questionIds = rows.map((row: any) => row.question?.id).filter(Boolean);
                if (questionIds.length > 0) {
                    const questionsWithCategories = await Models.SeaQAQuestion.findAll({
                        where: { id: { [Op.in]: questionIds } },
                        attributes: ['id'],
                        include: [
                            {
                                model: Models.Category,
                                as: 'categories',
                                through: { attributes: [] },
                                include: [
                                    {
                                        model: Models.CategoryContent,
                                        as: 'content',
                                        required: false,
                                        include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                                    },
                                    {
                                        model: Models.CategoryContent,
                                        as: 'defaultContent',
                                        required: false,
                                        include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                                    }
                                ]
                            }
                        ]
                    });

                    const questionsData = JSON.parse(JSON.stringify(questionsWithCategories));
                    for (const q of questionsData) {
                        questionCategoriesMap[q.id] = q.categories || [];
                    }
                }
            }

            // Format author object for each row
            const formattedRows = await Promise.all(rows.map(async (row: any) => {
                const answerObj = JSON.parse(JSON.stringify(row));
                if (answerObj.question && questionCategoriesMap[answerObj.question.id]) {
                    answerObj.question.categories = questionCategoriesMap[answerObj.question.id];
                }
                const answer = await this.formatAnswer(answerObj);

                // Add comment count
                const commentsCount = await Models.SeaQAComment.count({ where: { answerId: answer.id } });
                answer.commentsCount = commentsCount;

                answer.isLiked = await this.isEntityLiked(answer.id, 'answer');
                answer.isBookmarked = await this.isEntityBookmarked(answer.id, 'answer');

                return answer;
            }));

            return { count, rows: formattedRows };
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_GETTING_ANSWER_LIST', err);
        }
    }

    async getQuestionListAll(filters: SeaQAQuestionListRequest): Promise<{ count: number; rows: SeaQAQuestionSimpleResponse[] }> {
        try {
            const { page, perPage, searchText, status, categoryIds, isTrending, isGoodQuestion, myQuestions, answerFilter, userId } = filters;
            const offset = (page - 1) * perPage;
            const matchedQuestionIds = await this.resolveQuestionIdsForCategories(categoryIds);
            if (matchedQuestionIds !== null && matchedQuestionIds.length === 0) {
                return { count: 0, rows: [] };
            }

            const where: any = { accountId: this.accountId };
            if (status) where.status = status;

            if (matchedQuestionIds && matchedQuestionIds.length > 0) {
                where.id = { [Op.in]: matchedQuestionIds };
            }

            if (isGoodQuestion !== undefined && isGoodQuestion !== null) where.isGoodQuestion = isGoodQuestion;
            if (myQuestions) where.userId = this.userId;
            if (userId) where.userId = userId;

            if (searchText) {
                const escapedSearchText = searchText.replace(/'/g, "\\'");
                where[Op.and] = where[Op.and] || [];
                where[Op.and].push(
                    literal(`EXISTS (
                        SELECT 1 FROM sea_qa_question_content sqqc_search
                        WHERE sqqc_search.question_id = SeaQAQuestion.id
                        AND sqqc_search.deleted_at IS NULL
                        AND sqqc_search.title LIKE '%${escapedSearchText}%'
                    )`)
                );
            }

            // Applicability filtering
            if (!myQuestions && this.userId) {
                const userProfile = await Models.UserProfile.findOne({ where: { userId: this.userId }, attributes: ['rankId', 'shipTypeId'] });
                if (userProfile) {
                    const { rankId, shipTypeId } = userProfile;
                    const userStatus = rankId ? 'Seafarer' : 'Ashore';
                    where[Op.and] = where[Op.and] || [];
                    where[Op.and].push(
                        literal(`((
                            SELECT COUNT(*) 
                            FROM sea_qa_question_categories sqqc
                            JOIN category_applicabilities ca ON sqqc.category_id = ca.category_id
                            WHERE sqqc.question_id = SeaQAQuestion.id
                            AND (
                                (ca.applicable_ranks IS NOT NULL AND JSON_CONTAINS(ca.applicable_ranks, '${rankId}'))
                                OR (ca.applicable_ship_types IS NOT NULL AND JSON_CONTAINS(ca.applicable_ship_types, '${shipTypeId}'))
                                OR (ca.applicable_to IS NOT NULL AND JSON_EXTRACT(ca.applicable_to, '$') = '${userStatus}')
                                OR (ca.applicable_to IS NOT NULL AND JSON_EXTRACT(ca.applicable_to, '$') = 'Others')
                            )
                        ) > 0 OR NOT EXISTS (
                            SELECT 1 
                            FROM sea_qa_question_categories sqqc2
                            JOIN category_applicabilities ca2 ON sqqc2.category_id = ca2.category_id
                            WHERE sqqc2.question_id = SeaQAQuestion.id
                        ))`)
                    );
                }
            }

            if (answerFilter) {
                where[Op.and] = where[Op.and] || [];
                if (answerFilter === 'short_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND status = 'published' 
                            AND word_count < 100
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'long_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND status = 'published' 
                            AND word_count >= 100
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'reviewed_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND status = 'published' 
                            AND requires_approval = 0
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'not_reviewed_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND (status = 'pending_approval' OR requires_approval = 1 OR status = 'draft')
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'featured_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 
                            FROM sea_qa_answers 
                            WHERE question_id = SeaQAQuestion.id 
                            AND status = 'published' 
                            AND is_featured = 1
                            AND deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'answered') {
                    where.answersCount = { [Op.gt]: 0 };
                } else if (answerFilter === 'unanswered') {
                    where.answersCount = 0;
                }
            }

            const order: any[] = [];
            if (searchText) {
                const escapedSearchText = searchText.replace(/'/g, "\\'");
                order.push([literal(`(
                    SELECT COUNT(*) 
                    FROM sea_qa_question_content AS sqqc_order
                    WHERE sqqc_order.question_id = SeaQAQuestion.id
                    AND sqqc_order.deleted_at IS NULL
                    AND sqqc_order.title LIKE '%${escapedSearchText}%'
                )`), 'DESC']);
            }
            order.push(['id', 'DESC']);

            const { count, rows } = await Models.SeaQAQuestion.findAndCountAll({
                attributes: ['id', 'code', 'status'],
                where,
                offset,
                limit: searchText ? 20 : perPage,
                order,
                include: [
                    {
                        model: Models.SeaQAQuestionContent,
                        as: 'content',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                    },
                    {
                        model: Models.SeaQAQuestionContent,
                        as: 'defaultContent',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                    }
                ],
                distinct: true
            });

            const results = JSON.parse(JSON.stringify(rows));
            for (const question of results) {
                if (question.content) {
                    question.title = question.content.title || 'No Title Available';
                } else if (question.defaultContent) {
                    question.title = question.defaultContent.title || 'No Title Available';
                } else {
                    question.title = 'No Title Available';
                }
                delete question.categories;
                delete question.content;
                delete question.defaultContent;
            }

            return { count, rows: results };
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_GETTING_ALL_QUESTIONS', err);
        }
    }

    async updateQuestionsBulk(ids: number[], data: Partial<SeaQAQuestionAttributes>, transaction?: any): Promise<void> {
        try {
            await Models.SeaQAQuestion.update(data, { where: { id: ids }, transaction });
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_UPDATING_QUESTIONS_BULK', err);
        }
    }

    async updateAnswersBulk(ids: number[], data: Partial<SeaQAAnswerAttributes>, transaction?: any): Promise<void> {
        try {
            await Models.SeaQAAnswer.update(data, { where: { id: ids }, transaction });
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_UPDATING_ANSWERS_BULK', err);
        }
    }

    async getPublicQuestionList(filters: SeaQAQuestionListRequest): Promise<{ count: number; rows: SeaQAQuestionResponse[] }> {
        try {
            const { page, perPage, searchText, status, categoryIds, isTrending, isGoodQuestion, myQuestions, answerFilter, sortBy, sortDirection, userId, isGoodRandom, goodQuestionsRandom, goodQuestionsOnTopRandom } = filters;
            const offset = (page - 1) * perPage;
            const matchedQuestionIds = await this.resolveQuestionIdsForCategories(categoryIds);
            if (matchedQuestionIds !== null && matchedQuestionIds.length === 0) {
                return { count: 0, rows: [] };
            }

            const where: any = { accountId: this.accountId };

            if (matchedQuestionIds && matchedQuestionIds.length > 0) {
                where.id = { [Op.in]: matchedQuestionIds };
            }

            if (status) {
                where.status = status;
            } else if (!(myQuestions && this.userId)) {
                // where.status = SEA_QA.QUESTION_STATUS.APPROVED;
                where.status = { [Op.in]: [SEA_QA.QUESTION_STATUS.APPROVED, SEA_QA.QUESTION_STATUS.CLOSED] }
            }

            if (myQuestions && this.userId) {
                where.userId = this.userId;
            }
            if (userId) {
                where.userId = userId;
            }

            // Filter out merged questions that don't have an approved target
            where[Op.and] = where[Op.and] || [];
            where[Op.and].push(
                literal(`(SeaQAQuestion.status != 'merged' OR (SeaQAQuestion.status = 'merged' AND SeaQAQuestion.merged_into_question_id IS NOT NULL AND EXISTS (SELECT 1 FROM sea_qa_questions sqq WHERE sqq.id = SeaQAQuestion.merged_into_question_id AND sqq.status = 'approved' AND sqq.deleted_at IS NULL)))`)
            );

            if (isGoodQuestion !== undefined && isGoodQuestion !== null) where.isGoodQuestion = isGoodQuestion;

            // Text search
            if (searchText) {
                const escapedSearchText = searchText.replace(/'/g, "\\'");
                where[Op.and] = where[Op.and] || [];
                where[Op.and].push(
                    literal(`EXISTS (
                        SELECT 1 FROM sea_qa_question_content sqqc_search
                        WHERE sqqc_search.question_id = SeaQAQuestion.id
                        AND sqqc_search.deleted_at IS NULL
                        AND sqqc_search.title LIKE '%${escapedSearchText}%'
                    )`)
                );
            }

            // Applicability filtering
            if (!(myQuestions && this.userId) && this.userId) {
                const userProfile = await Models.UserProfile.findOne({ where: { userId: this.userId }, attributes: ['rankId', 'shipTypeId'] });
                if (userProfile) {
                    const { rankId, shipTypeId } = userProfile;
                    const userStatus = rankId ? 'Seafarer' : 'Ashore';
                    where[Op.and] = where[Op.and] || [];
                    where[Op.and].push(
                        literal(`((
                            SELECT COUNT(*) 
                            FROM sea_qa_question_categories sqqc
                            JOIN category_applicabilities ca ON sqqc.category_id = ca.category_id
                            WHERE sqqc.question_id = SeaQAQuestion.id
                            AND (
                                (ca.applicable_ranks IS NOT NULL AND JSON_CONTAINS(ca.applicable_ranks, '${rankId}'))
                                OR (ca.applicable_ship_types IS NOT NULL AND JSON_CONTAINS(ca.applicable_ship_types, '${shipTypeId}'))
                                OR (ca.applicable_to IS NOT NULL AND JSON_EXTRACT(ca.applicable_to, '$') = '${userStatus}')
                                OR (ca.applicable_to IS NOT NULL AND JSON_EXTRACT(ca.applicable_to, '$') = 'Others')
                            )
                        ) > 0 OR NOT EXISTS (
                            SELECT 1 
                            FROM sea_qa_question_categories sqqc2
                            JOIN category_applicabilities ca2 ON sqqc2.category_id = ca2.category_id
                            WHERE sqqc2.question_id = SeaQAQuestion.id
                        ))`)
                    );
                }
            }

            if (answerFilter) {
                where[Op.and] = where[Op.and] || [];
                if (answerFilter === 'short_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 FROM sea_qa_answers sqa 
                            WHERE sqa.question_id = SeaQAQuestion.id 
                            AND sqa.status = 'published' 
                            AND sqa.word_count < 100
                            AND sqa.deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'long_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 FROM sea_qa_answers sqa 
                            WHERE sqa.question_id = SeaQAQuestion.id 
                            AND sqa.status = 'published' 
                            AND sqa.word_count >= 100
                            AND sqa.deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'reviewed_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 FROM sea_qa_answers sqa 
                            WHERE sqa.question_id = SeaQAQuestion.id 
                            AND sqa.status = 'published' 
                            AND sqa.requires_approval = 0
                            AND sqa.deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'not_reviewed_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 FROM sea_qa_answers sqa 
                            WHERE sqa.question_id = SeaQAQuestion.id 
                            AND sqa.status = 'published' 
                            AND sqa.requires_approval = 1
                            AND sqa.deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'featured_answers') {
                    where[Op.and].push(
                        literal(`EXISTS (
                            SELECT 1 FROM sea_qa_answers sqa 
                            WHERE sqa.question_id = SeaQAQuestion.id 
                            AND sqa.status = 'published' 
                            AND sqa.is_featured = 1
                            AND sqa.deleted_at IS NULL
                        )`)
                    );
                } else if (answerFilter === 'answered') {
                    where.answersCount = { [Op.gt]: 0 };
                } else if (answerFilter === 'unanswered') {
                    where.answersCount = 0;
                }
            }

            // Ordering
            let order: any[] = [];
            let mappedSortBy = sortBy;
            if (mappedSortBy === 'sort-order' || mappedSortBy === 'created') mappedSortBy = 'createdAt';

            if (searchText) {
                const escapedSearchText = searchText.replace(/'/g, "\\'");
                order.push([literal(`(
                    SELECT COUNT(*) 
                    FROM sea_qa_question_content AS sqqc_order
                    WHERE sqqc_order.question_id = SeaQAQuestion.id
                    AND sqqc_order.deleted_at IS NULL
                    AND sqqc_order.title LIKE '%${escapedSearchText}%'
                )`), 'DESC']);
            }

            if (isGoodRandom || goodQuestionsRandom || goodQuestionsOnTopRandom || mappedSortBy === 'good-random' || mappedSortBy === 'random') {
                order.push(['isGoodQuestion', 'DESC']);
                order.push(literal('RAND()'));
            } else if (isTrending) {
                order.push(['views', 'DESC']);
            } else if (mappedSortBy) {
                order.push([mappedSortBy, sortDirection || 'DESC']);
            } else {
                order.push(['id', 'DESC']);
            }

            const { count, rows } = await Models.SeaQAQuestion.findAndCountAll({
                where,
                offset,
                limit: perPage,
                order,
                include: [
                    this.authorInclude(),
                    {
                        model: Models.Category,
                        as: 'categories',
                        through: { attributes: [] },
                        include: [
                            {
                                model: Models.CategoryContent,
                                as: 'content',
                                required: false,
                                include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                            },
                            {
                                model: Models.CategoryContent,
                                as: 'defaultContent',
                                required: false,
                                include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                            }
                        ]
                    },
                    {
                        model: Models.SeaQAQuestionContent,
                        as: 'content',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: this.language } }]
                    },
                    {
                        model: Models.SeaQAQuestionContent,
                        as: 'defaultContent',
                        required: false,
                        include: [{ model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                    },
                    {
                        model: Models.SeaQAQuestion,
                        as: 'mergedInto',
                        attributes: ['id', 'code', 'status'],
                        required: false
                    }
                ],
                distinct: true
            });

            console.log(`[getPublicQuestionList] searchText: ${searchText}, limit: ${perPage}, count: ${count}, rows returned: ${rows.length}`);
            // Format rows
            const formattedRows = await Promise.all(JSON.parse(JSON.stringify(rows)).map(async (row: any) => {
                const questionData = await this.formatQuestion(row);

                if (row.status === 'merged' && row.mergedInto && row.mergedInto.status === 'approved') {
                    questionData.code = row.mergedInto.code;
                    questionData.mergedInto = { code: row.mergedInto.code };
                }

                questionData.isLiked = await this.isEntityLiked(row.id, 'question');
                questionData.isBookmarked = await this.isEntityBookmarked(row.id, 'question');

                return questionData;
            }));

            return { count, rows: formattedRows };
        } catch (err) {
            throw new AppError(500, 'ERROR_WHILE_GETTING_PUBLIC_QUESTIONS', err);
        }
    }
}