const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
const workflowService = require("../workflows/workflow.service");
require("dotenv").config();

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

async function getAll(params) {
	try {
		const filterConditions = {};
		const andConditions = [];

		if (params.is_workflow_direct === true || params.is_workflow_direct === "true") {
			if (params.doc_id) {
				filterConditions.id = {
					[Op.eq]: parseInt(params.doc_id, 10),
				};
			}
		} else {
			if (
				params.access_read_all_data !== true &&
				params.access_read_all_data !== "true" &&
				params.administrator_of_hr !== true &&
				params.administrator_of_hr !== "true"
			) {
				if (params.employeesId && params.employeesId.length > 0) {
					filterConditions.employee_id = {
						[Op.in]: params.employeesId.map((item) => parseInt(item, 10)),
					};
				} else if (params.employee_id) {
					filterConditions.employee_id = {
						[Op.eq]: parseInt(params.employee_id, 10),
					};
				}
			}

			if (params.company_id) {
				filterConditions.company_id = { [Op.eq]: parseInt(params.company_id, 10) };
			} else if (params.companiesId && params.companiesId.length > 0) {
				filterConditions.company_id = {
					[Op.in]: params.companiesId.map((id) => parseInt(id, 10)),
				};
			}

			if (params.from_date && params.to_date) {
				andConditions.push({
					[Op.and]: [{ from_date: { [Op.lte]: params.to_date } }, { to_date: { [Op.gte]: params.from_date } }],
				});
			}

			if (params.q) {
				andConditions.push({
					[Op.or]: [
						{ reason: { [Op.like]: `%${params.q}%` } },
						{ explanation: { [Op.like]: `%${params.q}%` } },
						{ notes: { [Op.like]: `%${params.q}%` } },
						{ "$employee.employee_name$": { [Op.like]: `%${params.q}%` } },
						{ "$company.display_name$": { [Op.like]: `%${params.q}%` } },
					],
				});
			}

			if (params.f_employee_name) {
				filterConditions["$employee.employee_name$"] = {
					[Op.like]: `%${params.f_employee_name}%`,
				};
			}
			if (params.f_company_name) {
				filterConditions["$company.display_name$"] = {
					[Op.like]: `%${params.f_company_name}%`,
				};
			}
		}

		const whereClause = {
			...filterConditions,
			...(andConditions.length ? { [Op.and]: andConditions } : {}),
		};

		const rawSize = parseInt(params?.pageSize, 10);
		const pageSize = Number.isFinite(rawSize) && rawSize > 0 ? Math.min(rawSize, 100) : 10;
		const rawIndex = parseInt(params?.pageIndex, 10);
		const pageIndex = Number.isFinite(rawIndex) && rawIndex >= 0 ? rawIndex : 0;

		const allowedSortBy = ["modified", "creation", "from_date", "to_date"];
		const sortBy = allowedSortBy.includes(params?.sortBy) ? params.sortBy : "modified";
		const sortDir = (params?.sortDir || "DESC").toString().toUpperCase() === "ASC" ? "ASC" : "DESC";

		const { rows: permitEases, count: totalCount } = await dbSAPPS.PermitEase.findAndCountAll({
			where: whereClause,
			order: [[sortBy, sortDir]],
			offset: pageIndex * pageSize,
			limit: pageSize,
			distinct: true,
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
			],
		});

		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: { [Op.in]: permitEases.map((item) => item.id) },
				doctype_id: 70,
			},
			attributes: ["id", "doc_id", "doctype_id", "status"],
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
						},
					],
				},
			],
		});

		const permitEasesWithWorkflow = permitEases.map((item) => {
			const workflow = workflows.find((wf) => wf.doc_id === item.id);
			return {
				...item.dataValues,
				workflow: workflow ? workflow.dataValues : null,
				employee_name: item.employee && item.employee.dataValues ? item.employee.dataValues.employee_name : null,
			};
		});

		return {
			permitEases: permitEasesWithWorkflow,
			totalCount,
			pageIndex,
			pageSize,
			success: true,
			message: "permit eases loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to load permit eases!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function getByApprover(params) {
	try {
		const filterConditions = {};
		const filterConditionsPermitEase = {};

		if (params.designationIds) {
			filterConditions.designation_id = {
				[Op.in]: params.designationIds.map((id) => parseInt(id)),
			};
		}

		if (params.territoryIds) {
			filterConditions.territory_id = {
				[Op.in]: params.territoryIds.map((id) => parseInt(id)),
			};
		}

		const workflows = await dbSAPPS.Workflow.findAll({
			attributes: ["id", "doc_id", "doctype_id", "status"],
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
					where: filterConditions,
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
						},
					],
				},
			],
		});

		const filterConditionsPermitEaseOr = [];

		if (params.company_id) {
			filterConditionsPermitEase.company_id = {
				[Op.eq]: parseInt(params.company_id),
			};
		}

		if (workflows) {
			filterConditionsPermitEaseOr.push({
				id: {
					[Op.in]: workflows.map((item) => item.doc_id),
				},
			});
		}

		if (params.owner) {
			filterConditionsPermitEaseOr.push({
				owner: {
					[Op.eq]: parseInt(params.owner),
				},
			});
		}

		if (filterConditionsPermitEaseOr.length > 0) {
			filterConditionsPermitEase[Op.or] = filterConditionsPermitEaseOr;
		}

		const permitEases = await dbSAPPS.PermitEase.findAll({
			where: filterConditionsPermitEase,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "name", "display_name"],
				},
			],
		});

		let workflowsowners = [];
		if (!workflows || workflows.length === 0) {
			workflowsowners = await dbSAPPS.Workflow.findAll({
				where: {
					doc_id: {
						[Op.in]: permitEases.map((item) => item.id),
					},
					doctype_id: 70,
				},
				attributes: ["id", "doc_id", "doctype_id", "status"],
				include: [
					{
						model: dbSAPPS.WorkflowApprover,
						as: "workflowapprovers",
						attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
						include: [
							{
								model: dbSAPPS.Designation,
								as: "designation",
								attributes: ["id", "name", "display_name"],
							},
							{
								model: dbSAPPS.Territory,
								as: "territory",
								attributes: ["id", "name", "display_name"],
							},
						],
					},
				],
			});
		}

		const modifiedBy = await dbSAPPS.User.findAll({
			attributes: ["id", "full_name"],
			order: [["full_name", "ASC"]],
		});

		const permitEasesWithWorkflow = permitEases.map((item) => {
			const workflow = workflows.find((wf) => wf.doc_id === item.id);
			const workflowsowner = workflowsowners.find((wf) => wf.doc_id === item.id);

			return {
				...item.dataValues,
				modified_by_full_name: modifiedBy && modifiedBy.filter((modifiedBy) => modifiedBy.id === item.modified_by).map((modifiedBy) => modifiedBy.full_name)[0],
				workflow: workflow ? workflow.dataValues : workflowsowner ? workflowsowner.dataValues : null,
				employee_name: item.dataValues.employee ? item.dataValues.employee.dataValuesemployee_name : null,
			};
		});

		return {
			permitEases: permitEasesWithWorkflow,
			success: true,
			message: "permit eases has been loaded!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to load permit eases!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

	if (params.from_date) {
		filterConditions.from_date = {
			[Op.gte]: params.from_date,
		};
	}

	if (params.to_date) {
		filterConditions.to_date = {
			[Op.lte]: params.to_date,
		};
	}

	try {
		const permitEases = await dbSAPPS.PermitEase.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
		});

		return {
			success: true,
			permitEases,
			message: "permit eases loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to retrieve permitEases. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function option() {
	try {
		const permitEases = await dbSAPPS.PermitEase.findAll({
			attributes: ["id", "employee_id", "from_date", "to_date"],
			order: [["modified", "ASC"]],
		});

		return {
			success: true,
			permitEases,
			message: "permit eases loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to retrieve permitEases. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function count() {
	try {
		const count = await dbSAPPS.PermitEase.count();

		return {
			success: true,
			count,
			message: "permit eases loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to retrieve permitEases. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		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 existing = await dbSAPPS.PermitEase.findOne({
			where: {
				employee_id: params.employee_id,
				from_date: params.from_date,
				to_date: params.to_date,
			},
		});

		if (existing && !params.half_day_date)
			return {
				success: false,
				message: `permit ease with employee id "${params.employee_id}", from date "${params.from_date}", to date "${params.to_date}" is already taken.`,
			};

		const existingHalfDay = await dbSAPPS.PermitEase.findOne({
			where: {
				employee_id: params.employee_id,
				half_day_date: params.half_day_date,
			},
		});

		if (existingHalfDay && (!params.from_date || !params.to_date))
			return {
				success: false,
				message: `permit ease with employee id "${params.employee_id}", half day date "${params.half_day_date}" is already taken.`,
			};

		const permitEase = await dbSAPPS.PermitEase.create(params, { transaction });

		if (params.workflowterritories) {
			if (Array.isArray(params.workflowterritories) && params.workflowterritories.length > 0) {
				const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
					{
						where: {
							doctype_id: parseInt(params.doctype_id),
							type: "Territory",
							enabled: true,
						},
					},
					{ transaction },
				);

				const limitWorkflowTerritories = workflowSettings[0]?.limit || 0;
				const workflowTerritories = params.workflowterritories.slice(0, limitWorkflowTerritories);

				if (workflowTerritories.length === 0) {
					await transaction.rollback();
					return {
						success: false,
						message: "You must have at least one workflow territory.",
					};
				}

				const workflowData = {
					creation: Date.now(),
					modified: Date.now(),
					modified_by: parseInt(params.modified_by) || 1,
					owner: parseInt(params.owner) || 1,
					doctype_id: parseInt(params.doctype_id),
					doc_id: permitEase.id,
					status: "Draft",
				};

				const workflow = await dbSAPPS.Workflow.create(workflowData, { transaction });

				if (!workflow) {
					await transaction.rollback();
					return {
						success: false,
						message: "Failed to create workflow. Please try again later.",
					};
				}

				const workflowApproversData = workflowTerritories.map((workflowTerritory) => ({
					creation: Date.now(),
					modified: Date.now(),
					modified_by: parseInt(params.modified_by) || 1,
					owner: parseInt(params.owner) || 1,
					workflow_id: workflow.id,
					territory_id: parseInt(workflowTerritory.territory_id),
					status: null,
				}));

				const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, { transaction });

				if (
					!approvers ||
					approvers.length !== workflowTerritories.length ||
					!approvers.every((approver) => approver.workflow_id === workflow.id && approver.territory_id !== null)
				) {
					await transaction.rollback();
					return {
						success: false,
						message: "Failed to create workflow approvers. Please try again later.",
					};
				}
			}
		} else {
			if (params.workflowdesignations) {
				if (Array.isArray(params.workflowdesignations) && params.workflowdesignations.length > 0) {
					const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
						{
							where: {
								doctype_id: params.doctype_id,
								type: "Designation",
								enabled: true,
							},
						},
						{ transaction },
					);

					const limitWorkflowDesignations = workflowSettings[0]?.limit || 0;

					const workflowDesignations = params.workflowdesignations.slice(0, limitWorkflowDesignations);

					if (workflowDesignations.length === 0) {
						await transaction.rollback();
						return {
							success: false,
							message: "You must have at least one workflow designation.",
						};
					}

					const workflowData = {
						creation: Date.now(),
						modified: Date.now(),
						modified_by: parseInt(params.modified_by) || 1,
						owner: parseInt(params.owner) || 1,
						doctype_id: parseInt(params.doctype_id),
						doc_id: permitEase.id,
						status: "Draft",
					};

					let workflow = await dbSAPPS.Workflow.create(workflowData, {
						transaction,
						lock: transaction.LOCK.UPDATE,
					});

					if (!workflow) {
						await transaction.rollback();
						return {
							success: false,
							message: "Failed to create workflow. Please try again later.",
						};
					}

					const workflowApproversData = workflowDesignations.map((workflowDesignation) => ({
						creation: Date.now(),
						modified: Date.now(),
						modified_by: parseInt(params.modified_by) || 1,
						owner: parseInt(params.owner) || 1,
						workflow_id: workflow.id,
						designation_id: workflowDesignation.designation_id,
						status: null,
					}));

					let approvers = await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, { transaction, lock: transaction.LOCK.UPDATE });

					if (
						!approvers ||
						approvers.length !== workflowDesignations.length ||
						!approvers.every((approver) => approver.workflow_id === workflow.id && approver.designation_id !== null)
					) {
						await transaction.rollback();
						return {
							success: false,
							message: "Failed to create workflow approvers. Please try again later.",
						};
					}
				}
			}
		}

		if (!params.workflowdesignations && !params.workflowterritories) {
			await transaction.rollback();
			return {
				success: false,
				message: "You must have at least one workflow designation or workflow territory.",
			};
		}

		await transaction.commit();
		return {
			success: true,
			message: "permit ease data has been successfully added!",
		};
	} catch (error) {
		const errorMessage = error.message || "An error occurred during create permit ease processing.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function update(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const permitEase = await dbSAPPS.PermitEase.findByPk(id, { transaction, lock: transaction.LOCK.UPDATE });
		if (!permitEase)
			return {
				success: false,
				message: "permit ease not found!",
			};

		const existing = await dbSAPPS.PermitEase.findOne({
			where: {
				employee_id: params.employee_id,
				from_date: params.from_date,
				to_date: params.to_date,
			},
		});

		if (existing && !params.half_day_date)
			return {
				success: false,
				message: `permit ease with employee id "${params.employee_id}", from date "${params.from_date}", to date "${params.to_date}" is already taken.`,
			};

		const existingHalfDay = await dbSAPPS.PermitEase.findOne({
			where: {
				employee_id: params.employee_id,
				half_day_date: params.half_day_date,
			},
		});

		if (existingHalfDay && (!params.from_date || !params.to_date))
			return {
				success: false,
				message: `permit ease with employee id "${params.employee_id}", start date "${params.start_date}", end date "${params.end_date}" is already taken.`,
			};

		await permitEase.update(params, { transaction });

		params.permiteases = {
			...params,
			id: parseInt(params.id),
			modified_by: parseInt(params.modified_by) || 1,
			owner: parseInt(params.owner) || 1,
			half_day: params.half_day === "true" ? true : false,
			half_day_date: params.half_day_date === "0000-00-00" ? null : params.half_day_date,
			from_date: params.from_date === "0000-00-00" ? null : params.from_date,
			to_date: params.to_date === "0000-00-00" ? null : params.to_date,
			employee_id: parseInt(params.employee_id),
			company_id: parseInt(params.company_id),
			doctype_id: parseInt(params.doctype_id),
		};

		let createdAttendanceIds = [];

		if (params.workflow?.status === "Approved" && (params.administrator_of_hr === true || params.administrator_of_hr === "true")) {
			if (params.permiteases) {
				if (params.permiteases.from_date && params.permiteases.to_date) {
					if (params.permiteases.to_date < params.permiteases.from_date) {
						return {
							success: false,
							message: "to_date cannot be less than from_date",
						};
					} else {
						const startDate = moment(params && params.permiteases && params.permiteases.from_date);
						const endDate = moment(params && params.permiteases && params.permiteases.to_date);
						const dates = [];

						let totalHour = 0;
						const inTimeHour = 9;
						const inTimeMinute = 0;
						const outTimeHour = 18;
						const outTimeMinute = 0;

						const inTimeTotalMinute = inTimeHour * 60 + inTimeMinute;
						const outTimeTotalMinute = outTimeHour * 60 + outTimeMinute;

						totalHour = (outTimeTotalMinute - inTimeTotalMinute - 60) / 60;
						totalHour = Math.round(totalHour);

						while (startDate <= endDate) {
							dates.push(startDate.format("YYYY-MM-DD"));
							startDate.add(1, "day");
						}

						const foundAttendances = await dbSAPPS.Attendance.findAll({
							where: {
								employee_id: params.permiteases.employee_id,
								date: {
									[Op.in]: dates,
								},
							},
							lock: transaction.LOCK.UPDATE,
							transaction,
						});

						const employee = await dbSAPPS.Employee.findByPk(params.permiteases.employee_id, {
							attributes: ["shifttype_id"],
							lock: transaction.LOCK.UPDATE,
							transaction,
						});
						if (!employee) {
							return {
								success: false,
								message: "Employee not found!",
							};
						}
						const shifttype_id = employee.shifttype_id;

						if (foundAttendances.length) {
							const updatedAttendance = foundAttendances.map((attendance) => ({
								...attendance,
								modified: Date.now(),
								modified_by: params.permiteases.modified_by,
								permitease_id: params.permiteases.id ? params.permiteases.id : attendance.permitease_id,
								status_permitease: true,
								need_clarification: false,
								in_time: "09:00:00",
								out_time: "18:00:00",
								in_status: "On Time",
								out_status: "On Time",
								reason: params.permiteases.reason,
								notes: params.permiteases.notes,
								filename: params.permiteases.filename,
								id: attendance.id,
								total_hour: totalHour,
							}));

							const filteredUpdatedAttendance = updatedAttendance.filter((updatedAttendanceItem) => {
								const att = foundAttendances.find((a) => a.id === updatedAttendanceItem.id);
								if (!att) return true;
								if (att.absenceclarification_id && att.status_absenceclarification) return false;
								if (att.leaveapplication_id != null && att.leaveapplication_id !== 0) return false;
								if (att.attendancerequest_id != null && att.attendancerequest_id !== 0) return false;
								return true;
							});

							if (filteredUpdatedAttendance.length === 0) {
								return {
									success: false,
									message:
										"All attendance dates have been given absence clarification, or are already linked to Leave Application or Attendance Request. Cannot apply Permit Ease to those dates.",
								};
							}

							await dbSAPPS.Attendance.bulkCreate(filteredUpdatedAttendance, {
								updateOnDuplicate: [
									"modified",
									"modified_by",
									"permitease_id",
									"status_permitease",
									"need_clarification",
									"in_time",
									"out_time",
									"in_status",
									"out_status",
									"reason",
									"notes",
									"filename",
									"total_hour",
								],
								individualHooks: true,
								returning: true,
								lock: transaction.LOCK.UPDATE,
								transaction,
							});
							for (let i = 0; i < filteredUpdatedAttendance.length; i++) {
								await dbSAPPS.PermitEase.update({ attendance_id: filteredUpdatedAttendance[i].id }, { where: { id: params.permiteases.id }, transaction });
							}
						} else {
							const createAttendances = dates.map((date) => ({
								date: date,
								status: "Permit Ease",
								employee_id: params.permiteases.employee_id,
								permitease_id: params.permiteases.id,
								status_permitease: true,
								need_clarification: false,
								in_time: "09:00:00",
								out_time: "18:00:00",
								in_status: "On Time",
								out_status: "On Time",
								company_id: params.permiteases.company_id,
								shifttype_id: shifttype_id,
								reason: params.permiteases.reason,
								notes: params.permiteases.notes,
								filename: params.permiteases.filename,
								creation: Date.now(),
								modified: Date.now(),
								modified_by: params.permiteases.modified_by,
								owner: params.permiteases.owner,
								total_hour: totalHour,
							}));

							const createdAttendances = await dbSAPPS.Attendance.bulkCreate(createAttendances, { lock: transaction.LOCK.UPDATE, transaction });
							createdAttendanceIds = createdAttendances.map((attendance) => attendance.id);

							for (let i = 0; i < createdAttendanceIds.length; i++) {
								await dbSAPPS.PermitEase.update({ attendance_id: createdAttendanceIds[i] }, { where: { id: params.permiteases.id }, transaction });
							}
						}
					}
				} else if (params.permiteases.half_day_date && params.permiteases.half_day === true) {
					const foundAttendance = await dbSAPPS.Attendance.findOne({
						where: {
							date: params.permiteases.half_day_date,
							employee_id: params.permiteases.employee_id,
						},
						lock: transaction.LOCK.UPDATE,
						transaction,
					});

					if (foundAttendance) {
						const attObj = foundAttendance.dataValues || foundAttendance;
						const updatedAttendance = [
							{
								...attObj,
								modified: Date.now(),
								modified_by: params.permiteases.modified_by,
								permitease_id: params.permiteases.id ? params.permiteases.id : attObj.permitease_id,
								status_permitease: true,
								need_clarification: false,
								in_time: params.permiteases.half_day_type === "Morning to Afternoon" ? "09:00:00" : "13:00:00",
								out_time: params.permiteases.half_day_type === "Morning to Afternoon" ? "12:00:00" : "18:00:00",
								in_status: "On Time",
								out_status: "On Time",
								reason: params.permiteases.reason,
								notes: params.permiteases.notes,
								filename: params.permiteases.filename,
								id: attObj.id,
								total_hour: totalHour,
							},
						];

						const hasAbsenceClarification = attObj.absenceclarification_id && attObj.status_absenceclarification;
						const hasLAorAR =
							(attObj.leaveapplication_id != null && attObj.leaveapplication_id !== 0) ||
							(attObj.attendancerequest_id != null && attObj.attendancerequest_id !== 0);
						const filteredUpdatedAttendance = hasAbsenceClarification || hasLAorAR ? [] : updatedAttendance;

						if (filteredUpdatedAttendance.length === 0) {
							return {
								success: false,
								message:
									"This date has been given absence clarification or is already linked to Leave Application or Attendance Request. Cannot apply Permit Ease.",
							};
						}

						await dbSAPPS.Attendance.bulkCreate(filteredUpdatedAttendance, {
							updateOnDuplicate: [
								"modified",
								"modified_by",
								"permitease_id",
								"status_permitease",
								"need_clarification",
								"in_time",
								"out_time",
								"in_status",
								"out_status",
								"reason",
								"notes",
								"filename",
								"total_hour",
							],
							individualHooks: true,
							returning: true,
							lock: transaction.LOCK.UPDATE,
							transaction,
						});
						await dbSAPPS.PermitEase.update({ attendance_id: attObj.id }, { where: { id: params.permiteases.id }, transaction });
					} else {
						const createAttendances = {
							date: params.permiteases.half_day_date,
							status: "Permit Ease",
							employee_id: params.permiteases.employee_id,
							permitease_id: params.permiteases.id,
							status_permitease: true,
							need_clarification: false,
							in_time: params.permiteases.half_day_type === "Morning to Afternoon" ? "09:00:00" : "13:00:00",
							out_time: params.permiteases.half_day_type === "Morning to Afternoon" ? "12:00:00" : "18:00:00",
							in_status: "On Time",
							out_status: "On Time",
							company_id: params.permiteases.company_id,
							shifttype_id: shifttype_id,
							reason: params.permiteases.reason,
							notes: params.permiteases.notes,
							filename: params.permiteases.filename,
							creation: Date.now(),
							modified: Date.now(),
							modified_by: params.permiteases.modified_by,
							owner: params.permiteases.owner,
							total_hour: totalHour,
						};
						const createdAttendances = await dbSAPPS.Attendance.bulkCreate(createAttendances, { lock: transaction.LOCK.UPDATE, transaction });
						createdAttendanceIds = createdAttendances.map((attendance) => attendance.id);

						for (let i = 0; i < createdAttendanceIds.length; i++) {
							await dbSAPPS.PermitEase.update({ attendance_id: createdAttendanceIds[i] }, { where: { id: params.permiteases.id }, transaction });
						}
					}
				}
			}
		}

		await transaction.commit();

		// Bridge: if caller updates workflow status through this direct-update path,
		// sync it to workflow engine so it shows up in Inbox and triggers email/notifications.
		const requestedWorkflowStatus = params.workflow?.status;
		let workflowSync = null;

		if (requestedWorkflowStatus) {
			try {
				const workflowRow = await dbSAPPS.Workflow.findOne({
					where: { doc_id: parseInt(id, 10), doctype_id: 70 },
					attributes: ["id", "status"],
				});

				if (workflowRow && workflowRow.status !== requestedWorkflowStatus) {
					workflowSync = await workflowService.actionToWorkflow(workflowRow.id, {
						status: requestedWorkflowStatus,
						doctype_id: 70,
						doc_id: parseInt(id, 10),
						modified: params.modified || new Date(),
						modified_by: params.modified_by,
						owner: params.owner,
						...(params.workflow?.rejection_reason != null && {
							rejection_reason: params.workflow.rejection_reason,
						}),
						...(params.workflow?.revision_notes != null && {
							revision_notes: params.workflow.revision_notes,
						}),
						...(params.workflow?.cancel_reason != null && {
							cancel_reason: params.workflow.cancel_reason,
						}),
					});
				} else if (!workflowRow) {
					workflowSync = {
						success: false,
						message: "Workflow row not found for this Permit Ease (doctype_id 70).",
					};
				}
			} catch (workflowError) {
				workflowSync = {
					success: false,
					message: workflowError?.message || "Failed to sync workflow.",
				};
			}
		}

		return {
			success: true,
			message: "Data permit ease has been saved!",
			workflowSync,
		};
	} catch (error) {
		try {
			if (!transaction.finished) await transaction.rollback();
		} catch {
			// ignore rollback errors
		}
		const errorMessage = error.message || "Failed to update permit ease data.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const permitEase = await dbSAPPS.PermitEase.findByPk(id, { transaction, lock: transaction.LOCK.UPDATE });
		if (!permitEase) return { success: false, message: "permit ease not found!" };

		await dbSAPPS.WorkflowApprover.destroy({
			where: {
				workflow_id: {
					[Op.in]: (await dbSAPPS.Workflow.findAll({ where: { doc_id: permitEase.id, doctype_id: 70 }, attributes: ["id"], raw: true })).map((item) => item.id),
				},
			},
			transaction,
		});
		await dbSAPPS.Workflow.destroy({ where: { doc_id: permitEase.id, doctype_id: 70 }, transaction });

		await permitEase.destroy({ transaction });
		await transaction.commit();
		return {
			success: true,
			message: "Permit ease successfully deleted!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "Failed to delete permit ease.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function createWorkflow(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const existingWorkflow = await dbSAPPS.Workflow.findOne({
			where: { doc_id: parseInt(params.doc_id), doctype_id: parseInt(params.doctype_id) },
			transaction,
		});
		if (existingWorkflow) {
			await dbSAPPS.WorkflowApprover.destroy({ where: { workflow_id: existingWorkflow.id }, transaction });
			await existingWorkflow.destroy({ transaction });
		}

		if (params.territory_id) {
			const workflowData = {
				creation: Date.now(),
				modified: Date.now(),
				modified_by: parseInt(params.modified_by) || 1,
				owner: parseInt(params.owner) || 1,
				doctype_id: parseInt(params.doctype_id),
				doc_id: parseInt(params.doc_id),
				status: params.workflow_status ? params.workflow_status : "Draft",
			};

			const workflow = await dbSAPPS.Workflow.create(workflowData, { transaction, lock: transaction.LOCK.UPDATE });

			if (!workflow) {
				return {
					success: false,
					message: "Failed to create workflow. Please try again later.",
				};
			}

			const workflowApproversData = {
				creation: Date.now(),
				modified: Date.now(),
				modified_by: parseInt(params.modified_by) || 1,
				owner: parseInt(params.owner) || 1,
				workflow_id: workflow.id,
				territory_id: parseInt(params.territory_id),
				status: params.approver_status ? params.approver_status : null,
			};

			const approvers = await dbSAPPS.WorkflowApprover.bulkCreate([workflowApproversData], { transaction, lock: transaction.LOCK.UPDATE });

			if (
				!approvers ||
				approvers.length !== 1 ||
				!approvers.every((approver) => approver.workflow_id === workflow.id && approver.territory_id === parseInt(params.territory_id))
			) {
				await transaction.rollback();
				return {
					success: false,
					message: "Failed to create workflow approver. Please try again later.",
				};
			}
		} else {
			if (params.designation_id) {
				const workflowData = {
					creation: Date.now(),
					modified: Date.now(),
					modified_by: parseInt(params.modified_by) || 1,
					owner: parseInt(params.owner) || 1,
					doctype_id: parseInt(params.doctype_id),
					doc_id: parseInt(params.doc_id),
					status: params.workflow_status ? params.workflow_status : "Draft",
				};

				const workflow = await dbSAPPS.Workflow.create(workflowData, { transaction, lock: transaction.LOCK.UPDATE });

				if (!workflow) {
					return {
						success: false,
						message: "Failed to create workflow. Please try again later.",
					};
				}

				const workflowApproversData = {
					creation: Date.now(),
					modified: Date.now(),
					modified_by: parseInt(params.modified_by) || 1,
					owner: parseInt(params.owner) || 1,
					workflow_id: workflow.id,
					designation_id: parseInt(params.designation_id),
					status: params.approver_status ? params.approver_status : null,
				};

				let approvers = await dbSAPPS.WorkflowApprover.bulkCreate([workflowApproversData], { transaction, lock: transaction.LOCK.UPDATE });

				if (
					!approvers ||
					approvers.length !== 1 ||
					!approvers.every((approver) => approver.workflow_id === workflow.id && approver.designation_id === parseInt(params.designation_id))
				) {
					await transaction.rollback();
					return {
						success: false,
						message: "Failed to create workflow approvers. Please try again later.",
					};
				}
			}
		}

		await transaction.commit();
		return {
			success: true,
			message: "Workflow has been created!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to create workflow.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}
