const jwt = require("jsonwebtoken");
const bcrypt = require("bcryptjs");
const dbSAPPS = require("../../helpers/db");
const workflowService = require("../workflows/workflow.service");
require("dotenv").config();
const { Op, where } = require("sequelize");
const moment = require("moment");

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

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

		if (params.is_workflow_direct === true || params.is_workflow_direct === "true") {
			if (params.doc_id) {
				filterConditions.id = {
					[Op.eq]: parseInt(params.doc_id),
				};
			}
		} else {
			// Scoping logic
			if (
				params.access_read_all_data === true ||
				params.access_read_all_data === "true" ||
				params.administrator_of_hr === true ||
				params.administrator_of_hr === "true"
			) {
				// No additional scoping for HR/Admin unless explicit filter passed
			} else {
				if (params.employeesId && params.employeesId.length > 0) {
					filterConditions.employee_id = {
						[Op.in]: params.employeesId.map((item) => parseInt(item)),
					};
				} else if (params.employee_id) {
					filterConditions.employee_id = {
						[Op.eq]: parseInt(params.employee_id),
					};
				}
			}

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

			// Date range: show records that OVERLAP the filter range (record.from_date <= filter.to_date AND record.to_date >= filter.from_date)
			if (params.from_date && params.to_date) {
				andConditions.push({
					[Op.and]: [{ from_date: { [Op.lte]: params.to_date } }, { to_date: { [Op.gte]: params.from_date } }],
				});
			}

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

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

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

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

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

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

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

		const employees = await dbSAPPS.Employee.findAll({
			attributes: ["id", "employee_name", "employee_number"],
			order: [["employee_name", "ASC"]],
		});

		const companies = await dbSAPPS.Company.findAll({
			attributes: ["id", "name", "display_name"],
			order: [["display_name", "ASC"]],
		});

		const shiftTypes = await dbSAPPS.ShiftType.findAll({
			attributes: ["id", "name", "display_name"],
			order: [["display_name", "ASC"]],
		});

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

		const holidays = await dbSAPPS.Holiday.findAll({
			where: {
				enabled: true,
			},
			attributes: ["holiday_date", "is_national_holiday", "is_weekend_holiday", "display_name"],
		});

		const data = absenceClarifications.map((item) => {
			const workflow = workflows.find((wf) => wf.doc_id === item.id);

			return {
				...item.dataValues,
				absenceclarificationchilds: item.absenceclarificationchilds.map((absenceclarificationchild) => {
					const matchedDataEmployee = employees && employees.find((employee) => employee.id === absenceclarificationchild.employee_id);
					const matchedDataCompany = companies && companies.find((company) => company.id === absenceclarificationchild.company_id);
					const matchedDataShiftType = shiftTypes && shiftTypes.find((shiftType) => shiftType.id === absenceclarificationchild.shifttype_id);
					const matchedDataHariLibur = holidays && holidays.find((holiday) => holiday.holiday_date === absenceclarificationchild.date);
					const matchedModifiedBy = modifiedBy && modifiedBy.find((modifiedBy) => modifiedBy.id === absenceclarificationchild.modified_by);

					return {
						...absenceclarificationchild.dataValues,
						employee_id: matchedDataEmployee?.id || "",
						employee_name: matchedDataEmployee?.employee_name || "",
						employee_number: matchedDataEmployee?.employee_number || "",
						employee: matchedDataEmployee?.employee_name || "",
						day: matchedDataEmployee ? moment(absenceclarificationchild.date).format("dddd") : "",
						day_status: matchedDataHariLibur?.is_national_holiday
							? "National Holiday"
							: moment(absenceclarificationchild.date).format("dddd") === "Saturday" || moment(absenceclarificationchild.date).format("dddd") === "Sunday"
								? "Day Off"
								: "Work Day",
						holiday_name: matchedDataHariLibur?.display_name || "",
						company: matchedDataCompany?.display_name || "",
						shifttype: matchedDataShiftType?.display_name || "",
						in_time: absenceclarificationchild.in_time === "00:00:00" ? "" : absenceclarificationchild.in_time,
						out_time: absenceclarificationchild.out_time === "00:00:00" ? "" : absenceclarificationchild.out_time,
						status_absenceclarification: absenceclarificationchild?.status_absenceclarification || false,
						status_working_in_dayoff: absenceclarificationchild?.status_working_in_dayoff || false,
						modified_by_full_name: matchedModifiedBy?.full_name || "",
					};
				}),
				modified_by_full_name: modifiedBy && modifiedBy.find((m) => m.id === item.modified_by)?.full_name,
				workflow: workflow ? workflow.dataValues : null,
			};
		});

		return {
			absenceClarifications: data,
			totalCount,
			pageIndex,
			pageSize,
			success: true,
			message: "absence clarifications has been loaded!",
		};
	} catch (error) {
		console.error("Error in getAll absenceClarifications:", error);
		const errorMessage = error.message || "Failed to load absence clarifications!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

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

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

		const filterConditionsAbsenceClarificationOr = [];

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

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

		if (filterConditionsAbsenceClarificationOr.length > 0) {
			filterConditionsAbsenceClarification[Op.or] = filterConditionsAbsenceClarificationOr;
		}

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

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

		const employees = await dbSAPPS.Employee.findAll({
			attributes: ["id", "employee_name", "employee_number"],
			order: [["employee_name", "ASC"]],
		});

		const companies = await dbSAPPS.Company.findAll({
			attributes: ["id", "name", "display_name"],
			order: [["display_name", "ASC"]],
		});

		const shiftTypes = await dbSAPPS.ShiftType.findAll({
			attributes: ["id", "name", "display_name"],
			order: [["display_name", "ASC"]],
		});

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

		const holidays = await dbSAPPS.Holiday.findAll({
			where: {
				enabled: true,
				holiday_date: {
					[Op.between]: [params.from_date, params.to_date],
				},
			},
			attributes: ["holiday_date", "is_national_holiday", "is_weekend_holiday", "display_name"],
		});

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

			return {
				...item.dataValues,
				absenceclarificationchilds: item.absenceclarificationchilds.map((absenceclarificationchild) => {
					const matchedDataEmployee = employees && employees.filter((employee) => employee.id === absenceclarificationchild.employee_id);
					const matchedDataCompany = companies && companies.filter((company) => company.id === absenceclarificationchild.company_id);
					const matchedDataShiftType = shiftTypes && shiftTypes.filter((shiftType) => shiftType.id === absenceclarificationchild.shifttype_id);
					const matchedDataHariLibur = holidays && holidays.filter((holiday) => holiday.holiday_date === absenceclarificationchild.date);
					const dayStatus = matchedDataHariLibur[0]?.is_national_holiday
						? "National Holiday"
						: moment(absenceclarificationchild.date).format("dddd") === "Saturday" || moment(absenceclarificationchild.date).format("dddd") === "Sunday"
							? "Day Off"
							: "Work Day";
					const matchedModifiedBy = modifiedBy && modifiedBy.filter((modifiedBy) => modifiedBy.id === absenceclarificationchild.modified_by);

					return {
						...absenceclarificationchild.dataValues,
						employee_id: matchedDataEmployee?.[0]?.id || "",
						employee_name: matchedDataEmployee?.[0]?.employee_name || "",
						employee_number: matchedDataEmployee?.[0]?.employee_number || "",
						employee: matchedDataEmployee?.[0]?.employee_name || "",
						day: matchedDataEmployee ? moment(absenceclarificationchild.date).format("dddd") : "",
						day_status: matchedDataHariLibur[0]?.is_national_holiday
							? "National Holiday"
							: moment(absenceclarificationchild.date).format("dddd") === "Saturday" || moment(absenceclarificationchild.date).format("dddd") === "Sunday"
								? "Day Off"
								: "Work Day",
						holiday_name: matchedDataHariLibur[0]?.display_name || "",
						company: matchedDataCompany[0]?.display_name || "",
						shifttype: matchedDataShiftType[0]?.display_name || "",
						in_time: absenceclarificationchild.in_time === "00:00:00" ? "" : absenceclarificationchild.in_time,
						out_time: absenceclarificationchild.out_time === "00:00:00" ? "" : absenceclarificationchild.out_time,
						status_absenceclarification: absenceclarificationchild?.status_absenceclarification || false,
						status_working_in_dayoff: absenceclarificationchild?.status_working_in_dayoff || false,
						modified_by_full_name: matchedModifiedBy[0]?.full_name || "",
					};
				}),
				modified_by_full_name: modifiedBy && modifiedBy.filter((modifiedBy) => modifiedBy.id === item.modified_by).map((modifiedBy) => modifiedBy.full_name)[0],
				workflow: workflow ? workflow.dataValues : workflowsowner ? workflowsowner.dataValues : null,
			};
		});

		return {
			absenceClarifications: data,
			success: true,
			message: "absence clarifications has been loaded!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to load absence clarifications!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

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

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

		const filterConditionsAbsenceClarificationOr = [];

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

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

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

		if (filterConditionsAbsenceClarificationOr.length > 0) {
			filterConditionsAbsenceClarification[Op.or] = filterConditionsAbsenceClarificationOr;
		}

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

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

		const employees = await dbSAPPS.Employee.findAll({
			attributes: ["id", "employee_name", "employee_number"],
			order: [["employee_name", "ASC"]],
		});

		const companies = await dbSAPPS.Company.findAll({
			attributes: ["id", "name", "display_name"],
			order: [["display_name", "ASC"]],
		});

		const shiftTypes = await dbSAPPS.ShiftType.findAll({
			attributes: ["id", "name", "display_name"],
			order: [["display_name", "ASC"]],
		});

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

		const holidays = await dbSAPPS.Holiday.findAll({
			where: {
				enabled: true,
				holiday_date: {
					[Op.between]: [params.from_date, params.to_date],
				},
			},
			attributes: ["holiday_date", "is_national_holiday", "is_weekend_holiday", "display_name"],
		});

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

			return {
				...item.dataValues,
				absenceclarificationchilds: item.absenceclarificationchilds.map((absenceclarificationchild) => {
					const matchedDataEmployee = employees && employees.filter((employee) => employee.id === absenceclarificationchild.employee_id);
					const matchedDataCompany = companies && companies.filter((company) => company.id === absenceclarificationchild.company_id);
					const matchedDataShiftType = shiftTypes && shiftTypes.filter((shiftType) => shiftType.id === absenceclarificationchild.shifttype_id);
					const matchedDataHariLibur = holidays && holidays.filter((holiday) => holiday.holiday_date === absenceclarificationchild.date);
					const dayStatus = matchedDataHariLibur[0]?.is_national_holiday
						? "National Holiday"
						: moment(absenceclarificationchild.date).format("dddd") === "Saturday" || moment(absenceclarificationchild.date).format("dddd") === "Sunday"
							? "Day Off"
							: "Work Day";
					const matchedModifiedBy = modifiedBy && modifiedBy.filter((modifiedBy) => modifiedBy.id === absenceclarificationchild.modified_by);

					return {
						...absenceclarificationchild.dataValues,
						employee_id: matchedDataEmployee?.[0]?.id || "",
						employee_name: matchedDataEmployee?.[0]?.employee_name || "",
						employee_number: matchedDataEmployee?.[0]?.employee_number || "",
						employee: matchedDataEmployee?.[0]?.employee_name || "",
						day: matchedDataEmployee ? moment(absenceclarificationchild.date).format("dddd") : "",
						day_status: matchedDataHariLibur[0]?.is_national_holiday
							? "National Holiday"
							: moment(absenceclarificationchild.date).format("dddd") === "Saturday" || moment(absenceclarificationchild.date).format("dddd") === "Sunday"
								? "Day Off"
								: "Work Day",
						holiday_name: matchedDataHariLibur[0]?.display_name || "",
						company: matchedDataCompany[0]?.display_name || "",
						shifttype: matchedDataShiftType[0]?.display_name || "",
						in_time: absenceclarificationchild.in_time === "00:00:00" ? "" : absenceclarificationchild.in_time,
						out_time: absenceclarificationchild.out_time === "00:00:00" ? "" : absenceclarificationchild.out_time,
						status_absenceclarification: absenceclarificationchild?.status_absenceclarification || false,
						status_working_in_dayoff: absenceclarificationchild?.status_working_in_dayoff || false,
						modified_by_full_name: matchedModifiedBy[0]?.full_name || "",
					};
				}),
				modified_by_full_name: modifiedBy && modifiedBy.filter((modifiedBy) => modifiedBy.id === item.modified_by).map((modifiedBy) => modifiedBy.full_name)[0],
				workflow: workflow ? workflow.dataValues : workflowsowner ? workflowsowner.dataValues : null,
			};
		});

		return {
			absenceClarifications: data,
			success: true,
			message: "absence clarifications has been loaded!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to load absence clarifications!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

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

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

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

		const absenceClarifications = await dbSAPPS.AbsenceClarification.findAll({
			attributes: ["id", "name", "display_name", "slug_name", "valid_from", "valid_to", "company_id"],
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.AbsenceClarificationChild,
					as: "absenceclarificationchilds",
					attributes: ["id", "absenceclarification_id", "code", "name", "display_name", "is_parent", "parent_id", "company_id", "ids", "enabled"],
					include: [
						{
							model: dbSAPPS.Company,
							as: "company",
							attributes: ["name"],
						},
						{
							model: dbSAPPS.Territory,
							as: "parent",
							attributes: ["display_name"],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["name"],
				},
			],
		});

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

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

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

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

async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		if (!params.creation) params.creation = Date.now();
		if (!params.modified) params.modified = Date.now();
		if (!params.modified_by) params.modified_by = 1;
		if (!params.owner) params.owner = 1;

		// Phase 3: Filter dan validasi Attendance - hanya proses yang benar-benar perlu FKA
		// Filter out attendance yang sudah punya PE/AR/LA yang APPROVED
		if (params.absenceclarificationchilds && Array.isArray(params.absenceclarificationchilds) && params.absenceclarificationchilds.length > 0) {
			const childAttendanceIds = params.absenceclarificationchilds
				.map((c) => (c.attendance_id ? parseInt(c.attendance_id, 10) : null))
				.filter((aid) => Number.isFinite(aid) && aid > 0);
			if (childAttendanceIds.length > 0) {
				// Fetch attendance dengan PE, AR, LA dan workflow mereka
				const attendances = await dbSAPPS.Attendance.findAll({
					where: { id: { [Op.in]: childAttendanceIds } },
					attributes: ["id", "attendancerequest_id", "leaveapplication_id", "permitease_id", "status_permitease"],
					include: [
						{
							model: dbSAPPS.PermitEase,
							as: "permitease",
							required: false,
						},
						{
							model: dbSAPPS.AttendanceRequest,
							as: "attendancerequest",
							required: false,
						},
						{
							model: dbSAPPS.LeaveApplication,
							as: "leaveapplication",
							required: false,
						},
					],
					transaction,
				});

				// Fetch workflows untuk PE, AR, LA yang ada
				const permitEaseIds = attendances.map((att) => att.permitease_id).filter((id) => id != null && id !== 0);
				const attendanceRequestIds = attendances.map((att) => att.attendancerequest_id).filter((id) => id != null && id !== 0);
				const leaveApplicationIds = attendances.map((att) => att.leaveapplication_id).filter((id) => id != null && id !== 0);

				const workflows = await dbSAPPS.Workflow.findAll({
					where: {
						[Op.or]: [
							{ doc_id: { [Op.in]: permitEaseIds }, doctype_id: 70 },
							{ doc_id: { [Op.in]: attendanceRequestIds }, doctype_id: 102 },
							{ doc_id: { [Op.in]: leaveApplicationIds }, doctype_id: 101 },
						],
					},
					attributes: ["id", "doc_id", "doctype_id", "status"],
					transaction,
				});

				// Map workflows by doc_id and doctype_id
				const workflowMap = new Map();
				workflows.forEach((wf) => {
					const key = `${wf.doctype_id}_${wf.doc_id}`;
					workflowMap.set(key, wf);
				});

				// Attach workflow ke masing-masing PE, AR, LA
				attendances.forEach((att) => {
					if (att.permitease && att.permitease_id) {
						const wfKey = `70_${att.permitease_id}`;
						att.permitease.workflow = workflowMap.get(wfKey) || null;
					}
					if (att.attendancerequest && att.attendancerequest_id) {
						const wfKey = `102_${att.attendancerequest_id}`;
						att.attendancerequest.workflow = workflowMap.get(wfKey) || null;
					}
					if (att.leaveapplication && att.leaveapplication_id) {
						const wfKey = `101_${att.leaveapplication_id}`;
						att.leaveapplication.workflow = workflowMap.get(wfKey) || null;
					}
				});

				// Filter out attendance yang sudah punya PE/AR/LA yang APPROVED
				const approvedAttendanceIds = new Set();
				attendances.forEach((att) => {
					const hasPermitEaseApproved = att.permitease && att.permitease.workflow && att.permitease.workflow.status === "Approved";
					const hasAttendanceRequestApproved = att.attendancerequest && att.attendancerequest.workflow && att.attendancerequest.workflow.status === "Approved";
					const hasLeaveApplicationApproved = att.leaveapplication && att.leaveapplication.workflow && att.leaveapplication.workflow.status === "Approved";

					if (hasPermitEaseApproved || hasAttendanceRequestApproved || hasLeaveApplicationApproved) {
						approvedAttendanceIds.add(att.id);
					}
				});

				// Filter childTableData: hanya proses yang tidak punya PE/AR/LA approved
				if (approvedAttendanceIds.size > 0) {
					params.absenceclarificationchilds = params.absenceclarificationchilds.filter((c) => {
						const attId = c.attendance_id ? parseInt(c.attendance_id, 10) : null;
						return !attId || !approvedAttendanceIds.has(attId);
					});

					// Jika semua attendance sudah approved, return error
					if (params.absenceclarificationchilds.length === 0) {
						await transaction.rollback();
						return {
							success: false,
							message:
								"Cannot create absence clarification: all selected attendance(s) are already covered by an approved Permit Ease, Attendance Request, or Leave Application. Days covered by approved requests do not require clarification.",
						};
					}
				}
			}
		}

		const absenceClarification = await dbSAPPS.AbsenceClarification.create(params, { transaction });
		if (params.absenceclarificationchilds) {
			if (Array.isArray(params.absenceclarificationchilds) && params.absenceclarificationchilds.length > 0) {
				const absenceClarificationchildsData = params.absenceclarificationchilds.map((absenceClarificationChild) => ({
					...absenceClarificationChild,
					creation: Date.now(),
					modified: Date.now(),
					modified_by: parseInt(params.modified_by) || 1,
					owner: parseInt(params.owner) || 1,
					absenceclarification_id: absenceClarification.id,
				}));
				await dbSAPPS.AbsenceClarificationChild.bulkCreate(absenceClarificationchildsData, { transaction });
			}
		}

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

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

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

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

				const workflowApproversData = workflowTerritories.map((workflowTerritory) => ({
					creation: Date.now(),
					modified: Date.now(),
					modified_by: parseInt(params.modified_by) || 1,
					owner: parseInt(params.owner) || 1,
					workflow_id: workflow.id,
					territory_id: parseInt(workflowTerritory.territory_id),
					status: null,
				}));
				const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, { transaction });
				if (
					!approvers ||
					approvers.length !== workflowTerritories.length ||
					!approvers.every((approver) => approver.workflow_id === workflow.id && approver.territory_id !== null)
				) {
					await transaction.rollback();
					return {
						success: false,
						message: "Failed to create workflow approvers. Please try again later.",
					};
				}
			}
		} else {
			if (params.workflowdesignations) {
				if (Array.isArray(params.workflowdesignations) && params.workflowdesignations.length > 0) {
					const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
						{
							where: {
								doctype_id: params.doctype_id,
								type: "Designation",
								enabled: true,
							},
						},
						{ transaction },
					);
					const limitWorkflowDesignations = workflowSettings[0]?.limit || 0;
					const workflowDesignations = params.workflowdesignations.slice(0, limitWorkflowDesignations);

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

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

					let workflow = await dbSAPPS.Workflow.create(workflowData, {
						transaction,
						lock: transaction.LOCK.UPDATE,
					});
					if (!workflow) {
						await transaction.rollback();
						return {
							success: false,
							message: "Failed to create workflow. Please try again later.",
						};
					}

					const workflowApproversData = workflowDesignations.map((workflowDesignation) => ({
						creation: Date.now(),
						modified: Date.now(),
						modified_by: parseInt(params.modified_by) || 1,
						owner: parseInt(params.owner) || 1,
						workflow_id: workflow.id,
						designation_id: workflowDesignation.designation_id,
						status: null,
					}));
					let approvers = await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, { transaction, lock: transaction.LOCK.UPDATE });
					if (
						!approvers ||
						approvers.length !== workflowDesignations.length ||
						!approvers.every((approver) => approver.workflow_id === workflow.id && approver.designation_id !== null)
					) {
						await transaction.rollback();
						return {
							success: false,
							message: "Failed to create workflow approvers. Please try again later.",
						};
					}
				}
			}
		}

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

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

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

		params.company_id = parseInt(params.company_id) || null;
		params.employee_id = parseInt(params.employee_id) || null;
		params.modified_by = parseInt(params.modified_by) || null;
		params.owner = parseInt(params.owner) || null;
		params.attendances = params.absenceclarificationchilds ? params.absenceclarificationchilds : [];

		// Validasi: Filter dan validasi Attendance - hanya proses yang benar-benar perlu FKA
		// Filter out attendance yang sudah punya PE/AR/LA yang APPROVED
		if (params.absenceclarificationchilds && Array.isArray(params.absenceclarificationchilds) && params.absenceclarificationchilds.length > 0) {
			const childAttendanceIds = params.absenceclarificationchilds
				.map((c) => (c.attendance_id ? parseInt(c.attendance_id, 10) : null))
				.filter((aid) => Number.isFinite(aid) && aid > 0);
			if (childAttendanceIds.length > 0) {
				// Fetch attendance dengan PE, AR, LA dan workflow mereka
				const attendances = await dbSAPPS.Attendance.findAll({
					where: { id: { [Op.in]: childAttendanceIds } },
					attributes: ["id", "attendancerequest_id", "leaveapplication_id", "permitease_id", "status_permitease"],
					include: [
						{
							model: dbSAPPS.PermitEase,
							as: "permitease",
							required: false,
						},
						{
							model: dbSAPPS.AttendanceRequest,
							as: "attendancerequest",
							required: false,
						},
						{
							model: dbSAPPS.LeaveApplication,
							as: "leaveapplication",
							required: false,
						},
					],
					transaction,
				});

				// Fetch workflows untuk PE, AR, LA yang ada
				const permitEaseIds = attendances.map((att) => att.permitease_id).filter((id) => id != null && id !== 0);
				const attendanceRequestIds = attendances.map((att) => att.attendancerequest_id).filter((id) => id != null && id !== 0);
				const leaveApplicationIds = attendances.map((att) => att.leaveapplication_id).filter((id) => id != null && id !== 0);

				const workflows = await dbSAPPS.Workflow.findAll({
					where: {
						[Op.or]: [
							{ doc_id: { [Op.in]: permitEaseIds }, doctype_id: 70 },
							{ doc_id: { [Op.in]: attendanceRequestIds }, doctype_id: 102 },
							{ doc_id: { [Op.in]: leaveApplicationIds }, doctype_id: 101 },
						],
					},
					attributes: ["id", "doc_id", "doctype_id", "status"],
					transaction,
				});

				// Map workflows by doc_id and doctype_id
				const workflowMap = new Map();
				workflows.forEach((wf) => {
					const key = `${wf.doctype_id}_${wf.doc_id}`;
					workflowMap.set(key, wf);
				});

				// Attach workflow ke masing-masing PE, AR, LA
				attendances.forEach((att) => {
					if (att.permitease && att.permitease_id) {
						const wfKey = `70_${att.permitease_id}`;
						att.permitease.workflow = workflowMap.get(wfKey) || null;
					}
					if (att.attendancerequest && att.attendancerequest_id) {
						const wfKey = `102_${att.attendancerequest_id}`;
						att.attendancerequest.workflow = workflowMap.get(wfKey) || null;
					}
					if (att.leaveapplication && att.leaveapplication_id) {
						const wfKey = `101_${att.leaveapplication_id}`;
						att.leaveapplication.workflow = workflowMap.get(wfKey) || null;
					}
				});

				// Filter out attendance yang sudah punya PE/AR/LA yang APPROVED
				const approvedAttendanceIds = new Set();
				attendances.forEach((att) => {
					const hasPermitEaseApproved = att.permitease && att.permitease.workflow && att.permitease.workflow.status === "Approved";
					const hasAttendanceRequestApproved = att.attendancerequest && att.attendancerequest.workflow && att.attendancerequest.workflow.status === "Approved";
					const hasLeaveApplicationApproved = att.leaveapplication && att.leaveapplication.workflow && att.leaveapplication.workflow.status === "Approved";

					if (hasPermitEaseApproved || hasAttendanceRequestApproved || hasLeaveApplicationApproved) {
						approvedAttendanceIds.add(att.id);
					}
				});

				// Filter childTableData: hanya proses yang tidak punya PE/AR/LA approved
				if (approvedAttendanceIds.size > 0) {
					params.absenceclarificationchilds = params.absenceclarificationchilds.filter((c) => {
						const attId = c.attendance_id ? parseInt(c.attendance_id, 10) : null;
						return !attId || !approvedAttendanceIds.has(attId);
					});

					// Jika semua attendance sudah approved, return error
					if (params.absenceclarificationchilds.length === 0) {
						await transaction.rollback();
						return {
							success: false,
							message:
								"Cannot update absence clarification: all selected attendance(s) are already covered by an approved Permit Ease, Attendance Request, or Leave Application. Days covered by approved requests do not require clarification.",
						};
					}
				}
			}
		}

		await absenceClarification.update(params, { transaction });
		if (params.absenceclarificationchilds) {
			await dbSAPPS.AbsenceClarificationChild.destroy({
				where: { absenceclarification_id: id },
				transaction,
			});
			if (Array.isArray(params.absenceclarificationchilds) && params.absenceclarificationchilds.length > 0) {
				const absenceClarificationchildsData = params.absenceclarificationchilds.map((absenceClarificationChild) => ({
					...absenceClarificationChild,
					modified: params.modified,
					modified_by: parseInt(params.modified_by) || null,
					owner: parseInt(absenceClarificationChild.owner) || null,
					shifttype_id: parseInt(absenceClarificationChild.shifttype_id) || null,
					company_id: parseInt(absenceClarificationChild.company_id) || null,
					employee_id: parseInt(absenceClarificationChild.employee_id) || null,
					attendance_id: parseInt(absenceClarificationChild.attendance_id) || null,
					total_hour: parseInt(absenceClarificationChild.total_hour) || null,
					in_time:
						(absenceClarificationChild.day_status === "Day Off" || absenceClarificationChild.day_status === "National Holiday") &&
						!absenceClarificationChild.in_time
							? null
							: absenceClarificationChild.in_time
								? absenceClarificationChild.in_time
								: absenceClarificationChild.in_time === "00:00:00"
									? "09:00:00"
									: "09:00:00",
					out_time:
						(absenceClarificationChild.day_status === "Day Off" || absenceClarificationChild.day_status === "National Holiday") &&
						!absenceClarificationChild.out_time
							? null
							: absenceClarificationChild.out_time
								? absenceClarificationChild.out_time
								: absenceClarificationChild.out_time === "00:00:00"
									? "18:00:00"
									: "18:00:00",
					reason: absenceClarificationChild.reason
						? absenceClarificationChild.reason
						: absenceClarificationChild.status_absenceclarification === true && !absenceClarificationChild.reason
							? "lupa absen"
							: null,
					absenceclarification_id: id,
				}));
				await dbSAPPS.AbsenceClarificationChild.bulkCreate(absenceClarificationchildsData, { transaction });
			}
		}

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

		if (params.workflow?.status === "Approved" && (params.administrator_of_hr === true || params.administrator_of_hr === "true")) {
			if (Array.isArray(params.attendances) && params.attendances.length > 0) {
				const attendances = params.attendances.map((attendance) => ({
					...attendance,
					id: parseInt(attendance.id) || null,
					modified_by: parseInt(params.modified_by) || null,
					owner: parseInt(attendance.owner) || null,
					absenceclarification_id: parseInt(attendance.absenceclarification_id) || null,
					attendance_id: parseInt(attendance.attendance_id) || null,
					company_id: parseInt(attendance.company_id) || null,
					employee_id: parseInt(attendance.employee_id) || null,
					shifttype_id: parseInt(attendance.shifttype_id) || null,
					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);
				const absenceClarificationId = id;

				try {
					if (foundStatusAbsenceClarification.length > 0) {
						const attendanceData = foundStatusAbsenceClarification.map((attendance) => {
							const { id: _childId, ...attendanceWithoutId } = attendance;
							return {
								creation: Date.now(),
								modified: Date.now(),
								absenceclarification_id: absenceClarificationId,
								absenceclarificationchild_id: attendance?.id,
								is_absence_clarification: true,
								...attendanceWithoutId,
							};
						});

						const createdAttendances = await dbSAPPS.Attendance.bulkCreate(attendanceData, { transaction });
						const createdAttendanceIds = createdAttendances.map((attendance) => attendance.id);

						const absenceclarificationchildIds = foundStatusAbsenceClarification.map((attendance) => attendance.id);
						for (let i = 0; i < absenceclarificationchildIds.length; i++) {
							await dbSAPPS.AbsenceClarificationChild.update(
								{ attendance_id: createdAttendanceIds[i] },
								{ where: { id: absenceclarificationchildIds[i] }, transaction },
							);
						}
					}

					const updatedAttendance = foundAttendanceId.map((attendance) => ({
						modified: Date.now(),
						...attendance,
						absenceclarification_id: parseInt(attendance.absenceclarification_id) || null,
						absenceclarificationchild_id: parseInt(attendance.id) || null,
						modified_by: parseInt(params.modified_by) || null,
						id: parseInt(attendance.attendance_id) || null,
						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.",
					};
				}
			}
		}

		await transaction.commit();

		// Bridge: sync workflow status when this direct-update path is used to transition workflow.
		const requestedWorkflowStatus = params.workflow?.status;
		let workflowSync = null;

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

				if (workflowRow && workflowRow.status !== requestedWorkflowStatus) {
					const payload = {
						status: requestedWorkflowStatus,
						doctype_id: 104,
						doc_id: parseInt(id, 10),
						modified: params.modified || new Date(),
						modified_by: params.modified_by,
						owner: params.owner,
						...(params.workflow?.rejection_reason != null && {
							rejection_reason: params.workflow.rejection_reason,
						}),
						...(params.workflow?.revision_notes != null && {
							revision_notes: params.workflow.revision_notes,
						}),
						...(params.workflow?.cancel_reason != null && {
							cancel_reason: params.workflow.cancel_reason,
						}),
					};

					workflowSync =
						requestedWorkflowStatus === "Need Approval"
							? await workflowService.submitToWorkflow(workflowRow.id, payload)
							: await workflowService.actionToWorkflow(workflowRow.id, payload);
				} else if (!workflowRow) {
					workflowSync = {
						success: false,
						message: "Workflow row not found for this Absence Clarification (doctype_id 104).",
					};
				}
			} catch (workflowError) {
				workflowSync = {
					success: false,
					message: workflowError?.message || "Failed to sync workflow.",
				};
			}
		}

		return {
			createdAttendanceIds,
			updatedAttendanceIds,
			success: true,
			message: "Absence clarification has been updated!",
			workflowSync,
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to update absence clarification.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const absenceClarification = await dbSAPPS.AbsenceClarification.findByPk(id);
		if (!absenceClarification) return { success: false, message: "Absence clarification not found!" };

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

		await transaction.commit();
		return {
			success: true,
			message: "Absence clarification has been deleted!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "Failed to delete absence clarification.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

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

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

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

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

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

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

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

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

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

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

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