const jwt = require("jsonwebtoken");
const bcrypt = require("bcryptjs");
const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const { Op } = require("sequelize");

module.exports = {
	create,
	getAll,
	filter,
	option,
	count,
	update,
	enabled,
	delete: _delete,
};

/**
 * Sync data from master Designation to DesignationVersionChild
 * This ensures that designationversionchilds always have the latest data from master designation
 * @param {Array} designationVersionChilds - Array of designation version child objects
 * @param {Object} transaction - Sequelize transaction object
 * @returns {Promise<Array>} - Synced designation version childs data
 */
async function syncDesignationVersionChildsFromMaster(
	designationVersionChilds,
	transaction
) {
	if (
		!Array.isArray(designationVersionChilds) ||
		designationVersionChilds.length === 0
	) {
		return designationVersionChilds;
	}

	// Get all unique designation_ids from childs
	const designationIds = designationVersionChilds
		.map((child) => child.designation_id)
		.filter((id) => id != null && id !== undefined);

	if (designationIds.length === 0) {
		return designationVersionChilds;
	}

	// Fetch latest data from master Designation table
	const masterDesignations = await dbSAPPS.Designation.findAll({
		where: {
			id: { [Op.in]: designationIds },
		},
		attributes: [
			"id",
			"name",
			"display_name",
			"slug_name",
			"code",
			"type",
			"is_parent",
			"description",
			"company_id",
		],
		transaction,
	});

	// Create a map for quick lookup
	const masterDesignationMap = new Map();
	masterDesignations.forEach((designation) => {
		masterDesignationMap.set(designation.id, designation);
	});

	// Sync each child with master data
	const syncedChilds = designationVersionChilds.map((child) => {
		if (!child.designation_id) {
			// If no designation_id, return as is
			return child;
		}

		const masterDesignation = masterDesignationMap.get(child.designation_id);

		if (!masterDesignation) {
			// If master designation not found, return as is (might be deleted)
			// Log warning but don't fail - allow child to keep existing data
			console.warn(
				`Master designation with id ${child.designation_id} not found. Skipping sync for this child.`
			);
			return child;
		}

		// Sync fields from master designation
		// Preserve version-specific fields: parent_id (in version context), designationversion_id, etc.
		return {
			...child,
			// Sync these fields from master
			name: masterDesignation.name || child.name,
			display_name: masterDesignation.display_name || child.display_name,
			slug_name: masterDesignation.slug_name || child.slug_name,
			code: masterDesignation.code || child.code,
			type: masterDesignation.type || child.type,
			is_parent:
				masterDesignation.is_parent !== undefined
					? masterDesignation.is_parent
					: child.is_parent,
			description: masterDesignation.description || child.description,
			// Sync company_id from master if not explicitly set in child
			company_id: child.company_id || masterDesignation.company_id || null,
			// Preserve version-specific fields
			designation_id: child.designation_id, // Keep reference to master
			parent_id: child.parent_id, // Keep version-specific parent relationship
			designationversion_id: child.designationversion_id, // Keep version reference
		};
	});

	return syncedChilds;
}

/**
 * Fungsi utama untuk mendapatkan hierarki jabatan karyawan
 * @param {Object} user - Data user yang berisi informasi employee
 * @returns {Object} - Objek yang berisi semua data hierarki
 */
async function getDesignationHierarchy(user) {
	// Inisialisasi semua variabel
	const hierarchyData = {
		employeeDesignation: null,
		designationVersion: [],
		designationVersionChilds: [],
		designationParent: [],
		designationChild: [],
		designations: [],
		hierarchyDesignations: [],
		employeeDesignationParent: [],
		workflowDesignations: [],
		employeeDesignationChild: [],
		subCoordinateDesignations: [],
		subCoordinateEmployeeDesignations: [],
		designationsIds: [],
		workflowDesignationsDirect: [],
	};

	try {
		// Step 1: Dapatkan designation employee
		await getEmployeeDesignation(user, hierarchyData);

		// Step 2: Dapatkan versi designation dan children-nya
		await getDesignationVersions(user, hierarchyData);

		// Step 3: Bangun hierarki dan data terkait
		buildHierarchyData(hierarchyData);

		await getDirectParent(hierarchyData);

		// Step 4: Dapatkan data employee parent
		await getEmployeeDesignationParents(hierarchyData);

		// Step 5: Dapatkan data employee child
		await getEmployeeDesignationChildren(hierarchyData);

		return {
			designations: hierarchyData.designations,
			hierarchyDesignations: hierarchyData.hierarchyDesignations,
			workflowDesignations: hierarchyData.workflowDesignations,
			workflowDesignationsDirect: hierarchyData.workflowDesignationsDirect,
			subCoordinateDesignations: hierarchyData.subCoordinateDesignations,
			subCoordinateEmployeeDesignations:
				hierarchyData.subCoordinateEmployeeDesignations,
		};
	} catch (error) {
		console.error("Error in getDesignationHierarchy:", error);
		throw error;
	}
}

// **FUNGSI BARU: Ambil direct parent (1 tingkat di atas)**
async function getDirectParent(hierarchyData) {
	if (!hierarchyData.employeeDesignation) return;

	// Cari direct parent dari designation employee
	const directParent = hierarchyData.designationVersionChilds.find(
		(child) => child.designation_id === hierarchyData.employeeDesignation.id
	);

	if (directParent?.parent_id) {
		// Dapatkan informasi designation parent
		const parentDesignation = await dbSAPPS.Designation.findOne({
			attributes: ["id", "display_name"],
			where: { id: directParent.parent_id },
		});

		if (parentDesignation) {
			// Dapatkan employee dengan designation parent tersebut
			const directParentEmployees = await dbSAPPS.Employee.findAll({
				attributes: ["id", "designation_id"],
				where: { designation_id: parentDesignation.id },
				include: [
					{
						model: dbSAPPS.Designation,
						as: "designation",
						attributes: ["id", "display_name"],
					},
					{
						model: dbSAPPS.User,
						as: "user",
						attributes: ["id", "email", "username", "full_name"],
					},
				],
			});

			// Format data workflow direct
			hierarchyData.workflowDesignationsDirect = directParentEmployees.map(
				(employee) => ({
					employee_id: employee.id,
					designation_id: employee.designation_id,
					designation_name: employee.designation?.display_name,
					user_id: employee.user?.id,
					email: employee.user?.email,
					username: employee.user?.username,
					full_name: employee.user?.full_name,
				})
			);
		}
	}
}

/**
 * Mendapatkan designation dari employee yang bersangkutan
 */
async function getEmployeeDesignation(user, hierarchyData) {
	if (user?.employee?.designation_id) {
		hierarchyData.employeeDesignation = await dbSAPPS.Designation.findOne({
			attributes: ["id", "display_name"],
			where: { id: user.employee.designation_id },
		});
	}
}

/**
 * Mendapatkan semua versi designation yang valid untuk perusahaan employee
 */
async function getDesignationVersions(user, hierarchyData) {
	if (user?.employee?.id) {
		const today = new Date();
		const getDesignationVersion = await dbSAPPS.DesignationVersion.findAll({
			attributes: ["id", "display_name", "valid_from", "valid_to", "enabled"],
			where: {
				[Op.and]: [
					{ company_id: user.employee.company_id },
					{ enabled: true },
					{ valid_from: { [Op.lte]: today } },
					{ [Op.or]: [{ valid_to: { [Op.gte]: today } }, { valid_to: null }] },
				],
			},
			include: [
				{
					model: dbSAPPS.DesignationVersionChild,
					as: "designationversionchilds",
					attributes: [
						"id",
						"name",
						"parent_id",
						"designation_id",
						"display_name",
					],
				},
			],
		});

		if (getDesignationVersion?.length > 0) {
			hierarchyData.designationVersion = getDesignationVersion;
			hierarchyData.designationVersionChilds = getDesignationVersion.flatMap(
				(version) => version.designationversionchilds
			);
		}
	}
}

/**
 * Membangun struktur data hierarki
 */
function buildHierarchyData(hierarchyData) {
	// Fungsi rekursif untuk membangun hierarki
	const buildHierarchy = (parent_id = null) => {
		const children = hierarchyData.designationVersionChilds.filter(
			(c) => c.parent_id === parent_id
		);
		return children.map((child) => ({
			id: child.designation_id,
			name: child.display_name,
			display_name: child.display_name,
			children: buildHierarchy(child.designation_id),
		}));
	};

	// Fungsi untuk mendapatkan parent hierarchy
	const getParentHierarchy = (designation_id) => {
		const hierarchy = [];
		const findParent = (id) => {
			const parent = hierarchyData.designationVersionChilds.find(
				(c) => c.designation_id === id
			);
			if (parent?.parent_id) {
				if (parent.designation_id !== designation_id) {
					hierarchy.push({
						id: parent.designation_id,
						name: parent.display_name,
					});
				}
				findParent(parent.parent_id);
			}
		};

		findParent(designation_id);
		return hierarchy.filter((item) => item.id !== designation_id);
	};

	// Membangun hierarki utama
	hierarchyData.hierarchyDesignations = buildHierarchy();

	// Membangun data khusus untuk employee designation jika ada
	if (hierarchyData.employeeDesignation) {
		const empDesId = hierarchyData.employeeDesignation.id;
		hierarchyData.designationChild = buildHierarchy(empDesId);
		hierarchyData.designations = [
			{
				id: empDesId,
				name: hierarchyData.employeeDesignation.display_name,
				children: hierarchyData.designationChild,
			},
		];
		hierarchyData.designationParent = getParentHierarchy(empDesId);
	}
}

/**
 * Mendapatkan data employee yang memiliki designation parent
 */
async function getEmployeeDesignationParents(hierarchyData) {
	if (hierarchyData.designationParent?.length > 0) {
		const parentIds = hierarchyData.designationParent.map((t) => t.id);
		hierarchyData.employeeDesignationParent = await dbSAPPS.Employee.findAll({
			attributes: ["id", "designation_id"],
			where: { designation_id: parentIds },
			include: [
				{
					model: dbSAPPS.Designation,
					as: "designation",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.User,
					as: "user",
					attributes: ["id", "email", "username", "full_name"],
				},
			],
		});

		if (hierarchyData.employeeDesignationParent.length > 0) {
			// Urutkan berdasarkan hierarchy
			hierarchyData.employeeDesignationParent.sort(
				(a, b) =>
					hierarchyData.designationParent.findIndex(
						(t) => t.id === a.designation_id
					) -
					hierarchyData.designationParent.findIndex(
						(t) => t.id === b.designation_id
					)
			);

			// Mapping data workflow
			hierarchyData.workflowDesignations =
				hierarchyData.employeeDesignationParent.map((edp) => ({
					employee_id: edp.id,
					employee_name: edp.employee_name,
					employee_number: edp.employee_number,
					designation_id: edp.designation_id,
					designation_name: edp.designation?.display_name,
					user_id: edp.user?.id,
					email: edp.user?.email,
					username: edp.user?.username,
					full_name: edp.user?.full_name,
				}));
		}
	}
}

/**
 * Mendapatkan data employee yang memiliki designation child
 */
async function getEmployeeDesignationChildren(hierarchyData) {
	if (hierarchyData.designationChild?.length > 0) {
		// Fungsi untuk mendapatkan semua ids dari hierarki
		const getAllDesignationIds = (arr) => {
			const ids = new Set();
			const addIds = (items) => {
				items.forEach((item) => {
					ids.add(item.id);
					if (item.children?.length) addIds(item.children);
				});
			};
			addIds(arr);
			return Array.from(ids);
		};

		hierarchyData.designationsIds = getAllDesignationIds(
			hierarchyData.designations
		);

		hierarchyData.employeeDesignationChild = await dbSAPPS.Employee.findAll({
			attributes: ["id", "designation_id"],
			where: { designation_id: { [Op.in]: hierarchyData.designationsIds } },
			include: [
				{
					model: dbSAPPS.Designation,
					as: "designation",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.User,
					as: "user",
					attributes: ["id", "email", "username", "full_name"],
				},
			],
		});

		if (hierarchyData.employeeDesignationChild.length > 0) {
			// Urutkan berdasarkan parent hierarchy jika ada
			if (hierarchyData.designationParent?.length) {
				hierarchyData.employeeDesignationChild.sort(
					(a, b) =>
						hierarchyData.designationParent.findIndex(
							(t) => t.id === a.designation_id
						) -
						hierarchyData.designationParent.findIndex(
							(t) => t.id === b.designation_id
						)
				);
			}

			hierarchyData.subCoordinateDesignations =
				hierarchyData.employeeDesignationChild.map((edp) => ({
					employee_id: edp.id,
					employee_name: edp.employee_name,
					employee_number: edp.employee_number,
					designation_id: edp.designation_id,
					designation_name: edp.designation?.display_name,
					user_id: edp.user?.id,
					email: edp.user?.email,
					username: edp.user?.username,
					full_name: edp.user?.full_name,
				}));

			if (hierarchyData.subCoordinateDesignations) {
				hierarchyData.subCoordinateEmployeeDesignations =
					hierarchyData.subCoordinateDesignations.map((e) => e.employee_id);
			}
		}
	}
}

async function getAll(params = {}) {
	try {
		const {
			pageIndex = 0,
			pageSize = 25,
			sortBy,
			sortDir,
			q,
			company_id,
			companiesId,
			f_name,
			f_display_name,
			f_slug_name,
			f_company,
			enabled,
		} = params || {};

		// base where by company scope
		const baseWhere = {};

		if (company_id) {
			baseWhere.company_id = company_id;
		} else if (Array.isArray(companiesId) && companiesId.length > 0) {
			baseWhere.company_id = { [Op.in]: companiesId };
		}

		// column filters
		const andConditions = [];
		if (Object.keys(baseWhere).length > 0) {
			andConditions.push(baseWhere);
		}

		if (f_name) {
			andConditions.push({ name: { [Op.iLike]: `%${f_name}%` } });
		}

		if (f_display_name) {
			andConditions.push({
				display_name: { [Op.iLike]: `%${f_display_name}%` },
			});
		}

		if (f_slug_name) {
			andConditions.push({ slug_name: { [Op.iLike]: `%${f_slug_name}%` } });
		}

		if (f_company) {
			andConditions.push({
				"$company.name$": { [Op.iLike]: `%${f_company}%` },
			});
		}

		if (enabled !== undefined && enabled !== null && enabled !== "") {
			if (typeof enabled === "string") {
				andConditions.push({
					enabled:
						enabled === "1" || enabled.toLowerCase() === "true" ? true : false,
				});
			} else {
				andConditions.push({ enabled: { [Op.eq]: !!enabled } });
			}
		}

		// global search
		if (q && String(q).trim().length > 0) {
			const search = String(q).trim();
			andConditions.push({
				[Op.or]: [
					{ name: { [Op.iLike]: `%${search}%` } },
					{ display_name: { [Op.iLike]: `%${search}%` } },
					{ slug_name: { [Op.iLike]: `%${search}%` } },
					{ "$company.name$": { [Op.iLike]: `%${search}%` } },
				],
			});
		}

		const where =
			andConditions.length > 0
				? {
						[Op.and]: andConditions,
				  }
				: {};

		// pagination & sorting
		const safePageSize = parseInt(pageSize, 10);
		const limit = Math.max(
			1,
			Math.min(Number.isNaN(safePageSize) ? 25 : safePageSize, 100)
		);
		const safePageIndex = parseInt(pageIndex, 10);
		const currentPageIndex = Number.isNaN(safePageIndex) ? 0 : safePageIndex;
		const offset = currentPageIndex * limit;

		const sortableFields = [
			"modified",
			"display_name",
			"name",
			"valid_from",
			"valid_to",
		];
		const sortField = sortableFields.includes(sortBy) ? sortBy : "modified";
		const sortDirection =
			String(sortDir || "DESC").toUpperCase() === "ASC" ? "ASC" : "DESC";

		const { rows, count } = await dbSAPPS.DesignationVersion.findAndCountAll({
			where,
			order: [[sortField, sortDirection]],
			limit,
			offset,
			include: [
				{
					model: dbSAPPS.DesignationVersionChild,
					as: "designationversionchilds",
					attributes: [
						"id",
						"designationversion_id",
						"code",
						"name",
						"display_name",
						"is_parent",
						"parent_id",
						"company_id",
						"ids",
						"enabled",
						"type",
						"slug_name",
						"designation_id",
					],
					include: [
						{
							model: dbSAPPS.Company,
							as: "company",
							attributes: ["name"],
						},
						{
							model: dbSAPPS.Designation,
							as: "parent",
							attributes: ["display_name"],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["name"],
				},
			],
		});

		return {
			designationVersionsWithEmployee: rows,
			totalCount: count,
			pageIndex: currentPageIndex,
			pageSize: limit,
			sortBy: sortField,
			sortDir: sortDirection,
			success: true,
			message: "Designation versions has been loaded!",
		};
	} catch (error) {
		const errorMessage =
			error.message || "Failed to load designation versions!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function filter(params) {
	const filterConditions = {};

	if (params.name) {
		filterConditions.name = {
			[Op.like]: `%${params.name}%`,
		};
	}

	if (params.display_name) {
		filterConditions.display_name = {
			[Op.like]: `%${params.display_name}%`,
		};
	}

	if (params.slug_name) {
		filterConditions.slug_name = {
			[Op.like]: `%${params.slug_name}%`,
		};
	}

	if (params.enabled) {
		filterConditions.enabled = {
			[Op.eq]: params.enabled === "1" ? true : false,
		};
	}

	try {
		let designationVersions = [];
		designationVersions = await dbSAPPS.DesignationVersion.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.DesignationVersionChild,
					as: "designationversionchilds",
					attributes: [
						"id",
						"designationversion_id",
						"code",
						"name",
						"display_name",
						"is_parent",
						"parent_id",
						"company_id",
						"ids",
						"enabled",
						"type",
						"slug_name",
						"designation_id",
					],
					include: [
						{
							model: dbSAPPS.Company,
							as: "company",
							attributes: ["name"],
						},
						{
							model: dbSAPPS.Designation,
							as: "parent",
							attributes: ["display_name"],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["name"],
				},
			],
		});

		return {
			designationVersions,
			success: true,
			message: "Designation versions has been loaded!",
		};
	} catch (error) {
		console.error("Error retrieving designation versions: ", error);
		const errorMessage =
			error.message ||
			"Failed to retrieve designation versions. Please try again later.";

		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function option(params) {
	try {
		const today = new Date();

		const filterConditions = {};

		if (params?.company_id) {
			filterConditions.company_id = params.company_id;
		}

		filterConditions.valid_from = {
			[Op.lte]: params.valid_from || today,
		};

		filterConditions.valid_to = {
			[Op.or]: [
				{
					[Op.gte]: params.valid_to || today,
				},
				{
					[Op.eq]: null,
				},
			],
		};

		filterConditions.enabled = {
			[Op.eq]: true,
		};

		let designationVersions = [];
		designationVersions = await dbSAPPS.DesignationVersion.findAll({
			attributes: [
				"id",
				"name",
				"display_name",
				"slug_name",
				"valid_from",
				"valid_to",
				"company_id",
			],
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.DesignationVersionChild,
					as: "designationversionchilds",
					attributes: [
						"id",
						"designationversion_id",
						"code",
						"name",
						"display_name",
						"is_parent",
						"parent_id",
						"company_id",
						"ids",
						"enabled",
					],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "parent",
							attributes: ["display_name"],
						},
						{
							model: dbSAPPS.Company,
							as: "company",
							attributes: ["name"],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["name"],
				},
			],
		});

		const user = {
			employee: {
				id: params?.employee_id,
				company_id: params?.company_id,
				designation_id: params?.designation_id,
			},
		};

		const designationHierarchy = await getDesignationHierarchy(user);

		return {
			designationVersions,
			designationHierarchy,
			success: true,
			message: "Designation versions has been loaded!",
		};
	} catch (error) {
		console.error("Error retrieving designation versions: ", error);
		const errorMessage =
			error.message ||
			"Failed to retrieve designation versions. Please try again later.";

		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function count() {
	try {
		const count = await dbSAPPS.DesignationVersion.count({
			where: { enabled: true },
		});

		return {
			count,
			success: true,
			message: "Designation versions has been loaded!",
		};
	} catch (error) {
		console.error("Error counting designation versions: ", error);
		return {
			success: false,
			message:
				"Failed to retrieve count designation versions. Please try again later.",
		};
	}
}

async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();

	try {
		if (
			!params.name ||
			!params.display_name ||
			!params.valid_from ||
			!params.valid_to ||
			!params.company_id
		) {
			return {
				success: false,
				message:
					"Name, display name, valid from, valid to & company are required!",
			};
		}

		const existingName = await dbSAPPS.DesignationVersion.findOne({
			where: { name: params.name },
		});
		if (existingName) {
			return {
				success: false,
				message: `Name "${params.name}" is already taken.`,
			};
		}

		const existingDisplayName = await dbSAPPS.DesignationVersion.findOne({
			where: { display_name: params.display_name },
		});
		if (existingDisplayName) {
			return {
				success: false,
				message: `Display Name "${params.display_name}" is already taken.`,
			};
		}

		if (params.valid_from && params.valid_to && params.company_id) {
			const existingValidDate = await dbSAPPS.DesignationVersion.findOne({
				where: {
					company_id: params.company_id,
					valid_from: { [Op.lte]: params.valid_to },
					valid_to: { [Op.gte]: params.valid_from },
				},
			});

			if (existingValidDate) {
				return {
					success: false,
					message: `Designation version with valid from ${
						new Date(params.valid_from).toISOString().split("T")[0]
					} and valid to ${
						new Date(params.valid_to).toISOString().split("T")[0]
					} in company_id ${params.company_id} already exists.`,
				};
			}
		}

		if (!params.creation) params.creation = Date.now();
		if (!params.modified) params.modified = Date.now();
		if (!params.modified_by) params.modified_by = 1;
		if (!params.owner) params.owner = 1;

		const designationVersion = await dbSAPPS.DesignationVersion.create(params, {
			transaction,
		});
		if (params.designationversionchilds) {
			// Pastikan `designationversionchilds` adalah array yang valid
			if (typeof params.designationversionchilds === "string") {
				try {
					params.designationversionchilds = JSON.parse(
						params.designationversionchilds
					);
				} catch (err) {
					return {
						success: false,
						message: "Invalid JSON format for designationversionchilds",
					};
				}
			}

			if (
				Array.isArray(params.designationversionchilds) &&
				params.designationversionchilds.length > 0
			) {
				const duplicates = params.designationversionchilds.reduce(
					(acc, child, index, array) => {
						if (
							array.findIndex(
								(item) => item.display_name === child.display_name
							) !== index
						) {
							acc.push(child.display_name);
						}
						return acc;
					},
					[]
				);

				if (duplicates.length > 0) {
					await transaction.rollback();
					return {
						success: false,
						message: `Duplicate entries exist in designationversionchilds for display names: ${duplicates.join(
							", "
						)}. Transaction rolled back.`,
					};
				}

				// Sync data from master Designation before saving
				const syncedChilds = await syncDesignationVersionChildsFromMaster(
					params.designationversionchilds,
					transaction
				);

				const designationVersionsChildData = syncedChilds.map(
					(designationVersionChild) => ({
						...designationVersionChild,
						creation: Date.now(),
						modified: Date.now(),
						modified_by: params.modified_by || 1,
						owner: params.owner || 1,
						designationversion_id: designationVersion.id,
						parent_id:
							designationVersionChild.parent_id === 0 ||
							!designationVersionChild.parent_id
								? null
								: designationVersionChild.parent_id,
					})
				);

				await dbSAPPS.DesignationVersionChild.bulkCreate(
					designationVersionsChildData,
					{ transaction }
				);
			}
		}

		await transaction.commit();
		return {
			success: true,
			message: "Designation version has been created!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage =
			error.message || "An error occurred during creating designation version.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function update(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const designationVersion = await dbSAPPS.DesignationVersion.findByPk(id);
		if (!designationVersion)
			return {
				success: false,
				message: "Designation version not found!",
			};

		if (params.name && params.name !== designationVersion.name) {
			const existingName = await dbSAPPS.DesignationVersion.findOne({
				where: { name: params.name },
			});
			if (existingName) {
				return {
					success: false,
					message: `Name "${params.name}" is already taken. Try again!`,
				};
			}
		}

		if (
			params.display_name &&
			params.display_name !== designationVersion.display_name
		) {
			const existingDisplayName = await dbSAPPS.DesignationVersion.findOne({
				where: { display_name: params.display_name },
			});
			if (existingDisplayName) {
				return {
					success: false,
					message: `Display Name "${params.display_name}" is already taken. Try again!`,
				};
			}
		}

		if (
			params.valid_from &&
			params.valid_to &&
			params.company_id &&
			id !== designationVersion.id
		) {
			const existingValidDate = await dbSAPPS.DesignationVersion.findOne({
				where: {
					id: { [Op.ne]: designationVersion.id },
					company_id: params.company_id,
					valid_from: { [Op.lte]: params.valid_to },
					valid_to: { [Op.gte]: params.valid_from },
				},
			});
			if (existingValidDate)
				return {
					success: false,
					message: `Designation version with valid from ${
						new Date(params.valid_from).toISOString().split("T")[0]
					} and valid to ${
						new Date(params.valid_to).toISOString().split("T")[0]
					} in company_id ${params.company_id} already exists.`,
				};
		}

		await designationVersion.update(params, { transaction });
		if (params.designationversionchilds) {
			if (typeof params.designationversionchilds === "string") {
				try {
					params.designationversionchilds = JSON.parse(
						params.designationversionchilds
					);
				} catch (err) {
					return {
						success: false,
						message: "Invalid JSON format for designationversionchilds",
					};
				}
			}

			if (
				Array.isArray(params.designationversionchilds) &&
				params.designationversionchilds.length > 0
			) {
				const duplicateIds = params.designationversionchilds.reduce(
					(acc, child, index, array) => {
						const existing = array.findIndex(
							(item) => item.designation_id === child.designation_id
						);
						if (existing !== -1 && existing !== index) {
							acc.push(child.designation_id);
						}
						return acc;
					},
					[]
				);
				const duplicateDisplayNames = params.designationversionchilds.reduce(
					(acc, child, index, array) => {
						const existing = array.findIndex(
							(item) => item.display_name === child.display_name
						);
						if (existing !== -1 && existing !== index) {
							acc.push(child.display_name);
						}
						return acc;
					},
					[]
				);

				if (duplicateIds.length > 0 || duplicateDisplayNames.length > 0) {
					await transaction.rollback();
					const errorMessage = [];
					if (duplicateIds.length > 0) {
						errorMessage.push(
							`duplicate designation id: ${duplicateIds.join(", ")}`
						);
					}
					if (duplicateDisplayNames.length > 0) {
						errorMessage.push(
							`duplicate display names: ${duplicateDisplayNames.join(", ")}`
						);
					}
					return {
						success: false,
						message: `Duplicate entries exist in designationversionchilds. ${errorMessage.join(
							", "
						)}. Transaction rolled back.`,
					};
				}

				// Sync data from master Designation before saving
				const syncedChilds = await syncDesignationVersionChildsFromMaster(
					params.designationversionchilds,
					transaction
				);

				await dbSAPPS.DesignationVersionChild.destroy({
					where: { designationversion_id: id },
					transaction,
				});
				const designationVersionsChildData = syncedChilds.map(
					(designationVersionChild) => ({
						...designationVersionChild,
						creation: Date.now(),
						modified: Date.now(),
						modified_by: params.modified_by || 1,
						owner: params.owner || 1,
						designationversion_id: parseInt(id),
						parent_id:
							designationVersionChild.parent_id === 0 ||
							!designationVersionChild.parent_id
								? null
								: designationVersionChild.parent_id,
					})
				);

				try {
					await dbSAPPS.DesignationVersionChild.bulkCreate(
						designationVersionsChildData,
						{ transaction }
					);
				} catch (err) {
					await transaction.rollback();
					return {
						success: false,
						message: `Error saving designationversionchilds: ${
							err.message || "Unknown error"
						}`,
					};
				}
			} else {
				await dbSAPPS.DesignationVersionChild.destroy({
					where: { designationversion_id: id },
					transaction,
				});
			}
		}

		await transaction.commit();
		return {
			success: true,
			message: "Designation version has been updated!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage =
			error.message || "Failed to update designation version.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function enabled(id, params) {
	try {
		const designationVersion = await dbSAPPS.DesignationVersion.findByPk(id);
		if (!designationVersion)
			return {
				success: false,
				message: "Designation version not found!",
			};

		const updateData = {};
		updateData.enabled = params.enabled;
		if (params.modified_by) updateData.modified_by = params.modified_by;
		if (params.modified) updateData.modified = params.modified;

		await designationVersion.update(updateData);

		return {
			success: true,
			message: "Designation version has been updated!",
		};
	} catch (error) {
		const errorMessage =
			error.message || "Failed to update designation version.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function _delete(id) {
	try {
		const designationVersion = await dbSAPPS.DesignationVersion.findByPk(id);
		if (!designationVersion)
			return { success: false, message: "Designation version not found!" };

		await designationVersion.destroy();
		return {
			success: true,
			message: "Designation version has been deleted!",
		};
	} catch (error) {
		const errorMessage =
			error.message || "Failed to delete designation version.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}
