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,
	getMaterialRequestsByNames,
	getPurchaseOrderByMaterialRequest,
	getPurchaseOrdersByNames,
	getPurchaseOrders,
	getPurchaseReceiptByPurchaseOrder,
	getPurchaseOrderItems,

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

	// import
	importUser,
	importUsers,
	importAllUser,

	importEmployee,
	importEmployees,
	importAllEmployee,

	importCustomer,
	importCustomers,
	importAllCustomer,

	importDepartment,
	importDepartments,
	importAllDepartment,

	importDesignation,
	importDesignations,
	importAllDesignation,

	importItem,
	importItems,
	importAllItem,

	// sync
	syncItemPrices,

	importHolidayGroup,
};

const PREFERRED_PRICE_LIST_NAME = "Reguler";

const parseDateMaybe = (value) => {
	if (!value) return null;
	const date = new Date(value);
	return Number.isNaN(date.getTime()) ? null : date;
};

const isSellingFlagTrue = (value) => value === true || value === 1 || value === "1" || value === "true";

const isPriceEffectiveForDate = (price, now) => {
	const from = parseDateMaybe(price?.valid_from);
	const upto = parseDateMaybe(price?.valid_upto);
	if (from && now < from) return false;
	if (upto && now > upto) return false;
	return true;
};

const pickNewestByModifiedOrCreated = (prices) => {
	if (!Array.isArray(prices) || prices.length === 0) return null;
	return prices.slice().sort((a, b) => {
		const am = parseDateMaybe(a?.modified) || parseDateMaybe(a?.creation) || new Date(0);
		const bm = parseDateMaybe(b?.modified) || parseDateMaybe(b?.creation) || new Date(0);
		return bm.getTime() - am.getTime();
	})[0];
};

const resolveEffectiveSellingPrice = (allPricesForItem, now = new Date()) => {
	if (!Array.isArray(allPricesForItem) || allPricesForItem.length === 0) return null;

	const sellingPrices = allPricesForItem.filter((p) => isSellingFlagTrue(p?.selling));
	const candidates = sellingPrices.length > 0 ? sellingPrices : allPricesForItem;

	const preferReguler = candidates.filter((p) => String(p?.price_list ?? "").toLowerCase() === PREFERRED_PRICE_LIST_NAME.toLowerCase());
	const primary = preferReguler.length > 0 ? preferReguler : candidates;

	const effective = primary.filter((p) => isPriceEffectiveForDate(p, now));
	if (effective.length > 0) return pickNewestByModifiedOrCreated(effective);

	// If no effective found, fallback to newest overall (still respects Reguler preference if present)
	return pickNewestByModifiedOrCreated(primary);
};

const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

const isTransientAxiosError = (error) => {
	const status = error?.response?.status;
	if ([408, 429, 500, 502, 503, 504].includes(status)) return true;
	// Network / no-response cases
	if (!error?.response) return true;
	const code = String(error?.code ?? "");
	if (["ECONNRESET", "ETIMEDOUT", "EAI_AGAIN", "ENOTFOUND", "ECONNABORTED"].includes(code)) return true;
	return false;
};

const axiosGetWithRetry = async (url, options, { retries = 3, minDelayMs = 400, maxDelayMs = 4000 } = {}) => {
	let lastError;
	for (let attempt = 0; attempt <= retries; attempt += 1) {
		try {
			return await axios.get(url, options);
		} catch (error) {
			lastError = error;
			if (attempt >= retries || !isTransientAxiosError(error)) throw error;
			const baseDelay = Math.min(maxDelayMs, minDelayMs * Math.pow(2, attempt));
			const jitter = Math.floor(Math.random() * 200);
			await sleep(baseDelay + jitter);
		}
	}
	throw lastError;
};

const fetchItemPricesForItemCodes = async ({ api_url, api_key, api_secret, itemCodes }) => {
	if (!api_url || !api_key || !api_secret) throw new Error("Missing ERP credentials");
	if (!Array.isArray(itemCodes) || itemCodes.length === 0) return [];

	const uniqueCodes = Array.from(new Set(itemCodes.map(String).filter(Boolean)));
	if (uniqueCodes.length === 0) return [];

	const fields = '["item_code","price_list","price_list_rate","valid_from","valid_upto","selling","buying","creation","modified"]';
	const priceParams = {
		fields,
		limit_start: 0,
		limit_page_length: 0,
		order_by: "modified DESC",
	};

	// Always try server-side filters. Batch when itemCodes is large.
	const CHUNK_SIZE = 150;
	const chunks = [];
	for (let i = 0; i < uniqueCodes.length; i += CHUNK_SIZE) {
		chunks.push(uniqueCodes.slice(i, i + CHUNK_SIZE));
	}

	const all = [];
	for (const chunk of chunks) {
		const response = await axiosGetWithRetry(`${api_url}api/resource/Item%20Price`, {
			headers: {
				Authorization: `token ${api_key}:${api_secret}`,
				Accept: "application/json",
			},
			params: {
				...priceParams,
				filters: JSON.stringify([["item_code", "in", chunk]]),
			},
		});

		const list = response?.data?.data;
		if (Array.isArray(list) && list.length > 0) all.push(...list);
	}

	return all;
};

/**
 * 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 customer names in a list of customers (from ERP).
 * @param {Array<Object>} customers - List of customers from ERP.
 * @returns {Array<Object>} Same list with exists_in_database set.
 */
async function markExistingCustomerNames(customers) {
	if (!Array.isArray(customers)) throw new Error("dataset is not an array");
	const names = customers.map((c) => (c.customer_name || c.name || "").trim().replace(/\s+/g, " ")).filter(Boolean);
	if (names.length === 0) return customers;
	const existing = await dbSAPPS.CustomerList.findAll({
		attributes: ["name"],
		where: { name: { [Op.in]: names } },
	});
	const existingSet = new Set(existing.map((e) => e.name.trim().replace(/\s+/g, " ")));
	for (const c of customers) {
		const n = (c.customer_name || c.name || "").trim().replace(/\s+/g, " ");
		c.exists_in_database = !!n && existingSet.has(n);
	}
	return customers;
}

/**
 * Filters out customer names that already exist in the database from a given list of customers.
 * @param {Array<Object>} customers - List of customers from ERP.
 * @returns {Array<Object>} Only customers not yet in the database.
 */
async function filterNewCustomerNames(customers) {
	if (!Array.isArray(customers)) throw new Error("dataset is not an array");
	const names = customers.map((c) => (c.customer_name || c.name || "").trim().replace(/\s+/g, " ")).filter(Boolean);
	if (names.length === 0) return [];
	const existing = await dbSAPPS.CustomerList.findAll({
		attributes: ["name"],
		where: { name: { [Op.in]: names } },
	});
	const existingSet = new Set(existing.map((e) => e.name.trim().replace(/\s+/g, " ")));
	return customers.filter((c) => {
		const n = (c.customer_name || c.name || "").trim().replace(/\s+/g, " ");
		return !n || !existingSet.has(n);
	});
}

/**
 * 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 (Delivery Note) from the ERP system.
 * Only documents with docstatus = 1 (Submitted/Approved) are returned.
 * @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 {
		// Only fetch DN with docstatus 1 (Submitted/Approved in ERPNext)
		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: '["*"]',
				filters: '[["docstatus","=",1]]',
				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,
		};
	}
}

/**
 * Fetches Material Request docs by names; returns map name -> { modified, docstatus }.
 * When docstatus === 1 (Submitted/Approved), modified is used as MR approval date.
 * @param {Object} params - { api_url, api_key, api_secret, names: string[] }
 * @returns {Promise<{ success: boolean, data?: Object, message?: string }>}
 */
async function getMaterialRequestsByNames(params) {
	try {
		const names = Array.isArray(params.names) ? params.names.filter(Boolean) : [];
		if (names.length === 0) return { success: true, data: {} };

		const baseUrl = ensureTrailingSlash(params.api_url);
		const filters = JSON.stringify([["name", "in", names]]);
		const fields = JSON.stringify(["name", "modified", "docstatus"]);

		const res = await axios.get(`${baseUrl}api/resource/Material%20Request`, {
			headers: {
				Authorization: `token ${params.api_key}:${params.api_secret}`,
				Accept: "application/json",
			},
			params: {
				fields,
				filters,
				limit_page_length: Math.max(200, names.length),
			},
		});

		const list = res.data?.data || [];
		const data = {};
		list.forEach((row) => {
			const name = row?.name;
			if (!name) return;
			const docstatus = row.docstatus;
			const modified = row.modified;
			if (docstatus === 1 || docstatus === "1") {
				data[name] = { modified: modified || null };
			} else {
				data[name] = { modified: null };
			}
		});
		return { success: true, data };
	} catch (error) {
		const msg = error?.message || "Failed to fetch Material Requests by names";
		return { success: false, message: msg, data: {} };
	}
}

/**
 * Ensure API base URL has trailing slash for resource paths.
 * @param {string} url - api_url from config
 * @returns {string}
 */
function ensureTrailingSlash(url) {
	if (!url || typeof url !== "string") return url;
	const u = url.trim();
	return u.endsWith("/") ? u : u + "/";
}

/**
 * Normalize ERPNext creation/modified date to ISO string or null.
 * @param {*} value - creation/modified from API (string, Date, or undefined)
 * @returns {string|null}
 */
function normalizeErpDate(value) {
	if (value == null || value === "") return null;
	if (typeof value === "string") {
		const s = value.trim();
		return s.length > 0 ? s : null;
	}
	if (value instanceof Date) return value.toISOString();
	if (typeof value === "number" && Number.isFinite(value)) return new Date(value).toISOString();
	return null;
}

/**
 * Fetches Purchase Order docs by names from ERPNext; returns map name -> { creation }.
 * Uses document endpoint GET /api/resource/Purchase Order/{name} so "creation" is always
 * returned (list view may omit it). PO Create Date = creation only.
 * @param {Object} params - { api_url, api_key, api_secret, names: string[] }
 * @returns {Promise<{ success: boolean, data?: Object, message?: string }>}
 */
async function getPurchaseOrdersByNames(params) {
	try {
		const rawNames = Array.isArray(params.names) ? params.names : [];
		const names = [...new Set(rawNames.map((n) => (n != null ? String(n).trim() : "")).filter(Boolean))];
		if (names.length === 0) return { success: true, data: {} };

		if (!params.api_url || !params.api_key || !params.api_secret) {
			return { success: false, message: "Missing api_url, api_key, or api_secret", data: {} };
		}

		const baseUrl = ensureTrailingSlash(params.api_url);
		const authHeader = `token ${params.api_key}:${params.api_secret}`;

		const fetchOne = async (name) => {
			try {
				const encodedName = encodeURIComponent(name);
				const res = await axios.get(`${baseUrl}api/resource/Purchase%20Order/${encodedName}`, {
					headers: {
						Authorization: authHeader,
						Accept: "application/json",
					},
					timeout: 15000,
				});
				const doc = res.data?.data;
				if (!doc || doc.name == null) return { name, creation: null };
				const creation = normalizeErpDate(doc.creation);
				return { name: doc.name, creation };
			} catch (err) {
				if (err.response?.status === 404) return { name, creation: null };
				throw err;
			}
		};

		const results = await Promise.allSettled(names.map((name) => fetchOne(name)));
		const data = {};
		results.forEach((out, i) => {
			const name = names[i];
			if (!name) return;
			if (out.status === "fulfilled") {
				const { name: docName, creation } = out.value;
				const entry = { creation: creation ?? null };
				data[name] = entry;
				const key = docName != null ? String(docName).trim() : "";
				if (key && key !== name) data[key] = entry;
			} else {
				data[name] = { creation: null };
			}
		});
		return { success: true, data };
	} catch (error) {
		const msg = error?.message || "Failed to fetch Purchase Orders by names";
		return { success: false, message: msg, data: {} };
	}
}

/**
 * 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 customers from the ERP system (ERPNext Customer doctype).
 * @param {Object} params - api_url, api_key, api_secret, read_all_data (optional).
 * @returns {Promise<Object>} { customers, success, message }.
 */
async function getCustomers(params) {
	try {
		const response = await axios.get(`${params.api_url}api/resource/Customer`, {
			headers: {
				Authorization: `token ${params.api_key}:${params.api_secret}`,
				"Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
				Accept: "application/json",
			},
			params: {
				fields: '["name","customer_name","customer_type","customer_group","territory","default_currency","creation","modified","disabled"]',
				limit_start: "0",
				limit_page_length: "0",
				order_by: "modified DESC",
				debug: "true",
			},
		});

		let list = response.data.data || [];
		list = list.map((row) => {
			const { disabled, ...rest } = row;
			return {
				...rest,
				name: row.customer_name || row.name || "",
				enabled: disabled !== 1 && disabled !== true,
			};
		});
		if (params.read_all_data === "true" || params.read_all_data === true) {
			list = await markExistingCustomerNames(list);
		} else {
			list = await filterNewCustomerNames(list);
		}

		return {
			customers: list,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load customers from ERP.";
		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 {
		const { page, limit, q, read_all_data } = params;

		// Pagination params
		const limit_params = {};
		if (page && limit) {
			limit_params.limit_start = (parseInt(page) - 1) * parseInt(limit);
			limit_params.limit_page_length = parseInt(limit);
		} else {
			limit_params.limit_start = 0;
			limit_params.limit_page_length = 0;
		}

		// Base filters
		const baseFilters = [
			["disabled", "=", 0],
			["is_sales_item", "=", 1],
		];

		// Search filters
		let searchFilters = [];
		if (q) {
			// ERPNext supports 'or_filters' param
			searchFilters = [
				["item_name", "like", `%${q}%`],
				["item_code", "like", `%${q}%`],
				["description", "like", `%${q}%`],
			];
		}

		const requestParams = {
			fields: '["*"]',
			order_by: "modified DESC",
			filters: JSON.stringify(baseFilters),
			...limit_params,
			debug: "true",
		};

		if (searchFilters.length > 0) {
			requestParams.or_filters = JSON.stringify(searchFilters);
		}

		// Get Data
		const itemsResponse = 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: requestParams,
		});

		const items = itemsResponse.data.data || [];

		// Get Total Count (if pagination)
		let total = items.length;
		if (page && limit) {
			// Fetch count separately if pagination is active
			// We fetch "name" field only to reduce payload size for counting
			// If search is active, we must apply search filters to count too
			const countParams = {
				fields: '["name"]',
				filters: JSON.stringify(baseFilters),
				limit_start: 0,
				limit_page_length: 0,
				debug: "true",
			};
			if (searchFilters.length > 0) {
				countParams.or_filters = JSON.stringify(searchFilters);
			}

			const countResponse = await axios.get(`${params.api_url}api/resource/Item`, {
				headers: {
					Authorization: `token ${params.api_key}:${params.api_secret}`,
					Accept: "application/json",
				},
				params: countParams,
			});

			total = countResponse.data.data ? countResponse.data.data.length : 0;
		}

		let itemData = [];
		// If read_all_data is NOT explicitly false, we default to TRUE (show all, mark exist)
		// This allows pagination to work reliably on the full set.
		if (read_all_data === "false" || read_all_data === false) itemData = await filterNewItems(items);
		else itemData = await markExistingItems(items);

		// Resolve Item Prices (prefer price_list=Reguler, else other selling=true) and pick effective by valid_from/valid_upto.
		// For paginated view, itemCodes is small so we filter on server-side to keep payload small.
		const itemCodes = itemData.map((i) => i.item_code).filter(Boolean);
		let itemPrices = [];
		if (itemCodes.length > 0) {
			try {
				itemPrices = await fetchItemPricesForItemCodes({
					api_url: params.api_url,
					api_key: params.api_key,
					api_secret: params.api_secret,
					itemCodes,
				});
			} catch (err) {
				console.error("Error fetching Item Prices:", err.message);
				itemPrices = [];
			}
		}

		const pricesByCode = new Map();
		for (const p of itemPrices) {
			const code = String(p?.item_code ?? "");
			if (!code) continue;
			if (!pricesByCode.has(code)) pricesByCode.set(code, []);
			pricesByCode.get(code).push(p);
		}

		const now = new Date();
		const ItemPriceToItemERP = itemData.map((item) => {
			const code = String(item?.item_code ?? "");
			const list = pricesByCode.get(code) || [];
			const best = resolveEffectiveSellingPrice(list, now);
			const rate = best?.price_list_rate != null && best?.price_list_rate !== "" ? Number(best.price_list_rate) : null;
			return {
				...item,
				item_price: Number.isFinite(rate) ? rate : null,
				price_list: best?.price_list ?? null,
				price_valid_from: best?.valid_from ?? null,
				price_valid_upto: best?.valid_upto ?? null,
				// Backward compat for existing frontend columns
				standard_rate: Number.isFinite(rate) ? rate : (item?.standard_rate ?? null),
				valid_from: best?.valid_from ?? item?.valid_from ?? null,
			};
		});

		return {
			items: ItemPriceToItemERP,
			total: total,
			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 a single customer from ERP into CustomerList. Create requires company_id; territory_id is optional (can be set later in Customer List).
 * @param {Object} params - { params: { name|customer_name, company_id, territory_id?, ... } }
 */
async function importCustomer(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const p = params.params || params;
		const rawName = p.customer_name || p.name || "";
		const trimmedName = rawName.trim().replace(/\s+/g, " ");
		if (!trimmedName) {
			await transaction.rollback();
			return { success: false, message: "Customer name is required." };
		}

		const existing = await dbSAPPS.CustomerList.findOne({
			where: { name: trimmedName },
			transaction,
		});

		if (existing) {
			existing.import_from = "ERP";
			const erpId = p.name != null ? (typeof p.name === "number" ? p.name : parseInt(p.name, 10)) : null;
			if (erpId != null && Number.isFinite(erpId)) existing.ids = erpId;
			if (p.type != null || p.customer_type != null) existing.type = p.customer_type || p.type;
			if (p.currency != null) existing.currency = p.default_currency || p.currency;
			if (p.enabled !== undefined) existing.enabled = !!p.enabled;
			existing.modified = new Date();
			existing.modified_by = p.modified_by != null ? p.modified_by : 1;
			await existing.save({ transaction });
			await transaction.commit();
			return { success: true, message: `"${trimmedName}" has been updated!` };
		}

		const companyId = p.company_id;
		const territoryId = p.territory_id != null && p.territory_id !== "" ? p.territory_id : null;
		if (companyId == null) {
			await transaction.rollback();
			return {
				success: false,
				message: "company_id is required to create a new customer.",
			};
		}

		await dbSAPPS.CustomerList.create(
			{
				creation: p.creation || new Date(),
				modified: new Date(),
				modified_by: p.modified_by != null ? p.modified_by : 1,
				owner: p.owner != null ? p.owner : 1,
				enabled: p.enabled !== undefined ? !!p.enabled : true,
				name: trimmedName,
				company_id: companyId,
				territory_id: territoryId,
				type: p.customer_type || p.type || null,
				currency: p.default_currency || p.currency || null,
				import_from: "ERP",
				ids: p.name != null && Number.isFinite(Number(p.name)) ? (typeof p.name === "number" ? p.name : parseInt(p.name, 10)) : null,
			},
			{ transaction },
		);
		await transaction.commit();
		return { success: true, message: `"${trimmedName}" has been imported!` };
	} catch (error) {
		await transaction.rollback();
		return {
			success: false,
			message: error?.message || "Failed to import customer.",
		};
	}
}

/**
 * Imports multiple customers (one-by-one). Each item must include company_id and territory_id for new records.
 * @param {Object} params - { params: [ { customer_name|name, company_id, territory_id, ... }, ... ] }
 */
async function importCustomers(params) {
	const list = params.params && Array.isArray(params.params) ? params.params : [];
	const results = { success: true, imported: 0, updated: 0, failed: 0, messages: [] };
	for (const item of list) {
		const res = await importCustomer({ params: item });
		if (res.success) {
			if (res.message && res.message.includes("updated")) results.updated += 1;
			else results.imported += 1;
			results.messages.push(res.message);
		} else {
			results.failed += 1;
			results.messages.push(`${item.customer_name || item.name}: ${res.message}`);
		}
	}
	results.message =
		results.failed === 0
			? `Imported: ${results.imported}, Updated: ${results.updated}.`
			: `Imported: ${results.imported}, Updated: ${results.updated}, Failed: ${results.failed}.`;
	return results;
}

/**
 * Imports all customers in the given list (one-by-one). Same as importCustomers.
 * @param {Object} params - { params: [ { customer_name|name, company_id, territory_id, ... }, ... ] }
 */
async function importAllCustomer(params) {
	return importCustomers(params);
}

/**
 * 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 using OR condition (more robust)
		const existingItem = await dbSAPPS.Item.findOne({
			where: {
				[Op.or]: [
					...(param.item_code ? [{ code: param.item_code }] : []),
					...(param.sku_code ? [{ sku_code: param.sku_code }] : []),
					...(param.item_name ? [{ display_name: param.item_name }] : []),
				],
			},
			transaction,
		});

		if (existingItem) {
			// Update existing item
			existingItem.import_from = "ERP";
			existingItem.code = param.item_code || existingItem.code;
			existingItem.sku_code = param.sku_code || existingItem.sku_code;
			existingItem.display_name = param.item_name || existingItem.display_name;

			// 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 = price;
				}
			}

			existingItem.modified = new Date();
			existingItem.modified_by = param.modified_by || 1;
			// Other fields
			existingItem.stock_uom = param.stock_uom || existingItem.stock_uom;
			existingItem.alternative_sku_code = param.alternative_sku_code || existingItem.alternative_sku_code;
			existingItem.image = param.image || existingItem.image;
			existingItem.standard_rate = param.standard_rate || existingItem.standard_rate;
			existingItem.brand = param.brand || existingItem.brand;
			if (param.valid_from) existingItem.valid_from = new Date(param.valid_from);
			existingItem.idx = param.idx || existingItem.idx;

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

			return { success: true, message: "Item updated successfully", data: existingItem };
		}

		// 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,
			standard_rate: param.standard_rate || null,
			valid_from: param.valid_from ? new Date(param.valid_from) : null,
			idx: param.idx || null,
			import_from: "ERP",
		};

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

		return {
			success: true,
			message: "Item imported successfully",
			data: newItem,
		};
	} catch (error) {
		if (transaction && !transaction.finished) await transaction.rollback();
		// Re-throw (handler catches)
		throw error;
	}
}

/**
 * Imports a list of items from the ERP system.
 * NOTE: This function name suggests plural but implementation handles single via params.params logic in legacy code.
 * Redirecting to importAllItem if params is array, or importItem if params is object.
 */
async function importItems(params) {
	// If params.params is array, delegate to importAllItem
	if (params && params.params && Array.isArray(params.params)) {
		return importAllItem(params);
	}
	// Else delegate to importItem
	return importItem(params);
}

/**
 * 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/**
 * Imports all items from the ERP system.
 */
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.");
		}

		// 1. Gather all identifiers to find existing items in one go
		const itemNames = paramArray.map((p) => p.item_name).filter(Boolean);
		const itemCodes = paramArray.map((p) => p.item_code).filter(Boolean);
		const skuCodes = paramArray.map((p) => p.sku_code).filter(Boolean);

		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: [
				"id",
				"display_name",
				"code",
				"sku_code",
				"price",
				"stock_uom",
				"alternative_sku_code",
				"image",
				"standard_rate",
				"brand",
				"valid_from",
				"enabled",
				"idx",
			],
			transaction,
		});

		// Lookup maps
		const existingByCode = new Map();
		const existingBySku = new Map();
		const existingByName = new Map();
		existingItems.forEach((item) => {
			if (item.code) existingByCode.set(String(item.code), item);
			if (item.sku_code) existingBySku.set(String(item.sku_code), item);
			if (item.display_name) existingByName.set(String(item.display_name).toLowerCase(), item);
		});

		const itemsToCreate = [];
		const itemsToUpdate = [];
		let skippedCount = 0;
		const conflicts = [];

		for (const param of paramArray) {
			if (!param.item_name) continue; // Skip invalid

			// Per requirement: when item_code exists, ONLY match/update by item_code.
			// This prevents accidental updates by name/sku.
			const codeKey = param.item_code != null ? String(param.item_code) : null;
			const skuKey = param.sku_code != null ? String(param.sku_code) : null;
			const nameKey = param.item_name != null ? String(param.item_name).toLowerCase() : null;

			let existingItem = null;
			if (codeKey) {
				existingItem = existingByCode.get(codeKey) || null;
			}

			const newItemData = {
				idx: param.idx || null,
				import_from: "ERP",
				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,
				standard_rate: param.standard_rate || null,
				valid_from: param.valid_from ? new Date(param.valid_from) : null,
				enabled: true,
				modified: new Date(),
				modified_by: param.modified_by || 1,
			};

			if (existingItem) {
				// Logic to check if update is needed
				let isChanged = false;

				// Helper for comparison to handle null/undefined/types
				const isDifferent = (val1, val2) => {
					// specific handling for dates?
					if (val1 instanceof Date && val2 instanceof Date) return val1.getTime() !== val2.getTime();
					if (val1 instanceof Date && typeof val2 === "string") return val1.getTime() !== new Date(val2).getTime();

					// treat null and undefined as same for comparison
					const v1 = val1 === undefined ? null : val1;
					const v2 = val2 === undefined ? null : val2;
					return v1 != v2; // weak comparison for numbers/strings safety? or strict after normalization
				};

				// Compare relevant fields
				if (isDifferent(existingItem.display_name, newItemData.display_name)) isChanged = true;
				if (isDifferent(existingItem.code, newItemData.code)) isChanged = true;
				if (isDifferent(existingItem.sku_code, newItemData.sku_code)) isChanged = true;

				// Numerical comparison for price
				const oldPrice = existingItem.price ? Number(existingItem.price) : null;
				const newPrice = newItemData.price;
				if (oldPrice !== newPrice) isChanged = true;

				if (isDifferent(existingItem.stock_uom, newItemData.stock_uom)) isChanged = true;
				if (isDifferent(existingItem.alternative_sku_code, newItemData.alternative_sku_code)) isChanged = true;
				if (isDifferent(existingItem.image, newItemData.image)) isChanged = true;
				if (isDifferent(existingItem.brand, newItemData.brand)) isChanged = true;
				if (isDifferent(existingItem.standard_rate, newItemData.standard_rate)) isChanged = true;

				// Date comparison
				const oldValid = existingItem.valid_from ? new Date(existingItem.valid_from).getTime() : 0;
				const newValid = newItemData.valid_from ? newItemData.valid_from.getTime() : 0;
				if (oldValid !== newValid) isChanged = true;

				if (isDifferent(existingItem.idx, newItemData.idx)) isChanged = true;

				if (isChanged) {
					itemsToUpdate.push({ ...newItemData, id: existingItem.id });
				}
			} else {
				// New item.
				// Guard against unique conflicts (display_name is unique in Item model).
				// If we find same name/sku but different code, we SKIP to avoid breaking existing data.
				const nameConflict = nameKey ? existingByName.get(nameKey) : null;
				if (nameConflict && codeKey && String(nameConflict.code || "") !== codeKey) {
					skippedCount += 1;
					conflicts.push({ type: "display_name", item_code: codeKey, item_name: param.item_name, existing_code: nameConflict.code });
					continue;
				}
				const skuConflict = skuKey ? existingBySku.get(skuKey) : null;
				if (skuConflict && codeKey && String(skuConflict.code || "") !== codeKey) {
					skippedCount += 1;
					conflicts.push({ type: "sku_code", item_code: codeKey, sku_code: skuKey, existing_code: skuConflict.code });
					continue;
				}

				itemsToCreate.push({
					...newItemData,
					creation: param.creation ? new Date(param.creation) : new Date(),
					owner: param.owner || 1,
				});
			}
		}

		// Perform Bulk Actions

		// 1. Create New Items
		let createdCount = 0;
		if (itemsToCreate.length > 0) {
			await dbSAPPS.Item.bulkCreate(itemsToCreate, { transaction });
			createdCount = itemsToCreate.length;
		}

		// 2. Update Existing Items (Only if changed)
		let updatedCount = 0;
		if (itemsToUpdate.length > 0) {
			// Bulk update using bulkCreate with updateOnDuplicate is most efficient for multi-row updates
			// We know IDs, so it WILL match.
			await dbSAPPS.Item.bulkCreate(itemsToUpdate, {
				updateOnDuplicate: [
					"display_name",
					"code",
					"sku_code",
					"price",
					"stock_uom",
					"alternative_sku_code",
					"image",
					"brand",
					"standard_rate",
					"valid_from",
					"enabled",
					"modified",
					"modified_by",
					"idx",
					"import_from",
				],
				transaction,
			});
			updatedCount = itemsToUpdate.length;
		}

		await transaction.commit();

		// Messages
		const messages = [];
		if (createdCount > 0) messages.push(`${createdCount} new items imported`);
		if (updatedCount > 0) messages.push(`${updatedCount} items updated`);
		if (skippedCount > 0) messages.push(`${skippedCount} items skipped (conflict)`);
		if (createdCount === 0 && updatedCount === 0) messages.push("All items are up to date");

		return {
			success: true,
			message: messages.join(", "),
			data: {
				created: createdCount,
				updated: updatedCount,
				skipped: skippedCount,
				total_processed: paramArray.length,
				conflicts: conflicts.slice(0, 50),
			},
		};
	} catch (error) {
		if (transaction && !transaction.finished) await transaction.rollback();
		throw error;
	}
}

/**
 * Sync item prices from ERPNext into SAPPS Items.
 * Rules:
 * - Prefer Item Price with price_list="Reguler".
 * - Fallback to other price_list where selling=true.
 * - Pick effective by valid_from/valid_upto; if multiple, pick newest by modified/creation.
 *
 * Input:
 * { params: { api_url, api_key, api_secret, items: [{ item_code }...] } }
 */
async function syncItemPrices(params) {
	const payload = params?.params ?? params;
	const api_url = payload?.api_url;
	const api_key = payload?.api_key;
	const api_secret = payload?.api_secret;
	const syncAll = payload?.sync_all === true || payload?.sync_all === "true" || payload?.sync_all === 1 || payload?.sync_all === "1";
	const maxDurationMsRaw = payload?.max_duration_ms;
	const maxDurationMs = Number.isFinite(Number(maxDurationMsRaw)) ? Number(maxDurationMsRaw) : null;
	const items = Array.isArray(payload?.items) ? payload.items : [];
	let itemCodes = items
		.map((i) => i?.item_code)
		.filter(Boolean)
		.map(String);

	if (!api_url || !api_key || !api_secret) {
		return { success: false, message: "ERP credentials are required (api_url, api_key, api_secret)." };
	}
	if (syncAll) {
		// Safer default: sync only items that were imported from ERP.
		try {
			const allItems = await dbSAPPS.Item.findAll({
				where: {
					code: { [Op.ne]: null },
					import_from: "ERP",
				},
				attributes: ["code"],
			});
			itemCodes = allItems.map((it) => String(it.code)).filter(Boolean);
		} catch (error) {
			return { success: false, message: error?.message ?? "Failed to read SAPPS items for sync_all." };
		}
	}

	if (itemCodes.length === 0) {
		return { success: false, message: syncAll ? "No ERP-imported items found to sync." : "No items provided for price sync." };
	}

	// Step 1: Find existing items in SAPPS (no transaction yet)
	let existingItems = [];
	try {
		existingItems = await dbSAPPS.Item.findAll({
			where: { code: { [Op.in]: itemCodes } },
			attributes: ["id", "code", "price", "modified", "modified_by"],
		});
	} catch (error) {
		return { success: false, message: error?.message ?? "Failed to read SAPPS items." };
	}

	const existingByCode = new Map(existingItems.map((it) => [String(it.code), it]));
	const codesToSync = itemCodes.filter((c) => existingByCode.has(String(c)));

	if (codesToSync.length === 0) {
		return { success: false, message: "Selected items were not found in SAPPS (by code)." };
	}

	try {
		const now = new Date();
		const startedAt = Date.now();
		const effectiveMaxMs = syncAll ? (maxDurationMs ?? 25000) : (maxDurationMs ?? null);
		let updated = 0;
		let skipped = 0;
		let failed = 0;
		const failedCodes = new Set();
		const skippedCodes = new Set();
		const skippedNoPriceCodes = new Set();
		const skippedNotFoundCodes = new Set();
		const remainingCodes = new Set();
		const messages = [];
		const pushMessage = (msg) => {
			// Avoid huge payloads for sync_all.
			if (messages.length < 200) messages.push(msg);
		};
		const addCode = (set, code) => {
			if (!code) return;
			// Avoid huge payloads for sync_all.
			if (set.size < 2000) set.add(String(code));
		};

		// Process in chunks to keep ERP calls small and DB transactions short.
		const CHUNK_SIZE = 150;
		for (let i = 0; i < codesToSync.length; i += CHUNK_SIZE) {
			if (effectiveMaxMs != null && Date.now() - startedAt > effectiveMaxMs) {
				for (let j = i; j < codesToSync.length; j += 1) remainingCodes.add(String(codesToSync[j]));
				pushMessage(`Stopped early due to time limit (${effectiveMaxMs} ms). Remaining: ${codesToSync.length - i}.`);
				break;
			}

			const chunkCodes = codesToSync.slice(i, i + CHUNK_SIZE);
			let itemPrices = [];
			try {
				itemPrices = await fetchItemPricesForItemCodes({ api_url, api_key, api_secret, itemCodes: chunkCodes });
			} catch (error) {
				failed += chunkCodes.length;
				for (const code of chunkCodes) addCode(failedCodes, code);
				pushMessage(`Chunk ${i / CHUNK_SIZE + 1}: failed to fetch ERP Item Prices (${error?.message ?? "unknown error"})`);
				continue;
			}

			const pricesByCode = new Map();
			for (const p of itemPrices) {
				const code = String(p?.item_code ?? "");
				if (!code) continue;
				if (!pricesByCode.has(code)) pricesByCode.set(code, []);
				pricesByCode.get(code).push(p);
			}

			const transaction = await dbSAPPS.sequelize.transaction();
			try {
				for (const code of chunkCodes) {
					if (effectiveMaxMs != null && Date.now() - startedAt > effectiveMaxMs) {
						// Mark current + remaining chunk + remaining overall as remaining.
						remainingCodes.add(String(code));
						const idxInChunk = chunkCodes.indexOf(code);
						if (idxInChunk >= 0) {
							for (let k = idxInChunk + 1; k < chunkCodes.length; k += 1) remainingCodes.add(String(chunkCodes[k]));
						}
						for (let j = i + CHUNK_SIZE; j < codesToSync.length; j += 1) remainingCodes.add(String(codesToSync[j]));
						pushMessage(`Stopped early due to time limit (${effectiveMaxMs} ms). Remaining: ${remainingCodes.size}.`);
						break;
					}

					try {
						const item = existingByCode.get(String(code));
						if (!item) {
							skipped += 1;
							addCode(skippedCodes, code);
							addCode(skippedNotFoundCodes, code);
							pushMessage(`${code}: not found in SAPPS`);
							continue;
						}

						const list = pricesByCode.get(String(code)) || [];
						const best = resolveEffectiveSellingPrice(list, now);
						const rateRaw = best?.price_list_rate;
						const rate = rateRaw != null && rateRaw !== "" ? Number(rateRaw) : null;
						if (!Number.isFinite(rate)) {
							skipped += 1;
							addCode(skippedCodes, code);
							addCode(skippedNoPriceCodes, code);
							pushMessage(`${code}: no effective selling price found`);
							continue;
						}

						item.price = rate;
						item.modified = new Date();
						if (payload?.modified_by != null) item.modified_by = payload.modified_by;
						await item.save({ transaction });

						updated += 1;
						pushMessage(`${code}: synced (${best?.price_list ?? "unknown price_list"}) = ${rate}`);
					} catch (err) {
						failed += 1;
						addCode(failedCodes, code);
						pushMessage(`${code}: ${err?.message ?? "sync failed"}`);
					}
				}
				await transaction.commit();
			} catch (error) {
				if (transaction && !transaction.finished) await transaction.rollback();
				failed += chunkCodes.length;
				for (const code of chunkCodes) addCode(failedCodes, code);
				pushMessage(`Chunk ${i / CHUNK_SIZE + 1}: DB transaction failed (${error?.message ?? "unknown error"})`);
			}
		}

		const remaining_item_codes = Array.from(remainingCodes);
		const partial = remaining_item_codes.length > 0;

		return {
			success: true,
			message: partial
				? `Sync price partial. Updated: ${updated}, Skipped: ${skipped}, Failed: ${failed}. Remaining: ${remaining_item_codes.length}.`
				: `Sync price done. Updated: ${updated}, Skipped: ${skipped}, Failed: ${failed}.`,
			updated,
			skipped,
			failed,
			messages,
			failed_item_codes: Array.from(failedCodes),
			skipped_item_codes: Array.from(skippedCodes),
			skipped_no_price_item_codes: Array.from(skippedNoPriceCodes),
			skipped_not_found_item_codes: Array.from(skippedNotFoundCodes),
			remaining_item_codes,
			partial,
		};
	} catch (error) {
		return { success: false, message: error?.message ?? "Sync price failed." };
	}
}

/**
 * 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 {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,
		};
	}
}
