import { Sequelize, WhereOptions, FindOptions, literal, fn, col, Op } from "sequelize";
import Models, { sequelize } from "../models";
import { LocalizedContent } from "../../utils/contentGenerator";
import { AppError } from "../../utils/errors";
import Moment from "moment-timezone";
import _ from 'lodash';
import { CAMPAIGN, USER } from '../config/constants';
import { Common } from "../../utils/common"
import { EmailTemplateService } from "../services/emailTemplate.service"
import { CampaignUser } from "../models/CampaignUser";
import * as handlebars from 'handlebars';
import { AttachmentService } from "../services/attachment.service";

const campaignAttributes: AttributeElement[] = [
    'id', 'code', 'title', 'description', 'descriptionText', 'template', 'type', 'sendAt', 'filters', 'status', 'createdAt', 'updatedAt', 'isRecurring'
];

const userAttributes: AttributeElement[] = [
    'id', 'onlineStatus',
    [literal('`campaignUser->userProfile`.`name`'), 'name'],
    [
        literal(`(
              SELECT JSON_OBJECT(
                'id', a.id,
                'fileName', a.file_name,
                'uniqueName', a.unique_name,
                'filePath', CONCAT('${process.env.PROTOCOL}://', '${process.env.API_HOST}', '/attachment/', a.unique_name),
                'cdnUrl', CONCAT('${process.env.CDN_PATH}', '/attachment/', a.unique_name)
              )
              FROM user_profile as up
              JOIN attachments as a ON up.profile_image_id = a.id
              WHERE up.user_id = campaignUser.id
              LIMIT 1
            )`),
        'profileImage'
    ]
];

const authorAttributes: AttributeElement[] = [
    'id', 'onlineStatus',
    [literal('`author->userProfile`.`name`'), 'name'],
    [
        literal(`(
              SELECT JSON_OBJECT(
                'id', a.id,
                'fileName', a.file_name,
                'uniqueName', a.unique_name,
                'filePath', CONCAT('${process.env.PROTOCOL}://', '${process.env.API_HOST}', '/attachment/', a.unique_name),
                'cdnUrl', CONCAT('${process.env.CDN_PATH}', '/attachment/', a.unique_name)
              )
              FROM user_profile as up
              JOIN attachments as a ON up.profile_image_id = a.id
              WHERE up.user_id = author.id
              LIMIT 1
            )`),
        'profileImage'
    ]
];

const updatedByAttributes: AttributeElement[] = [
    'id', 'onlineStatus',
    [literal('`updatedBy->userProfile`.`name`'), 'name'],
    [
        literal(`(
              SELECT JSON_OBJECT(
                'id', a.id,
                'fileName', a.file_name,
                'uniqueName', a.unique_name,
                'filePath', CONCAT('${process.env.PROTOCOL}://', '${process.env.API_HOST}', '/attachment/', a.unique_name),
                'cdnUrl', CONCAT('${process.env.CDN_PATH}', '/attachment/', a.unique_name)
              )
              FROM user_profile as up
              JOIN attachments as a ON up.profile_image_id = a.id
              WHERE up.user_id = author.id
              LIMIT 1
            )`),
        'profileImage'
    ]
];

const attachmentAttributes: AttributeElement[] = [
    'id',
    'fileName',
    'uniqueName',
    [fn('CONCAT', process.env.PROTOCOL, '://', process.env.API_HOST, "/attachment/", literal('`campaignAttachment`.`unique_name`')), 'filePath'],
    [fn('CONCAT', process.env.CDN_PATH, literal('`campaignAttachment`.`file_path`')), 'cdnUrl'],
]

export class CampaignDao {
    private accountId: number | null;
    private userId: number | null;
    private language: string;
    private scope: string[] | null;
    private config: userConfig | null;
    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.scope = options.scope ?? [];
        this.config = options.config ?? null;
        this.userId = options.userId ?? null
        this.accountId = options.accountId ?? null
    }

    // set campaign sort Order
    setSortOrder = async (data: CampaignSetSortOrderDaoInput, options: DaoOptions = {}): Promise<boolean> => {
        const { id, before = null, after = null } = data;
        const { transaction } = options;
        try {
            let faqData = await Models.Campaign.findOne({ attributes: ['id', 'sortOrder'], where: { id: id, accountId: this.accountId, isRevision: false } });
            if (faqData) {
                if (before || after) {
                    let locationData = await Models.Campaign.findOne({ attributes: ['id', 'sortOrder'], where: { id: before ? before : after, accountId: this.accountId, isRevision: false } });
                    if (faqData.sortOrder < locationData.sortOrder) {
                        await Models.Campaign.decrement('sortOrder', {
                            by: 1,
                            where: {
                                accountId: this.accountId,
                                [Op.and]: [
                                    { sortOrder: { [Op.gt]: faqData.sortOrder } },
                                    before ? { sortOrder: { [Op.lt]: locationData.sortOrder } } : { sortOrder: { [Op.lte]: locationData.sortOrder } }
                                ]
                            },
                            transaction: transaction
                        });
                        await Models.Campaign.update(before ? { sortOrder: locationData.sortOrder - 1 } : { sortOrder: locationData.sortOrder }, { where: { id: faqData.id, accountId: this.accountId }, transaction: transaction });
                    } else if (faqData.sortOrder > locationData.sortOrder) {
                        await Models.Campaign.increment('sortOrder', {
                            by: 1,
                            where: {
                                accountId: this.accountId,
                                [Op.and]: [
                                    before ? { sortOrder: { [Op.gte]: locationData.sortOrder } } : { sortOrder: { [Op.gt]: locationData.sortOrder } },
                                    { sortOrder: { [Op.lt]: faqData.sortOrder } }
                                ]
                            },
                            transaction: transaction
                        });
                        await Models.Campaign.update(before ? { sortOrder: locationData.sortOrder } : { sortOrder: locationData.sortOrder + 1 }, { where: { id: faqData.id, accountId: this.accountId }, transaction: transaction });
                    }
                }
                else {
                    let maxSortOrder = await Models.Campaign.max('sortOrder', { where: { accountId: this.accountId } });
                    await Models.Campaign.update(
                        { sortOrder: (maxSortOrder || 0) + 1 }, // default to 1 if no max found
                        { where: { id: faqData.id, accountId: this.accountId }, transaction: transaction }
                    );
                }
                return true;
            } else {
                return false;
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // Get full campaign object
    getFullObject = async (data: CampaignGetFullObjectDaoInput, options: DaoOptions = {}): Promise<CampaignInterface> => {
        const { id } = data;
        const { transaction } = options;
        try {
            let Campaign = await Models.Campaign.findOne({
                where: {
                    id: id, accountId: {
                        [Op.or]: [null, this.accountId]
                    }
                },
                transaction
            });
            return JSON.parse(JSON.stringify(Campaign)) as unknown as CampaignInterface
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // Generate revision of campaign prior to update and delete functions.
    private storeRevision = async (data: CampaignStoreRevisionDaoInput, options: DaoOptions = {}): Promise<CampaignInterface> => {
        const { id } = data;
        const { transaction } = options;
        try {
            const getFullObjectInput: CampaignGetFullObjectDaoInput = { id };
            let Object: CampaignInterface = await this.getFullObject(getFullObjectInput, options);
            let revisonObject = JSON.parse(JSON.stringify(Object));
            let revisionId = revisonObject.id;
            revisonObject = _.omit(revisonObject, ['id', 'createdAt', 'updatedAt', 'deletedAt']);
            revisonObject.isRevision = true;
            revisonObject.code = revisonObject.code + '-' + Moment().toISOString();
            revisonObject.revisionId = revisionId;

            let revision = await Models.Campaign.create(revisonObject, { transaction: transaction });
            if (revision)
                return revision;
            else {
                throw new AppError(400, 'ERROR_WHILE_CREATING_REVISION', {});
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    cloneCampaign = async (id: number): Promise<CampaignInterface> => {
        try {
            const getFullObjectInput: CampaignGetFullObjectDaoInput = { id };
            let Object: CampaignInterface = await this.getFullObject(getFullObjectInput);
            let clonedObject = JSON.parse(JSON.stringify(Object));
            clonedObject = _.omit(clonedObject, ['id', 'code', 'lifeCycleStatus', 'sendAt', 'createdAt', 'updatedAt', 'deletedAt']);
            const code = Common.slugify(Object.title);
            clonedObject.code = code + new Date();
            clonedObject.sendAt = Moment().add(1, 'minutes').toDate();
            clonedObject.status = CAMPAIGN.STATUS.ACTIVE;
            clonedObject.parentId = id;

            let clone = await Models.Campaign.create(clonedObject);
            if (clone)
                return clone;
            else {
                throw new AppError(400, 'ERROR_WHILE_CLONING_CAMPAIGN', {});
            }
        } catch (err) {
            console.log(err)
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    resetCampaign = async (): Promise<void> => {
        try {
            
            const campaigns = await Models.Campaign.findAll({ attributes: ["id"], where: { isRevision: false, isRecurring: true, lifeCycleStatus: 6 } });

            for(let item of campaigns) {
                await Models.Campaign.update({ lifeCycleStatus: 0, sendAt: new Date() }, { where: { id: item.id } });
            }

        } catch (err) {
            console.log(err)
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get association for the entity
    private includeAssociations = (fullInfo: boolean = false): IncludeOption[] => {
        const includeModels: IncludeOption[] = [
            {
                attributes: attachmentAttributes,
                model: Models.Attachment,
                as: 'campaignAttachment'
            },

        ];
        if (fullInfo) {
            includeModels.push(
                // {
                //     attributes: parentAttributes,
                //     model: Models.Campaign,
                //     as: 'parentCampaign'
                // },
                {
                    attributes: authorAttributes,
                    model: Models.User,
                    as: 'author',
                    include: [{ attributes: [], model: Models.UserProfile, as: 'userProfile', include: [{ model: Models.Attachment, as: "profileImage" }] }]
                },
                {
                    attributes: updatedByAttributes,
                    model: Models.User,
                    as: 'updatedBy',
                    include: [{ attributes: [], model: Models.UserProfile, as: 'userProfile', include: [{ model: Models.Attachment, as: "profileImage" }] }]
                }
            );
        }
        return includeModels;
    }

    // check if child is parent
    ifChildIsParent = async (campaignId: number, parentId: number): Promise<boolean> => {
        try {
            let children = await Models.Campaign.findAll({ attributes: ['id'], where: { parentId: campaignId, accountId: this.accountId } });
            const isParentPresent = children.find((child: { id: number }) => child.id === parentId);
            if (isParentPresent) {
                return true;
            } else {
                for (let child of children) {
                    let levelHasParent = await this.ifChildIsParent(child.id!, parentId);
                    if (levelHasParent)
                        return true;
                }
                return false;
            }
        } catch (err) {
            return true;
        }
    }

    // get campaign by code or id
    getCampaign = async (data: CampaignGetDaoInput, options: DaoOptions = {}): Promise<CampaignObjectInteface> => {
        const { id = null, code = null, fullInfo = false, paranoid = true } = data;
        const { transaction } = options;
        try {
            let where: WhereOptions = id ? { id: id } : code ? { code: code } : {};
            if (this.accountId) {
                where = {
                    ...where, accountId: {
                        [Op.or]: [null, this.accountId]
                    }
                }
            }
            const campaign = await Models.Campaign.findOne({
                attributes: [...campaignAttributes],
                where: where,
                include: this.includeAssociations(fullInfo),
                paranoid: paranoid,
                transaction
            });
            if (campaign) {
                // const parentHirarchy = campaign.parentId ? await this.getParentHirarchy(campaign.parentId, code) : [];
                // let campaignData = { ...JSON.parse(JSON.stringify(campaign)), ...{ parentHirarchy: JSON.parse(JSON.stringify(parentHirarchy)) } }
                // delete campaignData.parentId;
                return JSON.parse(JSON.stringify(campaign)) as unknown as CampaignObjectInteface;
            } else {
                throw new AppError(404, 'CAMPAIGN_NOT_FOUND', {});
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get campaign by id
    getCampaignById = async (data: CampaignGetByIdDaoInput, options: DaoOptions = {}): Promise<CampaignObjectInteface> => {
        const { id, fullInfo = false, paranoid = true } = data;
        try {
            const getCampaignInput: CampaignGetDaoInput = { id, fullInfo, paranoid };
            return await this.getCampaign(getCampaignInput, options);
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get campaign by code
    getCampaignByCode = async (data: CampaignGetByCodeDaoInput, options: DaoOptions = {}): Promise<CampaignObjectInteface> => {
        const { code, fullInfo = false, paranoid = true } = data;
        try {
            const getCampaignInput: CampaignGetDaoInput = { code, fullInfo, paranoid };
            return await this.getCampaign(getCampaignInput, options);
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // check if campaign exists by code
    doExistsByCode = async (data: CampaignDoExistsByCodeDaoInput, options: DaoOptions = {}): Promise<CampaignInterface | false> => {
        const { code, excludeId = null, includeRevision = false } = data;
        const { transaction } = options;
        try {
            const campaign = await Models.Campaign.findOne({
                attributes: ['id', 'code'],
                where: { ...(excludeId ? { id: { [Op.ne]: excludeId } } : {}), code: code, accountId: this.accountId, isRevision: includeRevision },
                transaction
            });
            return campaign?.id ? JSON.parse(JSON.stringify(campaign)) : false;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // check if campaign exists by id
    doExistsById = async (data: CampaignDoExistsByIdDaoInput, options: DaoOptions = {}): Promise<CampaignInterface | false> => {
        const { id, includeRevision = false } = data;
        const { transaction } = options;
        try {
            const campaign = await Models.Campaign.findOne({
                attributes: ['id', 'code'],
                where: { id: id, accountId: this.accountId, isRevision: includeRevision },
                transaction
            });
            return campaign?.id ? JSON.parse(JSON.stringify(campaign)) : false;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // update campaign status
    updateStatus = async (data: CampaignUpdateStatusDaoInput, options: DaoOptions = {}): Promise<void> => {
        const { id, status } = data;
        const { transaction } = options;
        try {
            await Models.Campaign.update({ status: status }, { where: { id: id, accountId: this.accountId }, transaction });
            return;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create a new campaign
    create = async (data: CampaignCreateDaoInput, options: DaoOptions = {}): Promise<number> => {
        const { campaignObj, challengeId = null } = data;
        const { transaction } = options;
        try {
            const campaignObject: CampaignDataObject = { ...campaignObj, userId: this.userId, accountId: this.accountId };
            let campaign = await Models.Campaign.create(campaignObject, {
                transaction
            });

            if (challengeId) {
                await Models.Challenge.update({ campaignStatus: 1 }, { where: { id: challengeId }, transaction });
            }
            // if(!campaignObj.parentId) {
            //     await campaign.update({ parentId: campaign.id }, { transaction });
            // }
            return campaign.id;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // update campaign
    update = async (data: CampaignUpdateDaoInput, options: DaoOptions = {}): Promise<number> => {
        const { id, campaignObj } = data;
        const { transaction } = options;
        try {
            let where: WhereOptions = { id: id }
            if (this.accountId) {
                where = { ...where, accountId: this.accountId }
            }
            // generate revision for the existing state
            const storeRevisionInput: CampaignStoreRevisionDaoInput = { id };
            await this.storeRevision(storeRevisionInput, options);
            // check if content exists in requested language
            await Models.Campaign.update(campaignObj, { where: { id: id }, transaction: transaction })
            return id;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // delete campaign
    delete = async (data: CampaignDeleteDaoInput, options: DaoOptions = {}): Promise<void> => {
        const { id } = data;
        const { transaction } = options;
        try {
            let where: WhereOptions = {
                id: id, accountId: this.accountId
            }
            const getCampaignByIdInput: CampaignGetByIdDaoInput = { id, fullInfo: false };
            let campaign = await this.getCampaignById(getCampaignByIdInput, options);
            let code = campaign.code + '-' + Moment().toISOString();
            await Models.Campaign.update({ updatedBy: this.userId, code: code }, { where: where, transaction: transaction });
            await Models.Campaign.destroy({ where: where, transaction: transaction });
            return;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // Build order by clause
    private buildOrderBy = (field: string, direction: string = 'desc') => {
        switch (field) {
            case 'sort-order':
                return [['sortOrder', direction]];
            case 'name':
                return [[literal(`name`), direction]];
            case 'id':
                return [['id', direction]];
            default:
                // Fallback to default order
                return [
                    ['id', 'ASC']
                ];
        }
    }

    // Build filter
    private buildFilter = (where: WhereOptions & { [Op.and]: any[] }, searchText: string | null, status: number | null) => {
        let alphaString = '';
        let specialString = '';
        if (searchText) {
            let searchData: searchText = Common.prepareSearchText(searchText);
            if (searchData.alphaString) {
                alphaString = '*' + searchData.alphaString + '*'
            } if (searchData.specialString) {
                specialString = searchData.specialString
            }
            if (alphaString) {
                (where[Op.and] as any[]).push(Sequelize.literal('MATCH(`defaultContent`.`name`,`defaultContent`.`description_text`) AGAINST(:alphaString IN BOOLEAN MODE)'));
                (where[Op.and] as any[]).push(Sequelize.literal('MATCH(`content`.`name`,`content`.`description_text`) AGAINST(:alphaString IN BOOLEAN MODE)'));
            } if (specialString) {
                (where[Op.and] as any[]).push(Sequelize.literal('MATCH(`defaultContent`.`name`,`defaultContent`.`description_text`) AGAINST(:specialString IN BOOLEAN MODE)'));
                (where[Op.and] as any[]).push(Sequelize.literal('MATCH(`content`.`name`,`content`.`description_text`) AGAINST(:specialString IN BOOLEAN MODE)'));
            }
        }
        if (status != null) {
            where = { ...where, status: status }
        }
        return { where: where, replacements: { alphaString: alphaString, specialString: specialString } }
    }

    // list campaigns
    getCampaignList = async (data: CampaignGetListDaoInput, options: DaoOptions = {}): Promise<CampaignPaginatedData> => {
        const { listRequest } = data;
        const { transaction } = options;
        try {
            const { page, perPage, searchText, parentId, sortBy, sortDirection, status } = listRequest;
            let offset = (page - 1) * perPage;
            let where: WhereOptions & { [Op.and]: any[] } = {
                accountId: {
                    [Op.or]: [null, this.accountId]
                }, isRevision: false, parentId: parentId, [Op.and]: []
            };
            let applyfilter = this.buildFilter(where, searchText, status);
            const orderBy = this.buildOrderBy(sortBy, sortDirection);
            const campaigns = await Models.Campaign.findAndCountAll({
                attributes: campaignAttributes,
                where: applyfilter.where,
                include: this.includeAssociations(false),
                replacements: applyfilter.replacements,
                offset: offset,
                limit: perPage,
                order: orderBy,
                transaction
            });
            return JSON.parse(JSON.stringify(campaigns)) as unknown as CampaignPaginatedData;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // list all campaigns
    getAllCampaigns = async (data: CampaignGetAllListDaoInput, options: DaoOptions = {}): Promise<CampaignObjectInteface[]> => {
        const { listRequest } = data;
        const { transaction } = options;
        try {
            const { searchText, sortBy, sortDirection, status, parentId } = listRequest
            let where: WhereOptions & { [Op.and]: any[] } = {
                accountId: {
                    [Op.or]: [null, this.accountId]
                }, isRevision: false, parentId: parentId, [Op.and]: []
            };
            let applyfilter = this.buildFilter(where, searchText, status);
            const orderBy = this.buildOrderBy(sortBy, sortDirection);
            const campaigns = await Models.Campaign.findAll({
                attributes: campaignAttributes,
                where: applyfilter.where,
                replacements: applyfilter.replacements,
                include: this.includeAssociations(false),
                order: orderBy,
                limit: +process.env.LIST_ALL_LIMIT!,
                transaction
            });
            return JSON.parse(JSON.stringify(campaigns)) as unknown as CampaignObjectInteface[];
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // list campaign revisions
    getCampaignRevisionList = async (data: CampaignGetRevisionListDaoInput, options: DaoOptions = {}): Promise<CampaignPaginatedData> => {
        const { id, listRequest } = data;
        const { transaction } = options;
        try {
            const { page, perPage, searchText, status } = listRequest;
            let offset = (page - 1) * perPage;
            let where: WhereOptions & { [Op.and]: any[] } = { isRevision: true, revisionId: id, [Op.and]: [] };
            if (this.accountId) {
                where = { ...where, accountId: this.accountId }
            }
            let applyfilter = this.buildFilter(where, searchText, status);
            const campaigns = await Models.Campaign.findAndCountAll({
                attributes: campaignAttributes,
                where: applyfilter.where,
                include: this.includeAssociations(false),
                replacements: applyfilter.replacements,
                offset: offset,
                limit: perPage,
                transaction,
            });
            return JSON.parse(JSON.stringify(campaigns)) as unknown as CampaignPaginatedData;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // restore campaign revision
    restoreRevision = async (data: CampaignRestoreRevisionDaoInput, options: DaoOptions = {}): Promise<number> => {
        const { id } = data;
        const { transaction } = options;
        try {
            const doExistsByIdInput: CampaignDoExistsByIdDaoInput = { id, includeRevision: true };
            if (await this.doExistsById(doExistsByIdInput, options)) {
                // get full revision Data
                const getFullObjectInput: CampaignGetFullObjectDaoInput = { id };
                let Object: CampaignInterface = await this.getFullObject(getFullObjectInput, options);
                if (Object && Object.revisionId) {
                    // create new revision from existing state
                    const storeRevisionInput: CampaignStoreRevisionDaoInput = { id: Object.revisionId };
                    await this.storeRevision(storeRevisionInput, options);
                    let revisonObject = JSON.parse(JSON.stringify(Object));
                    // remove unnecessary data from object
                    revisonObject = _.omit(revisonObject, ['id', 'createdAt', 'updatedAt', 'deletedAt']);
                    // remove revision updates
                    // revisonObject.code = revisonObject.code.split('-').slice(0, -1).join('-');
                    // update entity type
                    await Models.Campaign.update({ lastUpdatedBy: this.userId }, { where: { id: revisonObject.revisionId }, transaction: transaction });
                    // update entity type content
                    if (revisonObject.campaignContents && revisonObject.campaignContents.length) {
                        for (let content of revisonObject.campaignContents) {
                            await Models.CampaignContent.upsert({ name: content.name, description: content.description, descriptionText: content.descriptionText, languageId: content.languageId, campaignId: Object.revisionId }, { transaction: transaction });
                        }
                    }
                    return Object.revisionId;
                } else {
                    throw new AppError(400, 'REQUESTED_ENTITY_IS_NOT_A_REVISION', { id: 'REQUESTED_ENTITY_IS_NOT_A_REVISION' });
                }
            } else {
                throw new AppError(404, 'CAMPAIGN_TYPE_NOT_FOUND', { id: 'CAMPAIGN_TYPE_NOT_FOUND' });
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // check if campaign has atleast one child
    hasChild = async (data: CampaignHasChildDaoInput, options: DaoOptions = {}): Promise<boolean> => {
        const { id } = data;
        const { transaction } = options;
        try {
            let childCampaign = await Models.Campaign.findOne({ attributes: ['id'], where: { isRevision: false, parentId: id }, transaction });
            if (childCampaign) {
                return true;
            } else {
                return false;
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // verify campaign id
    public verifyCampaignId = async (data: CampaignVerifyIdDaoInput, options: DaoOptions = {}): Promise<boolean> => {
        const { campaignId } = data;
        const { transaction } = options;
        try {
            let where: WhereOptions = { id: campaignId }
            if (this.accountId) {
                where = { ...where, accountId: this.accountId }
            }
            let campaign = await Models.Campaign.findOne({ where: where, transaction });
            if (campaign) {
                return true;
            } else {
                return false;
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get campaign id from campaign code
    getIdFromCode = async (data: CampaignGetIdDaoInput, options: DaoOptions = {}): Promise<number> => {
        const { code } = data;
        const { transaction } = options;
        try {
            let campaign = await Models.Campaign.findOne({ attributes: ['id'], where: { code: code }, transaction });
            if (campaign) {
                return campaign.id;
            } else {
                throw new AppError(404, 'CAMPAIGN_NOT_FOUND', { code: 'CAMPAIGN_NOT_FOUND' });
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get campaign by id
    addUsersInCampaign = async (data: CampaignAddUsersDaoInput, options: DaoOptions = {}) => {
        const { id, filters } = data;
        const { transaction } = options;
        try {
            let usersList;

            if (filters) {
                if (filters.userType) {
                    usersList = await Models.User.findAll({
                        attributes: ["id"],
                        where: { status: USER.STATUS.ACTIVE },
                        include: [
                            {
                                model: Models.Role,
                                as: "userRoles",
                                where: { code: filters.userType }
                            }
                        ]
                    });
                } else if (filters.userIds) {
                    usersList = await Models.User.findAll({
                        attributes: ["id"],
                        where: { status: USER.STATUS.ACTIVE, id: filters.userIds },
                    });
                }
            }


            const campaignUserArray = [];
            if (usersList && usersList.length > 0) {
                for (let item of JSON.parse(JSON.stringify(usersList))) {
                    campaignUserArray.push({ userId: item.id, campaignId: id });
                }
            }

            await Models.CampaignUser.bulkCreate(campaignUserArray, { transaction });
            const getCampaignInput: CampaignGetDaoInput = { id, fullInfo: false, paranoid: true };
            return await this.getCampaign(getCampaignInput, options);
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    assignUsersToCampaign = async (id: number, filters: CampaingnFilters) => {
        const transaction = await sequelize.transaction();
        try {
            let usersList;

            if (filters) {
                if (filters.userType) {
                    usersList = await Models.User.findAll({
                        attributes: ["id"],
                        where: { status: USER.STATUS.ACTIVE },
                        include: [
                            {
                                model: Models.Role,
                                as: "userRoles",
                                where: { code: filters.userType }
                            }
                        ]
                    });
                } else if (filters.userIds) {
                    usersList = await Models.User.findAll({
                        attributes: ["id"],
                        where: { status: USER.STATUS.ACTIVE, id: filters.userIds },
                    });
                }
            }


            const campaignUserArray = [];
            if (usersList && usersList.length > 0) {
                for (let item of JSON.parse(JSON.stringify(usersList))) {
                    campaignUserArray.push({ userId: item.id, campaignId: id });
                }
            }

            await Models.CampaignUser.bulkCreate(campaignUserArray, { transaction });
            await transaction.commit();
        } catch (err) {
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get users in the campaign
    getUsersInCampaign = async (data: CampaignGetUsersDaoInput, options: DaoOptions = {}) => {
        const { listRequest } = data;
        const { transaction } = options;
        try {
            const { page, perPage, searchText, parentId, sortBy, sortDirection, id } = listRequest;
            let offset = (page - 1) * perPage;
            const orderBy = this.buildOrderBy(sortBy, sortDirection);
            let where = {}
            if (id) {
                where = { ...where, id: id }
            } else if (parentId) {
                where = { ...where, [Op.or]: [{ parentId: parentId }, { id: parentId }] }
            }

            const users = await Models.CampaignUser.findAndCountAll({
                attributes: ["id", "campaignId", "status", "acknowledgementId"],
                include: [{
                    model: Models.User,
                    as: "user",
                    attributes: ["id", "email", "username", "countryCode", "mobile", "status", "createdAt", "updatedAt"],
                    include: [
                        {
                            attributes: ["name"], model: Models.UserProfile, as: "userProfile",
                            include: [
                                {
                                    model: Models.Attachment, as: "profileImage",
                                    attributes: ["id", "fileName", "uniqueName", [fn("CONCAT", process.env.PROTOCOL, "://", process.env.APPLICATION_HOST, "/attachment/", literal("`user->userProfile->profileImage`.`unique_name`")), "filePath"]]
                                }
                            ]
                        }
                    ]
                },
                {
                    model: Models.Campaign,
                    as: "campaign",
                    attributes: [],
                    where: where
                }],
                offset: offset,
                limit: perPage,
                subQuery: false,
                order: orderBy,
                transaction,
            });
            return JSON.parse(JSON.stringify(users));
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get users in the campaign
    getPendingCampaigns = async (): Promise<number[]> => {
        let pendingCampaign = await Models.Campaign.findAll({ attributes: ['id'], limit: 2, where: { sendAt: { [Op.lte]: literal(`NOW()`) }, status: CAMPAIGN.STATUS.ACTIVE, lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.PENDING, isUserAssigned: true } });
        if (pendingCampaign) {
            for (let campaign of pendingCampaign) {
                campaign.update({ lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SCHEDULING }, { where: { id: campaign.id } });
            }
        }
        return pendingCampaign
    }

    // get users in the campaign
    assignUserCampaign = async () => {
        try {
            let campaigns = await Models.Campaign.findAll({
                attributes: ['id', 'filters'],
                limit: 2,
                where: {
                    sendAt: { [Op.lte]: literal(`NOW()`) },
                    isUserAssigned: false,
                }
            });


            for (let campaign of JSON.parse(JSON.stringify(campaigns))) {
                const campaignId = campaign.id;
                const campaignFilters = campaign.filters;
                if (campaignId && campaignFilters) {
                    await this.assignUsersToCampaign(campaignId, campaignFilters);
                }
                await Models.Campaign.update({ isUserAssigned: true }, { where: { id: campaignId } })
            }

            return true;
        } catch (error) {
            console.log(error)
            return false;
        }
    }

    private normalizeTemplateReplacements = (
        template: string,
        replacements: Record<string, string>,
        defaultValue = "-"
    ): Record<string, string> => {
        const result: Record<string, string> = {};

        const regex = /{{\s*([^}]+)\s*}}/g;
        let match: RegExpExecArray | null;

        while ((match = regex.exec(template)) !== null) {
            const key = match[1];

            result[key] = key in replacements
                ? replacements[key]
                : defaultValue;
        }

        return result;
    };

    private convertTemplateVariables = (template: string) => {
        let index = 1;
        return template.replace(/{{\s*[^}]+\s*}}/g, () => {
            return `{{${index++}}}`;
        });
    }

    // Parse an Excel file from a local file path and return JSON per sheet
    parseExcelFile = async (id: number) => {
        try {
            const attachmentService = new AttachmentService({
                userId: this.userId,
                accountId: this.accountId,
                language: this.language,
                scope: this.scope,
                config: this.config
            });
            const getFileByUniqueIdentifierInput: AttachmentGetFileByUniqueIdentifierServiceInput = {
                id,
                uniqueName: null,
                size: null,
                format: null,
            };
            const fileData = await attachmentService.getFileByUniqueIdentifier(getFileByUniqueIdentifierInput)
            if (!fileData) throw new AppError(400, "FILE_NOT_READABLE");
            const { attachment, streamData } = fileData;
            if (attachment && attachment.extension.toLowerCase() !== "csv") {
                throw new AppError(400, "NOT_VALID_CSV_FILE");
            }
            const requiredHeaders = ["name", "mobile", "countrycode"];
            // Parse & validate CSV
            const rows = await Common.parseCSVFile(streamData, requiredHeaders);
            return rows;
        } catch (err) {
            if (err instanceof AppError) throw err;
            throw new AppError(500, 'SOMETHING_WENT_WRONG_IN_SERVICE', err);
        }
    };

    scheduleCampaign = async (): Promise<void> => {
        const BATCH_SIZE = 1000;
        let offset = 0;
        let hasMore = true;
        const transaction = await sequelize.transaction();
        try {
            let campaign = await Models.Campaign.findOne({ where: { sendAt: { [Op.lte]: literal(`NOW()`) }, status: CAMPAIGN.STATUS.ACTIVE, lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SCHEDULING }, transaction: transaction });

            if (campaign) {
                let campaignHasUsers = await Models.CampaignUser.findOne({ where: { campaignId: campaign.id } });
                await campaign.update({ lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SCHEDULING_IN_PROCESS }, { transaction: transaction });
                switch (campaign.type) {
                    case 'email':
                        while (hasMore) {
                            const [results, metadata] = await sequelize.query(`INSERT INTO campaigns_logs (user_id, account_id, campaign_id, template, send_to, replacements, created_at, updated_at)
                            SELECT u.id,${this.accountId},${campaign.id},'${'CAMPAIGN_' + campaign.type + '_' + campaign.id}',u.email,JSON_OBJECT('name', p.name, 'email', u.email),NOW(),NOW()
                            FROM users u
                            LEFT JOIN user_profile p ON u.id = p.user_id
                            ${campaignHasUsers ? `LEFT JOIN campaign_users as c on c.user_id=u.id and c.campaign_id=${campaign.id}` : ''}
                            LEFT JOIN campaign_users
                            where u.email is not null ${campaignHasUsers ? ` AND c.campaign_id=${campaign.id}` : ''}
                            ORDER BY u.id
                            LIMIT ${BATCH_SIZE} OFFSET ${offset};
                        `, { transaction: transaction });
                            offset += BATCH_SIZE;
                            const affected = (metadata as number) ?? 0;
                            hasMore = affected > 0;
                        }
                        await campaign.update({ lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SCHEDULED }, { transaction: transaction });
                        break;
                    case 'notification':
                        while (hasMore) {
                            const [_, metadata] = await sequelize.query(`INSERT INTO campaigns_logs (user_id,account_id,campaign_id,template,send_to,replacements,created_at,updated_at)
                            SELECT u.id,${this.accountId},${campaign.id},'${'CAMPAIGN_' + campaign.type + '_' + campaign.id}',d.device,JSON_OBJECT('name', p.name, 'email', u.email),NOW(),NOW()
                            FROM users u
                            JOIN user_profile p ON u.id = p.user_id
                            ${campaignHasUsers ? `LEFT JOIN campaign_users as c on c.user_id=u.id and c.campaign_id=${campaign.id}` : ''}
                            JOIN user_devices d ON u.id = d.user_id and d.status=1
                            WHERE d.id IS NOT NULL ${campaignHasUsers ? ` AND c.campaign_id=${campaign.id}` : ''}
                            ORDER BY u.id
                            LIMIT ${BATCH_SIZE} OFFSET ${offset};
                        `, { transaction: transaction });
                            offset += BATCH_SIZE;
                            const affected = (metadata as number) ?? 0;
                            hasMore = affected > 0;
                        }
                        await campaign.update({ lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SCHEDULED }, { transaction: transaction });
                        break;
                    case 'whatsapp':
                        const campaignObject = JSON.parse(JSON.stringify(campaign))
                        console.log(campaignObject, "===========>camp obj")
                        const attachmentId = campaignObject.attachmentId;
                        let templateBody = this.replaceTemplateVarsWithIndex(campaign.template);
                        Common.createWhatsAppTemplate(process.env.WHATSAPP_TEMPLATE_PREFIX + campaign.id, 'MARKETING', 'en_GB', templateBody.template, templateBody.examples);
                        await Models.Campaign.update({ templateStatus: false }, { where: { id: campaign.id }, transaction: transaction });

                        if(attachmentId) {
                            const users = await this.parseExcelFile(attachmentId);
                            for (let offset = 0; offset < users.length; offset += BATCH_SIZE) {
                                const batch = users.slice(offset, offset + BATCH_SIZE);
                                // console.log(`Processing records ${offset} to ${offset + batch.length - 1}`);

                                const mappedBatch = batch.map(user => ({
                                    ...user, 
                                    userId: null, 
                                    accountId: null, 
                                    replacements: {name: user.name, mobile: user.mobile}, 
                                    campaignId: campaign.id, 
                                    template: campaign.template, 
                                    sendTo: user.countrycode + user.mobile
                                }));
                               await Models.CampaignLog.bulkCreate(mappedBatch, { transaction });
                            }
                        } 
                        // else if(campaignObject?.isRecurring && campaignObject?.filters?.leadInterval) {
                        //     const leadService = new LeadService();
                        //     while(hasMore) {
                        //         const users = await leadService.getLeadsByMinutes(campaignObject.filters.leadInterval);
                        //         console.log(users)
                        //         hasMore = false;
                        //         if(users.length < 1) break;
                        //         offset += BATCH_SIZE;
                        //         const mappedBatch = users.map((user: any) => ({
                        //             userId: null, 
                        //             accountId: null, 
                        //             replacements: {name: user.name, mobile: user.mobile}, 
                        //             campaignId: campaign.id, 
                        //             template: campaign.template, 
                        //             sendTo: user.countryCode + user.mobile
                        //         }));
                        //        await Models.CampaignLog.bulkCreate(mappedBatch, { transaction });
                        //     }
                        // } 
                        else {
                            while (hasMore) {
                                const [_, metadata] = await sequelize.query(`INSERT INTO campaigns_logs (user_id,account_id,campaign_id,template,send_to,replacements,created_at,updated_at)
                                SELECT u.id,${this.accountId},${campaign.id},'${campaign.template}',CONCAT(u.country_code,u.mobile),JSON_OBJECT('name', p.name, 'mobile', u.mobile),NOW(),NOW()
                                FROM users u
                                JOIN user_profile p ON u.id = p.user_id
                                ${campaignHasUsers ? `LEFT JOIN campaign_users as c on c.user_id=u.id and c.campaign_id=${campaign.id}` : ''}
                                WHERE u.same_on_whatsapp=1 ${campaignHasUsers ? ` AND c.campaign_id=${campaign.id}` : ''}
                                ORDER BY u.id
                                LIMIT ${BATCH_SIZE} OFFSET ${offset};
                                `, 
                                { transaction: transaction });
                                offset += BATCH_SIZE;
                                const affected = (metadata as number) ?? 0;
                                hasMore = affected > 0;
                            }
                        }
                        await campaign.update({ lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SCHEDULED }, { transaction: transaction });
                        break;
                    default:
                        break;
                }
            }
            await transaction.commit();
            return;
        } catch (err) {
            console.log(err)
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    private replaceTemplateVars = (
        template: string,
        replacements: Record<string, string>
    ): string => {
        return template.replace(/{{\s*([^}]+)\s*}}/g, (match: string, key: string) => {
            return key in replacements ? replacements[key] : match;
        });
    }

    private replaceTemplateVarsWithIndex = (template: string): { template: string; mapping: Record<string, number>; examples: string[]; } => {
        const mapping: Record<string, number> = {};
        const examples: string[] = [];
        let index = 1;
        const updatedTemplate = template.replace(
            /{{\s*([^}]+)\s*}}/g,
            (match: string, key: string) => {
                if (!(key in mapping)) {
                    mapping[key] = index;
                    examples.push(`sample_${index}`); // example value
                    index++;
                }
                return `{{${mapping[key]}}}`;
            }
        );
        return {
            template: updatedTemplate,
            mapping,
            examples
        };
    };

    sendCampaing = async (): Promise<void> => {
        try {
            const BATCH_SIZE = 1000;
            let hasMore = true;
            let offset = 0;
            let updateFlag = true;
            let campaign = await Models.Campaign.findOne({ where: { sendAt: { [Op.lte]: literal(`NOW()`) }, status: CAMPAIGN.STATUS.ACTIVE, lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SCHEDULED } });
            if (campaign) {
                await campaign.update({ lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SENDING_IN_PROCESS });
                switch (campaign.type) {
                    case 'email':
                        let emailTemplateService = new EmailTemplateService({ userId: null, accountId: null, language: 'en', scope: [], config: null });
                        let templateName = 'CAMPAIGN_' + campaign.type + '_' + campaign.id;
                        while (hasMore) {
                            let templateData: any = [];
                            let pendingEmails = await Models.CampaignLog.findAll({ where: { campaignId: campaign.id, messageId: null }, offset: offset, limit: BATCH_SIZE });
                            for (let data of pendingEmails) {
                                let userData = { email: data.sendTo, templateData: data.replacements };
                                templateData.push(userData);
                            }
                            if (pendingEmails.length) {
                                let results = await emailTemplateService.sendBulkEmailsBCC(templateData, templateName, {
                                    TemplateName: templateName,
                                    SubjectPart: campaign.title,     // required
                                    HtmlPart: campaign.description,  // optional but recommended
                                    TextPart: campaign.descriptionText,           // optional but recommended
                                })
                                offset += BATCH_SIZE;
                                if (results && results.Status) {
                                    for (const [i, value] of results.Status.entries()) {
                                        await Models.CampaignLog.update({ messageId: results.Status[i].MessageId, status: 1 }, { where: { id: pendingEmails[i].id } });
                                    }
                                }
                            } else {
                                hasMore = false;
                            }
                        }
                        break;
                    case 'notification':
                        while (hasMore) {
                            let templateData: any = [];
                            let pendingNotificatioins = await Models.CampaignLog.findAll({ where: { campaignId: campaign.id, messageId: null }, offset: offset, limit: BATCH_SIZE });
                            if (pendingNotificatioins.length) {
                                for (let data of pendingNotificatioins) {
                                    let templateTemplate = handlebars.compile(campaign.template);
                                    let templateTitle = handlebars.compile(campaign.title);
                                    let templateDescription = handlebars.compile(campaign.description);
                                    let template = templateTemplate(data.replacements);
                                    let title = templateTitle(data.replacements);
                                    let description = templateDescription(data.replacements);
                                    let notification = await Common.sendFcmNotification(data.sendTo, null, { title: title, body: template }, { title: title, body: description }, { title: title, body: description }, false)
                                    let notificationData = notification ? notification.split("/").pop() : null
                                    if (notificationData)
                                        await Models.CampaignLog.update({ messageId: notification, status: 1 }, { where: { id: data.id } });
                                    else
                                        await Models.CampaignLog.update({ status: 1 }, { where: { id: data.id } });
                                }
                                offset += BATCH_SIZE;
                            } else {
                                hasMore = false;
                            }
                        }
                        break;
                    case 'whatsapp':
                        updateFlag = false;
                        let templateStatus = await Common.checkWhatsAppTemplateStatus(process.env.WHATSAPP_TEMPLATE_PREFIX + campaign.id);
                        console.log(JSON.stringify(templateStatus), " =============== template status at send stage")
                        if (templateStatus.status == 'success' && templateStatus?.data[0]?.languages?.length && templateStatus.data[0].languages[0].status=='APPROVED') {
                            await Models.Campaign.update({ templateStatus: true }, { where: { id: campaign.id } });
                            while (hasMore) {
                                let templateData = campaign.template.split('|');
                                let pendingMessages = await Models.CampaignLog.findAll({ where: { campaignId: campaign.id, messageId: null }, offset: offset, limit: BATCH_SIZE });
                                if (pendingMessages.length) {
                                    let senderData = [];
                                    let sentIds = []
                                    let userData = campaign.template;
                                    let templateName = process.env.WHATSAPP_TEMPLATE_PREFIX + campaign.id;
                                    let namespace = process.env.CAMPAIGN_WHATSAPP_NAMESPACE!;
                                    let receiverData: WhatsAppReceiverItem[] = []
                                    for (let data of pendingMessages) {
                                        data.sendTo = data.sendTo.trim().replace(/\+/g, "");
                                        senderData.push(data.sendTo);
                                        sentIds.push(data.id)
                                        let userVars = this.normalizeTemplateReplacements(campaign.template,data.replacements)
                                        let components: Record<string, { type: string; value: string }> = {};
                                        // userData = this.replaceTemplateVars(userData, data.replacements);
                                        // userData = this.replaceTemplateVarsWithIndex(userData);
                                        // let userVars = userData.split("|");
                                        // userVars.shift()
                                        Object.values(userVars).forEach((value: string, index: number) => {
                                            components[`body_${index + 1}`] = {
                                                type: "text",
                                                value
                                            };
                                        });
                                        receiverData.push({
                                            //"to": senderData,
                                            "to": [data.sendTo],
                                            "components": components
                                        })
                                    }
                                    console.log(receiverData)
                                    if (templateName && namespace) {
                                       let message = await Common.sendWhatsApp(templateName, receiverData, namespace);
                                        await Models.CampaignLog.update({ messageId: message.request_id }, { where: { id: sentIds } });
                                    }
                                    offset += BATCH_SIZE;
                                } else {
                                    hasMore = false;
                                }
                            }
                            updateFlag = true;
                        }
                        break;
                    default:
                        break;
                }
                if(updateFlag) {
                    await campaign.update({ lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SENT });
                } else {
                    await campaign.update({ lifeCycleStatus: CAMPAIGN.LEFE_CYCLE_STATUS.SCHEDULED });
                }
            }
            return;
        } catch (err) {
            console.log(err, " ======== send campaign error")
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    generateCampaignReport = async (data: CampaignGenerateReportDaoInput, options: DaoOptions = {}) => {
        const { campaignId, isComplete } = data;
        const { transaction } = options;
        try {
            let campaignIds: number[] = [campaignId];

            // STEP 1: get parent + children campaign IDs (only if isComplete = true)
            const current = await Models.Campaign.findOne({ where: { id: campaignId }, transaction });

            if (isComplete && current) {
                if (current.parentId) {
                    // This is a child → include parent and siblings
                    const parent = await Models.Campaign.findOne({ where: { id: current.parentId }, transaction });
                    const siblings = await Models.Campaign.findAll({
                        attributes: ['id'],
                        where: { parentId: current.parentId },
                        transaction
                    });

                    campaignIds = [parent?.id || campaignId, ...siblings.map((c: any) => c.id)];
                } else {
                    // This is a parent → include children
                    const children = await Models.Campaign.findAll({
                        attributes: ['id'],
                        where: { parentId: campaignId },
                        transaction
                    });

                    campaignIds = [campaignId, ...children.map((c: any) => c.id)];
                }
            }

            // STEP 2: SINGLE QUERY - Campaigns + Aggregated Logs
            let campaigns = await Models.Campaign.findAll({
                where: { id: campaignIds },
                attributes: [
                    ...campaignAttributes,
                    // Aggregated data (FAST!)
                    [Sequelize.fn('COUNT', Sequelize.col('logs.id')), 'totalUsers'],
                    [Sequelize.fn('SUM', Sequelize.literal('CASE WHEN logs.status = 0 THEN 1 ELSE 0 END')), 'inProgressUsers'],
                    [Sequelize.fn('SUM', Sequelize.literal('CASE WHEN logs.status = 1 THEN 1 ELSE 0 END')), 'successUsers'],
                    [Sequelize.fn("SUM", Sequelize.literal("CASE WHEN logs.status = 2 THEN 1 ELSE 0 END")), "failedUsers"]
                ],
                include: [
                    {
                        model: Models.CampaignLog,
                        as: 'logs',
                        attributes: [], // aggregation only
                    }
                ],
                group: ['Campaign.id'],
                transaction,
                //raw: true
            });

            campaigns = JSON.parse(JSON.stringify(campaigns));

            // STEP 3: SUMMARY OBJECT (top-level campaign)
            let campaign;
            if (isComplete) {
                // use first campaign (parent)
                const parentCampaign = campaigns[0] || null;
                campaign = parentCampaign
                    ? {
                        ...parentCampaign,
                        totalUsers: campaigns.reduce((sum: any, r: any) => sum + Number(r.totalUsers || 0), 0),
                        successUsers: campaigns.reduce((sum: any, r: any) => sum + Number(r.successUsers || 0), 0),
                        failedUsers: campaigns.reduce((sum: any, r: any) => sum + Number(r.failedUsers || 0), 0),
                        inProgressUsers: campaigns.reduce((sum: any, r: any) => sum + Number(r.inProgressUsers || 0), 0)
                    }
                    : null;
            } else {
                // only requested campaign (single)
                campaign = campaigns.find((c: any) => c.id === campaignId) || null;
            }

            // STEP 4: FINAL RESPONSE FORMAT
            return {
                totalRuns: campaigns.length,
                campaign,                              // single summary object
                campaigns: isComplete ? campaigns : [] // only include list if complete mode
            };
        } catch (err) {
            console.error(err);
            throw new Error("Error generating campaign report");
        }
    };

    getCampaignLogs = async (data: CampaignGetLogsDaoInput, options: DaoOptions = {}) => {
        const { campaignId, isComplete, page, perPage } = data;
        const { transaction } = options;
        try {
            let where: any = {};   // Base condition

            // ❗ STEP 1: FIND RELATED CAMPAIGN IDs (same logic as earlier)
            let campaignIds: number[] = [campaignId];
            const current = await Models.Campaign.findOne({ where: { id: campaignId }, transaction });

            if (isComplete && current) {
                if (current.parentId) {
                    const parent = await Models.Campaign.findOne({ where: { id: current.parentId }, transaction });
                    const siblings = await Models.Campaign.findAll({
                        attributes: ['id'],
                        where: { parentId: current.parentId },
                        transaction
                    });

                    campaignIds = [
                        parent?.id || campaignId,
                        ...siblings.map((c: any) => c.id)
                    ];
                } else {
                    const children = await Models.Campaign.findAll({
                        attributes: ['id'],
                        where: { parentId: campaignId },
                        transaction
                    });

                    campaignIds = [campaignId, ...children.map((c: any) => c.id)];
                }
            }

            // ❗ STEP 2: FILTER LOGS BY RELATED CAMPAIGNS
            where.campaignId = { [Op.in]: campaignIds };

            // ❗ STEP 3: PAGINATION
            const offset = (page - 1) * perPage;

            // ❗ STEP 4: FETCH DATA WITH PAGINATION
            const logs = await Models.CampaignLog.findAndCountAll({
                attributes: ["id", "campaignId", "sendTo", "replacements", "template", "status", "messageId", "createdAt"],
                where, offset, limit: perPage,
                include: [{
                    model: Models.User,
                    as: "campaignUser",
                    attributes: ["id", "email", "username", "countryCode", "mobile", "status", "createdAt", "updatedAt"],
                    include: [
                        {
                            attributes: ["name"], model: Models.UserProfile, as: "userProfile",
                            include: [
                                {
                                    model: Models.Attachment, as: "profileImage",
                                    attributes: ["id", "fileName", "uniqueName", [fn("CONCAT", process.env.PROTOCOL, "://", process.env.APPLICATION_HOST, "/attachment/", literal("`campaignUser->userProfile->profileImage`.`unique_name`")), "filePath"]]
                                }
                            ]
                        }
                    ]
                }],
                order: [['createdAt', 'DESC']],
                transaction
            });

            return JSON.parse(JSON.stringify(logs))
        } catch (err) {
            if (err instanceof AppError) throw err;
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    };

    campaignWhatsappWebhook = async (data: CampaignWhatsappWebhookDaoInput, options: DaoOptions = {}) => {
        const { requestId, eventName, customerNumber } = data;
        const { transaction } = options;
        try {
            const log = await Models.CampaignLog.findOne({ where: { messageId: requestId, sendTo: "+"+customerNumber }, transaction });
            if (log) {
                if (eventName === "delivered") {
                    await log.update({ status: CAMPAIGN.CAMPAIGN_LOG_STATUS.SUCCESS }, { transaction });
                } else if (eventName === "failed") {
                    await log.update({ status: CAMPAIGN.CAMPAIGN_LOG_STATUS.FAILED }, { transaction });
                    await Models.User.update({ sameOnWhatsapp: false }, { where: { id: log.userId }, transaction });
                } else {
                    await log.update({ status: CAMPAIGN.CAMPAIGN_LOG_STATUS.FAILED }, { transaction });
                    await Models.User.update({ sameOnWhatsapp: false }, { where: { id: log.userId }, transaction });
                }
            }
            return true;
        } catch (err) {
            if (err instanceof AppError) throw err;
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    };

}