const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const moment = require("moment");
const redisClient = require("../../../../redisClient");
const nodemailer = require("nodemailer");
const emailTemplateNeedApprovalAttendanceRequest = require("../attendanceRequests/emailTemplates/email.needapproval");
const emailTemplateNeedApprovalLeaveApplication = require("../leaveApplications/emailTemplates/email.needapproval");
const emailNotificationService = require("../notifications/emailNotification.service");

const DELIVERY_TRIP_NEED_APPROVAL_NOTIFICATION_TYPE = "DELIVERY_TRIP_NEED_APPROVAL";
const WORK_ACTIVITY_NEED_APPROVAL_NOTIFICATION_TYPE = "WORK_ACTIVITY_NEED_APPROVAL";
const WORK_ACTIVITY_PLAN_NEED_APPROVAL_NOTIFICATION_TYPE = "WORK_ACTIVITY_PLAN_NEED_APPROVAL";

// Doctype IDs for workflow inbox: support both legacy (110,111,230) and UI/doc_types (207,69,209) so filtering and sync work regardless of which ID is stored.
const ATTENDANCE_REQUEST_DOCTYPE_IDS = [110, 207];
const LEAVE_APPLICATION_DOCTYPE_IDS = [111, 69];
const WORK_ACTIVITY_DOCTYPE_IDS = [230, 209];
const WORK_ACTIVITY_PLAN_DOCTYPE_IDS = [246];

async function getApproverEmployeeAndEmail(activeApprover, transaction) {
	let recipientEmployeeId = null;
	let recipientEmail = null;
	let approverLabel = null;
	if (!activeApprover) return { recipientEmployeeId, recipientEmail, approverLabel };
	const includeUser = { model: dbSAPPS.User, as: "user", attributes: ["id", "email"] };
	if (activeApprover.designation_id) {
		const designation = await dbSAPPS.Designation.findByPk(activeApprover.designation_id, {
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "company_email"],
					include: [includeUser],
					limit: 1,
				},
			],
			transaction,
		});
		const emp = Array.isArray(designation?.employee) ? designation?.employee?.[0] : designation?.employee;
		recipientEmployeeId = emp?.id || null;
		recipientEmail = emp?.user?.email || emp?.company_email || null;
		approverLabel = designation?.display_name || null;
	} else if (activeApprover.territory_id) {
		const territory = await dbSAPPS.Territory.findByPk(activeApprover.territory_id, {
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employees",
					attributes: ["id", "employee_name", "company_email"],
					include: [includeUser],
					limit: 1,
				},
			],
			transaction,
		});
		const emp = territory?.employees?.[0];
		recipientEmployeeId = emp?.id || null;
		recipientEmail = emp?.user?.email || emp?.company_email || null;
		approverLabel = territory?.display_name || null;
	}
	return { recipientEmployeeId, recipientEmail, approverLabel };
}

async function notifyWorkActivityNeedApproval({ transaction, workflowId, docId, workflowApprovers, modifiedBy }) {
	try {
		const activeApprover = Array.isArray(workflowApprovers)
			? workflowApprovers.find((a) => a?.status === "Need Approval" || !a?.status)
			: workflowApprovers?.[0];
		if (!activeApprover) return;
		const { recipientEmployeeId, recipientEmail, approverLabel } = await getApproverEmployeeAndEmail(activeApprover, transaction);
		if (recipientEmployeeId) {
			const existingNotif = await dbSAPPS.Notification.findOne({
				where: {
					workflow_id: workflowId,
					employee_id: recipientEmployeeId,
					notification_type: WORK_ACTIVITY_NEED_APPROVAL_NOTIFICATION_TYPE,
				},
				transaction,
			});
			if (!existingNotif) {
				await dbSAPPS.Notification.create(
					{
						creation: new Date(),
						modified: new Date(),
						modified_by: modifiedBy || 1,
						owner: modifiedBy || 1,
						workflow_id: workflowId,
						employee_id: recipientEmployeeId,
						notification_type: WORK_ACTIVITY_NEED_APPROVAL_NOTIFICATION_TYPE,
						recipient_emails: recipientEmail ? [recipientEmail] : null,
						additional_recipients: null,
						is_sent: false,
						is_read: false,
						sentAt: null,
					},
					{ transaction },
				);
			}
		}
		if (recipientEmail) {
			const workActivity = await dbSAPPS.WorkActivity.findByPk(docId, {
				include: [
					{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name", "website"], required: false },
					{ model: dbSAPPS.Employee, as: "employee", attributes: ["id", "employee_name"] },
				],
				transaction,
			});
			if (workActivity?.company_id) {
				const docLabel = workActivity?.activity_date ? `Work Activity ${moment(workActivity.activity_date).format("YYYY-MM-DD")}` : `Work Activity #${docId}`;
				const companyName = workActivity?.company?.display_name || workActivity?.company?.name || "Company";
				const website = workActivity?.company?.website || "";
				const employeeName = workActivity?.employee?.employee_name || "Employee";
				const subject = `Work Activity Need Approval (${employeeName} - ${workActivity?.activity_date ? moment(workActivity.activity_date).format("YYYY-MM-DD") : docId})`;
				const html = `
					<div style="font-family: Arial, sans-serif; line-height: 1.5;">
						<p>Hi${approverLabel ? ` ${approverLabel}` : ""},</p>
						<p><b>${docLabel}</b> (${employeeName}) membutuhkan approval.</p>
						<p>Company: <b>${companyName}</b></p>
						${website ? `<p>Website: ${website}</p>` : ""}
						<p>Silakan buka SAPPS untuk melakukan approval.</p>
					</div>
				`;
				try {
					await emailNotificationService.sendEmailNotification({
						company_id: workActivity.company_id,
						to: recipientEmail,
						subject,
						html,
						modified_by: modifiedBy || 1,
						owner: modifiedBy || 1,
					});
				} catch (emailError) {
					console.error("Error sending email for work activity need approval:", emailError);
				}
			}
		}
	} catch (error) {
		console.error("notifyWorkActivityNeedApproval failed:", error);
	}
}

async function notifyWorkActivityPlanNeedApproval({ transaction, workflowId, docId, workflowApprovers, modifiedBy }) {
	try {
		const activeApprover = Array.isArray(workflowApprovers)
			? workflowApprovers.find((a) => a?.status === "Need Approval" || !a?.status)
			: workflowApprovers?.[0];
		if (!activeApprover) return;
		const { recipientEmployeeId, recipientEmail, approverLabel } = await getApproverEmployeeAndEmail(activeApprover, transaction);
		if (recipientEmployeeId) {
			const existingNotif = await dbSAPPS.Notification.findOne({
				where: {
					workflow_id: workflowId,
					employee_id: recipientEmployeeId,
					notification_type: WORK_ACTIVITY_PLAN_NEED_APPROVAL_NOTIFICATION_TYPE,
				},
				transaction,
			});
			if (!existingNotif) {
				await dbSAPPS.Notification.create(
					{
						creation: new Date(),
						modified: new Date(),
						modified_by: modifiedBy || 1,
						owner: modifiedBy || 1,
						workflow_id: workflowId,
						employee_id: recipientEmployeeId,
						notification_type: WORK_ACTIVITY_PLAN_NEED_APPROVAL_NOTIFICATION_TYPE,
						recipient_emails: recipientEmail ? [recipientEmail] : null,
						additional_recipients: null,
						is_sent: false,
						is_read: false,
						sentAt: null,
					},
					{ transaction },
				);
			}
		}
		if (recipientEmail) {
			const workActivityPlan = await dbSAPPS.WorkActivityPlan.findByPk(docId, {
				include: [
					{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name", "website"], required: false },
					{ model: dbSAPPS.Employee, as: "employee", attributes: ["id", "employee_name"] },
				],
				transaction,
			});
			if (workActivityPlan?.company_id) {
				const periodLabel =
					workActivityPlan.period_year && workActivityPlan.period_month
						? `${workActivityPlan.period_year}-${String(workActivityPlan.period_month).padStart(2, "0")}`
						: `#${docId}`;
				const docLabel = `Work Activity Plan ${periodLabel}`;
				const companyName = workActivityPlan?.company?.display_name || workActivityPlan?.company?.name || "Company";
				const website = workActivityPlan?.company?.website || "";
				const employeeName = workActivityPlan?.employee?.employee_name || "Employee";
				const subject = `Work Activity Plan Need Approval (${employeeName} - ${periodLabel})`;
				const html = `
					<div style="font-family: Arial, sans-serif; line-height: 1.5;">
						<p>Hi${approverLabel ? ` ${approverLabel}` : ""},</p>
						<p><b>${docLabel}</b> (${employeeName}) membutuhkan approval.</p>
						<p>Company: <b>${companyName}</b></p>
						${website ? `<p>Website: ${website}</p>` : ""}
						<p>Silakan buka SAPPS untuk melakukan approval.</p>
					</div>
				`;
				try {
					await emailNotificationService.sendEmailNotification({
						company_id: workActivityPlan.company_id,
						to: recipientEmail,
						subject,
						html,
						modified_by: modifiedBy || 1,
						owner: modifiedBy || 1,
					});
				} catch (emailError) {
					console.error("Error sending email for work activity plan need approval:", emailError);
				}
			}
		}
	} catch (error) {
		console.error("notifyWorkActivityPlanNeedApproval failed:", error);
	}
}

async function notifyDeliveryTripNeedApproval({ transaction, workflowId, docId, workflowApprovers, modifiedBy }) {
	try {
		const activeApprover = Array.isArray(workflowApprovers) ? workflowApprovers.find((a) => a?.status === "Need Approval") : null;

		if (!activeApprover) return;

		// Resolve recipient employee (and email) from designation/territory
		let recipientEmployeeId = null;
		let recipientEmail = null;
		let approverLabel = null;

		if (activeApprover.designation_id) {
			const designation = await dbSAPPS.Designation.findByPk(activeApprover.designation_id, {
				include: [
					{
						model: dbSAPPS.Employee,
						as: "employee",
						attributes: ["id", "employee_name", "email"],
					},
				],
				transaction,
			});
			recipientEmployeeId = designation?.employee?.id || null;
			recipientEmail = designation?.employee?.email || null;
			approverLabel = designation?.display_name || null;
		} else if (activeApprover.territory_id) {
			const territory = await dbSAPPS.Territory.findByPk(activeApprover.territory_id, {
				include: [
					{
						model: dbSAPPS.Employee,
						as: "employees",
						attributes: ["id", "employee_name", "email"],
						limit: 1,
					},
				],
				transaction,
			});
			recipientEmployeeId = territory?.employees?.[0]?.id || null;
			recipientEmail = territory?.employees?.[0]?.email || null;
			approverLabel = territory?.display_name || null;
		}

		// Create system notification (idempotent per workflow+employee+type)
		if (recipientEmployeeId) {
			const existingNotif = await dbSAPPS.Notification.findOne({
				where: {
					workflow_id: workflowId,
					employee_id: recipientEmployeeId,
					notification_type: DELIVERY_TRIP_NEED_APPROVAL_NOTIFICATION_TYPE,
				},
				transaction,
			});
			if (!existingNotif) {
				await dbSAPPS.Notification.create(
					{
						creation: new Date(),
						modified: new Date(),
						modified_by: modifiedBy || 1,
						owner: modifiedBy || 1,
						workflow_id: workflowId,
						employee_id: recipientEmployeeId,
						notification_type: DELIVERY_TRIP_NEED_APPROVAL_NOTIFICATION_TYPE,
						recipient_emails: recipientEmail ? [recipientEmail] : null,
						additional_recipients: null,
						is_sent: false,
						is_read: false,
						sentAt: null,
					},
					{ transaction },
				);
			}
		}

		// Send email (best-effort; do not fail workflow if email fails)
		if (recipientEmail) {
			const deliveryTrip = await dbSAPPS.DeliveryTrip.findByPk(docId, {
				include: [
					{
						model: dbSAPPS.Company,
						as: "company",
						attributes: ["id", "display_name", "website"],
						required: false,
					},
				],
				transaction,
			});

			if (deliveryTrip?.company_id) {
				const docNo = deliveryTrip?.delivery_no || `ID ${docId}`;
				const companyName = deliveryTrip?.company?.display_name || deliveryTrip?.company?.name || "Company";
				const website = deliveryTrip?.company?.website || "";
				const subject = `Delivery Trip Need Approval (${docNo})`;
				const html = `
					<div style="font-family: Arial, sans-serif; line-height: 1.5;">
						<p>Hi${approverLabel ? ` ${approverLabel}` : ""},</p>
						<p>Delivery Trip <b>${docNo}</b> membutuhkan approval.</p>
						<p>Company: <b>${companyName}</b></p>
						${website ? `<p>Website: ${website}</p>` : ""}
						<p>Silakan buka SAPPS untuk melakukan approval.</p>
					</div>
				`;

				try {
					await emailNotificationService.sendEmailNotification({
						company_id: deliveryTrip.company_id,
						to: recipientEmail,
						subject,
						html,
						modified_by: modifiedBy || 1,
						owner: modifiedBy || 1,
					});
				} catch (emailError) {
					console.error("Error sending email for delivery trip need approval:", emailError);
				}
			}
		}
	} catch (error) {
		console.error("notifyDeliveryTripNeedApproval failed:", error);
	}
}

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

async function invalidateCallPlanCaches() {
	try {
		await redisClient.delByPrefix("cp:list:v2:");
	} catch (e) {
		// do not block main flow on cache errors
	}
}

async function getAll() {
	try {
		const workflows = await dbSAPPS.Workflow.findAll({
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.DocType,
					as: "doctype",
					attributes: ["id", "name", "display_name"],
				},
			],
		});

		return {
			workflows,
			success: true,
			message: "workflow loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to load workflow!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function getByApprover(params) {
	try {
		// Normalize array params (query string can send single value or repeated keys)
		if (params.status_filter != null && !Array.isArray(params.status_filter)) {
			params.status_filter = [params.status_filter].filter(Boolean);
		}
		if (params.doctype_ids != null && !Array.isArray(params.doctype_ids)) {
			const raw = params.doctype_ids;
			params.doctype_ids = (typeof raw === "string" ? raw.split(",") : [raw]).map((id) => parseInt(String(id).trim(), 10)).filter((n) => !isNaN(n));
		}

		const filterConditionsApprover = {};
		const filterConditionsApproverOr = [];

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

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

		if (filterConditionsApproverOr.length > 0) {
			filterConditionsApprover[Op.or] = filterConditionsApproverOr;
		}

		// For History tab: get workflow_ids where user has acted (Approved, Rejected, or Revised/Draft)
		// For other tabs: get workflow_ids where approver status matches tab status (e.g. "Need Approval", "Approved")
		const approverWhere = { ...filterConditionsApprover };
		if (params.status === "History") {
			approverWhere.status = { [Op.in]: ["Approved", "Rejected", "Draft"] };
		} else {
			approverWhere.status = params.status;
		}
		const workflowApprovers = await dbSAPPS.WorkflowApprover.findAll({
			where: approverWhere,
			attributes: ["territory_id", "designation_id", "status", "workflow_id"],
		});

		const workflowIds = [...new Set(workflowApprovers.map((wa) => wa.workflow_id))];

		const filterConditions = {};

		if (params.status === "Need Approval") {
			filterConditions.status = {
				[Op.eq]: params.status,
			};
		}

		if (params.status === "Approved") {
			filterConditions.status = {
				[Op.and]: {
					[Op.in]: ["Approved", "Saved"],
				},
			};
		}

		if (params.status === "Saved") {
			filterConditions.status = {
				[Op.eq]: "Saved",
			};
		}

		// History: exclude only Need Approval and Saved (they have their own tabs); show Approved, Rejected, Draft (revised), etc.
		// Optional status_filter: when provided, filter History by selected status(es)
		if (params.status === "History") {
			if (params.status_filter && Array.isArray(params.status_filter) && params.status_filter.length > 0) {
				filterConditions.status = {
					[Op.in]: params.status_filter,
				};
			} else {
				filterConditions.status = {
					[Op.notIn]: ["Need Approval", "Saved"],
				};
			}
		}

		if (
			params.access_read_all_data === true ||
			params.access_read_all_data === "true" ||
			params.administrator_of_hr === true ||
			params.administrator_of_hr === "true"
		) {
			filterConditions.id = {
				[Op.ne]: null,
			};
		} else {
			if (workflowIds.length > 0) {
				filterConditions.id = {
					[Op.in]: workflowIds,
				};
			} else if (workflowIds.length === 0 || workflowIds === "" || workflowIds === null) {
				filterConditions.id = {
					[Op.eq]: null,
				};
			}
		}

		if (params.doctype_ids && Array.isArray(params.doctype_ids) && params.doctype_ids.length > 0) {
			filterConditions.doctype_id = {
				[Op.in]: params.doctype_ids.map((id) => parseInt(id, 10)),
			};
		} else if (params.doctype_id != null && params.doctype_id !== "") {
			filterConditions.doctype_id = {
				[Op.eq]: parseInt(params.doctype_id, 10),
			};
		}

		const workflows = await dbSAPPS.Workflow.findAll({
			order: [["modified", "DESC"]],
			where: filterConditions,
			include: [
				{
					model: dbSAPPS.DocType,
					as: "doctype",
					attributes: ["id", "name", "display_name"],
				},
				{
					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 workflowsWithDoctype = await getWorkflowsWithDoctype(workflows);

		return {
			workflows: workflowsWithDoctype,
			success: true,
			message: "Workflow loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to load workflow!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

	try {
		const workflow = await dbSAPPS.Workflow.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.DocType,
					as: "doctype",
					attributes: ["id", "name", "display_name"],
				},
			],
		});

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

async function option() {
	try {
		const workflow = await dbSAPPS.Workflow.findAll({
			attributes: ["id", "doctype_id", "status", "doc_id"],
			order: [["modified", "ASC"]],
			include: [
				{
					model: dbSAPPS.DocType,
					as: "doctype",
					attributes: ["id", "name", "display_name"],
				},
			],
		});

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

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

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

async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		if (!params.doctype_id || !params.doc_id)
			return {
				success: false,
				message: "doctype_id and doc_id are required!",
			};

		const existing = await dbSAPPS.Workflow.findOne({
			where: {
				doctype_id: params.doctype_id,
				doc_id: params.doc_id,
			},
		});

		if (existing)
			return {
				success: false,
				message: `workflow doctype_id "${params.doctype_id}" and doc_id "${params.doc_id}" is already taken.`,
			};

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

		if (params.workflow_approver) {
			params.workflow_approver = JSON.parse(params.workflow_approver);
		}

		if (Array.isArray(params.workflow_approver) && params.workflow_approver.length > 0) {
			const workflowApproverData = params.workflow_approver.map((workflowApprover) => ({
				...workflowApprover,
				workflow_id: workflow.id,
			}));

			await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproverData, {
				transaction,
			});
		}

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

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

		if (params.doctype_id && params.doctype_id !== workflow.doctype_id && params.doc_id && params.doc_id !== workflow.doc_id) {
			const existing = await dbSAPPS.Workflow.findOne({
				where: {
					doctype_id: params.doctype_id,
					doc_id: params.doc_id,
				},
			});

			if (existing) {
				return {
					success: false,
					message: `workfolw doctype_id "${params.doctype_id}" and doc_id "${params.doc_id}" is already taken. Try again!`,
				};
			}
		}

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

		if (params.workflow_approver) {
			params.workflow_approver = JSON.parse(params.workflow_approver);
		}

		if (Array.isArray(params.workflow_approver) && params.workflow_approver.length > 0) {
			await dbSAPPS.WorkflowApprover.destroy({
				where: { workflow_id: id },
				transaction,
			});

			const workflowApproverData = params.workflow_approver.map((workflowApprpver) => ({
				...workflowApprpver,
				workflow_id: id,
			}));

			await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproverData, {
				transaction,
			});
		} else {
			await dbSAPPS.WorkflowApprover.destroy({
				where: { workflow_id: id },
				transaction,
			});
		}

		await transaction.commit();
		await invalidateCallPlanCaches();
		return {
			success: true,
			message: "Data workflow has been saved!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "Failed to update workflow data.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function submitToWorkflow(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const workflow = await dbSAPPS.Workflow.findByPk(id);
		if (!workflow)
			return {
				success: false,
				message: "workflow not found!",
			};

		const hasWorkflowApproversProp = Object.prototype.hasOwnProperty.call(params, "workflowapprovers");
		if (typeof params.workflowapprovers === "string") {
			try {
				params.workflowapprovers = JSON.parse(params.workflowapprovers);
			} catch {
				// ignore invalid JSON
			}
		}
		const workflowApproversForLogic = Array.isArray(params.workflowapprovers)
			? params.workflowapprovers
			: !hasWorkflowApproversProp
				? await dbSAPPS.WorkflowApprover.findAll({
						where: { workflow_id: id },
						order: [["id", "ASC"]],
						raw: true,
						transaction,
					})
				: null;

		if (params.doctype_id && params.doctype_id !== workflow.doctype_id && params.doc_id && params.doc_id !== workflow.doc_id) {
			const existing = await dbSAPPS.Workflow.findOne({
				where: {
					doctype_id: params.doctype_id,
					doc_id: params.doc_id,
				},
			});

			if (existing) {
				return {
					success: false,
					message: `workfolw doctype_id "${params.doctype_id}" and doc_id "${params.doc_id}" is already taken. Try again!`,
				};
			}
		}

		const workflowData = {
			id: parseInt(id),
			doc_id: params.doc_id,
			doctype_id: params.doctype_id,
			modified: params.modified,
			modified_by: params.modified_by,
			status: params.status,
			workflow_id: params.workflow_id,
		};

		await workflow.update(workflowData, { transaction, individualHooks: true });

		// Only replace approvers if caller explicitly sends workflowapprovers.
		// Missing field should preserve existing approvers (important for "bridge" calls).
		if (hasWorkflowApproversProp) {
			if (Array.isArray(params.workflowapprovers) && params.workflowapprovers.length > 0) {
				await dbSAPPS.WorkflowApprover.destroy({
					where: { workflow_id: id },
					transaction,
				});
				const workflowApproverData = params.workflowapprovers.map((workflowApprover) => ({
					...workflowApprover,
					workflow_id: id,
				}));
				await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproverData, {
					transaction,
				});
			} else {
				await dbSAPPS.WorkflowApprover.destroy({
					where: { workflow_id: id },
					transaction,
				});
			}
		}

		// Sinkronkan status Workflow ke dokumen agar Workflow Approval (dashboard), Workflows Inbox, dan halaman LA/AR satu sumber data
		if (params.status === "Need Approval") {
			const docId = params.doc_id != null ? parseInt(params.doc_id) : workflow.doc_id;
			const doctypeId = params.doctype_id != null ? parseInt(params.doctype_id) : workflow.doctype_id;
			if (LEAVE_APPLICATION_DOCTYPE_IDS.includes(doctypeId) && docId) {
				const leaveApp = await dbSAPPS.LeaveApplication.findByPk(docId, {
					transaction,
				});
				if (leaveApp) {
					await leaveApp.update(
						{
							status: "Need Approval",
							modified: params.modified || new Date(),
							modified_by: params.modified_by != null ? parseInt(params.modified_by) : leaveApp.modified_by,
						},
						{ transaction },
					);
				}
			}
			if (ATTENDANCE_REQUEST_DOCTYPE_IDS.includes(doctypeId) && docId) {
				const attReq = await dbSAPPS.AttendanceRequest.findByPk(docId, {
					transaction,
				});
				if (attReq) {
					await attReq.update(
						{
							status: "Need Approval",
							modified: params.modified || new Date(),
							modified_by: params.modified_by != null ? parseInt(params.modified_by) : attReq.modified_by,
						},
						{ transaction },
					);
				}
			}
		}

		// Attendance from Absence Clarification adn Permit Ease
		let createdAttendanceIds = [];
		let updatedAttendanceIds = [];

		// JOB POSTING from EMPLOYEE REQUEST
		if (params.status === "Approved" && params.doctype_id === 109) {
			const employeeRequest = await dbSAPPS.EmployeeRequest.findByPk(params.doc_id, {
				include: [{ model: dbSAPPS.Designation, as: "required_designation" }],
				transaction,
			});

			if (employeeRequest) {
				// Generate posting_no
				const now = new Date();
				const currentYear = now.getFullYear();
				const yearString = currentYear.toString();

				const existingJobPostings = await dbSAPPS.JobPosting.findAll({
					where: {
						posting_no: {
							[Op.like]: `SAP/JOBPOST/${yearString}/%`,
						},
					},
					transaction,
				});

				let maxNumber = 0;
				existingJobPostings.forEach((jp) => {
					const parts = jp.posting_no.split("/");
					if (parts.length === 4) {
						const numberPart = parseInt(parts[3], 10);
						if (!isNaN(numberPart)) {
							maxNumber = Math.max(maxNumber, numberPart);
						}
					}
				});

				const nextNumber = (maxNumber + 1).toString();
				const newPostingNo = `SAP/JOBPOST/${yearString}/${nextNumber}`;

				const designationName = employeeRequest.required_designation ? employeeRequest.required_designation.display_name : "Unknown Designation";

				const newJobPosting = await dbSAPPS.JobPosting.create(
					{
						posting_no: newPostingNo,
						title: `Job Posting - ${designationName}`,
						designation_id: employeeRequest.required_designation_id,
						company_id: employeeRequest.company_id,
						province_id: employeeRequest.province_id,
						city_id: employeeRequest.city_id,
						enabled: false,
						creation: Date.now(),
						modified: Date.now(),
						modified_by: params.modified_by,
						owner: params.owner || employeeRequest.owner,
					},
					{ transaction },
				);

				// Create JobPostingChild
				await dbSAPPS.JobPostingChild.create(
					{
						jobposting_id: newJobPosting.id,
						employeerequest_id: employeeRequest.id,
						name: employeeRequest.required_designation?.display_name || "Unknown Designation",
						applicant_name: "",
						enabled: true,
						creation: Date.now(),
						modified: Date.now(),
						modified_by: params.modified_by,
						owner: params.owner || employeeRequest.owner,
					},
					{ transaction },
				);
			}
		}

		// ABSENCE CLARIFICATION
		if (params.status === "Approved" && params.doctype_id === 104) {
			// Get all absenceClarificationChilds that need to be synced to attendances
			const allAbsenceClarificationChilds = await dbSAPPS.AbsenceClarificationChild.findAll({
				where: {
					absenceclarification_id: params.doc_id,
					status_absenceclarification: true,
				},
				transaction,
			});

			if (Array.isArray(params.attendances) && params.attendances.length > 0) {
				// revisi tambahkan filter kalau sabtu dan minggu tidak termasuk filter ini
				const validationAttendance = params.attendances.filter(
					(attendance) =>
						(attendance.status_absenceclarification === "true" || attendance.status_absenceclarification === true) &&
						(!attendance.in_time || !attendance.out_time || !attendance.reason),
				);

				if (validationAttendance.length > 0) {
					return {
						success: false,
						message: `in_time, out_time and reason are required for attendance ${validationAttendance.map((attendance) => attendance.id).join(", ")}`,
					};
				}

				const attendances = params.attendances.map((attendance) => ({
					...attendance,
					owner: attendance.owner === 0 || attendance.owner === "0" || !attendance.owner ? parseInt(params.modified_by) : attendance.owner || 1,
					creation: attendance.creation || Date.now(),
					modified: attendance.modified || Date.now(),
					id: attendance.id ? parseInt(attendance.id) : null,
					modified_by: params.modified_by ? parseInt(params.modified_by) : null,
					absenceclarification_id: attendance.absenceclarification_id ? parseInt(attendance.absenceclarification_id) : null,
					attendance_id: attendance.attendance_id ? parseInt(attendance.attendance_id) : null,
					company_id: attendance.company_id ? parseInt(attendance.company_id) : null,
					employee_id: attendance.employee_id ? parseInt(attendance.employee_id) : null,
					shifttype_id: attendance.shifttype_id ? parseInt(attendance.shifttype_id) : null,
					total_hour: attendance.total_hour ? parseInt(attendance.total_hour) : null,
				}));

				const foundAttendanceId = attendances.filter((attendance) => attendance.attendance_id && attendance.attendance_id !== 0);

				const notFoundAttendanceId = attendances.filter((attendance) => !attendance.attendance_id);

				const foundStatusAbsenceClarification = notFoundAttendanceId.filter((attendance) => attendance.status_absenceclarification === true);

				try {
					if (foundStatusAbsenceClarification.length > 0) {
						// Update existing attendance by (date, employee_id, shifttype_id) to avoid duplicates; only create if not found
						const toCreate = [];
						const childIdToAttendanceId = [];

						for (const attendance of foundStatusAbsenceClarification) {
							const existingAtt = await dbSAPPS.Attendance.findOne({
								where: {
									date: attendance.date,
									employee_id: attendance.employee_id,
									shifttype_id: attendance.shifttype_id || null,
								},
								transaction,
							});
							if (existingAtt) {
								await dbSAPPS.Attendance.update(
									{
										modified: Date.now(),
										modified_by: params.modified_by ? parseInt(params.modified_by) : null,
										absenceclarification_id: params?.doc_id,
										absenceclarificationchild_id: attendance.id,
										is_absence_clarification: true,
										need_clarification: false,
										in_time: attendance.in_time,
										out_time: attendance.out_time,
										total_hour: attendance.total_hour,
										reason: attendance.reason,
										notes: attendance.notes,
										status_absenceclarification: true,
									},
									{ where: { id: existingAtt.id }, transaction },
								);
								childIdToAttendanceId.push({
									childId: attendance.id,
									attendanceId: existingAtt.id,
								});
							} else {
								toCreate.push(attendance);
							}
						}

						if (toCreate.length > 0) {
							const attendanceData = toCreate.map((attendance) => {
								const { id, ...attendanceWithoutId } = attendance;
								return {
									creation: Date.now(),
									modified: Date.now(),
									absenceclarification_id: params?.doc_id,
									absenceclarificationchild_id: attendance?.id,
									is_absence_clarification: true,
									need_clarification: false,
									...attendanceWithoutId,
								};
							});
							const createdAttendances = await dbSAPPS.Attendance.bulkCreate(attendanceData, { transaction });
							createdAttendances.forEach((att, i) => {
								childIdToAttendanceId.push({
									childId: toCreate[i].id,
									attendanceId: att.id,
								});
							});
						}

						for (const { childId, attendanceId } of childIdToAttendanceId) {
							await dbSAPPS.AbsenceClarificationChild.update({ attendance_id: attendanceId }, { where: { id: childId }, transaction });
						}
					}

					const updatedAttendance = foundAttendanceId.map((attendance) => ({
						modified: Date.now(),
						...attendance,
						absenceclarification_id: parseInt(attendance.absenceclarification_id),
						is_absence_clarification: true,
						absenceclarificationchild_id: parseInt(attendance.id),
						modified_by: parseInt(params.modified_by),
						id: parseInt(attendance.attendance_id),
						need_clarification: false,
					}));

					if (updatedAttendance.length > 0) {
						const updatedAttendances = await dbSAPPS.Attendance.bulkCreate(updatedAttendance, {
							updateOnDuplicate: [
								"modified",
								"modified_by",
								"owner",
								"company_id",
								"employee_id",
								"shifttype_id",
								"total_hour",
								"in_time",
								"out_time",
								"notes",
								"reason",
								"status_absenceclarification",
								"absenceclarification_id",
								"absenceclarificationchild_id",
								"is_absence_clarification",
								"need_clarification",
							],
							individualHooks: true,
							returning: true,
							transaction,
							ignoreDuplicates: true,
						});

						updatedAttendanceIds = updatedAttendances.map((attendance) => attendance.id);
					} else {
						console.error("No updated attendance records to process.");
					}
				} catch (error) {
					return {
						success: false,
						message: error.message || "Failed to create or update attendance.",
					};
				}
			}

			// Ensure all absenceClarificationChilds with status_absenceclarification=true are synced
			// This handles cases where some children might not be in params.attendances
			if (allAbsenceClarificationChilds && allAbsenceClarificationChilds.length > 0) {
				try {
					// Find children that don't have attendance_id yet
					const unsyncedChildren = allAbsenceClarificationChilds.filter((child) => !child.attendance_id);

					if (unsyncedChildren.length > 0) {
						const toCreate = [];
						const childIdToAttendanceId = [];

						for (const child of unsyncedChildren) {
							const existingAtt = await dbSAPPS.Attendance.findOne({
								where: {
									date: child.date,
									employee_id: child.employee_id,
									shifttype_id: child.shifttype_id || null,
								},
								transaction,
							});
							if (existingAtt) {
								await dbSAPPS.Attendance.update(
									{
										modified: Date.now(),
										modified_by: params.modified_by ? parseInt(params.modified_by) : null,
										absenceclarification_id: params.doc_id,
										absenceclarificationchild_id: child.id,
										is_absence_clarification: true,
										need_clarification: false,
										in_time: child.in_time,
										out_time: child.out_time,
										total_hour: child.total_hour,
										reason: child.reason || "lupa absen",
										notes: child.notes,
										status_absenceclarification: true,
									},
									{ where: { id: existingAtt.id }, transaction },
								);
								childIdToAttendanceId.push({
									childId: child.id,
									attendanceId: existingAtt.id,
								});
							} else {
								toCreate.push(child);
							}
						}

						if (toCreate.length > 0) {
							const attendanceDataToCreate = toCreate.map((child) => ({
								creation: Date.now(),
								modified: Date.now(),
								absenceclarification_id: params.doc_id,
								absenceclarificationchild_id: child.id,
								is_absence_clarification: true,
								need_clarification: false,
								date: child.date,
								employee_id: child.employee_id,
								company_id: child.company_id,
								shifttype_id: child.shifttype_id,
								in_time: child.in_time,
								out_time: child.out_time,
								total_hour: child.total_hour,
								reason: child.reason || "lupa absen",
								notes: child.notes,
								status_absenceclarification: true,
								modified_by: params.modified_by ? parseInt(params.modified_by) : null,
								owner: params.modified_by ? parseInt(params.modified_by) : null,
							}));

							const createdAttendances = await dbSAPPS.Attendance.bulkCreate(attendanceDataToCreate, { transaction });
							createdAttendances.forEach((att, i) => {
								childIdToAttendanceId.push({
									childId: toCreate[i].id,
									attendanceId: att.id,
								});
							});
						}

						for (const { childId, attendanceId } of childIdToAttendanceId) {
							await dbSAPPS.AbsenceClarificationChild.update({ attendance_id: attendanceId }, { where: { id: childId }, transaction });
						}
					}

					// Re-approve / force sync: update ALL attendances linked to FKA children so they have FKA fields set (even when params.attendances not sent)
					const childrenWithAttendanceId = allAbsenceClarificationChilds.filter((c) => c.attendance_id && c.attendance_id !== 0);
					for (const child of childrenWithAttendanceId) {
						await dbSAPPS.Attendance.update(
							{
								modified: Date.now(),
								modified_by: params.modified_by ? parseInt(params.modified_by) : null,
								absenceclarification_id: params.doc_id,
								absenceclarificationchild_id: child.id,
								is_absence_clarification: true,
								need_clarification: false,
								in_time: child.in_time,
								out_time: child.out_time,
								total_hour: child.total_hour,
								reason: child.reason || null,
								notes: child.notes || null,
								status_absenceclarification: true,
							},
							{ where: { id: child.attendance_id }, transaction },
						);
					}
				} catch (error) {
					console.error("Error syncing unsynced absenceClarificationChilds:", error);
					// Don't fail the transaction, just log the error
				}
			}
		}

		// PERMIT EASE
		if (params.status === "Approved" && params.doctype_id === 70) {
			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. please try again.",
						};
					} 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,
								},
							},
							transaction,
						});

						const foundDate = foundAttendances.find((attendance) => dates.includes(attendance.date));

						if (foundDate) {
							return {
								success: false,
								message: `Attendance on date ${foundDate.date} already exist.`,
							};
						}

						const employee = await dbSAPPS.Employee.findByPk(params.permiteases.employee_id, {
							attributes: ["shifttype_id"],
							transaction,
						});

						if (!employee) {
							return {
								success: false,
								message: "Employee not found!",
							};
						}

						const shifttype_id = employee.shifttype_id;

						if (foundAttendances && foundAttendances.length) {
							const updatedAttendance = foundAttendances.map((attendance) => ({
								...attendance,
								creation: attendance.creation || Date.now(),
								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 foundAbsenceClarification = foundAttendances.find(
									(attendance) => attendance.id === updatedAttendanceItem.id && attendance.absenceclarification_id && attendance.status_absenceclarification,
								);
								if (foundAbsenceClarification) return false;
								return true;
							});

							if (filteredUpdatedAttendance && filteredUpdatedAttendance.length === 0) {
								return {
									success: false,
									message: "All attendance dates have been given absence clarification, please check again!",
								};
							}

							await dbSAPPS.Attendance.bulkCreate(updatedAttendance, {
								updateOnDuplicate: true,
								individualHooks: true,
								returning: true,
								lock: transaction.LOCK.UPDATE,
								transaction,
							});

							for (let i = 0; i < updatedAttendance.length; i++) {
								await dbSAPPS.PermitEase.update({ attendance_id: updatedAttendance[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 || params.permiteases.half_day === "true")) {
					const foundAttendance = await dbSAPPS.Attendance.findOne({
						where: {
							date: params.permiteases.half_day_date,
							employee_id: params.permiteases.employee_id,
						},
						transaction,
					});

					if (foundAttendance) {
						const updatedAttendance = {
							...foundAttendance.dataValues,
							modified: Date.now(),
							modified_by: params.permiteases.modified_by,
							permitease_id: params.permiteases.id ? params.permiteases.id : foundAttendance.dataValues.permitease_id,
							status_permitease: true,
							need_clarification: false,
							in_time: foundAttendance.dataValues.in_time ? foundAttendance.dataValues.in_time : "09:00:00",
							out_time: foundAttendance.dataValues.out_time ? foundAttendance.dataValues.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: foundAttendance.dataValues.id,
							total_hour: 8,
						};

						const foundAbsenceClarification = foundAttendance.dataValues
							? foundAttendance.dataValues.absenceclarification_id && foundAttendance.dataValues.status_absenceclarification
							: false;
						const filteredUpdatedAttendance = !foundAbsenceClarification ? updatedAttendance : null;

						if (!filteredUpdatedAttendance) {
							return {
								success: false,
								message: "The attendance date has been given absence clarification, please check again!",
							};
						}

						if (updatedAttendance) {
							const updatedAttendances = await dbSAPPS.Attendance.upsert(updatedAttendance, {
								updateOnDuplicate: [
									"modified_by",
									"owner",
									"company_id",
									"employee_id",
									"shifttype_id",
									"total_hour",
									"in_time",
									"out_time",
									"notes",
									"reason",
									"filename",
									"status_permitease",
									"permitease_id",
									"need_clarification",
								],
								individualHooks: true,
								returning: true,
								transaction,
							});

							updatedAttendanceIds = [updatedAttendances.id];
						} else {
							console.error("No updated attendance records to process.");
						}

						await dbSAPPS.PermitEase.update({ attendance_id: updatedAttendance.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: 8,
						};

						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 });
						}
					}
				}
			}
		}

		// Send email notification when status is "Need Approval" for Attendance Request and Leave Application
		if (params.status === "Need Approval") {
			// ATTENDANCE REQUEST (doctype_id 110 or 207)
			if (ATTENDANCE_REQUEST_DOCTYPE_IDS.includes(parseInt(params.doctype_id, 10))) {
				try {
					const attendanceRequest = await dbSAPPS.AttendanceRequest.findByPk(params.doc_id, {
						include: [
							{
								model: dbSAPPS.Company,
								as: "company",
								attributes: ["id", "display_name", "website"],
							},
							{
								model: dbSAPPS.Employee,
								as: "employee",
								attributes: ["id", "employee_name", "employee_email"],
							},
						],
						transaction,
					});

					if (attendanceRequest && attendanceRequest.follow_via_email === true) {
						// Get first approver from workflow approvers
						const firstApprover = workflowApproversForLogic?.[0];
						let approverEmail = null;

						if (firstApprover?.designation_id) {
							const designation = await dbSAPPS.Designation.findByPk(firstApprover.designation_id, {
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employee",
										attributes: ["id", "employee_name", "email"],
									},
								],
								transaction,
							});
							approverEmail = designation?.employee?.email;
						} else if (firstApprover?.territory_id) {
							const territory = await dbSAPPS.Territory.findByPk(firstApprover.territory_id, {
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employees",
										attributes: ["id", "employee_name", "email"],
										limit: 1,
									},
								],
								transaction,
							});
							approverEmail = territory?.employees?.[0]?.email;
						}

						if (approverEmail) {
							const emailAccount = await dbSAPPS.EmailAccount.findOne({
								where: {
									company_id: attendanceRequest.company_id,
								},
								include: [
									{
										model: dbSAPPS.Email,
										as: "email",
										attributes: ["id", "smtp_server", "smtp_port", "use_ssl_for_outgoing", "use_tls", "company_id"],
									},
								],
								attributes: ["id", "email_account", "password", "company_id"],
								transaction,
							});

							if (emailAccount?.email && emailAccount?.email_account) {
								const transporter = nodemailer.createTransport({
									host: emailAccount?.email?.smtp_server,
									port: emailAccount?.email?.smtp_port,
									secure: emailAccount?.email?.use_ssl_for_outgoing,
									auth: {
										user: emailAccount?.email_account,
										pass: emailAccount?.password,
									},
									tls: {
										rejectUnauthorized: emailAccount?.email?.use_tls,
									},
								});

								const website = attendanceRequest.company?.website || "";
								const subject = `Attendance Request Need Approval (${
									attendanceRequest?.employee_name
								} - ${moment(attendanceRequest.date).format("yyyy-MM-DD")})`;
								// Pass workflow ID to email template for workflow-based action links
								const html = emailTemplateNeedApprovalAttendanceRequest.html({ ...attendanceRequest.dataValues, workflow_id: id }, website);

								let emailStatus = "sent";
								let errorMessage = null;

								try {
									await transporter.sendMail({
										from: `${attendanceRequest?.employee_name} <${emailAccount.email_account}>`,
										to: approverEmail,
										bcc: "it@sapharma.co.id",
										subject,
										html,
									});
								} catch (error) {
									console.error("Email sending failed:", error);
									emailStatus = "failed";
									errorMessage = error.message.substring(0, 255);
								}

								// Create history email
								await dbSAPPS.HistoryEmail.create(
									{
										creation: new Date(),
										modified: new Date(),
										modified_by: params.modified_by,
										owner: attendanceRequest.owner,
										company_id: attendanceRequest.company_id,
										employee_id: attendanceRequest.employee_id,
										recipient_email: approverEmail,
										subject: subject,
										body: html,
										status: emailStatus,
										error_message: errorMessage,
										attendancerequest_id: attendanceRequest.id,
									},
									{ transaction },
								);
							}
						}
					}
				} catch (error) {
					console.error("Error sending email for attendance request:", error);
					// Don't fail the transaction if email fails
				}
			}

			// LEAVE APPLICATION (doctype_id 111 or 69)
			if (LEAVE_APPLICATION_DOCTYPE_IDS.includes(parseInt(params.doctype_id, 10))) {
				try {
					const leaveApplication = await dbSAPPS.LeaveApplication.findByPk(params.doc_id, {
						include: [
							{
								model: dbSAPPS.Company,
								as: "company",
								attributes: ["id", "display_name", "website"],
							},
							{
								model: dbSAPPS.Employee,
								as: "employee",
								attributes: ["id", "employee_name", "email"],
							},
						],
						transaction,
					});

					if (leaveApplication && leaveApplication.follow_via_email === true) {
						// Get first approver from workflow approvers
						const firstApprover = workflowApproversForLogic?.[0];
						let approverEmail = null;

						if (firstApprover?.designation_id) {
							const designation = await dbSAPPS.Designation.findByPk(firstApprover.designation_id, {
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employee",
										attributes: ["id", "employee_name", "email"],
									},
								],
								transaction,
							});
							approverEmail = designation?.employee?.email;
						} else if (firstApprover?.territory_id) {
							const territory = await dbSAPPS.Territory.findByPk(firstApprover.territory_id, {
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employees",
										attributes: ["id", "employee_name", "email"],
										limit: 1,
									},
								],
								transaction,
							});
							approverEmail = territory?.employees?.[0]?.email;
						}

						if (approverEmail) {
							const emailAccount = await dbSAPPS.EmailAccount.findOne({
								where: {
									company_id: leaveApplication.company_id,
								},
								include: [
									{
										model: dbSAPPS.Email,
										as: "email",
										attributes: ["id", "smtp_server", "smtp_port", "use_ssl_for_outgoing", "use_tls", "company_id"],
									},
								],
								attributes: ["id", "email_account", "password", "company_id"],
								transaction,
							});

							if (emailAccount?.email && emailAccount?.email_account) {
								const transporter = nodemailer.createTransport({
									host: emailAccount?.email?.smtp_server,
									port: emailAccount?.email?.smtp_port,
									secure: emailAccount?.email?.use_ssl_for_outgoing,
									auth: {
										user: emailAccount?.email_account,
										pass: emailAccount?.password,
									},
									tls: {
										rejectUnauthorized: emailAccount?.email?.use_tls,
									},
								});

								const website = leaveApplication.company?.website || "";
								const subject = `Leave Request Approval (${leaveApplication?.leavetype_name}) - ${leaveApplication?.employee_name} - (${moment(
									leaveApplication.from_date,
								).format("yyyy-MM-DD")} to ${moment(leaveApplication.to_date).format("yyyy-MM-DD")})`;
								// Pass workflow ID to email template for workflow-based action links
								const html = emailTemplateNeedApprovalLeaveApplication.html({ ...leaveApplication.dataValues, workflow_id: id }, website);

								let emailStatus = "sent";
								let errorMessage = null;

								const attachments = leaveApplication.filename
									? [
											{
												filename: leaveApplication.filename,
												path: `./public/data/documents/leaveapplications/${leaveApplication.filename}`,
											},
										]
									: [];

								try {
									await transporter.sendMail({
										from: `${leaveApplication?.employee_name} <${emailAccount.email_account}>`,
										to: approverEmail,
										bcc: "it@sapharma.co.id",
										subject,
										html,
										attachments,
									});
								} catch (error) {
									console.error("Email sending failed:", error);
									emailStatus = "failed";
									errorMessage = error.message.substring(0, 255);
								}

								// Create history email
								await dbSAPPS.HistoryEmail.create(
									{
										creation: new Date(),
										modified: new Date(),
										modified_by: params.modified_by,
										owner: leaveApplication.owner,
										company_id: leaveApplication.company_id,
										employee_id: leaveApplication.employee_id,
										recipient_email: approverEmail,
										subject: subject,
										body: html,
										status: emailStatus,
										error_message: errorMessage,
										leaveapplication_id: leaveApplication.id,
									},
									{ transaction },
								);
							}
						}
					}
				} catch (error) {
					console.error("Error sending email for leave application:", error);
					// Don't fail the transaction if email fails
				}
			}

			// ABSENCE CLARIFICATION (doctype_id 104)
			if (params.doctype_id === 104) {
				try {
					const absenceClarification = await dbSAPPS.AbsenceClarification.findByPk(params.doc_id, {
						include: [
							{
								model: dbSAPPS.Company,
								as: "company",
								attributes: ["id", "display_name", "website"],
							},
							{
								model: dbSAPPS.Employee,
								as: "employee",
								attributes: ["id", "employee_name", "employee_email", "email", "employee_number"],
								include: [
									{
										model: dbSAPPS.EmployeeTerritory,
										as: "employeeterritories",
										include: [
											{
												model: dbSAPPS.Designation,
												as: "designation",
												attributes: ["id", "name", "display_name"],
											},
											{
												model: dbSAPPS.Territory,
												as: "territory",
												attributes: ["id", "name", "display_name"],
											},
										],
									},
								],
							},
						],
						transaction,
					});

					if (absenceClarification && absenceClarification.follow_via_email === true) {
						// Get first approver from workflow approvers
						const firstApprover = workflowApproversForLogic?.[0];
						let approverEmail = null;

						if (firstApprover?.designation_id) {
							const designation = await dbSAPPS.Designation.findByPk(firstApprover.designation_id, {
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employee",
										attributes: ["id", "employee_name", "email"],
									},
								],
								transaction,
							});
							approverEmail = designation?.employee?.email;
						} else if (firstApprover?.territory_id) {
							const territory = await dbSAPPS.Territory.findByPk(firstApprover.territory_id, {
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employees",
										attributes: ["id", "employee_name", "email"],
										limit: 1,
									},
								],
								transaction,
							});
							approverEmail = territory?.employees?.[0]?.email;
						}

						if (approverEmail) {
							// Flatten employee designation/territory for template
							const employeeData = absenceClarification.employee;
							const designationTerritory = employeeData?.employeeterritories?.[0]
								? `${employeeData.employeeterritories[0].designation?.display_name || ""} - ${
										employeeData.employeeterritories[0].territory?.display_name || ""
									}`
								: "";

							// Use emailNotificationService to send email
							// Wrap in try-catch to prevent email failure from blocking workflow transaction
							try {
								const emailResult = await emailNotificationService.sendAbsenceClarificationEmail({
									absenceClarification: {
										...absenceClarification.dataValues,
										employee_name: absenceClarification.employee?.employee_name || "",
										employee_designation_territory: designationTerritory,
										workflow_id: id,
									},
									status: "Need Approval",
									website: absenceClarification.company?.website || "sapps.sapharma.co.id",
									recipientEmail: approverEmail,
									company_id: absenceClarification.company_id,
									modified_by: params.modified_by || 1,
									owner: absenceClarification.owner || 1,
								});

								if (!emailResult.success) {
									console.warn(`Email notification failed for absence clarification ${absenceClarification.id}:`, emailResult.message);
								}
							} catch (emailError) {
								console.error("Error sending email notification for absence clarification:", emailError);
								// Don't fail the workflow transaction if email fails
							}
						}
					}
				} catch (error) {
					console.error("Error sending email for absence clarification:", error);
					// Don't fail the transaction if email fails
				}
			}

			// PERMIT EASE (doctype_id 70)
			if (params.doctype_id === 70) {
				try {
					const permitEase = await dbSAPPS.PermitEase.findByPk(params.doc_id, {
						include: [
							{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name", "website"] },
							{
								model: dbSAPPS.Employee,
								as: "employee",
								attributes: ["id", "employee_name", "employee_email", "email", "employee_number"],
							},
						],
						transaction,
					});

					if (permitEase) {
						const firstApprover = workflowApproversForLogic?.[0];
						let recipientEmail = null;

						if (firstApprover?.designation_id) {
							const designation = await dbSAPPS.Designation.findByPk(firstApprover.designation_id, {
								include: [{ model: dbSAPPS.Employee, as: "employee", attributes: ["id", "employee_name", "email"] }],
								transaction,
							});
							recipientEmail = designation?.employee?.email;
						} else if (firstApprover?.territory_id) {
							const territory = await dbSAPPS.Territory.findByPk(firstApprover.territory_id, {
								include: [{ model: dbSAPPS.Employee, as: "employees", attributes: ["id", "employee_name", "email"], limit: 1 }],
								transaction,
							});
							recipientEmail = territory?.employees?.[0]?.email;
						}

						if (recipientEmail) {
							try {
								const emailResult = await emailNotificationService.sendPermitEaseEmail({
									permitEase: {
										...permitEase.dataValues,
										employee_name: permitEase.employee?.employee_name || "",
										workflow_id: id,
									},
									status: "Need Approval",
									website: permitEase.company?.website || "sapps.sapharma.co.id",
									recipientEmail,
									company_id: permitEase.company_id,
									modified_by: params.modified_by || 1,
									owner: permitEase.owner || 1,
								});

								if (!emailResult.success) {
									console.warn(`Email notification failed for permit ease ${permitEase.id}:`, emailResult.message);
								}
							} catch (emailError) {
								console.error("Error sending email notification for permit ease:", emailError);
								// Don't fail the workflow transaction if email fails
							}
						}
					}
				} catch (error) {
					console.error("Error sending email for permit ease:", error);
					// Don't fail the transaction if email fails
				}
			}

			// DELIVERY TRIP (doctype_id 72)
			if (params.doctype_id === 72) {
				await notifyDeliveryTripNeedApproval({
					transaction,
					workflowId: parseInt(id),
					docId: params.doc_id,
					workflowApprovers: workflowApproversForLogic,
					modifiedBy: params.modified_by,
				});
			}

			// WORK ACTIVITY (doctype_id 230 or 209)
			if (WORK_ACTIVITY_DOCTYPE_IDS.includes(parseInt(params.doctype_id, 10))) {
				await notifyWorkActivityNeedApproval({
					transaction,
					workflowId: parseInt(id),
					docId: params.doc_id,
					workflowApprovers: workflowApproversForLogic,
					modifiedBy: params.modified_by,
				});
			}

			// WORK ACTIVITY PLAN (doctype_id 246)
			if (WORK_ACTIVITY_PLAN_DOCTYPE_IDS.includes(parseInt(params.doctype_id, 10))) {
				await notifyWorkActivityPlanNeedApproval({
					transaction,
					workflowId: parseInt(id),
					docId: params.doc_id,
					workflowApprovers: workflowApproversForLogic,
					modifiedBy: params.modified_by,
				});
			}
		}

		await transaction.commit();
		await invalidateCallPlanCaches();
		return {
			createdAttendanceIds,
			updatedAttendanceIds,
			success: true,
			message: "workflow has been saved!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "Failed to update workflow data.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function actionToWorkflow(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const workflow = await dbSAPPS.Workflow.findByPk(id);
		if (!workflow)
			return {
				success: false,
				message: "workflow not found!",
			};

		const hasWorkflowApproversProp = Object.prototype.hasOwnProperty.call(params, "workflowapprovers");
		if (typeof params.workflowapprovers === "string") {
			try {
				params.workflowapprovers = JSON.parse(params.workflowapprovers);
			} catch {
				// ignore invalid JSON
			}
		}
		const workflowApproversForLogic = Array.isArray(params.workflowapprovers)
			? params.workflowapprovers
			: !hasWorkflowApproversProp
				? await dbSAPPS.WorkflowApprover.findAll({
						where: { workflow_id: id },
						order: [["id", "ASC"]],
						raw: true,
						transaction,
					})
				: null;

		if (params.doctype_id && params.doctype_id !== workflow.doctype_id && params.doc_id && params.doc_id !== workflow.doc_id) {
			const existing = await dbSAPPS.Workflow.findOne({
				where: {
					doctype_id: params.doctype_id,
					doc_id: params.doc_id,
				},
			});

			if (existing) {
				return {
					success: false,
					message: `workfolw doctype_id "${params.doctype_id}" and doc_id "${params.doc_id}" is already taken. Try again!`,
				};
			}
		}

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

		// Only replace approvers if caller explicitly sends workflowapprovers.
		if (hasWorkflowApproversProp) {
			if (Array.isArray(params.workflowapprovers) && params.workflowapprovers.length > 0) {
				await dbSAPPS.WorkflowApprover.destroy({
					where: { workflow_id: id },
					transaction,
				});

				const workflowApproverData = params.workflowapprovers.map((workflowApprover) => ({
					...workflowApprover,
					workflow_id: id,
				}));

				await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproverData, {
					transaction,
				});
			} else {
				await dbSAPPS.WorkflowApprover.destroy({
					where: { workflow_id: id },
					transaction,
				});
			}
		}

		// Sinkronkan status Workflow ke dokumen agar Workflow Approval (dashboard) dan Workflows Inbox satu sumber data
		if (params.status && ["Approved", "Rejected", "Draft"].includes(params.status)) {
			const docId = params.doc_id != null ? parseInt(params.doc_id) : workflow.doc_id;
			const doctypeId = params.doctype_id != null ? parseInt(params.doctype_id) : workflow.doctype_id;
			if (LEAVE_APPLICATION_DOCTYPE_IDS.includes(doctypeId) && docId) {
				const leaveApp = await dbSAPPS.LeaveApplication.findByPk(docId, { transaction });
				if (leaveApp) {
					await leaveApp.update(
						{
							status: params.status,
							approver_date: new Date(),
							...(params.rejection_reason != null && { rejection_reason: params.rejection_reason }),
							...(params.revision_notes != null && { revision_notes: params.revision_notes }),
							modified: params.modified || new Date(),
							modified_by: params.modified_by != null ? parseInt(params.modified_by) : leaveApp.modified_by,
						},
						{ transaction },
					);
				}
			}
			if (ATTENDANCE_REQUEST_DOCTYPE_IDS.includes(doctypeId) && docId) {
				const attReq = await dbSAPPS.AttendanceRequest.findByPk(docId, { transaction });
				if (attReq) {
					await attReq.update(
						{
							status: params.status,
							approver_date: new Date(),
							...(params.rejection_reason != null && { rejection_reason: params.rejection_reason }),
							...(params.revision_notes != null && { revision_notes: params.revision_notes }),
							modified: params.modified || new Date(),
							modified_by: params.modified_by != null ? parseInt(params.modified_by) : attReq.modified_by,
						},
						{ transaction },
					);
				}
			}
		}

		// attendance from Absence Clarification
		let createdAttendanceIds = [];
		let updatedAttendanceIds = [];

		// DELIVERY TRIP (doctype_id 72): if still Need Approval, notify next/current approver
		if (params.status === "Need Approval" && params.doctype_id === 72) {
			await notifyDeliveryTripNeedApproval({
				transaction,
				workflowId: parseInt(id),
				docId: params.doc_id,
				workflowApprovers: workflowApproversForLogic,
				modifiedBy: params.modified_by,
			});
		}

		if (params.status === "Approved" && params.doctype_id === 104) {
			// Get all absenceClarificationChilds that need to be synced to attendances
			const allAbsenceClarificationChilds = await dbSAPPS.AbsenceClarificationChild.findAll({
				where: {
					absenceclarification_id: params.doc_id,
					status_absenceclarification: true,
				},
				transaction,
			});

			if (Array.isArray(params.attendances) && params.attendances.length > 0) {
				const attendances = params.attendances.map((attendance) => ({
					...attendance,
					id: parseInt(attendance.id),
					modified_by: parseInt(params.modified_by),
					owner: parseInt(attendance.owner),
					absenceclarification_id: parseInt(attendance.absenceclarification_id),
					attendance_id: parseInt(attendance.attendance_id),
					company_id: parseInt(attendance.company_id),
					employee_id: parseInt(attendance.employee_id),
					shifttype_id: parseInt(attendance.shifttype_id),
					total_hour: parseInt(attendance.total_hour),
				}));

				const foundAttendanceId = attendances.filter((attendance) => attendance.attendance_id && attendance.attendance_id !== 0);

				const notFoundAttendanceId = attendances.filter((attendance) => !attendance.attendance_id);

				const foundStatusAbsenceClarification = notFoundAttendanceId.filter((attendance) => attendance.status_absenceclarification === true);

				try {
					if (foundStatusAbsenceClarification.length > 0) {
						// Update existing attendance by (date, employee_id, shifttype_id) to avoid duplicates; only create if not found
						const toCreate = [];
						const childIdToAttendanceId = [];

						for (const attendance of foundStatusAbsenceClarification) {
							const existingAtt = await dbSAPPS.Attendance.findOne({
								where: {
									date: attendance.date,
									employee_id: attendance.employee_id,
									shifttype_id: attendance.shifttype_id || null,
								},
								transaction,
							});
							if (existingAtt) {
								await dbSAPPS.Attendance.update(
									{
										modified: Date.now(),
										modified_by: params.modified_by ? parseInt(params.modified_by) : null,
										absenceclarification_id: params?.doc_id,
										absenceclarificationchild_id: attendance.id,
										is_absence_clarification: true,
										need_clarification: false,
										in_time: attendance.in_time,
										out_time: attendance.out_time,
										total_hour: attendance.total_hour,
										reason: attendance.reason,
										notes: attendance.notes,
										status_absenceclarification: true,
									},
									{ where: { id: existingAtt.id }, transaction },
								);
								childIdToAttendanceId.push({
									childId: attendance.id,
									attendanceId: existingAtt.id,
								});
							} else {
								toCreate.push(attendance);
							}
						}

						if (toCreate.length > 0) {
							const attendanceData = toCreate.map((attendance) => {
								const { id, ...attendanceWithoutId } = attendance;
								return {
									creation: Date.now(),
									modified: Date.now(),
									absenceclarification_id: params?.doc_id,
									absenceclarificationchild_id: attendance?.id,
									is_absence_clarification: true,
									need_clarification: false,
									...attendanceWithoutId,
								};
							});
							const createdAttendances = await dbSAPPS.Attendance.bulkCreate(attendanceData, { transaction });
							createdAttendances.forEach((att, i) => {
								childIdToAttendanceId.push({
									childId: toCreate[i].id,
									attendanceId: att.id,
								});
							});
						}

						for (const { childId, attendanceId } of childIdToAttendanceId) {
							await dbSAPPS.AbsenceClarificationChild.update({ attendance_id: attendanceId }, { where: { id: childId }, transaction });
						}
					}

					const updatedAttendance = foundAttendanceId.map((attendance) => ({
						modified: Date.now(),
						...attendance,
						absenceclarification_id: parseInt(attendance.absenceclarification_id),
						absenceclarificationchild_id: parseInt(attendance.id),
						modified_by: parseInt(params.modified_by),
						id: parseInt(attendance.attendance_id),
						is_absence_clarification: true,
					}));

					if (updatedAttendance.length > 0) {
						const updatedAttendances = await dbSAPPS.Attendance.bulkCreate(updatedAttendance, {
							updateOnDuplicate: [
								"modified_by",
								"owner",
								"absenceclarification_id",
								"absenceclarificationchild_id",
								"company_id",
								"employee_id",
								"shifttype_id",
								"total_hour",
								"in_time",
								"out_time",
								"notes",
								"reason",
								"status_absenceclarification",
								"absenceclarificationchild_id",
								"is_absence_clarification",
							],
							individualHooks: true,
							returning: true,
							transaction,
						});

						updatedAttendanceIds = updatedAttendances.map((attendance) => attendance.id);
					} else {
						console.error("No updated attendance records to process.");
					}
				} catch (error) {
					return {
						success: false,
						message: error.message || "Failed to create or update attendance.",
					};
				}
			}

			// Ensure all absenceClarificationChilds with status_absenceclarification=true are synced
			// This handles cases where some children might not be in params.attendances
			if (allAbsenceClarificationChilds && allAbsenceClarificationChilds.length > 0) {
				try {
					// Find children that don't have attendance_id yet
					const unsyncedChildren = allAbsenceClarificationChilds.filter((child) => !child.attendance_id);

					if (unsyncedChildren.length > 0) {
						const toCreate = [];
						const childIdToAttendanceId = [];

						for (const child of unsyncedChildren) {
							const existingAtt = await dbSAPPS.Attendance.findOne({
								where: {
									date: child.date,
									employee_id: child.employee_id,
									shifttype_id: child.shifttype_id || null,
								},
								transaction,
							});
							if (existingAtt) {
								await dbSAPPS.Attendance.update(
									{
										modified: Date.now(),
										modified_by: params.modified_by ? parseInt(params.modified_by) : null,
										absenceclarification_id: params.doc_id,
										absenceclarificationchild_id: child.id,
										is_absence_clarification: true,
										need_clarification: false,
										in_time: child.in_time,
										out_time: child.out_time,
										total_hour: child.total_hour,
										reason: child.reason || "lupa absen",
										notes: child.notes,
										status_absenceclarification: true,
									},
									{ where: { id: existingAtt.id }, transaction },
								);
								childIdToAttendanceId.push({
									childId: child.id,
									attendanceId: existingAtt.id,
								});
							} else {
								toCreate.push(child);
							}
						}

						if (toCreate.length > 0) {
							const attendanceDataToCreate = toCreate.map((child) => ({
								creation: Date.now(),
								modified: Date.now(),
								absenceclarification_id: params.doc_id,
								absenceclarificationchild_id: child.id,
								is_absence_clarification: true,
								need_clarification: false,
								date: child.date,
								employee_id: child.employee_id,
								company_id: child.company_id,
								shifttype_id: child.shifttype_id,
								in_time: child.in_time,
								out_time: child.out_time,
								total_hour: child.total_hour,
								reason: child.reason || "lupa absen",
								notes: child.notes,
								status_absenceclarification: true,
								modified_by: params.modified_by ? parseInt(params.modified_by) : null,
								owner: params.modified_by ? parseInt(params.modified_by) : null,
							}));

							const createdAttendances = await dbSAPPS.Attendance.bulkCreate(attendanceDataToCreate, { transaction });
							createdAttendances.forEach((att, i) => {
								childIdToAttendanceId.push({
									childId: toCreate[i].id,
									attendanceId: att.id,
								});
							});
						}

						for (const { childId, attendanceId } of childIdToAttendanceId) {
							await dbSAPPS.AbsenceClarificationChild.update({ attendance_id: attendanceId }, { where: { id: childId }, transaction });
						}
					}

					// Re-approve / force sync: update ALL attendances linked to FKA children so they have FKA fields set (even when params.attendances not sent)
					const childrenWithAttendanceIdAction = allAbsenceClarificationChilds.filter((c) => c.attendance_id && c.attendance_id !== 0);
					for (const child of childrenWithAttendanceIdAction) {
						await dbSAPPS.Attendance.update(
							{
								modified: Date.now(),
								modified_by: params.modified_by ? parseInt(params.modified_by) : null,
								absenceclarification_id: params.doc_id,
								absenceclarificationchild_id: child.id,
								is_absence_clarification: true,
								need_clarification: false,
								in_time: child.in_time,
								out_time: child.out_time,
								total_hour: child.total_hour,
								reason: child.reason || null,
								notes: child.notes || null,
								status_absenceclarification: true,
							},
							{ where: { id: child.attendance_id }, transaction },
						);
					}
				} catch (error) {
					console.error("Error syncing unsynced absenceClarificationChilds:", error);
					// Don't fail the transaction, just log the error
				}
			}
		}

		await transaction.commit();

		// Send email notification when status is "Approved", "Revised", or "Rejected" for Absence Clarification
		// Note: We use a wrapper function or execute this after commit to avoid blocking/rolling back transaction on email fail,
		// but since we need data from DB which might depend on transaction, we can do it before commit but inside a try-catch,
		// OR since we already committed above, we can just run it. However, the original code had `await transaction.commit()` at the end.
		// Wait, I am replacing `await transaction.commit()` block. I should put it back.

		if (["Approved", "Revised", "Rejected"].includes(params.status) && params.doctype_id === 104) {
			try {
				const absenceClarification = await dbSAPPS.AbsenceClarification.findByPk(params.doc_id, {
					include: [
						{
							model: dbSAPPS.Company,
							as: "company",
							attributes: ["id", "display_name", "website"],
						},
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name", "employee_email", "email", "employee_number"],
							include: [
								{
									model: dbSAPPS.EmployeeTerritory,
									as: "employeeterritories",
									include: [
										{
											model: dbSAPPS.Designation,
											as: "designation",
											attributes: ["id", "name", "display_name"],
										},
										{
											model: dbSAPPS.Territory,
											as: "territory",
											attributes: ["id", "name", "display_name"],
										},
									],
								},
							],
						},
					],
				});

				if (absenceClarification && absenceClarification.follow_via_email === true) {
					const employeeEmail = absenceClarification.employee?.email || absenceClarification.employee?.employee_email;

					if (employeeEmail) {
						// Flatten employee designation/territory for template
						const employeeData = absenceClarification.employee;
						const designationTerritory = employeeData?.employeeterritories?.[0]
							? `${employeeData.employeeterritories[0].designation?.display_name || ""} - ${employeeData.employeeterritories[0].territory?.display_name || ""}`
							: "";

						// Use emailNotificationService to send email
						// Wrap in try-catch to prevent email failure from blocking workflow transaction
						try {
							const emailResult = await emailNotificationService.sendAbsenceClarificationEmail({
								absenceClarification: {
									...absenceClarification.dataValues,
									employee_name: absenceClarification.employee?.employee_name || "",
									employee_designation_territory: designationTerritory,
								},
								status: params.status,
								website: absenceClarification.company?.website || "sapps.sapharma.co.id",
								recipientEmail: employeeEmail,
								company_id: absenceClarification.company_id,
								modified_by: params.modified_by || 1,
								owner: absenceClarification.owner || 1,
							});

							if (!emailResult.success) {
								console.warn(`Email notification failed for absence clarification ${absenceClarification.id}:`, emailResult.message);
							}
						} catch (emailError) {
							console.error("Error sending email notification for absence clarification:", emailError);
							// Don't fail the workflow transaction if email fails
						}
					}
				}
			} catch (error) {
				console.error("Error sending email for absence clarification:", error);
			}
		}

		// Send email notification for Permit Ease (doctype_id 70)
		if (["Need Approval", "Approved", "Revised", "Rejected", "Cancelled"].includes(params.status) && params.doctype_id === 70) {
			try {
				const permitEase = await dbSAPPS.PermitEase.findByPk(params.doc_id, {
					include: [
						{
							model: dbSAPPS.Company,
							as: "company",
							attributes: ["id", "display_name", "website"],
						},
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name", "employee_email", "email", "employee_number"],
						},
					],
				});

				if (permitEase) {
					// Determine recipient email based on status
					let recipientEmail = null;
					if (params.status === "Need Approval") {
						// Get first approver from workflow approvers
						const firstApprover = workflowApproversForLogic?.[0];
						if (firstApprover?.designation_id) {
							const designation = await dbSAPPS.Designation.findByPk(firstApprover.designation_id, {
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employee",
										attributes: ["id", "employee_name", "email"],
									},
								],
							});
							recipientEmail = designation?.employee?.email;
						} else if (firstApprover?.territory_id) {
							const territory = await dbSAPPS.Territory.findByPk(firstApprover.territory_id, {
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employees",
										attributes: ["id", "employee_name", "email"],
										limit: 1,
									},
								],
							});
							recipientEmail = territory?.employees?.[0]?.email;
						}
					} else {
						// For Approved, Revised, Rejected, Cancelled: send to employee
						recipientEmail = permitEase.employee?.email || permitEase.employee?.employee_email;
					}

					if (recipientEmail) {
						// Use emailNotificationService to send email
						// Wrap in try-catch to prevent email failure from blocking workflow transaction
						try {
							const emailResult = await emailNotificationService.sendPermitEaseEmail({
								permitEase: {
									...permitEase.dataValues,
									employee_name: permitEase.employee?.employee_name || "",
									workflow_id: params.status === "Need Approval" ? id : permitEase.workflow_id || null,
								},
								status: params.status,
								website: permitEase.company?.website || "sapps.sapharma.co.id",
								recipientEmail: recipientEmail,
								company_id: permitEase.company_id,
								modified_by: params.modified_by || 1,
								owner: permitEase.owner || 1,
							});

							if (!emailResult.success) {
								console.warn(`Email notification failed for permit ease ${permitEase.id}:`, emailResult.message);
							}
						} catch (emailError) {
							console.error("Error sending email notification for permit ease:", emailError);
							// Don't fail the workflow transaction if email fails
						}
					}
				}
			} catch (error) {
				console.error("Error sending email for permit ease:", error);
				// Don't fail the transaction if email fails
			}
		}

		// WORK ACTIVITY: email ke employee (submitter) saat Approved / Revised / Rejected
		if (["Approved", "Revised", "Rejected"].includes(params.status) && WORK_ACTIVITY_DOCTYPE_IDS.includes(parseInt(params.doctype_id, 10))) {
			try {
				const workActivity = await dbSAPPS.WorkActivity.findByPk(params.doc_id, {
					include: [
						{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name", "website"] },
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name", "company_email"],
							include: [{ model: dbSAPPS.User, as: "user", attributes: ["id", "email"] }],
						},
					],
				});
				if (workActivity?.company_id) {
					const recipientEmail = workActivity?.employee?.user?.email || workActivity?.employee?.company_email;
					if (recipientEmail) {
						const employeeName = workActivity?.employee?.employee_name || "Employee";
						const companyName = workActivity?.company?.display_name || workActivity?.company?.name || "Company";
						const website = workActivity?.company?.website || "";
						const subject = `Work Activity ${params.status} - ${employeeName} (${workActivity?.activity_date ? moment(workActivity.activity_date).format("YYYY-MM-DD") : workActivity.id})`;
						const html = `
							<div style="font-family: Arial, sans-serif; line-height: 1.5;">
								<p>Hi ${employeeName},</p>
								<p>Work Activity Anda (${workActivity?.activity_date ? moment(workActivity.activity_date).format("YYYY-MM-DD") : workActivity.id}) telah di-<b>${params.status}</b>.</p>
								<p>Company: <b>${companyName}</b></p>
								${website ? `<p>Website: ${website}</p>` : ""}
								<p>Silakan buka SAPPS untuk melihat detail.</p>
							</div>
						`;
						await emailNotificationService.sendEmailNotification({
							company_id: workActivity.company_id,
							to: recipientEmail,
							subject,
							html,
							modified_by: params.modified_by || 1,
							owner: workActivity.owner || 1,
						});
					}
				}
			} catch (error) {
				console.error("Error sending email for work activity action:", error);
			}
		}

		// WORK ACTIVITY PLAN: email ke employee (submitter) saat Approved / Revised / Rejected
		if (["Approved", "Revised", "Rejected"].includes(params.status) && WORK_ACTIVITY_PLAN_DOCTYPE_IDS.includes(parseInt(params.doctype_id, 10))) {
			try {
				const workActivityPlan = await dbSAPPS.WorkActivityPlan.findByPk(params.doc_id, {
					include: [
						{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name", "website"] },
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name", "company_email"],
							include: [{ model: dbSAPPS.User, as: "user", attributes: ["id", "email"] }],
						},
					],
				});
				if (workActivityPlan?.company_id) {
					const recipientEmail = workActivityPlan?.employee?.user?.email || workActivityPlan?.employee?.company_email;
					if (recipientEmail) {
						const employeeName = workActivityPlan?.employee?.employee_name || "Employee";
						const companyName = workActivityPlan?.company?.display_name || workActivityPlan?.company?.name || "Company";
						const website = workActivityPlan?.company?.website || "";
						const periodLabel =
							workActivityPlan.period_year && workActivityPlan.period_month
								? `${workActivityPlan.period_year}-${String(workActivityPlan.period_month).padStart(2, "0")}`
								: workActivityPlan.id;
						const subject = `Work Activity Plan ${params.status} - ${employeeName} (${periodLabel})`;
						const html = `
							<div style="font-family: Arial, sans-serif; line-height: 1.5;">
								<p>Hi ${employeeName},</p>
								<p>Work Activity Plan Anda (${periodLabel}) telah di-<b>${params.status}</b>.</p>
								<p>Company: <b>${companyName}</b></p>
								${website ? `<p>Website: ${website}</p>` : ""}
								<p>Silakan buka SAPPS untuk melihat detail.</p>
							</div>
						`;
						await emailNotificationService.sendEmailNotification({
							company_id: workActivityPlan.company_id,
							to: recipientEmail,
							subject,
							html,
							modified_by: params.modified_by || 1,
							owner: workActivityPlan.owner || 1,
						});
					}
				}
			} catch (error) {
				console.error("Error sending email for work activity plan action:", error);
			}
		}

		await invalidateCallPlanCaches();
		return {
			success: true,
			message: "workflow has been saved!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "Failed to update workflow data.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

		await workflow.destroy();
		return {
			success: true,
			message: "workflow successfully deleted!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to delete workflow.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

const getWorkflowsWithDoctype = async (workflows) => {
	const workflowsWithDoctype = await Promise.all(
		workflows.map(async (workflow) => {
			const { doctype_id, doc_id } = workflow;

			// CALL PLAN
			if (doctype_id === 22) {
				const callPlans = await dbSAPPS.CallPlan.findOne({
					where: { id: doc_id },
					attributes: ["id", "doc_no", "territory_id", "year_id", "period_id"],
				});

				const data = await dbSAPPS.EmployeeTerritory.findOne({
					where: {
						territory_id: callPlans?.territory_id ? callPlans?.territory_id : null,
					},
					attributes: ["employee_id", "territory_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					callPlans,
					doc_no: callPlans ? callPlans.doc_no : null,
					employee_name: data?.employee?.employee_name || null,
				};
			}

			// REALIZATION PERMISSION
			if (doctype_id === 117) {
				const realizationPermission = await dbSAPPS.CallPlanRealization.findOne({
					where: { id: doc_id },
					attributes: ["id", "start_date", "note", "callplan_id"],
					include: [
						{
							model: dbSAPPS.CallPlan,
							as: "callplan",
							attributes: ["id", "doc_no", "territory_id", "year_id", "period_id"],
						},
					],
				});

				const data = await dbSAPPS.EmployeeTerritory.findOne({
					where: {
						territory_id: realizationPermission?.callplan ? realizationPermission?.callplan?.territory_id : null,
					},
					attributes: ["employee_id", "territory_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				const details = {
					doc_no: realizationPermission?.callplan ? realizationPermission?.callplan?.doc_no : null,
					note: realizationPermission?.note,
					date: realizationPermission?.start_date,
				};
				return {
					...workflow.toJSON(),
					realizationPermission,
					doc_no: realizationPermission?.callplan ? realizationPermission?.callplan?.doc_no : null,
					employee_name: data?.employee?.employee_name || null,
					details,
				};
			}

			// CALL REALIZATION DAILY
			if (doctype_id === 93) {
				const callPlan = await dbSAPPS.CallPlan.findOne({
					where: { id: doc_id },
					attributes: ["id", "doc_no", "territory_id", "year_id", "period_id"],
				});

				const data = await dbSAPPS.EmployeeTerritory.findOne({
					where: {
						territory_id: callPlan?.territory_id ? callPlan?.territory_id : null,
					},
					attributes: ["employee_id", "territory_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: callPlan ? callPlan?.doc_no : null,
					employee_name: data?.employee?.employee_name || null,
				};
			}

			// EMPLOYEE REQUEST
			if (doctype_id === 109) {
				const employeeRequest = await dbSAPPS.EmployeeRequest.findOne({
					where: { id: doc_id },
					attributes: ["id", "no_fpk"],
					include: [
						{
							model: dbSAPPS.User,
							as: "user",
							attributes: ["id", "email", "full_name", "phone_number"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["employee_name"],
								},
							],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: employeeRequest ? employeeRequest?.no_fpk.replace(/\//g, "-") : null,
					employee_name: employeeRequest?.user?.employee?.employee_name,
				};
			}

			// ABSENCE CLARIFICATION
			if (doctype_id === 104) {
				const absenceClarification = await dbSAPPS.AbsenceClarification.findOne({
					where: { id: doc_id },
					attributes: ["id", "periode_month", "periode_year", "employee_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: absenceClarification
						? "AC-" +
							absenceClarification?.id +
							"-" +
							absenceClarification?.periode_month +
							"-" +
							absenceClarification?.periode_year +
							"-" +
							absenceClarification?.employee_id
						: null,
					employee_name: absenceClarification?.employee?.employee_name,
				};
			}

			// PERMIT EASE
			if (doctype_id === 70) {
				const permitEase = await dbSAPPS.PermitEase.findOne({
					where: { id: doc_id },
					attributes: ["id", "from_date", "to_date", "half_day_date", "employee_id", "half_day"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: permitEase ? "PE-" + permitEase?.id + "-" + permitEase?.employee_id : null,
					employee_name: permitEase?.employee?.employee_name,
				};
			}

			// ATTENDANCE REQUEST (doctype_id 110 or 207)
			if (ATTENDANCE_REQUEST_DOCTYPE_IDS.includes(doctype_id)) {
				const attendanceRequest = await dbSAPPS.AttendanceRequest.findOne({
					where: { id: doc_id },
					attributes: ["id", "date", "employee_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});
				const docNo = attendanceRequest ? `AR-${attendanceRequest.id}-${attendanceRequest.date || ""}` : null;
				return {
					...workflow.toJSON(),
					doc_no: docNo,
					employee_name: attendanceRequest?.employee?.employee_name ?? null,
				};
			}

			// LEAVE APPLICATION (doctype_id 111 or 69)
			if (LEAVE_APPLICATION_DOCTYPE_IDS.includes(doctype_id)) {
				const leaveApplication = await dbSAPPS.LeaveApplication.findOne({
					where: { id: doc_id },
					attributes: ["id", "from_date", "to_date", "leavetype_name", "employee_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});
				const docNo = leaveApplication ? `LA-${leaveApplication.id}${leaveApplication.leavetype_name ? ` ${leaveApplication.leavetype_name}` : ""}` : null;
				return {
					...workflow.toJSON(),
					doc_no: docNo,
					employee_name: leaveApplication?.employee?.employee_name ?? null,
				};
			}

			// UDK
			if (doctype_id === 107) {
				const udk = await dbSAPPS.Udk.findOne({
					where: { id: doc_id },
					attributes: ["id", "name"],
					include: [
						{
							model: dbSAPPS.User,
							as: "user",
							attributes: ["id", "email", "full_name", "phone_number"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["employee_name"],
									include: [
										{
											model: dbSAPPS.Designation,
											as: "designation",
											attributes: ["display_name"],
										},
									],
								},
							],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: udk ? udk?.name.replace(/\//g, "-") : null,
					employee_name: udk?.user?.employee?.employee_name,
				};
			}

			// CLIENT KS
			if (doctype_id === 25) {
				const clientKS = await dbSAPPS.ClientCollab.findOne({
					where: { id: doc_id },
					attributes: ["id", "doc_no", "territory_id"],
				});

				const data = await dbSAPPS.EmployeeTerritory.findOne({
					where: {
						territory_id: clientKS?.territory_id ? clientKS?.territory_id : null,
					},
					attributes: ["employee_id", "territory_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: clientKS ? clientKS?.doc_no : null,
					employee_name: data?.employee?.employee_name,
				};
			}

			// SURVEY
			if (doctype_id === 23) {
				const survey = await dbSAPPS.Survey.findOne({
					where: { id: doc_id },
					attributes: ["id", "doc_no", "territory_id"],
				});

				const data = await dbSAPPS.EmployeeTerritory.findOne({
					where: {
						territory_id: survey?.territory_id ? survey?.territory_id : null,
					},
					attributes: ["employee_id", "territory_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: survey ? survey?.doc_no : null,
					employee_name: data?.employee?.employee_name,
				};
			}

			// UDP
			if (doctype_id === 24) {
				const udp = await dbSAPPS.Udp.findOne({
					where: { id: doc_id },
					attributes: ["id", "doc_no", "territory_id"],
				});

				const data = await dbSAPPS.EmployeeTerritory.findOne({
					where: {
						territory_id: udp?.territory_id ? udp?.territory_id : null,
					},
					attributes: ["employee_id", "territory_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: udp ? udp?.doc_no : null,
					employee_name: data?.employee?.employee_name,
				};
			}

			// DELIVERY TRIP
			if (doctype_id === 72) {
				const deliveryTrip = await dbSAPPS.DeliveryTrip.findOne({
					where: { id: doc_id },
					attributes: ["id", "delivery_no"],
					include: [
						{
							model: dbSAPPS.User,
							as: "user",
							attributes: ["id", "email", "full_name", "phone_number"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["employee_name"],
								},
							],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: deliveryTrip ? deliveryTrip?.delivery_no.replace(/\//g, "-") : null,
					employee_name: deliveryTrip?.user?.employee?.employee_name,
				};
			}

			// NC PRODUCT
			if (doctype_id === 149) {
				const ncProduct = await dbSAPPS.NcProduct.findOne({
					where: { id: doc_id },
					attributes: ["id", "nc_no"],
					include: [
						{
							model: dbSAPPS.User,
							as: "user",
							attributes: ["id", "email", "full_name", "phone_number"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["employee_name"],
								},
							],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: ncProduct ? ncProduct?.nc_no : null,
					employee_name: ncProduct?.user?.employee?.employee_name,
				};
			}

			// PRODUCT RETURN
			if (doctype_id === 150) {
				const productReturn = await dbSAPPS.ProductReturn.findOne({
					where: { id: doc_id },
					attributes: ["id", "barp_company"],
					include: [
						{
							model: dbSAPPS.User,
							as: "user",
							attributes: ["id", "email", "full_name", "phone_number"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["employee_name"],
								},
							],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: productReturn ? productReturn?.barp_company : null,
					employee_name: productReturn?.user?.employee?.employee_name,
				};
			}

			// FSE
			if (doctype_id === 155) {
				const fse = await dbSAPPS.Fse.findOne({
					where: { id: doc_id },
					attributes: ["id", "name"],
					include: [
						{
							model: dbSAPPS.User,
							as: "user",
							attributes: ["id", "email", "full_name", "phone_number"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["employee_name"],
								},
							],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: fse ? fse?.name : null,
					employee_name: fse?.user?.employee?.employee_name,
				};
			}

			// GIMMICK REQUEST
			if (doctype_id === 176) {
				const gimmickRequest = await dbSAPPS.GimmickRequest.findOne({
					where: { id: doc_id },
					attributes: ["id", "doc_no", "territory_id"],
				});

				const data = await dbSAPPS.EmployeeTerritory.findOne({
					where: {
						territory_id: gimmickRequest?.territory_id ? gimmickRequest?.territory_id : null,
					},
					attributes: ["employee_id", "territory_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: gimmickRequest ? gimmickRequest?.doc_no : null,
					employee_name: data?.employee?.employee_name,
				};
			}

			// UP COUNTRY
			if (doctype_id === 194) {
				const upcountry = await dbSAPPS.UpCountry.findOne({
					where: { id: doc_id },
					attributes: ["id", "doc_no", "territory_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_no: upcountry ? upcountry?.doc_no : null,
					employee_name: upcountry?.employee?.employee_name,
				};
			}

			// UC COST REALISASI
			if (doctype_id === 224) {
				const costRealization = await dbSAPPS.UcCostRealization.findOne({
					where: { id: doc_id },
					attributes: ["id", "filename", "territory_id", "upcountry_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});

				return {
					...workflow.toJSON(),
					doc_id: costRealization?.upcountry_id,
					doc_no: costRealization ? costRealization?.filename : null,
					employee_name: costRealization?.employee?.employee_name,
				};
			}

			// WORK ACTIVITY (doctype_id 230 or 209)
			if (WORK_ACTIVITY_DOCTYPE_IDS.includes(doctype_id)) {
				const workActivity = await dbSAPPS.WorkActivity.findOne({
					where: { id: doc_id },
					attributes: ["id", "activity_date", "work_type", "employee_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});
				const docNo = workActivity ? `${workActivity.activity_date || ""} ${workActivity.work_type || ""}`.trim() || `WA-${doc_id}` : `WA-${doc_id}`;
				return {
					...workflow.toJSON(),
					doc_no: docNo,
					employee_name: workActivity?.employee?.employee_name || null,
				};
			}

			// WORK ACTIVITY PLAN (doctype_id 246)
			if (doctype_id === 246) {
				const plan = await dbSAPPS.WorkActivityPlan.findOne({
					where: { id: doc_id },
					attributes: ["id", "period_month", "period_year", "employee_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["id", "employee_name"],
						},
					],
				});
				const docNo = plan ? `Plan ${plan.period_year}-${String(plan.period_month).padStart(2, "0")}`.trim() || `Plan-${doc_id}` : `Plan-${doc_id}`;
				return {
					...workflow.toJSON(),
					doc_no: docNo,
					employee_name: plan?.employee?.employee_name || null,
				};
			}

			return workflow.toJSON ? workflow.toJSON() : workflow;
		}),
	);

	return workflowsWithDoctype;
};
