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 _ from "lodash";
import { CATEGORY } from "../config/constants";
import { Common } from "../../utils/common";

const categoryAttributes: AttributeElement[] = [
  "id",
  "code",
  "status",
  "level",
  "refLink",
  "createdAt",
  "updatedAt",
  [
    literal(
      "(case when `content`.name is not null then `content`.name else `defaultContent`.name END)",
    ),
    "name",
  ],
  [
    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(
      `(select count(*) from categories AS children where children.parent_id = \`Category\`.\`id\` and deleted_at is null and is_revision=0)`,
    ),
    "childCount",
  ],
  [
    literal(
      `(select count(*) from categories AS tree where tree.order_sequence LIKE CONCAT(\`Category\`.\`order_sequence\`, '|%') and deleted_at is null and is_revision=0)`,
    ),
    "treeCount",
  ],
];

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 imageAttributes: AttributeElement[] = [
  "id",
  "fileName",
  "uniqueName",
  [
    fn(
      "CONCAT",
      process.env.PROTOCOL,
      "://",
      process.env.API_HOST,
      "/attachment/",
      literal("`categoryImage`.`unique_name`"),
    ),
    "filePath",
  ],
  [
    fn("CONCAT", process.env.CDN_PATH, literal("`categoryImage`.`file_path`")),
    "cdnUrl",
  ],
];

const categoryTypeAttributes: AttributeElement[] = [
  "id",
  "code",
  [
    literal(
      "(case when `categoryType->content`.name is not null then `categoryType->content`.name else `categoryType->defaultContent`.name END)",
    ),
    "name",
  ],
];

const hirarchyAttributes: AttributeElement[] = [
  "id",
  "code",
  "parentId",
  [
    sequelize.literal(
      "(case when `content`.name is not null then `content`.name else `defaultContent`.name END)",
    ),
    "name",
  ],
];

const parentAttributes: AttributeElement[] = [
  "id",
  "code",
  "parentId",
  [
    sequelize.literal(
      "(case when `content`.name is not null then `content`.name else `defaultContent`.name END)",
    ),
    "name",
  ],
];

const categoryTreeAttributes: AttributeElement[] = [
  "id",
  "code",
  [
    literal(
      "(case when `content`.name is not null then `content`.name else `defaultContent`.name END)",
    ),
    "name",
  ],
];

export class CategoryDao {
  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;
  }

  // get order sequence of a category
  private getOrderSequence = async (
    data: CategoryGetOrderSequenceDaoInput,
    options: DaoOptions = {},
  ): Promise<string> => {
    let orderSequence = "";
    let categoryId = data.id;
    let categoryCode = "";
    try {
      let loopcounter = 0;
      let category;
      orderSequence = "";
      while (categoryId != null) {
        if (loopcounter != 0)
          orderSequence =
            orderSequence != ""
              ? categoryCode + "|" + orderSequence
              : categoryCode;
        category = await Models.Category.findOne({
          attributes: ["parentId", "code"],
          where: { id: categoryId },
          transaction: options.transaction,
        });
        categoryCode = category?.code!;
        categoryId = category?.parentId!;
        loopcounter += 1;
      }
      if (orderSequence != "")
        orderSequence = category?.code + "|" + orderSequence;
      else orderSequence = category?.code!;
      return orderSequence;
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // set category sort Order
  setSortOrder = async (
    data: CategorySetSortOrderDaoInput,
    options: DaoOptions = {},
  ): Promise<boolean> => {
    const {
      id,
      categoryTypeId,
      before = null,
      after = null,
      asSuperAdmin = false,
    } = data;
    try {
      let categoryData = await Models.Category.findOne({
        attributes: ["id", "parentId", "sortOrder"],
        where: {
          id: id,
          accountId: asSuperAdmin ? [this.accountId, null] : this.accountId,
          categoryTypeId: categoryTypeId,
          isRevision: false,
        },
      });
      if (categoryData) {
        if (before || after) {
          let locationData = await Models.Category.findOne({
            attributes: ["id", "parentId", "sortOrder"],
            where: {
              accountId: asSuperAdmin ? [this.accountId, null] : this.accountId,
              categoryTypeId: categoryTypeId,
              id: before ? before : after,
              isRevision: false,
            },
          });
          if (!locationData) {
            return false;
          }
          if (locationData.parentId != categoryData.parentId) {
            return false;
          }
          if (categoryData.sortOrder < locationData.sortOrder) {
            await Models.Category.decrement("sortOrder", {
              by: 1,
              where: {
                accountId: asSuperAdmin
                  ? [this.accountId, null]
                  : this.accountId,
                categoryTypeId: categoryTypeId,
                parentId: categoryData.parentId,
                [Op.and]: [
                  { sortOrder: { [Op.gt]: categoryData.sortOrder } },
                  before
                    ? { sortOrder: { [Op.lt]: locationData.sortOrder } }
                    : { sortOrder: { [Op.lte]: locationData.sortOrder } },
                ],
              },
              transaction: options.transaction,
            });
            await Models.Category.update(
              before
                ? { sortOrder: locationData.sortOrder - 1 }
                : { sortOrder: locationData.sortOrder },
              {
                where: {
                  id: categoryData.id,
                  categoryTypeId: categoryTypeId,
                  accountId: asSuperAdmin
                    ? [this.accountId, null]
                    : this.accountId,
                },
                transaction: options.transaction,
              },
            );
          } else if (categoryData.sortOrder > locationData.sortOrder) {
            await Models.Category.increment("sortOrder", {
              by: 1,
              where: {
                accountId: asSuperAdmin
                  ? [this.accountId, null]
                  : this.accountId,
                categoryTypeId: categoryTypeId,
                parentId: categoryData.parentId,
                [Op.and]: [
                  before
                    ? { sortOrder: { [Op.gte]: locationData.sortOrder } }
                    : { sortOrder: { [Op.gt]: locationData.sortOrder } },
                  { sortOrder: { [Op.lt]: categoryData.sortOrder } },
                ],
              },
              transaction: options.transaction,
            });
            await Models.Category.update(
              before
                ? { sortOrder: locationData.sortOrder }
                : { sortOrder: locationData.sortOrder + 1 },
              {
                where: {
                  id: categoryData.id,
                  accountId: asSuperAdmin
                    ? [this.accountId, null]
                    : this.accountId,
                  categoryTypeId: categoryTypeId,
                },
                transaction: options.transaction,
              },
            );
          }
        } else {
          let maxSortOrder = await Models.Category.max("sortOrder", {
            where: {
              parentId: categoryData.parentId,
              categoryTypeId: categoryTypeId,
              accountId: asSuperAdmin ? [this.accountId, null] : this.accountId,
            },
          });
          await Models.Category.update(
            { sortOrder: (maxSortOrder || 0) + 1 }, // default to 1 if no max found
            {
              where: {
                id: categoryData.id,
                accountId: asSuperAdmin
                  ? [this.accountId, null]
                  : this.accountId,
                categoryTypeId: categoryTypeId,
              },
              transaction: options.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 all parents
  private getParentHirarchy = async (
    data: CategoryGetParentHirarchyDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryHirarchyInterface[]> => {
    try {
      const { id = null, code = null } = data;
      let parentHirarchy: CategoryHirarchyInterface[] = [];
      let parent = await Models.Category.findOne({
        attributes: hirarchyAttributes,
        where: { ...(id ? { id: id } : code ? { code: code } : {}) },
        include: [
          {
            attributes: [],
            model: Models.CategoryContent,
            as: "content",
            include: [
              {
                attributes: [],
                model: Models.Language,
                as: "language",
                where: { code: this.language },
              },
            ],
          },
          {
            attributes: [],
            model: Models.CategoryContent,
            as: "defaultContent",
            include: [
              {
                attributes: [],
                model: Models.Language,
                as: "language",
                where: { code: process.env.DEFAULT_LANGUAGE_CODE },
              },
            ],
          },
        ],
      });
      let object = JSON.parse(JSON.stringify(parent));
      if (object.parentId) {
        parentHirarchy.push(object);
        let parentCategory: CategoryHirarchyInterface[] =
          await this.getParentHirarchy(
            { id: object.parentId, code: null },
            options,
          );
        parentHirarchy = parentHirarchy.concat(parentCategory);
        return parentHirarchy;
      } else {
        parentHirarchy.push(object);
        return parentHirarchy;
      }
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // Get full category object
  private getFullObject = async (
    data: CategoryGetFullObjectDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryInterface> => {
    try {
      let Category = await Models.Category.findOne({
        where: {
          id: data.id,
          accountId: {
            [Op.or]: [null, this.accountId],
          },
        },
        include: [{ model: Models.CategoryContent, as: "categoryContents" }],
        transaction: options.transaction,
      });
      return JSON.parse(
        JSON.stringify(Category),
      ) as unknown as CategoryInterface;
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // Generate revision of category prior to update and delete functions.
  private storeRevision = async (
    data: CategoryStoreRevisionDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryInterface> => {
    try {
      let Object: CategoryInterface = await this.getFullObject(
        { id: data.id },
        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;
      for (const key in revisonObject.categoryContents) {
        revisonObject.categoryContents[key] = _.omit(
          revisonObject.categoryContents[key],
          ["id", "categoryId"],
        );
      }
      let revision = await Models.Category.create(revisonObject, {
        include: [{ model: Models.CategoryContent, as: "categoryContents" }],
        transaction: options.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 = (
    fullInfo: boolean = false,
  ): IncludeOption[] => {
    const includeModels: IncludeOption[] = [
      {
        attributes: [],
        model: Models.CategoryContent,
        as: "content",
        include: [
          {
            attributes: [],
            model: Models.Language,
            as: "language",
            where: { code: this.language },
          },
        ],
      },
      {
        attributes: [],
        model: Models.CategoryContent,
        as: "defaultContent",
        include: [
          {
            attributes: [],
            model: Models.Language,
            as: "language",
            where: { code: process.env.DEFAULT_LANGUAGE_CODE },
          },
        ],
      },
      {
        attributes: imageAttributes,
        model: Models.Attachment,
        as: "categoryImage",
      },
    ];
    if (fullInfo) {
      includeModels.push(
        {
          model: Models.CategoryType,
          attributes: categoryTypeAttributes,
          as: "categoryType",
          include: [
            {
              attributes: [],
              model: Models.CategoryTypeContent,
              as: "content",
              include: [
                {
                  attributes: [],
                  model: Models.Language,
                  as: "language",
                  where: { code: this.language },
                },
              ],
            },
            {
              attributes: [],
              model: Models.CategoryTypeContent,
              as: "defaultContent",
              include: [
                {
                  attributes: [],
                  model: Models.Language,
                  as: "language",
                  where: { code: process.env.DEFAULT_LANGUAGE_CODE },
                },
              ],
            },
          ],
        },
        {
          attributes: parentAttributes,
          model: Models.Category,
          as: "parentCategory",
        },
        {
          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 (
    data: CategoryIfChildIsParentDaoInput,
    options: DaoOptions = {},
  ): Promise<boolean> => {
    try {
      const { categoryId, parentId } = data;
      let children = await Models.Category.findAll({
        attributes: ["id"],
        where: { parentId: categoryId, 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(
            { categoryId: child.id!, parentId },
            options,
          );
          if (levelHasParent) return true;
        }
        return false;
      }
    } catch (err) {
      return true;
    }
  };

  // get category by code or id
  getCategory = async (
    data: CategoryGetDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryObjectInteface> => {
    try {
      const {
        id = null,
        code = null,
        fullInfo = false,
        paranoid = true,
      } = data;
      let where: WhereOptions = id ? { id: id } : code ? { code: code } : {};
      if (this.accountId) {
        where = {
          ...where,
          accountId: {
            [Op.or]: [null, this.accountId],
          },
        };
      }
      const category = await Models.Category.findOne({
        attributes: [...categoryAttributes, "parentId"],
        where: where,
        include: this.includeAssociations(fullInfo),
        paranoid: paranoid,
        transaction: options.transaction,
      });
      if (category) {
        const parentHirarchy = category.parentId
          ? await this.getParentHirarchy(
              { id: category.parentId, code },
              options,
            )
          : [];
        let categoryData = {
          ...JSON.parse(JSON.stringify(category)),
          ...{ parentHirarchy: JSON.parse(JSON.stringify(parentHirarchy)) },
        };
        delete categoryData.parentId;
        return categoryData as unknown as CategoryObjectInteface;
      } else {
        throw new AppError(404, "CATEGORY_NOT_FOUND", {});
      }
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // get category by id
  getCategoryById = async (
    data: CategoryGetByIdDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryObjectInteface> => {
    try {
      const getCategoryInput: CategoryGetDaoInput = {
        id: data.id,
        code: null,
        fullInfo: data.fullInfo ?? false,
        paranoid: data.paranoid ?? true,
      };
      return await this.getCategory(getCategoryInput, options);
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // get category by code
  getCategoryByCode = async (
    data: CategoryGetByCodeDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryObjectInteface> => {
    try {
      const getCategoryInput: CategoryGetDaoInput = {
        id: null,
        code: data.code,
        fullInfo: data.fullInfo ?? false,
        paranoid: data.paranoid ?? true,
      };
      return await this.getCategory(getCategoryInput, options);
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

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

  // check if category exists by id
  doExistsById = async (
    data: CategoryDoExistsByIdDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryInterface | false> => {
    try {
      const { id, includeRevision = false } = data;
      const category = await Models.Category.findOne({
        attributes: ["id", "code", "categoryTypeId"],
        where: {
          id: id,
          accountId: { [Op.or]: [null, this.accountId] },
          isRevision: includeRevision,
        },
        transaction: options.transaction,
      });
      return category?.id ? JSON.parse(JSON.stringify(category)) : false;
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

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

  // create a new category
  create = async (
    data: CategoryCreateDaoInput,
    options: DaoOptions = {},
  ): Promise<number> => {
    try {
      const { categoryObj, categoryContentObj, asSuperAdmin, languages } = data;
      const categoryContents: CategoryContentObject[] =
        LocalizedContent.generate(
          categoryContentObj,
          languages,
        ) as unknown as CategoryContentObject[];
      const categoryObject: CategoryDataObject = {
        ...categoryObj,
        userId: this.userId,
        accountId: asSuperAdmin ? null : this.accountId,
        categoryContents: categoryContents,
      };
      let category = await Models.Category.create(categoryObject, {
        include: [{ model: Models.CategoryContent, as: "categoryContents" }],
        transaction: options.transaction,
      });
      let orderSequence = await this.getOrderSequence(
        { id: category.id! },
        options,
      );
      let level = orderSequence.split("|").length;
      await Models.Category.update(
        { orderSequence: orderSequence, level: level },
        { where: { id: category.id }, transaction: options.transaction },
      );
      return category.id;
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // update category
  update = async (
    data: CategoryUpdateDaoInput,
    options: DaoOptions = {},
  ): Promise<void> => {
    try {
      const { id, categoryObj, categoryContentObj, languages } = data;
      let where: WhereOptions = { id: id };
      if (this.accountId) {
        where = { ...where, accountId: this.accountId };
      }
      // generate revision for the existing state
      await this.storeRevision({ id }, options);
      // check if content exists in requested language
      let verification = await Models.CategoryContent.findOne({
        where: { categoryId: id, languageId: languages.requested.id },
        transaction: options.transaction,
      });
      if (verification) {
        // update if content exists in the requested language
        let categoryContent: CategoryContentObject = {
          ...categoryContentObj,
          ...{ languageId: languages.requested.id, categoryId: id },
        };
        await Models.CategoryContent.update(categoryContent, {
          where: { id: verification.id },
          transaction: options.transaction,
        });
      } else {
        // create if content does not exists in the requested language
        let categoryContent: CategoryContentObject = {
          ...categoryContentObj,
          ...{ languageId: languages.requested.id, categoryId: id },
        };
        await Models.CategoryContent.create(categoryContent, {
          transaction: options.transaction,
        });
      }
      let orderSequence = await this.getOrderSequence({ id }, options);
      let level = orderSequence.split("|").length;
      await Models.Category.update(
        { ...categoryObj, orderSequence: orderSequence, level: level },
        { where: { id: id }, transaction: options.transaction },
      );
      return;
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // delete category
  delete = async (
    data: CategoryDeleteDaoInput,
    options: DaoOptions = {},
  ): Promise<void> => {
    try {
      const { id, asSuperAdmin } = data;
      let where: WhereOptions = {
        id: id,
        accountId: asSuperAdmin
          ? {
              [Op.or]: [null, this.accountId],
            }
          : this.accountId,
      };
      let category = await this.getCategoryById(
        { id, fullInfo: false },
        options,
      );
      let code = category.code + "-" + Moment().toISOString();
      await Models.Category.update(
        { updatedBy: this.userId, code: code },
        { where: where, transaction: options.transaction },
      );
      await Models.Category.destroy({
        where: where,
        transaction: options.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 categories
  getCategoryList = async (
    data: CategoryGetCategoryListDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryPaginatedData> => {
    try {
      const { categoryTypeId, listRequest } = data;
      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]: [],
      };
      if (categoryTypeId) {
        where = { ...where, categoryTypeId: categoryTypeId };
      }
      let applyfilter = this.buildFilter(where, searchText, status);
      const orderBy = this.buildOrderBy(sortBy, sortDirection);
      const categories = await Models.Category.findAndCountAll({
        attributes: categoryAttributes,
        where: applyfilter.where,
        include: this.includeAssociations(false),
        replacements: applyfilter.replacements,
        offset: offset,
        limit: perPage,
        order: orderBy,
        transaction: options.transaction,
      });
      return JSON.parse(
        JSON.stringify(categories),
      ) as unknown as CategoryPaginatedData;
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // list all categories
  getAllCategories = async (
    data: CategoryGetAllCategoriesDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryObjectInteface[]> => {
    try {
      const { categoryTypeId, listRequest } = data;
      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]: [],
      };
      if (categoryTypeId) {
        where = { ...where, categoryTypeId: categoryTypeId };
      }
      let applyfilter = this.buildFilter(where, searchText, status);
      const orderBy = this.buildOrderBy(sortBy, sortDirection);
      const categories = await Models.Category.findAll({
        attributes: categoryAttributes,
        where: applyfilter.where,
        replacements: applyfilter.replacements,
        include: this.includeAssociations(false),
        order: orderBy,
        limit: +process.env.LIST_ALL_LIMIT!,
        transaction: options.transaction,
      });
      return JSON.parse(
        JSON.stringify(categories),
      ) as unknown as CategoryObjectInteface[];
    } catch (err) {
      console.log(err);
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // list category revisions
  getCategoryRevisionList = async (
    data: CategoryGetCategoryRevisionListDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryPaginatedData> => {
    try {
      const { id, listRequest } = data;
      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 categories = await Models.Category.findAndCountAll({
        attributes: categoryAttributes,
        where: applyfilter.where,
        include: this.includeAssociations(false),
        replacements: applyfilter.replacements,
        offset: offset,
        limit: perPage,
        transaction: options.transaction,
      });
      return JSON.parse(
        JSON.stringify(categories),
      ) as unknown as CategoryPaginatedData;
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // restore category revision
  restoreRevision = async (
    data: CategoryRestoreRevisionDaoInput,
    options: DaoOptions = {},
  ): Promise<number> => {
    try {
      if (
        await this.doExistsById({ id: data.id, includeRevision: true }, options)
      ) {
        // get full revision Data
        let Object: CategoryInterface = await this.getFullObject(
          { id: data.id },
          options,
        );
        if (Object && Object.revisionId) {
          // create new revision from existing state
          await this.storeRevision({ id: Object.revisionId }, 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.Category.update(
            { lastUpdatedBy: this.userId },
            {
              where: { id: revisonObject.revisionId },
              transaction: options.transaction,
            },
          );
          // update entity type content
          if (
            revisonObject.categoryContents &&
            revisonObject.categoryContents.length
          ) {
            for (let content of revisonObject.categoryContents) {
              await Models.CategoryContent.upsert(
                {
                  name: content.name,
                  description: content.description,
                  descriptionText: content.descriptionText,
                  languageId: content.languageId,
                  categoryId: Object.revisionId,
                },
                { transaction: options.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, "CATEGORY_TYPE_NOT_FOUND", {
          id: "CATEGORY_TYPE_NOT_FOUND",
        });
      }
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

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

  // generate category tree
  private getCategoryTree = async (
    data: CategoryGetCategoryTreeDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryTree[]> => {
    try {
      const { categoryTypeId, parentId, language } = data;
      let arrayIds: number[] = [];
      let tree: CategoryTree[] = [];
      let parentFlag = "";
      let categories = await Models.Category.findAll({
        attributes: categoryTreeAttributes,
        include: this.includeAssociations(false),
        where: {
          parentId: parentId,
          categoryTypeId: categoryTypeId,
          status: CATEGORY.STATUS.ACTIVE,
          isRevision: false,
          accountId: {
            [Op.or]: [null, this.accountId],
          },
        },
        order: [["sortOrder", "ASC"]],
      });
      if (categories.length > 0) {
        for (let category of categories) {
          category = JSON.parse(JSON.stringify(category));
          if (category.name && category.id) {
            let categoryObject: CategoryTree = {
              id: category.id,
              name: category.name,
              code: category.code,
              categoryImage: category.categoryImage,
              childCategories: [],
            };
            let childTree = await this.getCategoryTree(
              { categoryTypeId, parentId: category.id, language },
              options,
            );
            categoryObject.childCategories = childTree;
            tree.push(categoryObject);
          }
        }
        return tree;
      } else {
        return [];
      }
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // generate category tree structure
  public getCategoryTreeStructure = async (
    data: CategoryGetCategoryTreeStructureDaoInput,
    options: DaoOptions = {},
  ): Promise<CategoryTree[]> => {
    try {
      const responseData = await this.getCategoryTree(
        {
          categoryTypeId: data.categoryTypeId,
          parentId: data.parentId,
          language: this.language,
        },
        options,
      );
      if (responseData) {
        return JSON.parse(
          JSON.stringify(responseData),
        ) as unknown as CategoryTree[];
      } else {
        throw new AppError(400, "ERROR_WHILE_GENERATING_TREE", {});
      }
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

  // verify category id
  public verifyCategoryId = async (
    data: CategoryVerifyCategoryIdDaoInput,
    options: DaoOptions = {},
  ): Promise<boolean> => {
    try {
      let where: WhereOptions = { id: data.categoryId };
      if (this.accountId && !["permission"].includes(data.categoryTypeCode)) {
        where = { ...where, accountId: { [Op.or]: [null, this.accountId] } };
      }
      let category = await Models.Category.findOne({
        where: where,
        include: [
          {
            model: Models.CategoryType,
            as: "categoryType",
            where: { code: data.categoryTypeCode },
            required: true,
          },
        ],
        transaction: options.transaction,
      });
      if (category) {
        return true;
      } else {
        return false;
      }
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_WITH_DAO", err);
    }
  };

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