import { Sequelize, WhereOptions, FindOptions, literal, fn, col, Transaction, Op } from "sequelize";
import Models, { sequelize } from "../models";
import { LocalizedContent } from "../../utils/contentGenerator";
import { AppError } from "../../utils/errors";
import Moment from "moment-timezone";
import { Common } from "../../utils/common"
import _ from 'lodash';
import { POST } from "../config/constants"
import { is } from "useragent";

const postAttributes: AttributeElement[] = [
    'id', 'code', 'postType', 'status', 'visibility', 'isExclusive', 'publishedAt', 'isFeatured', 'createdAt', 'updatedAt',
    [literal('(case when `content`.title is not null then `content`.title else `defaultContent`.title END)'), 'title'],
    [literal('(case when `content`.description is not null then `content`.description else `defaultContent`.description END)'), 'description'],
    [literal('(case when `content`.description_text is not null then `content`.description_text else `defaultContent`.description_text END)'), 'descriptionText'],
    [literal('(case when `content`.exerpt is not null then `content`.exerpt else `defaultContent`.exerpt END)'), 'exerpt'],
    [literal('(case when `content`.limited_content is not null then `content`.limited_content else `defaultContent`.limited_content END)'), 'limitedContent'],
    [literal('(case when `content`.meta_title is not null then `content`.meta_title else `defaultContent`.meta_title END)'), 'metaTitle'],
    [literal('(case when `content`.meta_description is not null then `content`.meta_description else `defaultContent`.meta_description END)'), 'metaDescription'],
    [literal('(case when `content`.meta_keywords is not null then `content`.meta_keywords else `defaultContent`.meta_keywords END)'), 'metaKeywords'],
    [literal('(case when `content`.canonical_url is not null then `content`.canonical_url else `defaultContent`.canonical_url END)'), 'canonicalUrl'],
    [
        sequelize.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 post_attachments la
          JOIN attachments a ON a.id = la.attachment_id
          WHERE la.post_id = \`Post\`.\`id\`
            AND la.is_featured = 1
          LIMIT 1
        )`),
        "featuredImage"
    ],
    [
        sequelize.literal(`(
          SELECT COUNT(*)
          FROM comments c
          WHERE c.post_id = \`Post\`.\`id\`
            AND c.deleted_at IS NULL
        )`),
        "commentsCount"
    ]
];

const postListAttributes: AttributeElement[] = [
    'id', 'code', 'postType', 'status', 'visibility', 'isExclusive', 'publishedAt', 'isFeatured', 'createdAt', 'updatedAt',
    [literal('(case when `content`.title is not null then `content`.title else `defaultContent`.title END)'), 'title'],
    [literal('(case when `content`.exerpt is not null then `content`.exerpt else `defaultContent`.exerpt END)'), 'exerpt'],
    [literal('(case when `content`.limited_content is not null then `content`.limited_content else `defaultContent`.limited_content END)'), 'limitedContent'],
    [
        sequelize.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 post_attachments la
          JOIN attachments a ON a.id = la.attachment_id
          WHERE la.post_id = \`Post\`.\`id\`
            AND la.is_featured = 1
          LIMIT 1
        )`),
        "featuredImage"
    ],
    [
        sequelize.literal(`(
          SELECT COUNT(*)
          FROM comments c
          WHERE c.post_id = \`Post\`.\`id\`
            AND c.deleted_at IS NULL
        )`),
        "commentsCount"
    ]
];

const restrictedPostAttributes: AttributeElement[] = [
    'id', 'code', 'postType', 'status', 'visibility', 'isExclusive', 'publishedAt', 'isFeatured', 'createdAt', 'updatedAt',
    [literal('(case when `content`.title is not null then `content`.title else `defaultContent`.title END)'), 'title'],
    [literal('(case when `content`.description is not null then `content`.description else `defaultContent`.description END)'), 'description'],
    [literal('(case when `content`.description_text is not null then `content`.description_text else `defaultContent`.description_text END)'), 'descriptionText'],
    [literal('(case when `content`.exerpt is not null then `content`.exerpt else `defaultContent`.exerpt END)'), 'exerpt'],
    [literal('(case when `content`.limited_content is not null then `content`.limited_content else `defaultContent`.limited_content END)'), 'limitedContent'],
    [
        sequelize.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 post_attachments la
          JOIN attachments a ON a.id = la.attachment_id
          WHERE la.post_id = \`Post\`.\`id\`
            AND la.is_featured = 1
          LIMIT 1
        )`),
        "featuredImage"
    ],
    [
        sequelize.literal(`(
          SELECT COUNT(*)
          FROM comments c
          WHERE c.post_id = \`Post\`.\`id\`
            AND c.deleted_at IS NULL
        )`),
        "commentsCount"
    ]
];

const restrictedPostListAttributes: AttributeElement[] = [
    'id', 'code', 'postType', 'status', 'visibility', 'isExclusive', 'publishedAt', 'isFeatured', 'createdAt', 'updatedAt',
    [literal('(case when `content`.title is not null then `content`.title else `defaultContent`.title END)'), 'title'],
    [literal('(case when `content`.exerpt is not null then `content`.exerpt else `defaultContent`.exerpt END)'), 'exerpt'],
    [literal('(case when `content`.limited_content is not null then `content`.limited_content else `defaultContent`.limited_content END)'), 'limitedContent'],
    [
        sequelize.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 post_attachments la
          JOIN attachments a ON a.id = la.attachment_id
          WHERE la.post_id = \`Post\`.\`id\`
            AND la.is_featured = 1
          LIMIT 1
        )`),
        "featuredImage"
    ],
    [
        sequelize.literal(`(
          SELECT COUNT(*)
          FROM comments c
          WHERE c.post_id = \`Post\`.\`id\`
            AND c.deleted_at IS NULL
        )`),
        "commentsCount"
    ]
];

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

const authorAttributes: AttributeElement[] = [
    'id', 'onlineStatus',
    [literal('`author->userProfile`.`name`'), 'name'],
    [literal('`author->userProfile`.`about_me`'), 'aboutMe'],
    [literal('`author->userProfile`.`social_media_links`'), 'socialMediaLinks'],
    [
        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 postAuthorAttributes: AttributeElement[] = [
    'id', 'code',
    [literal('(case when `postAuthor->content`.name is not null then `postAuthor->content`.name else `postAuthor->defaultContent`.name END)'), 'name'],
    [literal('(case when `postAuthor->content`.description is not null then `postAuthor->content`.description else `postAuthor->defaultContent`.description END)'), 'description'],
    [literal('(case when `postAuthor->content`.description_text is not null then `postAuthor->content`.description_text else `postAuthor->defaultContent`.description_text END)'), 'descriptionText'],
    [
        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 attachments as a
              WHERE a.id = postAuthor.image_id
              LIMIT 1
            )`),
        'postAuthorImage'
    ]
];

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 = updatedBy.id
              LIMIT 1
            )`),
        'profileImage'
    ]
];

const postCategoryAttributes: AttributeElement[] = [
    'id',
    'code',
    [literal('(case when `postCategories->content`.name is not null then `postCategories->content`.name else `postCategories->defaultContent`.name END)'), 'name'],
]

const postTagAttributes: AttributeElement[] = [
    'id',
    'code',
    [literal('(case when `postTags->content`.name is not null then `postTags->content`.name else `postTags->defaultContent`.name END)'), 'name'],
]

export class PostDao {
    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 post sortOrder
    setSortOrder = async (data: PostSetSortOrderDaoInput, options: DaoOptions): Promise<boolean> => {
        const { id, postType, before = null, after = null } = data;
        const { transaction } = options;
        try {
            let postData = await Models.Post.findOne({ attributes: ['id', 'sortOrder'], where: { id: id, accountId: this.accountId, postType: postType, isRevision: false } });
            if (postData) {
                if (before || after) {
                    let locationData = await Models.Post.findOne({ attributes: ['id', 'sortOrder'], where: { id: before ? before : after, accountId: this.accountId, postType: postType, isRevision: false } });
                    if (!locationData) {
                        return false;
                    }
                    if (postData.sortOrder < locationData.sortOrder) {
                        await Models.Post.decrement('sortOrder', {
                            by: 1,
                            where: {
                                accountId: this.accountId,
                                postType: postType,
                                [Op.and]: [
                                    { sortOrder: { [Op.gt]: postData.sortOrder } },
                                    before ? { sortOrder: { [Op.lt]: locationData.sortOrder } } : { sortOrder: { [Op.lte]: locationData.sortOrder } }
                                ]
                            },
                            transaction: transaction
                        });
                        await Models.Post.update(before ? { sortOrder: locationData.sortOrder - 1 } : { sortOrder: locationData.sortOrder }, { where: { id: postData.id }, transaction: transaction });
                    } else if (postData.sortOrder > locationData.sortOrder) {
                        await Models.Post.increment('sortOrder', {
                            by: 1,
                            where: {
                                accountId: this.accountId,
                                postType: postType,
                                [Op.and]: [
                                    before ? { sortOrder: { [Op.gte]: locationData.sortOrder } } : { sortOrder: { [Op.gt]: locationData.sortOrder } },
                                    { sortOrder: { [Op.lt]: postData.sortOrder } }
                                ]
                            },
                            transaction: transaction
                        });
                        await Models.Post.update(before ? { sortOrder: locationData.sortOrder } : { sortOrder: locationData.sortOrder + 1 }, { where: { id: postData.id }, transaction: transaction });
                    }
                }
                else {
                    let maxSortOrder = await Models.Post.max('sortOrder');
                    await Models.Post.update(
                        { sortOrder: (maxSortOrder || 0) + 1 }, // default to 1 if no max found
                        { where: { id: postData.id, accountId: this.accountId, postType: postType }, 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 post object
    private getFullObject = async (id: number): Promise<PostInterface> => {
        try {
            let Post = await Models.Post.findOne({
                where: { id: id },
                include: [{ model: Models.PostContent, as: 'postContents' }]
            });
            return JSON.parse(JSON.stringify(Post))
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'ERROR_WHILE_GETTING_FULL_DATA', err);
        }
    }

    // Generate revision of Email Template prior to update and delete functions.
    private storeRevision = async (data: PostStoreRevisionDaoInput, options: DaoOptions): Promise<PostInterface> => {
        const { id } = data;
        const { transaction } = options;
        try {
            let Object: PostInterface = await this.getFullObject(id);
            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;
            for (const key in revisonObject.postContents) {
                revisonObject.postContents[key] = _.omit(revisonObject.postContents[key], ['id', 'postId'])
            }
            let revision = await Models.Post.create(revisonObject, { include: [{ model: Models.PostContent, as: 'postContents' }], 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);
        }
    }

    // get association for the entity
    private includeAssociations = (language: string | null, fullInfo: boolean = false, trackRecord: boolean = false, tags: number[] | null = null, includeTags: boolean = false, categoryId: number | null = null, includeAuthor: boolean = false): IncludeOption[] => {

        const includeModels: IncludeOption[] = [
            {
                attributes: [],
                model: Models.PostContent,
                as: 'content',
                required: true,
                include: [
                    {
                        attributes: [],
                        model: Models.Language,
                        as: 'language',
                        required: true,
                        where: { code: language },
                    },
                ],
            },
            {
                attributes: [],
                model: Models.PostContent,
                as: 'content',
                required: true,
                include: [
                    {
                        attributes: [],
                        model: Models.Language,
                        as: 'language',
                        required: true,
                        where: { code: language },
                    },
                ],
            },
            {
                attributes: [],
                model: Models.PostContent,
                as: 'defaultContent',
                required: true,
                include: [
                    {
                        attributes: [],
                        model: Models.Language,
                        as: 'language',
                        required: true,
                        where: { code: process.env.DEFAULT_LANGUAGE_CODE },
                    },
                ],
            },
            {
                attributes: postCategoryAttributes,
                model: Models.Category,
                as: 'postCategories',
                through: { attributes: [] },
                ...(categoryId ? { where: { id: categoryId }, required: true } : {}),
                include: [
                    {
                        attributes: [],
                        model: Models.CategoryContent,
                        as: 'content',
                        include: [{ required: true, attributes: [], model: Models.Language, as: 'language', where: { code: language } }]
                    },
                    {
                        attributes: [],
                        model: Models.CategoryContent,
                        as: 'defaultContent',
                        include: [{ required: true, attributes: [], model: Models.Language, as: 'language', where: { code: process.env.DEFAULT_LANGUAGE_CODE } }]
                    }
                ]
            },
        ];
        if (tags && tags.length) {
            includeModels.push({
                model: Models.Category,
                as: "postTags",
                where: { id: tags },
                required: true,
                through: { attributes: [] },
                attributes: []
            });
        }
        else if (includeTags) {
            includeModels.push({
                model: Models.Category,
                as: "postTags",
                through: { attributes: [] },
                attributes: postTagAttributes,
                include: [
                    {
                        attributes: [],
                        model: Models.CategoryContent,
                        as: "content",
                        include: [
                            {
                                attributes: [],
                                model: Models.Language,
                                as: "language",
                                where: { code: language },
                            },
                        ],
                    },
                    {
                        attributes: [],
                        model: Models.CategoryContent,
                        as: "defaultContent",
                        include: [
                            {
                                attributes: [],
                                model: Models.Language,
                                as: "language",
                                where: { code: process.env.DEFAULT_LANGUAGE_CODE },
                            },
                        ],
                    },
                ]
            });
        }
        if (fullInfo || includeAuthor) {
            includeModels.push(
                {
                    attributes: authorAttributes,
                    model: Models.User,
                    as: 'author',
                    include: [{ attributes: [], model: Models.UserProfile, as: 'userProfile', include: [{ model: Models.Attachment, as: "profileImage" }] }]
                }
            );
        }
        if (fullInfo) {
            includeModels.push(
                {
                    attributes: updatedByAttributes,
                    model: Models.User,
                    as: 'updatedBy',
                    include: [{ attributes: [], model: Models.UserProfile, as: 'userProfile', include: [{ model: Models.Attachment, as: "profileImage" }] }]
                },
                {
                    attributes: postAttachmentAttributes,
                    model: Models.Attachment,
                    as: 'postAttachments',
                    through: { attributes: [] }
                },
            );
        }

        return includeModels;
    }

    // get post
    getPost = async (data: PostGetPostDaoInput, options: DaoOptions): Promise<PostObjectInteface> => {
        const { id = null, code = null, accountId, postType, language, expanded = true, paranoid = true, trackRecord = true } = data;
        try {
            let pAttributes = postAttributes;
            let whereOptions = { ...(id ? { id } : code ? { code } : {}), accountId: accountId };
            if (postType) {
                whereOptions = { ...(id ? { id, postType } : code ? { code, postType } : { postType }), accountId: accountId };
            }

            const post = await Models.Post.findOne({
                attributes: pAttributes,
                where: whereOptions,
                include: this.includeAssociations(language, expanded, trackRecord, null, true),
                paranoid: paranoid
            });
            if (post) {
                return JSON.parse(JSON.stringify(post)) as unknown as PostObjectInteface;
            } else {
                throw new AppError(404, 'POST_NOT_FOUND', { id: 'POST_NOT_FOUND', code: 'POST_NOT_FOUND' });
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get post by id
    getPostById = async (data: PostGetByIdDaoInput, options: DaoOptions): Promise<PostObjectInteface> => {
        const { id, postType, expanded = true, paranoid = true, trackIncludes = false } = data;
        try {
            const getPostDaoInput: PostGetPostDaoInput = { id, code: null, accountId: this.accountId, postType, language: this.language, expanded, paranoid, trackRecord: trackIncludes };
            return await this.getPost(getPostDaoInput, options);
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get email Template by code
    getPostByCode = async (data: PostGetByCodeDaoInput, options: DaoOptions): Promise<PostObjectInteface> => {
        const { code, postType, expanded = true, paranoid = true } = data;
        try {
            const getPostDaoInput: PostGetPostDaoInput = { id: null, code, accountId: this.accountId, postType, language: this.language, expanded, paranoid };
            return await this.getPost(getPostDaoInput, options);
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // check if Email Template exists by code
    doExistsByCode = async (data: PostDoExistsByCodeDaoInput, options: DaoOptions): Promise<PostInterface | false> => {
        const { code, postType, excludeId = null, includeRevision = false } = data;
        try {
            const post = await Models.Post.findOne({
                attributes: ['id'],
                where: { ...(excludeId ? { id: { [Op.ne]: excludeId } } : {}), accountId: this.accountId, postType: postType, code: code, isRevision: includeRevision }
            });
            return post?.id ? post : false;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // check if Email Template exists by id
    doExistsById = async (data: PostDoExistsByIdDaoInput, options: DaoOptions): Promise<PostInterface | false> => {
        const { id, postType, includeRevision = false } = data;
        try {
            //let whereOptions: { id: number, postType?: string, accountId: number | null, isRevision: boolean } = { id: id, postType: postType, accountId: this.accountId, isRevision: includeRevision };
            const whereOptions: any = { id, accountId: this.accountId, isRevision: includeRevision };
            // add postType only if provided
            if (postType) { whereOptions.postType = postType; }

            const post = await Models.Post.findOne({
                attributes: ['id', 'isFeatured'],
                where: whereOptions
            });
            return post?.id ? post : false;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // create a new Email Template
    create = async (data: PostCreateDaoInput, options: DaoOptions): Promise<number> => {
        const { postObj, postContentObj, postAttachments, featuredImageId, languages, postTagsIds, postCategoriesIds } = data;
        const { transaction } = options;
        try {
            const postContents: PostContentObject[] = LocalizedContent.generate(postContentObj, languages) as unknown as PostContentObject[];
            const postObject: PostDataObject = { ...postObj, authorId: postObj.authorId || this.userId, accountId: this.accountId, postContents: postContents };
            let post = await Models.Post.create(postObject, {
                include: [{ model: Models.PostContent, as: 'postContents' }],
                transaction
            });
            if (postAttachments || featuredImageId) {
                const attachmentsToSet = [...(postAttachments || [])];
                if (featuredImageId && !attachmentsToSet.includes(featuredImageId)) {
                    attachmentsToSet.push(featuredImageId);
                }
                if (attachmentsToSet.length > 0) {
                    await post.setPostAttachments(attachmentsToSet, { transaction: transaction });
                }

                await Models.PostAttachment.update({ isFeatured: false }, { where: { postId: post.id }, transaction: transaction });
                if (featuredImageId) {
                    await Models.PostAttachment.update({ isFeatured: true }, { where: { postId: post.id, attachmentId: featuredImageId }, transaction: transaction });
                } else if (postAttachments && postAttachments.length > 0) {
                    await Models.PostAttachment.update({ isFeatured: true }, { where: { postId: post.id, attachmentId: postAttachments[0] }, transaction: transaction });
                }
            }
            if (postTagsIds)
                await post.setPostTags(postTagsIds, { transaction: transaction });
            if (postCategoriesIds)
                await post.setPostCategories(postCategoriesIds, { transaction: transaction });
            return post.id;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // update post
    update = async (data: PostUpdateDaoInput, options: DaoOptions): Promise<void> => {
        const { id, postObj, postContentObj, postAttachments, featuredImageId, languages, postTagsIds, postCategoriesIds } = data;
        const { transaction } = options;
        try {
            // generate revision for the existing state
            const storeRevisionDaoInput: PostStoreRevisionDaoInput = { id };
            await this.storeRevision(storeRevisionDaoInput, options);
            await Models.Post.update({ ...postObj, lastUpdatedBy: this.userId }, { where: { id: id }, transaction: transaction })
            // check if content exists in requested language
            let verification = await Models.PostContent.findOne({ where: { postId: id, languageId: languages.requested.id }, transaction: transaction });
            if (verification) { // update if content exists in the requested language
                let postContent: PostContentObject = { ...postContentObj, ...{ languageId: languages.requested.id, postId: id } }
                await Models.PostContent.update(postContent, { where: { id: verification.id }, transaction: transaction })

            } else { // create if content does not exists in the requested language
                let postContent: PostContentObject = { ...postContentObj, ...{ languageId: languages.requested.id, postId: id } }
                await Models.PostContent.create(postContent, { transaction: transaction })
            }
            let post = Models.Post.build({ id: id }, { isNewRecord: false, transaction });
            if (postAttachments || featuredImageId) {
                const attachmentsToSet = [...(postAttachments || [])];
                if (featuredImageId && !attachmentsToSet.includes(featuredImageId)) {
                    attachmentsToSet.push(featuredImageId);
                }
                if (attachmentsToSet.length > 0) {
                    await post.setPostAttachments(attachmentsToSet, { transaction: transaction });
                }

                await Models.PostAttachment.update({ isFeatured: false }, { where: { postId: post.id }, transaction: transaction });
                if (featuredImageId) {
                    await Models.PostAttachment.update({ isFeatured: true }, { where: { postId: post.id, attachmentId: featuredImageId }, transaction: transaction });
                } else if (postAttachments && postAttachments.length > 0) {
                    await Models.PostAttachment.update({ isFeatured: true }, { where: { postId: post.id, attachmentId: postAttachments[0] }, transaction: transaction });
                }
            }

            if (postTagsIds)
                await post.setPostTags(postTagsIds, { transaction: transaction });
            if (postCategoriesIds)
                await post.setPostCategories(postCategoriesIds, { transaction: transaction });
            return;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // delete post
    delete = async (data: PostDeleteDaoInput, options: DaoOptions): Promise<void> => {
        const { id, postType } = data;
        const { transaction } = options;
        try {
            const getPostByIdDaoInput: PostGetByIdDaoInput = { id, postType, expanded: false };
            let post = await this.getPostById(getPostByIdDaoInput, options);
            let code = post.code + '-' + Moment().toISOString();
            await Models.Post.update({ lastUpdatedBy: this.userId, code: code }, { where: { id: id }, transaction: transaction });
            await Models.Post.destroy({ where: { id: id }, 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', searchText: string | null = null) => {
        const order: any[] = [];
        if (searchText) {
            const escapedSearchText = searchText.replace(/'/g, "\\'");
            order.push([literal(`(
                SELECT COUNT(*) 
                FROM post_content AS pc_order
                WHERE pc_order.post_id = Post.id
                AND pc_order.title LIKE '%${escapedSearchText}%'
            )`), 'DESC']);
        }
        switch (field) {
            case 'sort-order':
                order.push([`sortOrder`, direction]);
                break;
            case 'name':
                order.push([literal(`title`), direction]);
                break;
            case 'publishedAt':
                order.push(['publishedAt', direction]);
                break;
            case 'id':
                order.push(['id', direction]);
                break;
            default:
                // Fallback to default order
                order.push(['id', 'ASC']);
                break;
        }
        return order;
    }

    // Build filter
    private buildFilter = (where: WhereOptions & { [Op.and]: any[] }, searchText: string | null, status: string | null, isPremium: boolean | null = null, types: string[] | null = null, categoryId: number | null = null, userId: number | null = null, categories: number[] | null = null) => {
        if (searchText) {
            const escapedSearchText = searchText.replace(/'/g, "\\'");
            (where[Op.and] as any[]).push(
                literal(`(
                    EXISTS (SELECT 1 FROM post_content pc WHERE pc.post_id = \`Post\`.id AND (pc.title LIKE '%${escapedSearchText}%' OR pc.description_text LIKE '%${escapedSearchText}%'))
                )`)
            );
        }

        if (status !== null) {
            where = { ...where, status: status }
        }
        if (isPremium !== null) {
            where = { ...where, isExclusive: isPremium }
        }
        if (types && types !== null) {
            where = { ...where, postType: types }
        }
        if (categoryId) {
            (where[Op.and] as any[]).push(
                literal(`EXISTS (
                    SELECT 1 FROM post_categories pc
                    WHERE pc.post_id = \`Post\`.id
                    AND pc.category_id = ${categoryId}
                )`)
            );
        }
        if (categories && categories.length > 0) {
            (where[Op.and] as any[]).push(
                literal(`EXISTS (
                    SELECT 1 FROM post_categories pc
                    WHERE pc.post_id = \`Post\`.id
                    AND pc.category_id IN (${categories.join(',')})
                )`)
            );
        }
        if (userId) {
            where = { ...where, authorId: userId }
        }
        return { where: where, replacements: {} }
    }

    // list post content with pagination
    getPostList = async (data: PostGetPostListDaoInput, options: DaoOptions): Promise<PostPaginatedData> => {
        const { postType, listRequest } = data;
        try {
            const { page, perPage, searchText, status, sortBy, sortDirection, isPremium, categoryId } = listRequest;
            let offset = (page - 1) * perPage;
            let where: WhereOptions & { [Op.and]: any[] } = { accountId: this.accountId, postType: postType, isRevision: false, [Op.and]: [] };
            let applyfilter = this.buildFilter(where, searchText, status, isPremium, null, categoryId, listRequest.userId, listRequest.categories);
            const orderBy = this.buildOrderBy(sortBy, sortDirection, searchText);
            const posts = await Models.Post.findAndCountAll({
                attributes: postListAttributes,
                where: applyfilter.where,
                include: this.includeAssociations(this.language, false, false, null, false, null, true),
                replacements: applyfilter.replacements,
                offset: offset,
                limit: perPage,
                order: orderBy,
                distinct: true
            });
            return JSON.parse(JSON.stringify(posts)) as unknown as PostPaginatedData;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // associate user roles to user
    private getPostTags = async (postIds: number[], language: string): Promise<postTagsObject[]> => {
        try {
            const post = await Models.Post.findAll({
                attributes: ['id'],
                where: {
                    accountId: this.accountId,
                    id: { [Op.in]: postIds },
                },
                include: [
                    {
                        attributes: postTagAttributes,
                        model: Models.Category,
                        as: 'postTags',
                        through: { attributes: [] },
                        include: [
                            {
                                attributes: [],
                                model: Models.CategoryContent,
                                as: 'content',
                                include: [
                                    {
                                        attributes: [],
                                        model: Models.Language,
                                        as: 'language',
                                        where: { code: language }
                                    }
                                ]
                            },
                            {
                                attributes: [],
                                model: Models.CategoryContent,
                                as: 'defaultContent',
                                include: [
                                    {
                                        attributes: [],
                                        model: Models.Language,
                                        as: 'language',
                                        where: { code: process.env.DEFAULT_LANGUAGE_CODE }
                                    }
                                ]
                            }
                        ]
                    }
                ],
                // preserve order using FIELD
                order: [[Sequelize.literal(`FIELD(\`Post\`.id, ${postIds.join(',')})`)]],
            });
            return JSON.parse(JSON.stringify(post));
        } catch (err) {
            if (err instanceof AppError) throw err;
            throw new AppError(400, 'ERROR_WHILE_GETTING_POST_TAGS', err);
        }
    };

    // list post content with pagination
    getPostByTags = async (data: PostGetPostByTagsDaoInput, options: DaoOptions): Promise<PostPaginatedData> => {
        const { listRequest } = data;
        try {
            let { page, perPage, searchText, status, sortBy, sortDirection, isPremium, types, tags, categoryId } = listRequest;
            let offset = (page - 1) * perPage;
            let where: WhereOptions & { [Op.and]: any[] } = { accountId: this.accountId, isRevision: false, [Op.and]: [] };

            let applyfilter = this.buildFilter(where, searchText, status || POST.STATUS.PUBLISH, isPremium, types, categoryId, null, listRequest.categories);
            const orderBy = this.buildOrderBy(sortBy, sortDirection, searchText);
            const include = this.includeAssociations(this.language, false, false, tags, false, null, true);

            const post = await Models.Post.findAndCountAll({
                attributes: postListAttributes,
                where: applyfilter.where,
                include,
                replacements: applyfilter.replacements,
                offset: offset,
                limit: perPage,
                order: orderBy,
                distinct: true
            });
            return JSON.parse(JSON.stringify(post)) as unknown as PostPaginatedData;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // list all post content
    getAllPost = async (data: PostGetAllPostDaoInput, options: DaoOptions): Promise<PostObjectInteface[] | PostObjectSummaryInteface[]> => {
        const { postType, listRequest } = data;
        try {
            const { searchText, status, sortBy, sortDirection, categoryId } = listRequest;
            let where: WhereOptions & { [Op.and]: any[] } = { accountId: this.accountId, postType: postType, isRevision: false, [Op.and]: [] };
            let applyfilter = this.buildFilter(where, searchText, status, null, null, categoryId, null, listRequest.categories);
            const orderBy = this.buildOrderBy(sortBy, sortDirection, searchText);
            const posts = await Models.Post.findAll({
                attributes: postListAttributes,
                where: applyfilter.where,
                replacements: applyfilter.replacements,
                include: this.includeAssociations(this.language, false, false, null, false, null, true),
                order: orderBy,
                limit: searchText ? 20 : +process.env.LIST_ALL_LIMIT!
            });
            return JSON.parse(JSON.stringify(posts)) as unknown as PostObjectInteface[] | PostObjectSummaryInteface[];
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // list Post revisions
    getPostRevisionList = async (data: PostGetPostRevisionListDaoInput, options: DaoOptions): Promise<PostPaginatedData> => {
        const { id, postType, listRequest } = data;
        try {
            const { page, perPage, searchText, status, sortBy, sortDirection } = listRequest;
            let offset = (page - 1) * perPage;
            let where: WhereOptions & { [Op.and]: any[] } = { accountId: this.accountId, postType: postType, isRevision: true, revisionId: id, [Op.and]: [] };
            let applyfilter = this.buildFilter(where, searchText, status);
            const orderBy = this.buildOrderBy(sortBy, sortDirection, searchText);
            const posts = await Models.Post.findAndCountAll({
                attributes: postListAttributes,
                where: applyfilter.where,
                include: this.includeAssociations(this.language, false),
                replacements: applyfilter.replacements,
                offset: offset,
                limit: perPage,
                order: orderBy,
                distinct: true
            });
            return JSON.parse(JSON.stringify(posts)) as unknown as PostPaginatedData;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // restore Post revision 
    restoreRevision = async (data: PostRestoreRevisionDaoInput, options: DaoOptions): Promise<number> => {
        const { id, postType } = data;
        const { transaction } = options;
        try {
            const doExistsByIdDaoInput: PostDoExistsByIdDaoInput = { id, postType, includeRevision: true };
            if (await this.doExistsById(doExistsByIdDaoInput, options)) {
                // get full revision Data
                let Object: PostInterface = await this.getFullObject(id);
                if (Object && Object.revisionId) {
                    // create new revision from existing state
                    const storeRevisionDaoInput: PostStoreRevisionDaoInput = { id: Object.revisionId };
                    await this.storeRevision(storeRevisionDaoInput, options);
                    let revisonObject = JSON.parse(JSON.stringify(Object));
                    // remove unnecessary data from object
                    revisonObject = _.omit(revisonObject, ['id', 'createdAt', 'updatedAt', 'deletedAt']);

                    const updatePostData = {
                        categoryId: revisonObject.categoryId,
                        status: revisonObject.status,
                        visibility: revisonObject.visibility,
                        isExclusive: revisonObject.isExclusive,
                        isFeatured: revisonObject.isFeatured,
                        lastUpdatedBy: this.userId
                    };

                    await Models.Post.update(updatePostData, { where: { id: revisonObject.revisionId }, transaction: transaction });
                    // update entity type content
                    if (revisonObject.postContents && revisonObject.postContents.length) {
                        for (let content of revisonObject.postContents) {
                            await Models.PostContent.upsert({
                                title: content.title,
                                description: content.description,
                                descriptionText: content.descriptionText,
                                embedCode: content.embedCode,
                                exerpt: content.exerpt,
                                metaTitle: content.metaTitle,
                                metaDescription: content.metaDescription,
                                metaKeywords: content.metaKeywords,
                                canonicalUrl: content.canonicalUrl,
                                languageId: content.languageId,
                                postId: 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, 'POST_CONTENT_NOT_FOUND', { id: 'POST_CONTENT_NOT_FOUND' });
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get Post id from Post code
    getIdFromCode = async (code: string, postType: string): Promise<number> => {
        try {
            let post = await Models.Post.findOne({ attributes: ['id'], where: { accountId: this.accountId, code: code, postType: postType } });
            if (post) {
                return post.id;
            } else {
                throw new AppError(404, 'POST_CONTENT_NOT_FOUND', { code: 'POST_CONTENT_NOT_FOUND' });
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get Post id from WordPress post id
    getIdFromWpId = async (wpPostId: number, postType: string): Promise<number | null> => {
        try {
            let post = await Models.Post.findOne({ attributes: ['id'], where: { accountId: this.accountId, wpPostId: wpPostId, postType: postType } });
            return post ? post.id : null;
        } catch (err) {
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

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

    // track post content view
    trackPost = async (id: number, userId: number, trackType: string, action: 'add' | 'remove', watchTime?: number | null) => {
        const transaction = await sequelize.transaction();
        try {
            const existsView = await Models.PostView.findOne({ where: { postId: id, userId }, transaction: transaction });
            const existsReaction = await Models.PostReaction.findOne({ where: { postId: id, userId }, transaction: transaction });
            const existsBookmark = await Models.PostBookmark.findOne({ where: { postId: id, userId }, transaction: transaction });
            let view = existsView;
            let reaction = existsReaction;
            let bookmark = existsBookmark;
            switch (trackType) {
                case 'view':
                    if (!watchTime) watchTime = 0;
                    if (existsView) {
                        view = await existsView.update({ watchTime: existsView.watchTime + watchTime }, { transaction: transaction });
                        break;
                    }
                    view = await Models.PostView.create({ postId: id, userId, watchTime }, { transaction: transaction });
                    await Models.Post.increment({ views: 1 }, { where: { id }, transaction: transaction });
                    break;

                case 'like':
                case 'dislike':
                    if (action === 'add') {
                        if (existsReaction) {
                            reaction = await existsReaction.update({ type: trackType }, { transaction: transaction });
                            break;
                        }
                        reaction = await Models.PostReaction.create({ postId: id, type: trackType, userId }, { transaction: transaction });
                        const field = trackType === 'like' ? { likes: 1 } : { dislikes: 1 };
                        await Models.Post.increment(field, { where: { id }, transaction: transaction });
                    } else if (existsReaction) {
                        await existsReaction.destroy();
                        const field = trackType === 'like' ? { likes: -1 } : { dislikes: -1 };
                        await Models.Post.increment(field, { where: { id }, transaction: transaction }); reaction = null;
                    }
                    break;

                case 'bookmark':
                    if (action === 'add') {
                        if (existsBookmark) {
                            bookmark = existsBookmark;
                            break;
                        }
                        bookmark = await Models.PostBookmark.create({ postId: id, userId }, { transaction: transaction });
                        await Models.Post.increment({ bookmarks: 1 }, { where: { id }, transaction: transaction });
                    } else if (existsBookmark) {
                        await existsBookmark.destroy();
                        await Models.Post.increment({ bookmarks: -1 }, { where: { id }, transaction: transaction });
                        bookmark = null;
                    }
                    break;
                default:
                    throw new AppError(400, 'INVALID_TRACKING_TYPE', { trackType: 'INVALID_TRACKING_TYPE' });
            }
            await transaction.commit();
            return { view, reaction, bookmark };
        } catch (err) {
            await transaction.rollback();
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }
    // list published pages publicly with full attributes
    getPublicPageList = async (data: PostGetPublicPostListDaoInput, options: DaoOptions): Promise<PostPaginatedData> => {
        const { listRequest } = data;
        try {
            const { page, perPage, searchText, sortBy, sortDirection, isPremium, categoryId } = listRequest;
            let offset = (page - 1) * perPage;
            let where: WhereOptions & { [Op.and]: any[] } = {
                accountId: this.accountId,
                isRevision: false,
                postType: 'page',
                [Op.and]: []
            };

            let applyfilter = this.buildFilter(where, searchText, null, isPremium, null, categoryId, null, listRequest.categories);
            const orderBy = this.buildOrderBy(sortBy ?? 'id', sortDirection ?? 'asc', searchText);
            const include = this.includeAssociations(this.language, true, false, null, true, null, true);

            const posts = await Models.Post.findAndCountAll({
                attributes: postAttributes, // Use all keys instead of restrictedPostListAttributes
                where: applyfilter.where,
                include,
                replacements: applyfilter.replacements,
                offset,
                limit: perPage,
                order: orderBy,
                distinct: true
            });
            return JSON.parse(JSON.stringify(posts)) as unknown as PostPaginatedData;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }
    // list published posts publicly (no auth required)
    getPublicPostList = async (data: PostGetPublicPostListDaoInput, options: DaoOptions): Promise<PostPaginatedData> => {
        const { listRequest } = data;
        try {
            const { page, perPage, searchText, sortBy, sortDirection, isPremium, categoryId, postType } = listRequest;
            let offset = (page - 1) * perPage;
            let where: WhereOptions & { [Op.and]: any[] } = {
                accountId: this.accountId,
                isRevision: false,
                status: POST.STATUS.PUBLISH,
                publishedAt: { [Op.lte]: new Date() },
                [Op.and]: []
            };
            if (postType) {
                (where as any).postType = postType;
            }
            let applyfilter = this.buildFilter(where, searchText, null, isPremium, null, categoryId, null, listRequest.categories);
            const orderBy = this.buildOrderBy(sortBy ?? 'id', sortDirection ?? 'desc', searchText);
            const include = this.includeAssociations(this.language, false, false, null, true, null, true);
            const posts = await Models.Post.findAndCountAll({
                attributes: restrictedPostListAttributes,
                where: applyfilter.where,
                include,
                replacements: applyfilter.replacements,
                offset,
                limit: perPage,
                order: orderBy,
                distinct: true
            });
            return JSON.parse(JSON.stringify(posts)) as unknown as PostPaginatedData;
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }

    // get post by code
    getPublicPostByCode = async (data: PostGetPublicPostByCodeDaoInput, options: DaoOptions): Promise<PostObjectInteface> => {

        const { code, expanded = true, paranoid = true } = data;
        try {
            let pAttributes = postAttributes;
            let whereOptions = { code: code, status: POST.STATUS.PUBLISH, accountId: this.accountId, publishedAt: { [Op.lte]: new Date() } };
            const post = await Models.Post.findOne({
                attributes: pAttributes,
                where: whereOptions,
                include: this.includeAssociations(this.language, expanded, false, null, true),
                paranoid: paranoid
            });
            if (post) {
                return JSON.parse(JSON.stringify(post)) as unknown as PostObjectInteface;
            } else {
                throw new AppError(404, 'POST_NOT_FOUND', { id: 'POST_NOT_FOUND', code: 'POST_NOT_FOUND' });
            }
        } catch (err) {
            if (err instanceof AppError) { throw err; }
            throw new AppError(500, 'SOMETHING_WENT_WRONG_WITH_DAO', err);
        }
    }
}