const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const bcrypt = require("bcryptjs");
const axios = require("axios");
const { Op } = require("sequelize");
const { createValidationError, createBadRequestError } = require("../../../../utils/createError");
const ValidationHelper = require("../../../../utils/validationHelper");

module.exports = {
  // status
  getStatus,

  // get for dropdown
  getBatch,
  getDeliveryOrder,
  getSalesInvoice,
  getPrincipal,
  getSupplier,
  getMaterialRequests,
  getPurchaseOrderByMaterialRequest,
  getPurchaseOrders,
  getPurchaseReceiptByPurchaseOrder,
  getPurchaseOrderItems,

  // get for table
  getUsers,
  getEmployees,
  getDepartments,
  getDesignations,
  getItems,
  getHolidayGroups,
  getAccounts,

  // import
  importUser,
  importUsers,
  importAllUser,

  importEmployee,
  importEmployees,
  importAllEmployee,

  importDepartment,
  importDepartments,
  importAllDepartment,

  importDesignation,
  importDesignations,
  importAllDesignation,

  importItem,
  importItems,
  importAllItem,

  importHolidayGroup,
};

/**
 * Slugify a given text.
 * @param {string} text The text to slugify.
 * @returns {string} The slugified text.
 * @description
 * This function takes a given text and slugifies it by removing all non-alphanumeric
 * characters, converting it to lowercase, and replacing all spaces with hyphens. If the
 * text is empty or cannot be slugified, an empty string is returned.
 */
function slugify(text) {
  const str = text
    .toString()
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")
    .toLowerCase()
    .trim()
    .replace(/\s+/g, "-")
    .replace(/[^a-zA-Z0-9-]/g, "");

  return str ? str : "";
}

/**
 * Marks existing emails in a list of users.
 * @param {Array<Object>} users A list of users to check if their emails exist in the database.
 * @returns {Array<Object>} The same list of users with an additional property `exists_in_database` set to `true` if the email exists in the database, and `false` otherwise.
 * @description
 * This function takes a list of users and checks if their emails exist in the database. It adds a property `exists_in_database` to each user object in the list with a value of `true` if the email exists in the database, and `false` otherwise.
 * @throws {Error} If the argument is not an array.
 */
async function markExistingEmails(users) {
  // Check if the argument is an array
  if (!Array.isArray(users)) throw new Error("dataset is not an array");

  // Convert emails to lowercase
  const emails = users.map((user) => user.email.trim().toLowerCase());

  // Check if emails exist in the database
  const existingEmails = await dbSAPPS.User.findAll({
    attributes: ["email"],
    where: { email: { [Op.in]: emails } },
  });

  // Create a set of the existing names
  const existingEmailsSet = new Set(existingEmails.map((user) => user.email.trim().toLowerCase()));

  // Mark each dataset as existing or not
  for (const user of users) {
    user.exists_in_database = existingEmailsSet.has(user.email.trim().toLowerCase());
  }

  return users;
}

/**
 * Marks existing holiday groups in a list of holiday groups.
 * @param {Array<Object>} holidayGroups A list of holiday groups to check if they exist in the database.
 * @returns {Array<Object>} The same list of holiday groups with an additional property `exists_in_database` set to `true` if the holiday group exists in the database, and `false` otherwise.
 * @description
 * This function takes a list of holiday groups and checks if they exist in the database. It adds a property `exists_in_database` to each holiday group object in the list with a value of `true` if the holiday group exists in the database, and `false` otherwise.
 * @throws {Error} If the argument is not an array.
 */
async function markExistingHolidayGroups(holidayGroups) {
  // Check if the argument is an array
  if (!Array.isArray(holidayGroups)) throw new Error("dataset is not an array");

  // Convert holiday group names to lowercase
  const holidayGroupNames = holidayGroups.map((group) => group.name.trim().toLowerCase());

  // Check if holiday group names exist in the database
  const existingholidayGroups = await dbSAPPS.HolidayGroup.findAll({
    attributes: ["display_name"],
    where: { display_name: { [Op.in]: holidayGroupNames } },
  });

  // Create a set of the existing names
  const existingHolidayGroupsSet = new Set(
    existingholidayGroups.map((holidayGroup) => holidayGroup.display_name.trim().toLowerCase())
  );

  // Mark each dataset as existing or not
  for (const holidayGroup of holidayGroups) {
    holidayGroup.exists_in_database = existingHolidayGroupsSet.has(holidayGroup.name.trim().toLowerCase());
  }

  return holidayGroups;
}

/**
 * Marks existing employee names in a list of employees.
 * @param {Array<Object>} employees A list of employees to check if they exist in the database.
 * @returns {Array<Object>} The same list of employees with an additional property `exists_in_database` set to `true` if the employee exists in the database, and `false` otherwise.
 * @description
 * This function takes a list of employees and checks if their names exist in the database. It adds a property `exists_in_database` to each employee object in the list with a value of `true` if the employee name exists in the database, and `false` otherwise.
 * @throws {Error} If the argument is not an array.
 */
async function markExistingEmployeeNames(employees) {
  // Check if the argument is an array
  if (!Array.isArray(employees)) throw new Error("dataset is not an array");

  // Convert employee names to lowercase
  const names = employees.map((employee) => employee.employee_name.trim().replace(/\s+/g, " "));

  // Check if employee names exist in the database
  const existingNames = await dbSAPPS.Employee.findAll({
    attributes: ["employee_name"],
    where: { employee_name: { [Op.in]: names } },
  });

  // Create a set of the existing names
  const existingNamesSet = new Set(existingNames.map((employee) => employee.employee_name.trim().replace(/\s+/g, " ")));

  // Mark each dataset as existing or not
  for (const employee of employees) {
    employee.exists_in_database = existingNamesSet.has(employee.employee_name.trim().replace(/\s+/g, " "));
  }

  return employees;
}

/**
 * Marks existing items in a list of items.
 * @param {Array<Object>} items A list of items to check if they exist in the database.
 * @returns {Array<Object>} The same list of items with an additional property `exists_in_database` set to `true` if the item exists in the database, and `false` otherwise.
 * @description
 * This function takes a list of items and checks if they exist in the database. It adds a property `exists_in_database` to each item object in the list with a value of `true` if the item exists in the database, and `false` otherwise.
 * @throws {Error} If the argument is not an array.
 */
async function markExistingItems(items) {
  // Check if the argument is an array
  if (!Array.isArray(items)) throw new Error("dataset is not an array");

  // Check if items exist in the database
  const existingItems = await dbSAPPS.Item.findAll({
    attributes: ["display_name", "code", "sku_code"],
    where: {
      [Op.or]: items.map((item) => ({
        display_name: item.item_name,
        code: item.item_code,
        sku_code: item.sku_code,
      })),
    },
  });

  // Create a set of the existing items
  const existingItemsSet = new Set(existingItems.map((item) => `${item.display_name}.${item.code}.${item.sku_code}`));

  // Mark each item as existing or not
  for (const item of items) {
    item.exists_in_database = existingItemsSet.has(`${item.item_name}.${item.item_code}.${item.sku_code}`);
  }

  return items;
}

/**
 * Marks existing department names in a list of departments.
 * @param {Array<Object>} departments A list of departments to check if they exist in the database.
 * @returns {Array<Object>} The same list of departments with an additional property `exists_in_database` set to `true` if the department exists in the database, and `false` otherwise.
 * @description
 * This function takes a list of departments and checks if their names exist in the database. It adds a property `exists_in_database` to each department object in the list with a value of `true` if the department name exists in the database, and `false` otherwise.
 * @throws {Error} If the argument is not an array.
 */
async function markExistingDepartmentNames(departments) {
  // Check if the argument is an array
  if (!Array.isArray(departments)) throw new Error("dataset is not an array");

  // Convert department names to lowercase
  const departmentNames = departments.map((department) => department.department_name);

  // Check if the names exist in the database
  const existingNames = await dbSAPPS.Department.findAll({
    attributes: ["display_name"],
    where: { display_name: { [Op.in]: departmentNames } },
  });

  // Create a set of the existing names
  const existingNamesSet = new Set(existingNames.map((department) => department.display_name));

  // Mark each dataset as existing or not
  for (const department of departments) {
    department.exists_in_database = existingNamesSet.has(department.department_name);
  }

  return departments;
}

/**
 * Marks existing designation names in a list of designations.
 * @param {Array<Object>} designations A list of designations to check if they exist in the database.
 * @returns {Array<Object>} The same list of designations with an additional property `exists_in_database` set to `true` if the designation exists in the database, and `false` otherwise.
 * @description
 * This function takes a list of designations and checks if their names exist in the database. It adds a property `exists_in_database` to each designation object in the list with a value of `true` if the designation name exists in the database, and `false` otherwise.
 * @throws {Error} If the argument is not an array.
 */
async function markExistingDesignationNames(designations) {
  // Check if the argument is an array
  if (!Array.isArray(designations)) throw new Error("dataset is not an array");

  // Convert department names to lowercase
  const designationNames = designations.map((designation) => designation.designation_name);

  // Check if the names exist in the database
  const existingNames = await dbSAPPS.Designation.findAll({
    attributes: ["display_name"],
    where: { display_name: { [Op.in]: designationNames } },
  });

  // Create a set of the existing names
  const existingNamesSet = new Set(existingNames.map((designation) => designation.display_name));

  // Mark each dataset as existing or not
  for (const designation of designations) {
    designation.exists_in_database = existingNamesSet.has(designation.designation_name);
  }

  return designations;
}

/**
 * Filters out emails that already exist in the database from a given list of users.
 * @param {Array<Object>} users A list of users to check if their emails exist in the database.
 * @returns {Array<Object>} The same list of users with emails that do not exist in the database.
 * @description
 * This function takes a list of users and checks if their emails exist in the database. It returns a new list of users with only the emails that do not exist in the database.
 * @throws {Error} If the argument is not an array.
 */
async function filterNewEmails(users) {
  // Check if the argument is an array
  if (!Array.isArray(users)) throw new Error("dataset is not an array");

  // check data if exist
  const existingEmails = await dbSAPPS.User.findAll({
    attributes: ["email"],
    where: {
      email: {
        [Op.in]: users.map((user) => user.email.trim().toLowerCase()),
      },
    },
  });

  // Create a set of the existing
  const existingEmailsSet = new Set(existingEmails.map((user) => user.email.trim().toLowerCase()));

  // Mark each dataset as existing or not
  const newUsers = users.filter((user) => !existingEmailsSet.has(user.email.trim().toLowerCase()));

  return newUsers;
}

/**
 * Filters out holiday groups that already exist in the database from a given list of holiday groups.
 * @param {Array<Object>} holidayGroups A list of holiday groups to check if they exist in the database.
 * @returns {Array<Object>} The same list of holiday groups with only the holiday groups that do not exist in the database.
 * @description
 * This function takes a list of holiday groups and checks if their names exist in the database. It returns a new list of holiday groups with only the holiday groups that do not exist in the database.
 * @throws {Error} If the argument is not an array.
 */
async function filterNewHolidayGroups(holidayGroups) {
  // Check if the argument is an array
  if (!Array.isArray(holidayGroups)) throw new Error("dataset is not an array");

  // Convert holiday group names to lowercase
  const holidayGroupNames = holidayGroups.map((group) => group.name.trim().toLowerCase());

  // Check if the names exist in the database
  const existingHolidayGroups = await dbSAPPS.HolidayGroup.findAll({
    attributes: ["display_name"],
    where: { display_name: { [Op.in]: holidayGroupNames } },
  });

  // Create a set of the existing names
  const existingHolidayGroupsSet = new Set(
    existingHolidayGroups.map((group) => group.display_name.trim().toLowerCase())
  );

  // Mark each dataset as existing or not
  const newHolidayGroups = holidayGroups.filter(
    (group) => !existingHolidayGroupsSet.has(group.name.trim().toLowerCase())
  );

  return newHolidayGroups;
}

/**
 * Filters out employee names that already exist in the database from a given list of employees.
 * @param {Array<Object>} employees A list of employees to check if their names exist in the database.
 * @returns {Array<Object>} The same list of employees with only the employee names that do not exist in the database.
 * @description
 * This function takes a list of employees and checks if their names exist in the database. It returns a new list of employees with only the employee names that do not exist in the database.
 * @throws {Error} If the argument is not an array.
 */
async function filterNewEmployeeNames(employees) {
  // Check if the argument is an array
  if (!Array.isArray(employees)) throw new Error("dataset is not an array");

  // check data if exist
  const existingEmployeeNames = await dbSAPPS.Employee.findAll({
    attributes: ["employee_name"],
    where: {
      employee_name: {
        [Op.in]: employees.map((employee) => employee.employee_name.trim().replace(/\s+/g, " ")),
      },
    },
  });

  // Create a set of the existing
  const existingNamesSet = new Set(
    existingEmployeeNames.map((employee) => employee.employee_name.trim().replace(/\s+/g, " "))
  );

  // Mark each dataset as existing or not
  const newEmployees = employees.filter(
    (employee) => !existingNamesSet.has(employee.employee_name.trim().replace(/\s+/g, " "))
  );

  return newEmployees;
}

/**
 * Filters out items that already exist in the database from a given list of items.
 * @param {Array<Object>} items A list of items to check if their names exist in the database.
 * @returns {Array<Object>} The same list of items with only the items that do not exist in the database.
 * @description
 * This function takes a list of items and checks if their names exist in the database. It returns a new list of items with only the items that do not exist in the database.
 * @throws {Error} If the argument is not an array.
 */
async function filterNewItems(items) {
  // Check if the argument is an array
  if (!Array.isArray(items)) throw new Error("items is not an array");

  // check data if exist
  const existingItems = await dbSAPPS.Item.findAll({
    attributes: ["display_name", "code", "sku_code"],
    where: {
      [Op.or]: items.map((item) => ({
        display_name: item.item_name,
        code: item.item_code,
        sku_code: item.sku_code,
      })),
    },
  });

  // Create a set of the existing
  const existingItemsSet = new Set(existingItems.map((item) => `${item.display_name}.${item.code}.${item.sku_code}`));

  // Mark each dataset as existing or not
  const newItems = items.filter(
    (item) => !existingItemsSet.has(`${item.item_name}.${item.item_code}.${item.sku_code}`)
  );

  return newItems;
}

/**
 * Filters out departments that already exist in the database from a given list of departments.
 * @param {Array<Object>} departments A list of departments to check if their names exist in the database.
 * @returns {Array<Object>} The same list of departments with only the departments that do not exist in the database.
 * @description
 * This function takes a list of departments and checks if their names exist in the database. It returns a new list of departments with only the departments that do not exist in the database.
 * @throws {Error} If the argument is not an array.
 */
async function filterNewDepartmentNames(departments) {
  // Check if the argument is an array
  if (!Array.isArray(departments)) throw new Error("dataset is not an array");

  // check data if exist
  const existingDepartmentNames = await dbSAPPS.Department.findAll({
    attributes: ["display_name"],
    where: {
      display_name: {
        [Op.in]: departments.map((department) => department.department_name),
      },
    },
  });

  // Create a set of the existing
  const existingNamesSet = new Set(existingDepartmentNames.map((department) => department.display_name));

  // Mark each dataset as existing or not
  const newDepartments = departments.filter((department) => !existingNamesSet.has(department.department_name));

  return newDepartments;
}

/**
 * Filters out designation names that already exist in the database from a given list of designations.
 * @param {Array<Object>} designations A list of designations to check if their names exist in the database.
 * @returns {Array<Object>} The same list of designations with only the designations that do not exist in the database.
 * @description
 * This function takes a list of designations and checks if their names exist in the database. It returns a new list of designations with only the designations that do not exist in the database.
 * @throws {Error} If the argument is not an array.
 */
async function filterNewDesignationNames(designations) {
  // Check if the argument is an array
  if (!Array.isArray(designations)) throw new Error("dataset is not an array");

  // check data if exist
  const existingDesignationNames = await dbSAPPS.Designation.findAll({
    attributes: ["display_name"],
    where: {
      display_name: {
        [Op.in]: designations.map((designation) => designation.designation_name),
      },
    },
  });

  // Create a set of the existing
  const existingNamesSet = new Set(existingDesignationNames.map((designation) => designation.display_name));

  // Mark each dataset as existing or not
  const newDesignation = designations.filter((designation) => !existingNamesSet.has(designation.designation_name));

  return newDesignation;
}

/**
 * Creates additional information for a user if any of the parameters are set.
 * @param {Number} userId The ID of the user to create additional information for.
 * @param {Object} params The parameters to create additional information with.
 * @param {String} [params.gender] The gender of the user.
 * @param {Number} [params.age] The age of the user.
 * @param {String} [params.occupation] The occupation of the user.
 * @param {String} [params.last_education] The last education of the user.
 * @param {Sequelize.Transaction} transaction The transaction to perform the operation in.
 * @returns {Promise<void>}
 * @description
 * This function creates additional information for a user if any of the parameters are set. The parameters are gender, age, occupation, and last education. The function will not create additional information if none of the parameters are set.
 */
async function createAdditionalInfo(userId, params, transaction) {
  // validate
  if (params.gender || params.age || params.occupation || params.last_education) {
    const additionalInfoData = {
      user_id: userId,
      gender: params?.gender,
      age: params?.age,
      occupation: params?.occupation,
      last_education: params?.last_education,
    };

    // save
    await dbSAPPS.UserAdditionalInfo.create(additionalInfoData, {
      transaction,
    });
  }
}

/**
 * Updates additional information for a user if any of the parameters are set.
 * @param {Number} userId The ID of the user to update additional information for.
 * @param {Object} params The parameters to update additional information with.
 * @param {String} [params.gender] The gender of the user.
 * @param {Number} [params.age] The age of the user.
 * @param {String} [params.occupation] The occupation of the user.
 * @param {String} [params.last_education] The last education of the user.
 * @param {Sequelize.Transaction} transaction The transaction to perform the operation in.
 * @returns {Promise<void>}
 * @description
 * This function updates additional information for a user if any of the parameters are set. The parameters are gender, age, occupation, and last education. The function will not update additional information if none of the parameters are set.
 */
async function updateAdditionalInfo(userId, params, transaction) {
  // validate
  if (params.gender || params.age || params.occupation || params.last_education) {
    const additionalInfoData = {
      user_id: userId,
      gender: params?.gender,
      age: params?.age,
      occupation: params?.occupation,
      last_education: params?.last_education,
    };

    // save
    await dbSAPPS.UserAdditionalInfo.upsert(additionalInfoData, {
      transaction,
    });
  }
}

/**
 * Retrieves a list of delivery orders from the ERP system.
 * @param {Object} params The parameters to retrieve delivery orders with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving delivery orders from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving delivery orders from the ERP system.
 * @returns {Promise<Object>}
 * @description
 * This function retrieves a list of delivery orders from the ERP system. The function will return a promise that resolves to an object with the delivery orders and a success message. If the function fails, the promise will reject with an error message.
 */
async function getDeliveryOrder(params) {
  try {
    // get
    const deliveryOrder = await axios.get(`${params.api_url}api/resource/Delivery%20Note`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
      },
    });

    return {
      deliveryOrders: deliveryOrder.data,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of sales invoices from the ERP system.
 * @param {Object} params The parameters to retrieve sales invoices with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving sales invoices from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving sales invoices from the ERP system.
 * @returns {Promise<Object>} A promise that resolves to an object with the sales invoices and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of sales invoices from the ERP system. The function will return a promise that resolves to an object with the sales invoices and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getSalesInvoice(params) {
  try {
    // get
    const salesInvoice = await axios.get(`${params.api_url}api/resource/Sales%20Invoice`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
      },
    });

    return {
      salesInvoices: salesInvoice.data,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of principals from the ERP system.
 * @param {Object} params The parameters to retrieve principals with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving principals from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving principals from the ERP system.
 * @returns {Promise<Object>} A promise that resolves to an object with the principals and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of principals from the ERP system. The function will return a promise that resolves to an object with the principals and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getPrincipal(params) {
  try {
    // get
    const principal = await axios.get(`${params.api_url}api/resource/Supplier`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
      },
    });

    return {
      principals: principal.data,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of suppliers from the ERP system.
 * @param {Object} params The parameters to retrieve suppliers with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving suppliers from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving suppliers from the ERP system.
 * @returns {Promise<Object>} A promise that resolves to an object with the suppliers and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of suppliers from the ERP system. The function will return a promise that resolves to an object with the suppliers and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getSupplier(params) {
  try {
    // get
    const supplier = await axios.get(`${params.api_url}api/resource/Supplier`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
      },
    });

    return {
      suppliers: supplier.data,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of batchs from the ERP system.
 * @param {Object} params The parameters to retrieve batchs with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving batchs from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving batchs from the ERP system.
 * @returns {Promise<Object>} A promise that resolves to an object with the batchs and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of batchs from the ERP system. The function will return a promise that resolves to an object with the batchs and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getBatch(params) {
  try {
    // get
    const batch = await axios.get(`${params.api_url}api/resource/Batch`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
      },
    });

    const batchData = batch.data;

    return {
      batchs: batchData,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of material requests from the ERP system.
 * @param {Object} params The parameters to retrieve material requests with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving material requests from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving material requests from the ERP system.
 * @returns {Promise<Object>} A promise that resolves to an object with the material requests and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of material requests from the ERP system. The function will return a promise that resolves to an object with the material requests and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getMaterialRequests(params) {
  try {
    // get
    const materialRequests = await axios.get(`${params.api_url}api/resource/Material%20Request`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
        filters: '[["docstatus","=",1],["material_request_type","=","Purchase"]]',
      },
    });

    return {
      materialRequests: materialRequests.data,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of purchase orders based on the material request from the ERP system.
 * @param {Object} params The parameters to retrieve purchase orders with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving purchase orders from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving purchase orders from the ERP system.
 * @param {String} params.material_request The material request to retrieve purchase orders for.
 * @returns {Promise<Object>} A promise that resolves to an object with the purchase orders and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of purchase orders based on the material request from the ERP system.
 * The function will return a promise that resolves to an object with the purchase orders and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getPurchaseOrderByMaterialRequest(params) {
  try {
    // get
    const purchaseOrder = await axios.get(`${params.api_url}api/method/custom_app.get_purchase_order`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        material_request: params.material_request,
      },
    });

    return {
      purchaseOrder: purchaseOrder.data,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of purchase orders items based from the ERP system.
 * @param {Object} params The parameters to retrieve purchase orders with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving purchase orders from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving purchase orders from the ERP system.
 * @param {String} params.material_request The material request to retrieve purchase orders for.
 * @returns {Promise<Object>} A promise that resolves to an object with the purchase orders and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of purchase orders based on the material request from the ERP system.
 * The function will return a promise that resolves to an object with the purchase orders and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getPurchaseOrderItems(params) {
  try {
    // get
    const purchaseOrder = await axios.get(`${params.api_url}api/method/custom_app.get_purchase_order_items`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        name: params.purchase_order,
      },
    });

    return {
      purchaseOrder: purchaseOrder.data,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of purchase orders from the ERP system.
 * @param {Object} params The parameters to retrieve purchase orders with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving purchase orders from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving purchase orders from the ERP system.
 * @returns {Promise<Object>} A promise that resolves to an object with the purchase orders and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of purchase orders from the ERP system. The function will return a promise that resolves to an object with the purchase orders and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getPurchaseOrders(params) {
  try {
    // get
    const purchaseOrders = await axios.get(`${params.api_url}api/resource/Purchase%20Order`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
      },
    });

    return {
      purchaseOrders: purchaseOrders.data,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of purchase receipts by purchase order from the ERP system.
 * @param {Object} params The parameters to retrieve purchase receipts with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving purchase receipts from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving purchase receipts from the ERP system.
 * @param {String} params.purchase_order The purchase order to retrieve purchase receipts for.
 * @returns {Promise<Object>} A promise that resolves to an object with the purchase receipts and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of purchase receipts by purchase order from the ERP system.
 * The function will return a promise that resolves to an object with the purchase receipts and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getPurchaseReceiptByPurchaseOrder(params) {
  try {
    // get
    const purchaseReceipt = await axios.get(`${params.api_url}api/method/custom_app.get_purchase_receipt`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        purchase_order: params.purchase_order,
      },
    });

    return {
      purchaseReceipt: purchaseReceipt.data,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of users from the ERP system.
 * @param {Object} params The parameters to retrieve users with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving users from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving users from the ERP system.
 * @param {String} [params.read_all_data] Whether to read all data or not. If true, all data will be read.
 * @returns {Promise<Object>} A promise that resolves to an object with the users and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of users from the ERP system. The function will return a promise that resolves to an object with the users and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getUsers(params) {
  try {
    // get
    const users = await axios.get(`${params.api_url}api/resource/User`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
        filters: '[["disabled","=",0]]',
      },
    });

    let userData = [];
    if (params.read_all_data === "true" || params.read_all_data === true)
      userData = await markExistingEmails(users.data.data);
    else userData = await filterNewEmails(users.data.data);

    // update data relation
    userData = userData.map((user) => {
      const { is_active, ...data } = user;
      return {
        enabled: is_active,
        ...data,
      };
    });

    return {
      users: userData,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of employees from the ERP system.
 * @param {Object} params The parameters to retrieve employees with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving employees from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving employees from the ERP system.
 * @param {String} [params.read_all_data] Whether to read all data or not. If true, all data will be read.
 * @returns {Promise<Object>} A promise that resolves to an object with the employees and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of employees from the ERP system. The function will return a promise that resolves to an object with the employees and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getEmployees(params) {
  try {
    // get
    const employees = await axios.get(`${params.api_url}api/resource/Employee`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
        filters: '[["disabled","=",0]]',
      },
    });

    let employeeData = [];
    if (params.read_all_data === "true" || params.read_all_data === true)
      employeeData = await markExistingEmployeeNames(employees.data.data);
    else employeeData = await filterNewEmployeeNames(employees.data.data);

    // update data relation
    employeeData = employeeData.map((employee) => {
      const { is_active, ...data } = employee;
      return {
        enabled: is_active,
        ...data,
      };
    });

    return {
      employees: employeeData,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of departments from the ERP system.
 * @param {Object} params The parameters to retrieve departments with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving departments from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving departments from the ERP system.
 * @param {String} [params.read_all_data] Whether to read all data or not. If true, all data will be read.
 * @returns {Promise<Object>} A promise that resolves to an object with the departments and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of departments from the ERP system. The function will return a promise that resolves to an object with the departments and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getDepartments(params) {
  try {
    // get
    const departments = await axios.get(`${params.api_url}api/resource/Department`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
        filters: '[["disabled","=",0]]',
      },
    });

    let departmentData = [];
    if (params.read_all_data === "true" || params.read_all_data === true)
      departmentData = await markExistingDepartmentNames(departments.data.data);
    else departmentData = await filterNewDepartmentNames(departments.data.data);

    return {
      departments: departmentData,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of designations from the ERP system.
 * @param {Object} params The parameters to retrieve designations with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving designations from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving designations from the ERP system.
 * @param {String} [params.read_all_data] Whether to read all data or not. If true, all data will be read.
 * @returns {Promise<Object>} A promise that resolves to an object with the designations and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of designations from the ERP system. The function will return a promise that resolves to an object with the designations and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getDesignations(params) {
  try {
    // get
    const designations = await axios.get(`${params.api_url}api/resource/Designation`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
        filters: '[["disabled","=",0]]',
      },
    });

    let designationData = [];
    if (params.read_all_data === "true" || params.read_all_data === true)
      designationData = await markExistingDesignationNames(designations.data.data);
    else designationData = await filterNewDesignationNames(designations.data.data);

    // update data relation
    designationData = designationData.map((designation) => {
      const { is_active, ...data } = designation;
      return {
        enabled: is_active,
        ...data,
      };
    });

    return {
      designations: designationData,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves a list of items from the ERP system.
 * @param {Object} params The parameters to retrieve items with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when retrieving items from the ERP system.
 * @param {String} params.api_secret The API secret to use when retrieving items from the ERP system.
 * @param {String} [params.read_all_data] Whether to read all data or not. If true, all data will be read.
 * @returns {Promise<Object>} A promise that resolves to an object with the items and a success message, or rejects with an error message.
 * @description
 * This function retrieves a list of items from the ERP system. The function will return a promise that resolves to an object with the items and a success message if the function succeeds, or rejects with an error message if the function fails.
 */
async function getItems(params) {
  try {
    // get
    const items = await axios.get(`${params.api_url}api/resource/Item`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
        filters: '[["disabled","=",0],["is_sales_item","=",1]]',
      },
    });

    const itemPrice = await axios.get(`${params.api_url}api/resource/Item%20Price`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
      },
    });

    let itemData = [];
    if (params.read_all_data === "true" || params.read_all_data === true)
      itemData = await markExistingItems(items.data.data);
    else itemData = await filterNewItems(items.data.data);

    const resItemPriceERP = itemPrice.data.data;

    // filter
    const itemPriceERP = resItemPriceERP.filter((item) => item.valid_upto === null);

    // update data relation
    const ItemPriceToItemERP = itemData.map((item) => {
      const priceData = itemPriceERP.find((priceItem) => `${priceItem.item_code}` === `${item.item_code}`);
      return {
        ...item,
        item_price: priceData ? priceData.price_list_rate : null,
        valid_from: priceData ? priceData.valid_from : null,
      };
    });

    return {
      items: ItemPriceToItemERP,
      success: true,
      message: "Items has been loaded!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load items!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Loads holiday groups and their holidays from the ERPNext API.
 * @param {Object} params - The parameters to pass to the API.
 * @param {string} params.api_url - The URL of the ERPNext API.
 * @param {string} params.api_key - The API key to use for authentication.
 * @param {string} params.api_secret - The API secret to use for authentication.
 * @param {string} [params.read_all_data=true] - If set to true, will load all holiday groups and their holidays, otherwise it will only load the holiday groups that do not exist in the database.
 * @returns {Promise<Object>} - A promise that resolves to an object with two properties: `holidayGroups` and `success`. `holidayGroups` is an array of objects with properties `name`, `description`, `holidays`, `exists_in_database`. `success` is a boolean indicating whether the operation was successful. If the operation fails, `success` will be false and the `message` property will contain the error message.
 */
async function getHolidayGroups(params) {
  try {
    // get
    const holidayGroupsResponse = await axios.get(`${params.api_url}api/resource/Holiday%20List`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        order_by: "modified DESC",
        debug: "true",
      },
    });

    const holidayGroupsData = holidayGroupsResponse.data.data;

    let holidayGroups = [];
    if (params.read_all_data === "true" || params.read_all_data === true)
      holidayGroups = await markExistingHolidayGroups(holidayGroupsData);
    else holidayGroups = await filterNewHolidayGroups(holidayGroupsData);

    // Prepare an array to hold the holiday groups with their holidays
    const holidayGroupWithHolidays = await Promise.all(
      holidayGroups.map(async (group) => {
        // Fetch holidays for each holiday group
        const holidaysResponse = await axios.get(
          `${params.api_url}api/resource/Holiday%20List/${encodeURIComponent(group.name)}`,
          {
            headers: {
              Authorization: `token ${params.api_key}:${params.api_secret}`,
              Accept: "application/json",
            },
            params: {
              fields: '["*"]',
              order_by: "modified DESC",
              debug: "true",
            },
          }
        );

        // Return the holiday group with its holidays
        return {
          ...group,
          holidays: holidaysResponse.data.data.holidays || [],
        };
      })
    );

    return {
      holidayGroups: holidayGroupWithHolidays,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Get all accounts from ERP system.
 * @param {Object} params The parameters to get the accounts with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when getting the accounts.
 * @param {String} params.api_secret The API secret to use when getting the accounts.
 * @param {String} [params.read_all_data=false] A flag to indicate whether to read all data or not.
 * @returns {Promise<Object>} A promise that resolves to an object with the accounts and a success message if the accounts are loaded successfully, or rejects with an error message if the accounts are not loaded successfully.
 * @description
 * This function gets all accounts from the ERP system. The function will return a promise that resolves to an object with the accounts and a success message if the accounts are loaded successfully, or rejects with an error message if the accounts are not loaded successfully.
 */
async function getAccounts(params) {
  try {
    // get
    const accounts = await axios.get(`${params.api_url}api/resource/Account`, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
        Accept: "application/json",
      },
      params: {
        fields: '["*"]',
        limit_start: "0",
        limit_page_length: "0",
        order_by: "modified DESC",
        debug: "true",
        filters: '[["disabled","=",0]]',
      },
    });

    let accountData = [];
    if (params.read_all_data === "true" || params.read_all_data === true)
      accountData = await markExistingAccountNames(accounts.data.data);
    else accountData = await filterNewAccountNames(accounts.data.data);

    // update data relation
    accountData = accountData.map((account) => {
      const { is_active, ...data } = account;
      return {
        enabled: is_active,
        ...data,
      };
    });

    return {
      accounts: accountData,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Checks the status of the ERP system API.
 * @param {Object} params The parameters to check the status of the ERP system API with.
 * @param {String} params.api_url The base URL of the ERP system API.
 * @param {String} params.api_key The API key to use when checking the status of the ERP system API.
 * @param {String} params.api_secret The API secret to use when checking the status of the ERP system API.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message if the ERP system API is online, or rejects with an error message if the ERP system API is offline.
 * @description
 * This function checks the status of the ERP system API. The function will return a promise that resolves to an object with a success message if the ERP system API is online, or rejects with an error message if the ERP system API is offline.
 */
async function getStatus(params) {
  try {
    const apiUrl = `${
      params.api_url ? params.api_url : "https://erp.sapharma.co.id/"
    }api/method/frappe.auth.get_logged_user`;
    await axios.get(apiUrl, {
      headers: {
        Authorization: `token ${params.api_key}:${params.api_secret}`,
        "Content-Type": "application/json",
      },
    });

    return {
      success: true,
      message: "ERP API is online!",
    };
  } catch (error) {
    const errorMessage = error.message || "ERP API is offline!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Imports a user from the ERP system.
 * @param {Object} params The parameters to import a user with.
 * @param {String} params.email The email of the user to import.
 * @param {String} [params.full_name] The full name of the user to import.
 * @param {String} [params.mobile_no] The mobile number of the user to import.
 * @param {String} [params.username] The username of the user to import.
 * @param {String} [params.api_key] The API key of the user to import.
 * @param {String} [params.api_secret] The API secret of the user to import.
 * @param {Boolean} [params.enabled] Whether the user is enabled or not.
 * @param {String} [params.import_from] The source of the user to import from.
 * @param {String} [params.creation] The creation date of the user to import.
 * @param {String} [params.modified] The modified date of the user to import.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message if the user is imported successfully, or rejects with an error message if the user is not imported successfully.
 * @description
 * This function imports a user from the ERP system. The function will return a promise that resolves to an object with a success message if the user is imported successfully, or rejects with an error message if the user is not imported successfully.
 */
async function importUser(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validating existing
    const trimmedEmail = params.email.trim().toLowerCase();
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

    if (!emailRegex.test(trimmedEmail))
      return {
        success: false,
        message: `Invalid email format: "${trimmedEmail}".`,
      };

    const existingUser = await dbSAPPS.User.findOne({
      where: { email: trimmedEmail },
    });

    // update
    if (existingUser) {
      existingUser.idx = params.email || existingUser.idx;
      existingUser.import_from = "ERP";

      // update
      await existingUser.save({ transaction });

      // update additional info
      await updateAdditionalInfo(existingUser.id, params, transaction);

      await transaction.commit();
      return {
        success: true,
        message: `"${trimmedEmail}" has been updated!`,
      };
    }

    params.password = "Sap1234!#";
    params.hash = await bcrypt.hash(params.password, 10);
    params.secret = params.password;

    params.username = params.username ? params.username : params.full_name.replace(/\s+/g, "").toLowerCase();

    // create
    const newParams = {
      creation: params?.creation || new Date(),
      modified: new Date(),
      modified_by: 1,
      owner: 1,
      enabled: params?.enabled !== undefined ? params.enabled : true,
      email: trimmedEmail,
      phone_number: params?.mobile_no || null,
      hash: params?.hash,
      secret: params?.secret,
      full_name: params?.full_name,
      username: params?.username,
      idx: params?.idx || null,
      import_from: "ERP",
      api_key: params?.api_key || null,
      api_secret: params?.api_secret || null,
    };

    // save
    const user = await dbSAPPS.User.create(newParams, { transaction });

    // save additional info
    await createAdditionalInfo(user.id, params, transaction);

    await transaction.commit();
    return {
      success: true,
      message: existingUser ? "Updated successfully!" : "Imported successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Imports a user from the ERP system.
 * @param {Object} params The parameters to create a user with.
 * @param {String} params.email The email of the user.
 * @param {String} [params.full_name] The full name of the user.
 * @param {String} [params.username] The username of the user. If not provided, the full_name will be used.
 * @param {String} [params.mobile_no] The mobile number of the user.
 * @param {String} [params.api_key] The API key of the user.
 * @param {String} [params.api_secret] The API secret of the user.
 * @param {String} [params.idx] The idx of the user.
 * @returns {Promise<Object>} A promise that resolves to an object with the user and a success message, or rejects with an error message.
 * @description
 * This function imports a user from the ERP system. If a user with the given email already exists, the function will update the user. If not, the function will create a new user.
 */
async function importUsers(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validating existing
    const trimmedEmail = params.email.trim().toLowerCase();
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

    if (!emailRegex.test(trimmedEmail)) {
      return {
        success: false,
        message: `Invalid email format: "${trimmedEmail}".`,
      };
    }

    const existingUser = await dbSAPPS.User.findOne({
      where: { email: trimmedEmail },
    });

    // update
    if (existingUser) {
      existingUser.idx = params.email || existingUser.idx;
      existingUser.import_from = "ERP";

      // save
      await existingUser.save({ transaction });

      // save additional info
      await updateAdditionalInfo(existingUser.id, params, transaction);

      await transaction.commit();
      return {
        success: true,
        message: `"${trimmedEmail}" has been updated!`,
      };
    }

    params.password = "Sap1234!#";
    params.hash = await bcrypt.hash(params.password, 10);
    params.secret = params.password;
    params.username = params.username ? params.username : params.full_name.replace(/\s+/g, "").toLowerCase();

    // create
    const newParams = {
      creation: params?.creation || new Date(),
      modified: new Date(),
      modified_by: 1,
      owner: 1,
      enabled: params?.enabled !== undefined ? params.enabled : true,
      email: trimmedEmail,
      phone_number: params?.mobile_no || null,
      hash: params?.hash,
      secret: params?.secret,
      full_name: params?.full_name,
      username: params?.username,
      idx: params?.idx || null,
      import_from: "ERP",
      api_key: params?.api_key || null,
      api_secret: params?.api_secret || null,
    };

    // save
    const user = await dbSAPPS.User.create(newParams, { transaction });

    // save additional info
    await createAdditionalInfo(user.id, params, transaction);

    await transaction.commit();
    return {
      success: true,
      message: existingUser ? "Updated successfully!" : "Imported successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Imports all users from the ERP system.
 * @param {Object} params The parameters to import all users with.
 * @param {Object[]} params.params The parameters for each user to import.
 * @param {String} params.params.email The email of the user to import.
 * @param {String} [params.params.full_name] The full name of the user to import.
 * @param {String} [params.params.mobile_no] The mobile number of the user to import.
 * @param {String} [params.params.username] The username of the user to import.
 * @param {String} [params.params.api_key] The API key of the user to import.
 * @param {String} [params.params.api_secret] The API secret of the user to import.
 * @param {Boolean} [params.params.enabled] Whether the user is enabled or not.
 * @param {String} [params.params.import_from] The source of the user to import from.
 * @param {String} [params.params.creation] The creation date of the user to import.
 * @param {String} [params.params.modified] The modified date of the user to import.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message if the users are imported successfully, or rejects with an error message if the users are not imported successfully.
 * @description
 * This function imports all users from the ERP system. The function will return a promise that resolves to an object with a success message if the users are imported successfully, or rejects with an error message if the users are not imported successfully.
 */
async function importAllUser(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validating existing
    let newParamsArray = [];
    let updateParamsArray = [];

    // validate parameters
    if (!Array.isArray(params) || params.length === 0)
      return {
        success: false,
        message: "No parameters provided for import.",
      };

    // validate existing
    const existingUsers = await dbSAPPS.User.findAll({
      where: {
        email: params.map((param) => param.email.trim().toLowerCase()),
      },
      attributes: ["email", "id", "idx", "import_from"],
    });

    // existing names
    const existingEmailSet = new Set(existingUsers.map((user) => user.email.trim().toLowerCase()));

    // existing map
    const existingUserMap = new Map(existingUsers.map((user) => [user.email.trim().toLowerCase(), user]));

    // hash
    const passwordHashPromises = params.map(async (param) => {
      const password = "Sap1234!#";
      const hash = await bcrypt.hash(password, 10);
      return { ...param, hash, password };
    });

    // hash
    const hashedParams = await Promise.all(passwordHashPromises);

    // loop
    for (const param of hashedParams) {
      const trimmedEmail = param.email.trim().toLowerCase();

      if (!trimmedEmail || !param.full_name) {
        console.error(`Missing required fields for: ${JSON.stringify(param)}`);
        continue;
      }

      if (existingEmailSet.has(trimmedEmail)) {
        console.log(`Email "${trimmedEmail}" is already taken. Preparing to update idx and import_from...`);

        const existingUser = existingUserMap.get(trimmedEmail);
        updateParamsArray.push({
          id: existingUser.id,
          idx: trimmedEmail,
          import_from: "ERP",
        });
        continue;
      }

      const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
      if (!emailRegex.test(trimmedEmail)) {
        console.error(`Invalid email format for: ${trimmedEmail}. Skipping...`);
        continue;
      }

      param.username = param.username || param.full_name.replace(/\s+/g, "").toLowerCase();

      // create
      newParamsArray.push({
        creation: param.creation || new Date(),
        modified: new Date(),
        modified_by: 7,
        owner: 7,
        enabled: param.enabled !== undefined ? param.enabled : true,
        email: trimmedEmail,
        phone_number: param.mobile_no || null,
        hash: param.hash,
        secret: param.password || null,
        full_name: param.full_name,
        username: param.username,
        idx: trimmedEmail || null,
        import_from: "ERP",
        api_key: param.api_key || null,
        api_secret: param.api_secret || null,
      });
    }

    // save
    if (newParamsArray.length > 0) {
      const result = await dbSAPPS.User.bulkCreate(newParamsArray, {
        updateOnDuplicate: [
          "hash",
          "secret",
          "modified",
          "modified_by",
          "owner",
          "enabled",
          "phone_number",
          "full_name",
          "username",
          "idx",
          "import_from",
          "api_key",
          "api_secret",
        ],
        returning: true,
        transaction,
      });
      console.log(`${result.length} new records have been inserted.`);

      // save additional info
      for (const newUser of result) {
        await createAdditionalInfo(newUser.id, newUser, transaction);
      }
    }

    // update
    if (updateParamsArray.length > 0) {
      const updatePromises = updateParamsArray.map((updateParam) => {
        return dbSAPPS.User.update(
          {
            idx: updateParam.idx,
            import_from: updateParam.import_from,
          },
          {
            where: { id: updateParam.id },
            transaction,
          }
        );
      });

      await Promise.all(updatePromises);

      console.log(`${updateParamsArray.length} existing records have been updated.`);

      // save additional info
      for (const updateParam of updateParamsArray) {
        await updateAdditionalInfo(updateParam.id, updateParam, transaction);
      }
    }

    await transaction.commit();
    return {
      newdata: newParamsArray,
      updatedata: updateParamsArray,
      success: true,
      message:
        newParamsArray.length > 0
          ? `${newParamsArray.length} new records have been imported!`
          : "No new records to import.",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to save, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Imports an employee from the ERP system.
 * @param {Object} params The parameters to import a user with.
 * @param {String} params.employee_name The employee name of the user to import.
 * @param {String} [params.employee_number] The employee number of the user to import.
 * @param {String} [params.cell_number] The cell phone number of the user to import.
 * @param {String} [params.date_of_joining] The date of joining of the user to import.
 * @param {String} [params.date_of_birth] The date of birth of the user to import.
 * @param {String} [params.id_number] The ID number of the user to import.
 * @param {String} [params.religion] The religion of the user to import.
 * @param {String} [params.status] The status of the user to import.
 * @param {Boolean} [params.enabled] Whether the user is enabled or not.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message if the user is imported successfully, or rejects with an error message if the user is not imported successfully.
 * @description
 * This function imports an employee from the ERP system. The function will return a promise that resolves to an object with a success message if the user is imported successfully, or rejects with an error message if the user is not imported successfully.
 */
async function importEmployee(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // convert to lowercase
    const trimmedName = params.employee_name.trim().replace(/\s+/g, " ");

    // validate existing
    const existingEmployee = await dbSAPPS.Employee.findOne({
      where: { employee_name: trimmedName },
    });

    // update
    if (existingEmployee) {
      existingEmployee.idx = params.employee_name || existingEmployee.idx;
      existingEmployee.import_from = "ERP";
      existingEmployee.employee_number = params?.employee_number;
      existingEmployee.mobile_no = params?.cell_number || null;
      existingEmployee.idx = params?.employee_name || null;
      existingEmployee.date_of_joining = params?.date_of_joining || null;
      existingEmployee.date_of_birth = params?.date_of_birth || null;
      existingEmployee.id_number = params?.id_number || null;
      existingEmployee.religion = params?.religion || null;
      existingEmployee.status = params?.status || null;
      existingEmployee.enabled = params?.status === "Active" ? true : false || null;

      // save
      await existingEmployee.save({ transaction });

      await transaction.commit();
      return {
        success: true,
        message: `"${trimmedName}" has been updated!`,
      };
    }

    // create
    const newParams = {
      creation: params?.creation || new Date(),
      modified: new Date(),
      modified_by: 1,
      owner: 1,
      enabled: params?.enabled !== undefined ? params.enabled : true,
      employee_name: trimmedName,
      employee_number: params?.employee_number,
      mobile_no: params?.cell_number || null,
      idx: params?.employee_name || null,
      import_from: "ERP",
      date_of_joining: params?.date_of_joining || null,
      date_of_birth: params?.date_of_birth || null,
      id_number: params?.id_number || null,
      religion: params?.religion || null,
      status: params?.status || null,
      enabled: params?.status === "Active" ? true : false || null,
    };

    await dbSAPPS.Employee.create(newParams, { transaction });
    await transaction.commit();

    return {
      success: true,
      message: existingDesignation ? "Updated successfully!" : "Imported successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Imports an employee from the ERP system.
 * @param {Object} params The parameters to import a user with.
 * @param {String} params.employee_name The name of the employee to import.
 * @param {String} [params.employee_number] The employee number of the employee to import.
 * @param {String} [params.cell_number] The mobile number of the employee to import.
 * @param {String} [params.date_of_joining] The date of joining of the employee to import.
 * @param {String} [params.date_of_birth] The date of birth of the employee to import.
 * @param {String} [params.id_number] The ID number of the employee to import.
 * @param {String} [params.religion] The religion of the employee to import.
 * @param {String} [params.status] The status of the employee to import.
 * @param {String} [params.enabled] Whether the employee is enabled or not.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message if the employee is imported successfully, or rejects with an error message if the employee is not imported successfully.
 * @description
 * This function imports an employee from the ERP system. The function will return a promise that resolves to an object with a success message if the employee is imported successfully, or rejects with an error message if the employee is not imported successfully.
 */
async function importEmployees(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // convert to lowercase
    const trimmedName = params.employee_name.trim().replace(/\s+/g, " ");

    // validate existing
    const existingEmployee = await dbSAPPS.Employee.findOne({
      where: { employee_name: trimmedName },
    });

    // update
    if (existingEmployee) {
      existingEmployee.idx = params.employee_name || existingEmployee.idx;
      existingEmployee.import_from = "ERP";
      existingEmployee.employee_number = params?.employee_number;
      existingEmployee.mobile_no = params?.cell_number || null;
      existingEmployee.idx = params?.employee_name || null;
      existingEmployee.date_of_joining = params?.date_of_joining || null;
      existingEmployee.date_of_birth = params?.date_of_birth || null;
      existingEmployee.id_number = params?.id_number || null;
      existingEmployee.religion = params?.religion || null;
      existingEmployee.status = params?.status || null;
      existingEmployee.enabled = params?.status === "Active" ? true : false || null;

      // save
      await existingEmployee.save({ transaction });

      await transaction.commit();
      return {
        success: true,
        message: `"${trimmedName}" has been updated!`,
      };
    }

    // create
    const newParams = {
      creation: params?.creation || new Date(),
      modified: new Date(),
      modified_by: 1,
      owner: 1,
      enabled: params?.enabled !== undefined ? params.enabled : true,
      employee_name: trimmedName,
      employee_number: params?.employee_number,
      mobile_no: params?.cell_number || null,
      idx: params?.employee_name || null,
      import_from: "ERP",
      date_of_joining: params?.date_of_joining || null,
      date_of_birth: params?.date_of_birth || null,
      id_number: params?.id_number || null,
      religion: params?.religion || null,
      status: params?.status || null,
      enabled: params?.status === "Active" ? true : false,
    };

    // save
    await dbSAPPS.Employee.create(newParams, { transaction });

    await transaction.commit();
    return {
      success: true,
      message: existingDesignation ? "Updated successfully!" : "Imported successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Import multiple employees from the ERP system.
 * @param {Object} params The parameters to import with.
 * @param {Array<Object>} params.params An array of objects with the following properties: employee_name, employee_number, cell_number, date_of_joining, date_of_birth, id_number, religion, status, enabled.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message and new data if the import is successful, or rejects with an error message if the import fails.
 * @description
 * This function imports multiple employees from the ERP system. The function will return a promise that resolves to an object with a success message and new data if the import is successful, or rejects with an error message if the import fails.
 */
async function importAllEmployee(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validate existing
    let newParamsArray = [];
    let updateParamsArray = [];

    // validate parameters
    if (!Array.isArray(params) || params.length === 0)
      return {
        success: false,
        message: "No parameters provided for import.",
      };

    // validate existing
    const existingEmployees = await dbSAPPS.Employee.findAll({
      where: {
        employee_name: params.map((param) => param.employee_name.trim().replace(/\s+/g, " ")),
      },
      attributes: ["employee_name", "id", "idx", "import_from"],
    });

    // existing names
    const existingEmployeeNameSet = new Set(
      existingEmployees.map((employee) => employee.employee_name.trim().replace(/\s+/g, " "))
    );

    // existing map
    const existingEmployeeMap = new Map(
      existingEmployees.map((employee) => [employee.employee_name.trim().replace(/\s+/g, " "), employee])
    );

    // loop
    for (const param of params) {
      const trimmedName = param.employee_name.trim().replace(/\s+/g, " ");

      if (!param.employee_name) {
        console.error(`Missing required fields for: ${JSON.stringify(param)}`);
        continue;
      }

      if (existingEmployeeNameSet.has(trimmedName)) {
        console.log(`"${trimmedName}" is already taken. Preparing to update idx and import_from...`);

        const existingEmployee = existingEmployeeMap.get(trimmedName);

        // update
        updateParamsArray.push({
          id: existingEmployee.id,
          idx: param.employee_name.trim().replace(/\s+/g, " "),
          import_from: "ERP",
          employee_number: param.employee_number,
          religion: param.religion,
          status: param.status,
          enabled: param.status === "Active" ? true : false,
        });
        continue;
      }

      // create
      newParamsArray.push({
        creation: param.creation || new Date(),
        modified: new Date(),
        modified_by: 1,
        owner: 1,
        enabled: param.enabled !== undefined ? params.enabled : true,
        employee_name: trimmedName,
        employee_number: param.employee_number,
        mobile_no: param.cell_number || null,
        idx: param.employee_name || null,
        import_from: "ERP",
        date_of_joining: param.date_of_joining || null,
        date_of_birth: param.date_of_birth || null,
        id_number: param.id_number || null,
        religion: param.religion || null,
        status: param.status || null,
        enabled: param.status === "Active" ? true : false,
      });
    }

    // create
    if (newParamsArray.length > 0) {
      const result = await dbSAPPS.Employee.bulkCreate(newParamsArray, {
        updateOnDuplicate: [
          "modified",
          "modified_by",
          "owner",
          "enabled",
          "mobile_no",
          "employee_name",
          "employee_number",
          "idx",
          "import_from",
          "date_of_joining",
          "date_of_birth",
          "id_number",
          "religion",
          "status",
          "enabled",
        ],
        returning: true,
        transaction,
      });
      console.log(`${result.length} new records have been inserted.`);
    }

    // update
    if (updateParamsArray.length > 0) {
      const updatePromises = updateParamsArray.map((updateParam) => {
        return dbSAPPS.Employee.update(
          {
            idx: updateParam.idx,
            import_from: updateParam.import_from,
            status: updateParam.status,
            employee_number: updateParam.employee_number,
            religion: updateParam.religion,
            status: updateParam.status,
            enabled: updateParam.enabled,
          },
          {
            where: { id: updateParam.id },
            transaction,
          }
        );
      });
      await Promise.all(updatePromises);
      console.log(`${updateParamsArray.length} records have been updated with new idx and import_from.`);
    }

    await transaction.commit();
    return {
      newdata: newParamsArray,
      updatedata: updateParamsArray,
      success: true,
      message:
        newParamsArray.length > 0
          ? `${newParamsArray.length} new records have been imported!`
          : "No new records to import.",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to save, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Imports an item from the ERP system.
 * @param {Object} params The parameters to import an item with.
 * @param {String} params.item_code The code of the item to import.
 * @param {String} params.item_name The name of the item to import.
 * @param {String} [params.sku_code] The sku code of the item to import.
 * @param {Number} [params.item_price] The price of the item to import.
 * @param {String} [params.stock_uom] The stock uom of the item to import.
 * @param {String} [params.alternative_sku_code] The alternative sku code of the item to import.
 * @param {String} [params.image] The image of the item to import.
 * @param {String} [params.standard_rate] The standard rate of the item to import.
 * @param {String} [params.brand] The brand of the item to import.
 * @param {String} [params.valid_from] The valid from date of the item to import.
 * @param {String} [params.api_key] The API key of the item to import.
 * @param {String} [params.api_secret] The API secret of the item to import.
 * @param {String} [params.idx] The idx of the item to import.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message if the item is imported successfully, or rejects with an error message if the item is not imported successfully.
 * @description
 * This function imports an item from the ERP system. If an item with the given code and sku code already exists, the function will update the item. If not, the function will create a new item.
 */
async function importItem(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Validate input parameters
    if (!params || !params.params) {
      throw createBadRequestError("Invalid parameters: 'params' object is required");
    }

    const param = params.params;
    const errors = {};

    // Validate required fields for import
    if (!param.item_code && !param.sku_code) {
      errors.item_code = "Either item_code or sku_code is required";
      errors.sku_code = "Either item_code or sku_code is required";
    }

    if (!param.item_name) {
      errors.item_name = "Item name is required";
    }

    // Validate item_code format if provided
    if (param.item_code && typeof param.item_code !== 'string') {
      errors.item_code = "Item code must be a string";
    } else if (param.item_code && param.item_code.length > 30) {
      errors.item_code = "Item code must not exceed 30 characters";
    }

    // Validate sku_code format if provided
    if (param.sku_code && typeof param.sku_code !== 'string') {
      errors.sku_code = "SKU code must be a string";
    }

    // Validate price if provided
    if (param.item_price !== undefined && param.item_price !== null) {
      const price = Number(param.item_price);
      if (isNaN(price)) {
        errors.item_price = "Item price must be a valid number";
      } else if (price < 0) {
        errors.item_price = "Item price cannot be negative";
      }
    }

    // Validate creation date if provided
    if (param.creation) {
      const creationDate = new Date(param.creation);
      if (isNaN(creationDate.getTime())) {
        errors.creation = "Invalid creation date format";
      }
    }

    // Validate valid_from date if provided
    if (param.valid_from) {
      const validFromDate = new Date(param.valid_from);
      if (isNaN(validFromDate.getTime())) {
        errors.valid_from = "Invalid valid_from date format";
      }
    }

    // Validate modified_by if provided
    if (param.modified_by !== undefined && param.modified_by !== null) {
      const modifiedBy = Number(param.modified_by);
      if (isNaN(modifiedBy) || modifiedBy <= 0) {
        errors.modified_by = "Modified by must be a positive integer";
      }
    }

    // Validate owner if provided
    if (param.owner !== undefined && param.owner !== null) {
      const owner = Number(param.owner);
      if (isNaN(owner) || owner <= 0) {
        errors.owner = "Owner must be a positive integer";
      }
    }

    // Validate display_name length if provided
    if (param.item_name && param.item_name.length > 255) {
      errors.item_name = "Item name must not exceed 255 characters";
    }

    // Throw validation error if any errors found
    if (Object.keys(errors).length > 0) {
      await transaction.rollback();
      throw createValidationError(errors, "Item import validation failed");
    }

    // Check for existing item
    const existingItem = await dbSAPPS.Item.findOne({
      where: {
        code: param.item_code,
        sku_code: param.sku_code,
      },
      transaction,
    });

    if (existingItem) {
      // Update existing item
      existingItem.import_from = "ERP";
      existingItem.display_name = existingItem.display_name ?? param.item_name;
      existingItem.code = existingItem.code ?? param.item_code;
      existingItem.sku_code = existingItem.sku_code ?? param.sku_code;
      
      // Update price only if provided and valid
      if (param.item_price !== undefined && param.item_price !== null) {
        const price = Number(param.item_price);
        if (!isNaN(price) && price >= 0) {
          existingItem.price = existingItem.price ?? price;
        }
      }
      
      existingItem.modified = new Date();
      existingItem.modified_by = param.modified_by || 1;

      try {
        await existingItem.save({ transaction });
        await transaction.commit();

        return { success: true, message: "Item updated successfully", data: existingItem };
      } catch (error) {
        await transaction.rollback();
        // Sequelize validation errors will be automatically formatted by error handler
        throw error;
      }
    }

    // Create new item
    const itemData = {
      creation: param.creation ? new Date(param.creation) : new Date(),
      modified: new Date(),
      modified_by: param.modified_by || 1,
      owner: param.owner || 1,
      enabled: true,
      display_name: param.item_name,
      code: param.item_code || null,
      sku_code: param.sku_code || null,
      price: param.item_price !== undefined && param.item_price !== null 
        ? Number(param.item_price) 
        : null,
      stock_uom: param.stock_uom || null,
      alternative_sku_code: param.alternative_sku_code || null,
      image: param.image || null,
      brand: param.brand || null,
      valid_from: param.valid_from ? new Date(param.valid_from) : null,
      idx: param.idx || null,
      import_from: "ERP",
    };

    try {
      const newItem = await dbSAPPS.Item.create(itemData, { transaction });
      await transaction.commit();

      return { 
        success: true, 
        message: "Item imported successfully", 
        data: newItem 
      };
    } catch (error) {
      await transaction.rollback();
      // Sequelize validation errors will be automatically formatted by error handler
      // This includes:
      // - SequelizeValidationError (field validation)
      // - SequelizeUniqueConstraintError (duplicate display_name, etc.)
      // - SequelizeForeignKeyConstraintError (invalid foreign keys)
      throw error;
    }
  } catch (error) {
    // Ensure transaction is rolled back if not already done
    if (transaction && !transaction.finished) {
      await transaction.rollback();
    }
    
    // Re-throw error to be handled by error handler middleware
    // Error handler will automatically format the error with:
    // - Detailed error message
    // - Field-specific validation errors
    // - Stack trace (in development)
    // - Request context
    // - File location
    throw error;
  }
}

/**
 * Imports a list of items from the ERP system.
 * @param {Object} params The parameters to import items with.
 * @param {Object[]} params.params An array of objects containing the item's data.
 * @param {String} params.params.item_name The item name of the item to import.
 * @param {String} params.params.item_code The item code of the item to import.
 * @param {String} [params.params.sku_code] The sku code of the item to import.
 * @param {Number} [params.params.item_price] The price of the item to import.
 * @param {String} [params.params.stock_uom] The stock uom of the item to import.
 * @param {String} [params.params.alternative_sku_code] The alternative sku code of the item to import.
 * @param {String} [params.params.image] The image of the item to import.
 * @param {Number} [params.params.standard_rate] The standard rate of the item to import.
 * @param {String} [params.params.brand] The brand of the item to import.
 * @param {Date} [params.params.valid_from] The valid from of the item to import.
 * @returns {Promise<Object>} A promise that resolves to an object with the item and a success message, or rejects with an error message.
 * @description
 * This function imports a list of items from the ERP system. If an item with the same code already exists, the function will update the item. If not, the function will create a new item.
 */
async function importItems(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    const param = params.params;
    console.log("PARAM", param);
    console.log("PARAMS", params);

    // validate existing
    const existingItem = await dbSAPPS.Item.findOne({
      where: {
        code: param?.item_code || null,
        sku_code: param?.sku_code || null,
      },
    });

    // update
    if (existingItem) {
      existingItem.idx = existingItem.idx;
      existingItem.import_from = "ERP";
      existingItem.code = existingItem?.item_code || param?.item_code;
      existingItem.sku_code = existingItem?.sku_code || param?.sku_code;
      existingItem.price = param?.item_price;

      // save
      await existingItem.save({ transaction });

      await transaction.commit();
      return {
        success: true,
        message: `"${param.item_name}" has been updated!`,
      };
    }

    const newParams = {
      creation: param?.creation || new Date(),
      modified: new Date(),
      modified_by: 1,
      owner: 1,
      enabled: true,

      display_name: param?.item_name || null,
      code: param?.item_code || null,
      sku_code: param?.sku_code || null,
      price: param?.item_price || null,
      stock_uom: param?.stock_uom || null,
      alternative_sku_code: param?.alternative_sku_code || null,
      image: param?.image || null,
      standard_rate: param?.standard_rate || null,
      brand: param?.brand || null,
      valid_from: param?.valid_from || null,
      idx: param?.idx || null,
      import_from: "ERP",
    };

    // save
    await dbSAPPS.Item.create(newParams, { transaction });

    await transaction.commit();
    return {
      success: true,
      message: existingItem ? "Updated successfully!" : "Imported successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Imports all items from the ERP system.
 * @param {Object} params The parameters to import all items with.
 * @param {Object[]} params.params The parameters for each item to import.
 * @param {String} params.params.item_name The name of the item to import.
 * @param {String} [params.params.item_code] The code of the item to import.
 * @param {String} [params.params.sku_code] The sku code of the item to import.
 * @param {Number} [params.params.item_price] The price of the item to import.
 * @param {String} [params.params.stock_uom] The stock uom of the item to import.
 * @param {String} [params.params.alternative_sku_code] The alternative sku code of the item to import.
 * @param {String} [params.params.image] The image of the item to import.
 * @param {String} [params.params.standard_rate] The standard rate of the item to import.
 * @param {String} [params.params.brand] The brand of the item to import.
 * @param {String} [params.params.valid_from] The valid from date of the item to import.
 * @param {Number} [params.params.idx] The idx of the item to import.
 * @param {String} [params.params.import_from] The source of the item to import from. If not provided, the item will not be imported.
 * @param {Boolean} [params.params.enabled] Whether the item is enabled or not. If not provided, the item will be enabled.
 * @returns {Promise<Object>} A promise that resolves to an object with the items and a success message, or rejects with an error message.
 * @description
 * This function imports all items from the ERP system. If an item with the given name and sku code already exists, the function will update the item. If not, the function will create a new item.
 */
async function importAllItem(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Validate input parameters
    if (!params || !params.params) {
      throw createBadRequestError("Invalid parameters: 'params' object is required");
    }

    const paramArray = params.params;

    // Validate parameters is array
    if (!Array.isArray(paramArray)) {
      throw createValidationError({
        params: "Parameters must be an array"
      }, "Invalid parameters format");
    }

    if (paramArray.length === 0) {
      throw createBadRequestError("No items provided for import. Array cannot be empty.");
    }

    // Validate each item in array
    const allItemErrors = {};
    paramArray.forEach((param, index) => {
      const itemPrefix = `items[${index}]`;

      // Validate required fields
      if (!param.item_name) {
        allItemErrors[`${itemPrefix}.item_name`] = "Item name is required";
      } else if (typeof param.item_name !== 'string') {
        allItemErrors[`${itemPrefix}.item_name`] = "Item name must be a string";
      } else if (param.item_name.length > 255) {
        allItemErrors[`${itemPrefix}.item_name`] = "Item name must not exceed 255 characters";
      }

      // Validate item_code format if provided
      if (param.item_code !== undefined && param.item_code !== null) {
        if (typeof param.item_code !== 'string') {
          allItemErrors[`${itemPrefix}.item_code`] = "Item code must be a string";
        } else if (param.item_code.length > 30) {
          allItemErrors[`${itemPrefix}.item_code`] = "Item code must not exceed 30 characters";
        }
      }

      // Validate sku_code format if provided
      if (param.sku_code !== undefined && param.sku_code !== null && typeof param.sku_code !== 'string') {
        allItemErrors[`${itemPrefix}.sku_code`] = "SKU code must be a string";
      }

      // Validate price if provided
      if (param.item_price !== undefined && param.item_price !== null) {
        const price = Number(param.item_price);
        if (isNaN(price)) {
          allItemErrors[`${itemPrefix}.item_price`] = "Item price must be a valid number";
        } else if (price < 0) {
          allItemErrors[`${itemPrefix}.item_price`] = "Item price cannot be negative";
        }
      }

      // Validate creation date if provided
      if (param.creation) {
        const creationDate = new Date(param.creation);
        if (isNaN(creationDate.getTime())) {
          allItemErrors[`${itemPrefix}.creation`] = "Invalid creation date format";
        }
      }

      // Validate valid_from date if provided
      if (param.valid_from) {
        const validFromDate = new Date(param.valid_from);
        if (isNaN(validFromDate.getTime())) {
          allItemErrors[`${itemPrefix}.valid_from`] = "Invalid valid_from date format";
        }
      }
    });

    // Throw validation error if any errors found
    if (Object.keys(allItemErrors).length > 0) {
      await transaction.rollback();
      throw createValidationError(allItemErrors, "Item import validation failed");
    }

    // Prepare arrays
    let newParamsArray = [];
    let updateParamsArray = [];

    // Get unique item names, codes, and sku_codes for efficient lookup
    const itemNames = paramArray.map((param) => param.item_name).filter(Boolean);
    const itemCodes = paramArray.map((param) => param.item_code).filter(Boolean);
    const skuCodes = paramArray.map((param) => param.sku_code).filter(Boolean);

    // Validate existing items using OR condition for efficient lookup
    const existingItems = await dbSAPPS.Item.findAll({
      where: {
        [Op.or]: [
          ...(itemNames.length > 0 ? [{ display_name: { [Op.in]: itemNames } }] : []),
          ...(itemCodes.length > 0 ? [{ code: { [Op.in]: itemCodes } }] : []),
          ...(skuCodes.length > 0 ? [{ sku_code: { [Op.in]: skuCodes } }] : []),
        ],
      },
      attributes: ["display_name", "code", "sku_code", "price", "id", "idx", "import_from", "stock_uom", "alternative_sku_code", "image", "standard_rate", "brand", "valid_from", "enabled"],
      transaction,
    });

    // Create lookup maps for efficient matching
    const existingItemNameMap = new Map();
    const existingItemCodeMap = new Map();
    const existingItemSkuMap = new Map();

    existingItems.forEach((item) => {
      if (item.display_name) {
        existingItemNameMap.set(item.display_name.toLowerCase(), item);
      }
      if (item.code) {
        existingItemCodeMap.set(item.code, item);
      }
      if (item.sku_code) {
        existingItemSkuMap.set(item.sku_code, item);
      }
    });

    // Process each item
    for (let i = 0; i < paramArray.length; i++) {
      const param = paramArray[i];
      const itemName = param.item_name;
      const itemCode = param.item_code;
      const skuCode = param.sku_code;

      // Find existing item by name, code, or sku_code
      let existingItem = null;
      if (itemName) {
        existingItem = existingItemNameMap.get(itemName.toLowerCase());
      }
      if (!existingItem && itemCode) {
        existingItem = existingItemCodeMap.get(itemCode);
      }
      if (!existingItem && skuCode) {
        existingItem = existingItemSkuMap.get(skuCode);
      }

      if (existingItem) {
        // Prepare update data
        const updateData = {
          id: existingItem.id,
          idx: param.idx || existingItem.idx || null,
          import_from: "ERP",
          price: param.item_price !== undefined && param.item_price !== null 
            ? Number(param.item_price) 
            : existingItem.price || null,
          stock_uom: param.stock_uom || existingItem.stock_uom || null,
          sku_code: param.sku_code || existingItem.sku_code || null,
          alternative_sku_code: param.alternative_sku_code || existingItem.alternative_sku_code || null,
          image: param.image || existingItem.image || null,
          standard_rate: param.standard_rate || existingItem.standard_rate || null,
          brand: param.brand || existingItem.brand || null,
          valid_from: param.valid_from ? new Date(param.valid_from) : existingItem.valid_from || null,
          enabled: param.status === "Active" ? true : param.enabled !== undefined ? param.enabled : existingItem.enabled !== undefined ? existingItem.enabled : true,
        };
        updateParamsArray.push(updateData);
        continue;
      }

      // Prepare new item data
      const newItemData = {
        creation: param.creation ? new Date(param.creation) : new Date(),
        modified: new Date(),
        modified_by: param.modified_by || 1,
        owner: param.owner || 1,
        enabled: param.enabled !== undefined ? param.enabled : true,
        display_name: param.item_name || null,
        code: param.item_code || null,
        sku_code: param.sku_code || null,
        price: param.item_price !== undefined && param.item_price !== null 
          ? Number(param.item_price) 
          : null,
        stock_uom: param.stock_uom || null,
        alternative_sku_code: param.alternative_sku_code || null,
        image: param.image || null,
        standard_rate: param.standard_rate || null,
        brand: param.brand || null,
        valid_from: param.valid_from ? new Date(param.valid_from) : null,
        idx: param.idx || null,
        import_from: "ERP",
      };
      newParamsArray.push(newItemData);
    }

    // Save new items
    let createdItems = [];
    if (newParamsArray.length > 0) {
      try {
        const result = await dbSAPPS.Item.bulkCreate(newParamsArray, {
          updateOnDuplicate: [
            "modified",
            "modified_by",
            "owner",
            "enabled",
            "display_name",
            "code",
            "sku_code",
            "price",
            "stock_uom",
            "alternative_sku_code",
            "image",
            "standard_rate",
            "brand",
            "valid_from",
            "idx",
            "import_from",
          ],
          returning: true,
          transaction,
        });
        createdItems = result;
      } catch (error) {
        await transaction.rollback();
        // Sequelize errors will be automatically formatted by error handler
        throw error;
      }
    }

    // Update existing items
    let updatedCount = 0;
    if (updateParamsArray.length > 0) {
      try {
        const updatePromises = updateParamsArray.map((updateParam) => {
          return dbSAPPS.Item.update(
            {
              idx: updateParam.idx,
              import_from: updateParam.import_from,
              price: updateParam.price,
              stock_uom: updateParam.stock_uom,
              sku_code: updateParam.sku_code,
              alternative_sku_code: updateParam.alternative_sku_code,
              image: updateParam.image,
              standard_rate: updateParam.standard_rate,
              brand: updateParam.brand,
              valid_from: updateParam.valid_from,
              enabled: updateParam.enabled,
              modified: new Date(),
              modified_by: updateParam.modified_by || 1,
            },
            {
              where: { id: updateParam.id },
              transaction,
            }
          );
        });
        const updateResults = await Promise.all(updatePromises);
        updatedCount = updateResults.reduce((sum, result) => sum + result[0], 0);
      } catch (error) {
        await transaction.rollback();
        // Sequelize errors will be automatically formatted by error handler
        throw error;
      }
    }

    await transaction.commit();

    // Build success message
    const messages = [];
    if (createdItems.length > 0) {
      messages.push(`${createdItems.length} new record(s) imported`);
    }
    if (updatedCount > 0) {
      messages.push(`${updatedCount} record(s) updated`);
    }
    if (messages.length === 0) {
      messages.push("No changes made");
    }

    return {
      success: true,
      message: messages.join(", "),
      data: {
        created: createdItems.length,
        updated: updatedCount,
        total: paramArray.length,
      },
      createdItems: createdItems,
      updatedItems: updateParamsArray,
    };
  } catch (error) {
    // Ensure transaction is rolled back if not already done
    if (transaction && !transaction.finished) {
      await transaction.rollback();
    }
    
    // Re-throw error to be handled by error handler middleware
    // Error handler will automatically format the error with:
    // - Detailed error message
    // - Field-specific validation errors
    // - Stack trace (in development)
    // - Request context
    // - File location
    throw error;
  }
}

/**
 * Imports a holiday group from the ERP system.
 * @param {Object} params The parameters to import the holiday group with.
 * @param {String} params.name The name of the holiday group to import.
 * @param {String} [params.description] The description of the holiday group to import.
 * @param {String} [params.api_key] The API key of the holiday group to import.
 * @param {String} [params.api_secret] The API secret of the holiday group to import.
 * @param {String} [params.modified_by] The modified by of the holiday group to import.
 * @param {String} [params.owner] The owner of the holiday group to import.
 * @param {String} [params.creation] The creation date of the holiday group to import.
 * @param {String} [params.modified] The modified date of the holiday group to import.
 * @param {Array<Object>} [params.holidays] The holidays of the holiday group to import.
 * @param {String} params.holidays.holiday_date The holiday date of the holiday to import.
 * @param {String} [params.holidays.description] The description of the holiday to import.
 * @param {String} [params.holidays.weekly_off] The weekly off of the holiday to import.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message if the holiday group is imported successfully, or rejects with an error message if the holiday group is not imported successfully.
 * @description
 * This function imports a holiday group from the ERP system. If a holiday group with the same name already exists, the function will update the holiday group. If not, the function will create a new holiday group.
 * The function will return a promise that resolves to an object with a success message if the holiday group is imported successfully, or rejects with an error message if the holiday group is not imported successfully.
 */
async function importHolidayGroup(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // trim
    const trimmedName = params.name.trim().replace(/\s+/g, " ");

    // validate existing
    const existingHolidayGroup = await dbSAPPS.HolidayGroup.findOne({
      where: { display_name: trimmedName },
    });

    // update
    if (existingHolidayGroup && params.holidays) {
      // Ensure data is an array
      if (typeof params.holidays === "string") {
        params.holidays = JSON.parse(params.holidays);
      }

      // Delete existing child holidays before adding new ones
      await dbSAPPS.Holiday.destroy({
        where: { holidaygroup_id: existingHolidayGroup.id },
        transaction,
      });

      // Prepare bulk-create data
      const holidayData = params.holidays.map((holiday) => {
        // Validate required fields
        if (!holiday.holiday_date) {
          throw new Error("Missing required fields in holidays child.");
        }

        return {
          ...holiday,
          display_name: holiday.description,
          is_national_holiday: holiday.weekly_off ? 0 : 1,
          is_weekend_holiday: holiday.weekly_off ? 1 : 0,
          enabled: 1,
          date: holiday.date,
          creation: Date.now(),
          modified: Date.now(),
          modified_by: params.modified_by,
          owner: params.owner,
          holidaygroup_id: existingHolidayGroup.id, // Link to updated Holiday Group
        };
      });

      // Perform bulk-create for child holidays
      await dbSAPPS.Holiday.bulkCreate(holidayData, {
        transaction,
      });

      await transaction.commit();
      return {
        success: true,
        message: `"${existingHolidayGroup.display_name}" has been updated!`,
      };
    } else if (!existingHolidayGroup) {
      const newParams = {
        creation: params?.creation || new Date(),
        modified: new Date(),
        modified_by: 1,
        code: "",
        owner: 1,
        enabled: 1,
        display_name: params.name,
        company_id: 8,
      };

      const newHolidayGroup = await dbSAPPS.HolidayGroup.create(newParams, {
        transaction,
      });

      if (params.holidays) {
        if (typeof params.holidays === "string") {
          params.holidays = JSON.parse(params.holidays);
        }

        // Preparing Bulk-create holiday
        const holidayData = params.holidays.map((holiday) => {
          // Validate required fields
          if (!holiday.holiday_date) {
            throw new Error("Missing required fields in holidays child.");
          }

          return {
            ...holiday,
            display_name: holiday.description,
            is_national_holiday: holiday.weekly_off ? 0 : 1,
            is_weekend_holiday: holiday.weekly_off ? 1 : 0,
            enabled: 1,
            date: holiday.date,
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by,
            owner: params.owner,
            holidaygroup_id: newHolidayGroup.id, // Link to updated Holiday Group
          };
        });

        // Perform bulkCreate
        try {
          await dbSAPPS.Holiday.bulkCreate(holidayData, {
            transaction,
          });
        } catch (bulkCreateError) {
          await transaction.rollback();
          return {
            success: false,
            message: "Failed to create Holiday children: " + bulkCreateError.message,
          };
        }
      }

      await transaction.commit();
      return {
        success: true,
        message: "Imported successfully!",
      };
    }
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Import Department
 * @param {Object} params - Object containing department data
 * @return {Promise<Object>} - Imported department data
 * @throws {Error} - If something went wrong during import process
 */
async function importDepartment(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validate existing
    const existingDepartment = await dbSAPPS.Department.findOne({
      where: { display_name: params.department_name },
      transaction,
    });

    // update
    if (existingDepartment) {
      existingDepartment.idx = params.department_name || existingDepartment.idx;
      existingDepartment.import_from = "ERP";
      existingDepartment.display_name = params?.department_name || existingDepartment.display_name;
      existingDepartment.name = slugify(params?.department_name) || existingDepartment.name;
      existingDepartment.is_parent = params?.is_group || existingDepartment.is_parent;
      existingDepartment.enabled = existingDepartment.enabled || true;
      existingDepartment.modified = new Date();
      existingDepartment.modified_by = params.user_id || 1;

      // save
      await existingDepartment.save({ transaction });

      await transaction.commit();
      return {
        success: true,
        message: "Saved successfully!",
      };
    }

    // create
    const newParams = {
      creation: params?.creation || new Date(),
      modified: new Date(),
      modified_by: params.user_id || 1,
      owner: params.user_id || 1,
      display_name: params.department_name,
      name: slugify(params.department_name),
      is_parent: params?.is_group || false,
      import_from: "ERP",
      enabled: params?.enabled || true,
    };

    // save
    await dbSAPPS.Department.create(newParams, { transaction });

    await transaction.commit();
    return {
      success: true,
      message: existingDepartment ? "Updated successfully!" : "Imported successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Import Department
 * @param {Object} params - Object containing department data
 * @return {Promise<Object>} - Imported department data
 * @throws {Error} - If something went wrong during import process
 */
async function importDepartments(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validate existing
    const existingDepartment = await dbSAPPS.Department.findOne({
      where: { display_name: params.department_name },
      transaction,
    });

    // update
    if (existingDepartment) {
      existingDepartment.idx = params.department_name || existingDepartment.idx;
      existingDepartment.import_from = "ERP";
      existingDepartment.display_name = params?.department_name || existingDepartment.display_name;
      existingDepartment.name = slugify(params?.department_name) || existingDepartment.name;
      existingDepartment.is_parent = params?.is_group || existingDepartment.is_parent;
      existingDepartment.enabled = params?.status === "Active" ? true : false || null;
      existingDepartment.modified = new Date();
      (existingDepartment.modified_by = params.user_id || 1),
        // save
        await existingDepartment.save({ transaction });

      await transaction.commit();
      return {
        success: true,
        message: "Saved successfully!",
      };
    }

    // create
    const newParams = {
      creation: params?.creation || new Date(),
      modified: new Date(),
      modified_by: params.user_id || 1,
      owner: params.user_id || 1,
      display_name: params.department_name,
      name: slugify(params.department_name),
      is_parent: params?.is_group || false,
      import_from: "ERP",
      enabled: params?.status === "Active" ? true : false || null,
    };

    // save
    await dbSAPPS.Department.create(newParams, { transaction });

    await transaction.commit();
    return {
      success: true,
      message: existingDepartment ? "Updated successfully!" : "Imported successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Import multiple departments from the ERP system.
 * @param {Object} params The parameters to import with.
 * @param {Array<Object>} params.params An array of objects with the following properties: department_name, is_group, status.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message and new data if the import is successful, or rejects with an error message if the import fails.
 * @description
 * This function imports multiple departments from the ERP system. The function will return a promise that resolves to an object with a success message and new data if the import is successful, or rejects with an error message if the import fails.
 */
async function importAllDepartment(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validate existing
    let newParamsArray = [];
    let updateParamsArray = [];

    // validate parameters
    if (!Array.isArray(params) || params.length === 0)
      return {
        success: false,
        message: "No parameters provided for import.",
      };

    // validate existing
    const existingDepartments = await dbSAPPS.Department.findAll({
      where: {
        display_name: params.map((param) => param.department_name),
      },
      attributes: ["display_name", "id", "idx", "import_from"],
    });

    // existing names
    const existingNameSet = new Set(existingDepartments.map((department) => department.display_name));

    // existing map
    const existingMap = new Map(existingDepartments.map((department) => [department.display_name, employee]));

    // loop
    for (const param of params) {
      if (!param.department_name) {
        console.error(`Missing required fields for: ${JSON.stringify(param.department_name)}`);
        continue;
      }

      if (existingNameSet.has(param.department_name)) {
        console.log(`"${param.department_name}" is already taken. Preparing to update idx and import_from...`);

        const existingDepartment = existingMap.get(params.department_name);

        updateParamsArray.push({
          id: existingDepartment.id,
          idx: param.department_name,
          import_from: "ERP",
          name: slugify(param.department_name),
          display_name: param.department_name,
          is_parent: param.is_group || false,
          enabled: param.enabled || true,
          modified: new Date(),
          modified_by: params.user_id || 1,
        });
        continue;
      }

      newParamsArray.push({
        creation: param.creation || new Date(),
        modified: new Date(),
        modified_by: params.user_id || 1,
        owner: params.user_id || 1,
        enabled: param.enabled !== undefined ? params.enabled : true,
        display_name: param.department_name,
        name: slugify(param.department_name),
        is_parent: param.is_group || false,
        import_from: "ERP",
      });
    }

    if (newParamsArray.length > 0) {
      const result = await dbSAPPS.Employee.bulkCreate(newParamsArray, {
        updateOnDuplicate: [
          "modified",
          "modified_by",
          "owner",
          "enabled",
          "display_name",
          "name",
          "is_parent",
          "idx",
          "import_from",
        ],
        returning: true,
        transaction,
      });
      console.log(`${result.length} new users have been inserted.`);
    }

    if (updateParamsArray.length > 0) {
      const updatePromises = updateParamsArray.map((updateParam) => {
        return dbSAPPS.Department.update(
          {
            idx: updateParam.idx,
            import_from: updateParam.import_from,
            name: updateParam.name,
            display_name: updateParam.display_name,
            is_parent: updateParam.is_parent,
            enabled: updateParam.enabled,
          },
          {
            where: { id: updateParam.id },
            transaction,
          }
        );
      });
      await Promise.all(updatePromises);
      console.log(`${updateParamsArray.length} existing records have been updated.`);
    }

    await transaction.commit();
    return {
      newdata: newParamsArray,
      updatedata: updateParamsArray,
      success: true,
      message:
        newParamsArray.length > 0
          ? `${newParamsArray.length} new records have been imported!`
          : "No new records to import.",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to save, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Imports a designation from the ERP system.
 * @param {Object} params The parameters to import a designation with.
 * @param {String} params.designation_name The name of the designation to import.
 * @param {String} [params.is_group] Whether the designation is a group or not.
 * @param {String} [params.status] The status of the designation to import.
 * @param {String} [params.user_id] The ID of the user performing the import.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message if the designation is imported successfully, or rejects with an error message if the designation is not imported successfully.
 * @description
 * This function imports a designation from the ERP system. The function will return a promise that resolves to an object with a success message if the designation is imported successfully, or rejects with an error message if the designation is not imported successfully.
 */
async function importDesignation(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validate existing
    const existingDesignation = await dbSAPPS.Designation.findOne({
      where: { display_name: params.designation_name },
      transaction,
    });

    // update
    if (existingDesignation) {
      existingDesignation.idx = params.designation_name || existingDesignation.idx;
      existingDesignation.import_from = "ERP";
      existingDesignation.display_name = params?.designation_name || existingDesignation.display_name;
      existingDesignation.name = slugify(params?.designation_name) || existingDesignation.name;
      existingDesignation.is_parent = params?.is_group || existingDesignation.is_parent;
      existingDesignation.enabled = existingDesignation.enabled || true;
      existingDesignation.modified = new Date();
      existingDesignation.modified_by = params.user_id || 1;

      // save
      await existingDesignation.save({ transaction });

      await transaction.commit();
      return {
        success: true,
        message: "Saved successfully!",
      };
    }

    // create
    const newParams = {
      creation: params?.creation || new Date(),
      modified: new Date(),
      modified_by: params.user_id || 1,
      owner: params.user_id || 1,
      display_name: params.designation_name,
      name: slugify(params.designation_name),
      is_parent: params?.is_group || false,
      import_from: "ERP",
      enabled: params?.enabled || true,
    };

    // save
    await dbSAPPS.Designation.create(newParams, { transaction });

    await transaction.commit();
    return {
      success: true,
      message: existingDesignation ? "Updated successfully!" : "Imported successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Imports a designation from ERP.
 *
 * @param {object} params - Designation data to be imported.
 * @param {string} params.designation_name - Designation name.
 * @param {string} [params.is_group] - Whether the designation is a group.
 * @param {string} [params.status] - Status of the designation.
 * @param {string} [params.user_id] - User ID of the importer.
 * @param {Date} [params.creation] - Timestamp of the designation's creation.
 *
 * @returns {Promise<{success: boolean, message: string}>} Result of the import process.
 */
async function importDesignations(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validate existing
    const existingDesignation = await dbSAPPS.Designation.findOne({
      where: { display_name: params.designation_name },
      transaction,
    });

    // update
    if (existingDesignation) {
      existingDesignation.idx = params.designation_name || existingDesignation.idx;
      existingDesignation.import_from = "ERP";
      existingDesignation.display_name = params?.designation_name || existingDesignation.display_name;
      existingDesignation.name = slugify(params?.designation_name) || existingDesignation.name;
      existingDesignation.is_parent = params?.is_group || existingDesignation.is_parent;
      existingDesignation.enabled = existingDesignation.enabled || true;
      existingDesignation.modified = new Date();
      (existingDesignation.modified_by = params.user_id || 1),
        // save
        await existingDesignation.save({ transaction });

      await transaction.commit();
      return {
        success: true,
        message: "Saved successfully!",
      };
    }

    // create
    const newParams = {
      creation: params?.creation || new Date(),
      modified: new Date(),
      modified_by: params.user_id || 1,
      owner: params.user_id || 1,
      display_name: params.designation_name,
      name: slugify(params.designation_name),
      is_parent: params?.is_group || false,
      import_from: "ERP",
      enabled: params?.enabled || true,
    };

    // save
    await dbSAPPS.Designation.create(newParams, { transaction });

    await transaction.commit();
    return {
      success: true,
      message: existingDesignation ? "Updated successfully!" : "Imported successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Import multiple designations from ERP.
 * @param {Object} params - Object containing a list of designations to import.
 * @param {Object[]} params.params - Array of objects with the following properties: designation_name, is_group, status.
 * @returns {Promise<Object>} A promise that resolves to an object with a success message and new data if the import is successful, or rejects with an error message if the import fails.
 * @description
 * This function imports multiple designations from the ERP system. The function will return a promise that resolves to an object with a success message and new data if the import is successful, or rejects with an error message if the import fails.
 */
async function importAllDesignation(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // params
    params = params.params;

    // validate existing
    let newParamsArray = [];
    let updateParamsArray = [];

    // validate parameters
    if (!Array.isArray(params) || params.length === 0)
      return {
        success: false,
        message: "No parameters provided for import.",
      };

    // validate existing
    const existingDesignations = await dbSAPPS.Designation.findAll({
      where: {
        display_name: params.map((param) => param.designation_name),
      },
      attributes: ["display_name", "id", "idx", "import_from"],
    });

    // existing names
    const existingNameSet = new Set(existingDesignations.map((designation) => designation.display_name));

    // existing map
    const existingMap = new Map(existingDesignations.map((designation) => [designation.display_name, employee]));

    // loop
    for (const param of params) {
      if (!param.designation_name) {
        console.error(`Missing required fields for: ${JSON.stringify(param.designation_name)}`);
        continue;
      }

      if (existingNameSet.has(param.designation_name)) {
        console.log(`"${param.designation_name}" is already taken. Preparing to update idx and import_from...`);

        const existingDesignation = existingMap.get(params.designation_name);

        updateParamsArray.push({
          id: existingDesignation.id,
          idx: param.designation_name,
          import_from: "ERP",
          name: slugify(param.designation_name),
          display_name: param.designation_name,
          is_parent: param.is_group || false,
          enabled: params.enabled || true,
          modified: new Date(),
          modified_by: params.user_id || 1,
        });
        continue;
      }

      newParamsArray.push({
        creation: param.creation || new Date(),
        modified: new Date(),
        modified_by: params.user_id || 1,
        owner: params.user_id || 1,
        enabled: param.enabled !== undefined ? params.enabled : true,
        display_name: param.designation_name,
        name: slugify(param.designation_name),
        is_parent: param.is_group || false,
        import_from: "ERP",
      });
    }

    if (newParamsArray.length > 0) {
      const result = await dbSAPPS.Designation.bulkCreate(newParamsArray, {
        updateOnDuplicate: [
          "modified",
          "modified_by",
          "owner",
          "enabled",
          "display_name",
          "name",
          "is_parent",
          "idx",
          "import_from",
        ],
        returning: true,
        transaction,
      });
      console.log(`${result.length} new records have been inserted.`);
    }

    if (updateParamsArray.length > 0) {
      const updatePromises = updateParamsArray.map((updateParam) => {
        return dbSAPPS.Designation.update(
          {
            idx: updateParam.idx,
            import_from: updateParam.import_from,
            name: updateParam.name,
            display_name: updateParam.display_name,
            is_parent: updateParam.is_parent,
            enabled: updateParam.enabled,
          },
          {
            where: { id: updateParam.id },
            transaction,
          }
        );
      });
      await Promise.all(updatePromises);
      console.log(`${updateParamsArray.length} existing records have been updated.`);
    }

    await transaction.commit();
    return {
      newdata: newParamsArray,
      updatedata: updateParamsArray,
      success: true,
      message:
        newParamsArray.length > 0
          ? `${newParamsArray.length} new records have been imported!`
          : "No new records to import.",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to save, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
