const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
const moment = require("moment");
require("dotenv").config();
const nodemailer = require("nodemailer");
const path = require("path");
const emailTemplateRejected = require("./emailTemplates/email.rejected");
const emailTemplateApproved = require("./emailTemplates/email.approved");
const emailTemplateCancelled = require("./emailTemplates/email.cancelled");
const emailTemplateNeedApproval = require("./emailTemplates/email.needapproval");
const emailTemplateRevised = require("./emailTemplates/email.revised");

module.exports = {
	create,
	getAll,
	option,
	count,
	update,
	approve,
	revise,
	reject,
	delete: _delete,
	createWorkflow,
};

// Doctype ID for Attendance Request - must match workflow doctype in database
const ATTENDANCE_REQUEST_DOCTYPE_ID = 110;

const getDatesInRange = (fromDate, toDate) => {
	const dates = [];
	const currentDate = new Date(fromDate);
	const endDate = new Date(toDate);

	// Clone date untuk menghindari mutasi
	const date = new Date(currentDate);

	while (date <= endDate) {
		dates.push(new Date(date));
		date.setDate(date.getDate() + 1);
	}

	return dates;
};

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

		if (params.companiesId) {
			filterConditions.company_id = {
				[Op.in]: params.companiesId,
			};
		}

		if (params.company_id) {
			filterConditions.company_id = {
				[Op.in]: params.company_id,
			};
		}

		if (params.employeesId) {
			filterConditions.employee_id = {
				[Op.in]: params.employeesId,
			};
		}

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

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

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

		if (params.from_date && params.to_date) {
			filterConditions.date = {
				[Op.between]: [params.from_date, params.to_date],
			};
		} else if (params.date) {
			filterConditions.date = {
				[Op.eq]: params.date,
			};
		}

		let attendanceRequests = [];
		attendanceRequests = await dbSAPPS.AttendanceRequest.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name", "name"],
				},
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
				{
					model: dbSAPPS.Department,
					as: "department",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.User,
					as: "modifier", // pastikan alias sesuai relasi di model Attendance
					attributes: ["full_name"],
				},
				{
					model: dbSAPPS.Employee,
					as: "leaveapprover_name", // pastikan alias sesuai relasi di model attendanceRequest
					attributes: ["employee_name"],
				},
			],
		});

		// Fetch workflows for attendance requests (similar to employee request pattern)
		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.in]: attendanceRequests.map((item) => item.id),
				},
				doctype_id: ATTENDANCE_REQUEST_DOCTYPE_ID,
			},
			attributes: [
				"id",
				"doc_id",
				"doctype_id",
				"status",
				"modified_by",
				"modified",
			],
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: [
						"id",
						"workflow_id",
						"designation_id",
						"territory_id",
						"status",
						"modified",
					],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["id", "employee_name"],
								},
							],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employees",
									attributes: ["id", "employee_name"],
									include: [
										{
											model: dbSAPPS.Designation,
											as: "designation",
											attributes: ["display_name"],
										},
									],
								},
							],
						},
					],
				},
			],
		});

		const data = attendanceRequests.map((item) => {
			const workflow = workflows.find((wf) => wf.doc_id === item.id);
			return {
				...item.dataValues,
				company_name: item?.company?.display_name,
				employee_name: item?.employee?.employee_name,
				leavetype_name: item?.leavetype?.display_name,
				department_name: item?.department?.display_name,
				modifier: item?.modifier?.full_name,
				leaveapprover_name: item?.leaveapprover_name?.employee_name,
				workflow: workflow ? workflow.dataValues : null,
			};
		});

		return {
			attendanceRequests: data,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

		if (params.companiesId) {
			filterConditions.company_id = {
				[Op.in]: params.companiesId,
			};
		}

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

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

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

		if (params.from_date && params.to_date) {
			filterConditions.date = {
				[Op.between]: [params.from_date, params.to_date],
			};
		} else if (params.date) {
			filterConditions.date = {
				[Op.eq]: params.date,
			};
		}

		let options = [];
		options = await dbSAPPS.AttendanceRequest.findAll({
			where: filterConditions,
			order: [["name", "ASC"]],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name", "name"],
				},
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
				{
					model: dbSAPPS.Department,
					as: "department",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.User,
					as: "modifier", // pastikan alias sesuai relasi di model Attendance
					attributes: ["full_name"],
				},
				{
					model: dbSAPPS.Employee,
					as: "leaveapprover_name", // pastikan alias sesuai relasi di model attendanceRequest
					attributes: ["employee_name"],
				},
			],
		});

		options = options.map((option) => {
			return {
				...option.dataValues,
				company_name: option?.company?.display_name,
			};
		});

		return {
			options,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

		const count = await dbSAPPS.AttendanceRequest.count({
			where: filterConditions,
		});

		return {
			success: true,
			count,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

	try {
		// validate existing
		const existing = await dbSAPPS.AttendanceRequest.findOne({
			where: {
				employee_id: params.employee_id,
				company_id: params.company_id,
				date: params.date,
				status: {
					[Op.in]: ["Approved", "Need Approval", "Draft"],
				},
			},
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
			],
			transaction,
		});
		if (existing) {
			return {
				success: false,
				message: `Employee ${existing.employee?.employee_name} has already applied for ${existing.date}.`,
			};
		}

		// Block if date already has Absence Clarification (FKA), Leave Application, or Permit Ease
		const existingAttForCreate = await dbSAPPS.Attendance.findOne({
			where: {
				employee_id: params.employee_id,
				date: params.date,
			},
			attributes: ["id", "date", "absenceclarification_id", "is_absence_clarification", "leaveapplication_id", "permitease_id", "status_permitease"],
			transaction,
		});
		if (existingAttForCreate) {
			const hasFKA =
				(existingAttForCreate.absenceclarification_id != null && existingAttForCreate.absenceclarification_id !== 0) ||
				existingAttForCreate.is_absence_clarification === true ||
				existingAttForCreate.is_absence_clarification === 1 ||
				existingAttForCreate.is_absence_clarification === "1";
			if (hasFKA) {
				await transaction.rollback();
				return {
					success: false,
					message: `Tanggal ${params.date} sudah tercatat dalam Absence Clarification (FKA). Tidak dapat membuat Attendance Request untuk tanggal yang sama.`,
				};
			}
			if (existingAttForCreate.leaveapplication_id != null && existingAttForCreate.leaveapplication_id !== 0) {
				await transaction.rollback();
				return {
					success: false,
					message: `Tanggal ${params.date} sudah tercatat sebagai Cuti (Leave Application). Tidak dapat membuat Attendance Request untuk tanggal yang sama.`,
				};
			}
			if (
				(existingAttForCreate.permitease_id != null && existingAttForCreate.permitease_id !== 0) ||
				existingAttForCreate.status_permitease === true ||
				existingAttForCreate.status_permitease === 1 ||
				existingAttForCreate.status_permitease === "true"
			) {
				await transaction.rollback();
				return {
					success: false,
					message: `Tanggal ${params.date} sudah tercatat sebagai Permit Ease (Izin). Tidak dapat membuat Attendance Request untuk tanggal yang sama.`,
				};
			}
		}

		if (!params.status) params.status = "Draft";

		// save
		const attendanceRequest = await dbSAPPS.AttendanceRequest.create(params, {
			transaction,
		});

		// create HistoryAttendanceRequest
		await dbSAPPS.HistoryAttendanceRequest.create(
			{
				creation: new Date(),
				modified: params.modified,
				modified_by: params.modified_by,
				owner: attendanceRequest.owner,
				attendacerequest_id: attendanceRequest.id,
				action: params.status,
				date: new Date(),
			},
			{ transaction }
		);

		await transaction.commit();
		return {
			success: true,
			message: "Saved successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function update(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();

	try {
		// validate existing
		const attendanceRequest = await dbSAPPS.AttendanceRequest.findByPk(id);
		if (!attendanceRequest) return { success: false, message: "ID not found!" };

		const existing = await dbSAPPS.AttendanceRequest.findOne({
			where: {
				employee_id: params.employee_id,
				company_id: params.company_id,
				status: {
					[Op.in]: ["Approved", "Need Approval", "Draft"],
				},
				date: {
					[Op.eq]: params.date,
				},
				id: {
					[Op.ne]: id,
				},
			},
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
			],
			transaction,
		});
		if (existing) {
			return {
				success: false,
				message: `Employee ${existing.employee?.employee_name} has already applied for ${existing.date}.`,
			};
		}

		// Block if date already has Absence Clarification (FKA), Leave Application, or Permit Ease
		const existingAttForUpdate = await dbSAPPS.Attendance.findOne({
			where: {
				employee_id: params.employee_id,
				date: params.date,
			},
			attributes: ["id", "date", "absenceclarification_id", "is_absence_clarification", "leaveapplication_id", "permitease_id", "status_permitease"],
			transaction,
		});
		if (existingAttForUpdate) {
			const hasFKA =
				(existingAttForUpdate.absenceclarification_id != null && existingAttForUpdate.absenceclarification_id !== 0) ||
				existingAttForUpdate.is_absence_clarification === true ||
				existingAttForUpdate.is_absence_clarification === 1 ||
				existingAttForUpdate.is_absence_clarification === "1";
			if (hasFKA) {
				await transaction.rollback();
				return {
					success: false,
					message: `Tanggal ${params.date} sudah tercatat dalam Absence Clarification (FKA). Tidak dapat mengubah Attendance Request untuk tanggal yang sama.`,
				};
			}
			if (existingAttForUpdate.leaveapplication_id != null && existingAttForUpdate.leaveapplication_id !== 0) {
				await transaction.rollback();
				return {
					success: false,
					message: `Tanggal ${params.date} sudah tercatat sebagai Cuti (Leave Application). Tidak dapat mengubah Attendance Request untuk tanggal yang sama.`,
				};
			}
			if (
				(existingAttForUpdate.permitease_id != null && existingAttForUpdate.permitease_id !== 0) ||
				existingAttForUpdate.status_permitease === true ||
				existingAttForUpdate.status_permitease === 1 ||
				existingAttForUpdate.status_permitease === "true"
			) {
				await transaction.rollback();
				return {
					success: false,
					message: `Tanggal ${params.date} sudah tercatat sebagai Permit Ease (Izin). Tidak dapat mengubah Attendance Request untuk tanggal yang sama.`,
				};
			}
		}

		// save
		await attendanceRequest.update(params, { transaction });

		// Sinkronkan status ke Workflow agar Workflow Approval (dashboard) dan Workflows Inbox satu sumber data
		if (params.status && ["Approved", "Rejected", "Draft", "Need Approval"].includes(params.status)) {
			const workflow = await dbSAPPS.Workflow.findOne({
				where: { doc_id: parseInt(id), doctype_id: ATTENDANCE_REQUEST_DOCTYPE_ID },
				transaction,
			});
			if (workflow) {
				await workflow.update(
					{
						status: params.status,
						modified: params.modified || new Date(),
						modified_by: params.modified_by != null ? parseInt(params.modified_by) : workflow.modified_by,
					},
					{ transaction }
				);
				// Sync WorkflowApprover agar History tab menampilkan item yang diapprove/reject/revise dari form
				if (["Approved", "Rejected", "Draft"].includes(params.status)) {
					await dbSAPPS.WorkflowApprover.update(
						{
							status: params.status,
							modified: params.modified || new Date(),
							modified_by: params.modified_by != null ? parseInt(params.modified_by) : null,
						},
						{ where: { workflow_id: workflow.id, status: "Need Approval" }, transaction }
					);
				}
			}
		}

		// get company dengan company_id
		const company = await dbSAPPS.Company.findByPk(
			attendanceRequest.company_id,
			{
				attributes: ["id", "display_name", "website"],
			}
		);
		if (!company) {
			return {
				success: false,
				message: "Company not found!",
			};
		}

		const website = company?.website || "";

		// create HistoryAttendanceRequest
		await dbSAPPS.HistoryAttendanceRequest.create(
			{
				creation: new Date(),
				modified: params.modified,
				modified_by: params.modified_by,
				owner: attendanceRequest.owner,
				attendacerequest_id: attendanceRequest.id,
				action: params.status,
				date: new Date(),
			},
			{ transaction }
		);

		// Draft
		if (
			params.status === "Draft" &&
			(params.is_save === false || params.is_save === "false")
		) {
			// send email
			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,
			});

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				attendanceRequest.follow_via_email === true
			) {
				// 1. Step 1 - Set konfigurasi email
				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,
					},
				});

				// 2. Step 2 - Set mail options
				const subject = `Attendance Request Revision (${
					attendanceRequest?.employee_name
				} - ${moment(attendanceRequest.date).format("yyyy-MM-DD")})`;

				const html = emailTemplateRevised.html(attendanceRequest);

				// 3. Send email and handle status
				let emailStatus = "sent";
				let errorMessage = null;

				try {
					await transporter.sendMail({
						from: emailAccount.email_account,
						to: attendanceRequest.employee_email,
						bcc: "it@sapharma.co.id",
						subject,
						html,
					});
				} catch (error) {
					console.error("Email sending failed:", error);
					emailStatus = "failed";
					errorMessage = error.message.substring(0, 255); // Truncate to fit in DB field
				}

				// 4. Create history email
				await dbSAPPS.HistoryEmail.create(
					{
						// main
						creation: new Date(),
						modified: new Date(),
						modified_by: params.modified_by,
						owner: params.owner,

						// extra
						company_id: attendanceRequest.company_id,
						employee_id: attendanceRequest.employee_id,
						recipient_email: attendanceRequest.employee_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						attendancerequest_id: attendanceRequest.id,
					},
					{ transaction }
				);
			}
			// Reject
		} else if (
			params.status === "Rejected" &&
			(params.is_save === false || params.is_save === "false")
		) {
			// send email
			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,
			});

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				attendanceRequest.follow_via_email === true
			) {
				// 1. Step 1 - Set konfigurasi email
				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,
					},
				});

				// 2. Step 2 - Set mail options
				const subject = `Attendance Request Rejected (${
					attendanceRequest?.employee_name
				} - ${moment(attendanceRequest.date).format("yyyy-MM-DD")})`;

				// 3. Send email and handle status
				let emailStatus = "sent";
				let errorMessage = null;

				const html = emailTemplateRejected.html(attendanceRequest);

				try {
					await transporter.sendMail({
						from: emailAccount.email_account,
						to: attendanceRequest.employee_email,
						bcc: "it@sapharma.co.id",
						subject,
						html,
					});
				} catch (error) {
					console.error("Email sending failed:", error);
					emailStatus = "failed";
					errorMessage = error.message.substring(0, 255); // Truncate to fit in DB field
				}

				// 4. Create history email
				await dbSAPPS.HistoryEmail.create(
					{
						// main
						creation: new Date(),
						modified: new Date(),
						modified_by: params.modified_by,
						owner: params.owner,

						// extra
						company_id: attendanceRequest.company_id,
						employee_id: attendanceRequest.employee_id,
						recipient_email: attendanceRequest.employee_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						attendancerequest_id: attendanceRequest.id,
					},
					{ transaction }
				);
			}

			// Need Approval
		} else if (params.status === "Need Approval") {
			// send email
			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,
			});

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				attendanceRequest.follow_via_email === true
			) {
				// 1. Step 1 - Set konfigurasi email
				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,
					},
				});

				// 2. Step 2 - Set mail options
				const subject = `Attendance Request Need Approval (${
					attendanceRequest?.employee_name
				} - ${moment(attendanceRequest.date).format("yyyy-MM-DD")})`;

				const html = emailTemplateNeedApproval.html(attendanceRequest, website);

				// 3. Send email and handle status
				let emailStatus = "sent";
				let errorMessage = null;

				try {
					await transporter.sendMail({
						from: `${attendanceRequest?.employee_name} <${emailAccount.email_account}>`,
						to: attendanceRequest.leave_approver_email,
						bcc: "it@sapharma.co.id",
						subject,
						html,
						attachments: attendanceRequest.filename
							? [
									{
										filename: attendanceRequest.filename,
										path: `./public/data/documents/attendanceRequests/${attendanceRequest.filename}`,
									},
							  ]
							: [],
					});
				} catch (error) {
					console.error("Email sending failed:", error);
					emailStatus = "failed";
					errorMessage = error.message.substring(0, 255); // Truncate to fit in DB field
				}

				// 4. Create history email
				await dbSAPPS.HistoryEmail.create(
					{
						// main
						creation: new Date(),
						modified: new Date(),
						modified_by: params.modified_by,
						owner: params.owner,

						// extra
						company_id: attendanceRequest.company_id,
						employee_id: attendanceRequest.employee_id,
						recipient_email: attendanceRequest.leave_approver_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						attendancerequest_id: attendanceRequest.id,
					},
					{ transaction }
				);
			}

			// Approved
		} else if (
			params.status === "Approved" &&
			(params.is_save === false || params.is_save === "false")
		) {
			// update attendance
			// Ambil semua attendance yang berpotensi di update/create
			const existingAttendances = await dbSAPPS.Attendance.findAll({
				where: {
					employee_id: attendanceRequest.employee_id,
					date: {
						[Op.eq]: params.date,
					},
				},
				transaction,
			});

			// Mapping attendance berdasarkan tanggal yyyy-MM-dd untuk akses cepat
			const attendanceMap = new Map();
			existingAttendances.forEach((att) => {
				const key = new Date(att.date).toISOString().split("T")[0]; // att.date.toISOString().split("T")[0];
				attendanceMap.set(key, att);
			});

			const datesToCreate = getDatesInRange(params.date, params.date);
			const attendanceRecordsToCreate = [];

			for (const date of datesToCreate) {
				const dateKey = new Date(date).toISOString().split("T")[0];

				const existingAttendance = attendanceMap.get(dateKey);

				if (existingAttendance) {
					// Jangan timpa tanggal yang sudah Leave atau Permit Ease
					if (existingAttendance.leaveapplication_id != null && existingAttendance.leaveapplication_id !== 0) {
						throw new Error(
							`Tanggal ${dateKey} sudah tercatat sebagai Cuti (Leave Application). Tidak dapat menyetujui Attendance Request untuk tanggal yang sama.`
						);
					}
					if (
						(existingAttendance.permitease_id != null && existingAttendance.permitease_id !== 0) ||
						existingAttendance.status_permitease === true ||
						existingAttendance.status_permitease === 1 ||
						existingAttendance.status_permitease === "true"
					) {
						throw new Error(
							`Tanggal ${dateKey} sudah tercatat sebagai Permit Ease (Izin). Tidak dapat menyetujui Attendance Request untuk tanggal yang sama.`
						);
					}
					// Cek kondisi in_time dan out_time
					const hasInTime = !!existingAttendance.in_time;
					const hasOutTime = !!existingAttendance.out_time;

					// Hanya boleh update jika salah satu atau keduanya belum terisi,
					// bukan jika keduanya sudah terisi
					if ((hasInTime && !hasOutTime) || (!hasInTime && hasOutTime)) {
						existingAttendance.attendancerequest_id = attendanceRequest.id;
						existingAttendance.leaveapplication_id = null;
						existingAttendance.permitease_id = null;
						existingAttendance.status_permitease = null;
						existingAttendance.in_time = attendanceRequest.in_time;
						existingAttendance.out_time = attendanceRequest.out_time;
						existingAttendance.in_status = attendanceRequest.in_status;
						existingAttendance.out_status = attendanceRequest.out_status;
						existingAttendance.total_hour = attendanceRequest.total_hour;
						existingAttendance.reason = attendanceRequest.reason;
						existingAttendance.need_clarification = false;

						await existingAttendance.save({ transaction });
					} else if (
						existingAttendance.in_status === "Late" ||
						existingAttendance.out_status === "Left Earlier"
					) {
						existingAttendance.attendancerequest_id = attendanceRequest.id;
						existingAttendance.leaveapplication_id = null;
						existingAttendance.permitease_id = null;
						existingAttendance.status_permitease = null;
						existingAttendance.in_time = attendanceRequest.in_time;
						existingAttendance.out_time = attendanceRequest.out_time;
						existingAttendance.in_status = attendanceRequest.in_status;
						existingAttendance.out_status = attendanceRequest.out_status;
						existingAttendance.total_hour = attendanceRequest.total_hour;
						existingAttendance.reason = attendanceRequest.reason;
						existingAttendance.need_clarification = false;

						await existingAttendance.save({ transaction });
					} else if (hasInTime && hasOutTime) {
						// Kedua waktu sudah ada, tidak bisa update half_day
						// Bisa skip atau lempar error sesuai kebutuhan
						// Contoh: lempar error
						throw new Error(
							`Attendance on ${dateKey} already has full in_time and out_time, cannot update.`
						);
					} else {
						// Kondisi lain, misal belum ada waktu sama sekali (tidak punya in_time/out_time)
						// Bisa logika lain sesuai kebutuhan, atau skip
					}

					// Jika ada attendance tapi bukan half_day_date, lempar error atau skip
					// throw new Error(`Employee ${attendanceRequest?.employee_name} already has an attendance on ${dateKey}`);
				} else {
					// Jika tidak ada attendance, buat baru
					const newRecord = {
						creation: new Date(),
						modified: params.modified,
						modified_by: params.modified_by,
						owner: params.owner,
						date: dateKey,
						in_time: attendanceRequest.in_time || null,
						out_time: attendanceRequest.out_time || null,
						in_status: attendanceRequest.in_status || null,
						out_status: attendanceRequest.out_status || null,
						in_distance: null,
						out_distance: null,
						in_address: null,
						out_address: null,
						total_hour: attendanceRequest.total_hour || null,
						notes: null,
						company_id: attendanceRequest.company_id || null,
						employee_id: attendanceRequest.employee_id || null,
						shifttype_id: attendanceRequest.shifttype_id || null,
						leavetype_id: null,
						attendancerequest_id: attendanceRequest.id,
						is_attendance_request: true,
						ids: null,
						import_from: null,
						in_latitude: null,
						out_latitude: null,
						in_longitude: null,
						out_longitude: null,
						absenceclarification_id: null,
						status_absenceclarification: null,
						status_working_in_dayoff: null,
						reason: attendanceRequest.reason || null,
						permitease_id: null,
						status_permitease: null,
						need_clarification: false,
					};
					attendanceRecordsToCreate.push(newRecord);
				}
			}

			if (attendanceRecordsToCreate.length > 0) {
				await dbSAPPS.Attendance.bulkCreate(attendanceRecordsToCreate, {
					transaction,
				});
			}

			// send email
			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,
			});

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				attendanceRequest.follow_via_email === true
			) {
				// 1. Step 1 - Set konfigurasi email
				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,
					},
				});

				// 2. Step 2 - Set mail options
				const subject = `Attendance Request Approved (${
					attendanceRequest?.employee_name
				} - ${moment(attendanceRequest.date).format("yyyy-MM-DD")})`;

				// 3. Step 3 - Set email template
				const html = emailTemplateApproved.html(attendanceRequest);

				// 4. Send email and handle status
				let emailStatus = "sent";
				let errorMessage = null;

				try {
					await transporter.sendMail({
						from: emailAccount.email_account,
						to: attendanceRequest.employee_email,
						bcc: "it@sapharma.co.id",
						subject,
						html,
					});
				} catch (error) {
					console.error("Email sending failed:", error);
					emailStatus = "failed";
					errorMessage = error.message.substring(0, 255); // Truncate to fit in DB field
				}

				// 5. Create history email
				await dbSAPPS.HistoryEmail.create(
					{
						// main
						creation: new Date(),
						modified: new Date(),
						modified_by: params.modified_by,
						owner: params.owner,

						// extra
						company_id: attendanceRequest.company_id,
						employee_id: attendanceRequest.employee_id,
						recipient_email: attendanceRequest.employee_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						attendancerequest_id: attendanceRequest.id,
					},
					{ transaction }
				);
			}

			// cancelled
		} else if (
			params.status === "Cancelled" &&
			(params.is_save === false || params.is_save === "false")
		) {
			// delete or update attendance
			// Ambil attendance terkait Attendance Request yang akan dicancel
			const existingAttendances = await dbSAPPS.Attendance.findAll({
				where: {
					attendancerequest_id: id,
				},
				transaction,
			});

			const attendanceIdsToDelete = [];
			const attendanceIdsToClear = [];

			for (const att of existingAttendances) {
				const hasInTime = att.in_time !== null;
				const hasOutTime = att.out_time !== null;

				if (hasInTime || hasOutTime) {
					// Jika ada in_time atau out_time, update attendance untuk clear cuti tetap simpan data kehadiran
					attendanceIdsToClear.push(att.id);
				} else {
					// Jika tidak ada keduanya, berarti ini cuti tanpa absen, bisa dihapus
					attendanceIdsToDelete.push(att.id);
				}
			}

			// Hapus attendance yang tidak memiliki in_time dan out_time
			if (attendanceIdsToDelete.length > 0) {
				await dbSAPPS.Attendance.destroy({
					where: { id: attendanceIdsToDelete },
					transaction,
				});
			}

			// Update attendance yang memiliki in_time atau out_time untuk membersihkan data AR
			if (attendanceIdsToClear.length > 0) {
				await dbSAPPS.Attendance.update(
					{
						attendancerequest_id: null,
						is_attendance_request: false,
						reason: null,
					},
					{
						where: { id: attendanceIdsToClear },
						transaction,
					}
				);
			}

			// send email
			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,
			});

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				attendanceRequest.follow_via_email === true
			) {
				// 1. Step 1 - Set konfigurasi email
				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,
					},
				});

				// 2. Step 2 - Set mail options
				const subject = `Attendance Request Cancelled (${
					attendanceRequest?.employee_name
				} - (${moment(attendanceRequest.date).format("yyyy-MM-DD")})`;

				const html = emailTemplateCancelled.html(attendanceRequest);

				// 3. Send email and handle status
				let emailStatus = "sent";
				let errorMessage = null;

				try {
					await transporter.sendMail({
						from: emailAccount.email_account,
						to: attendanceRequest.employee_email,
						bcc: "it@sapharma.co.id",
						subject,
						html,
					});
				} catch (error) {
					console.error("Email sending failed:", error);
					emailStatus = "failed";
					errorMessage = error.message.substring(0, 255); // Truncate to fit in DB field
				}

				// 4. Create history email
				await dbSAPPS.HistoryEmail.create(
					{
						// main
						creation: new Date(),
						modified: new Date(),
						modified_by: params.modified_by,
						owner: params.owner,

						// extra
						company_id: attendanceRequest.company_id,
						employee_id: attendanceRequest.employee_id,
						recipient_email: attendanceRequest.employee_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						attendancerequest_id: attendanceRequest.id,
					},
					{ transaction }
				);
			}
		}

		await transaction.commit();
		return {
			success: true,
			message: "Saved successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function approve(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const attendanceRequest = await dbSAPPS.AttendanceRequest.findByPk(id);
		if (!attendanceRequest) return { success: false, message: "ID not found!" };

		if (attendanceRequest.status === "Approved")
			throw new Error("Already approved!");
		if (attendanceRequest.status !== "Need Approval")
			throw new Error("You are not allowed to approve a attendance request!");

		// Ambil employee beserta shiftType relasi
		const employee = await dbSAPPS.Employee.findOne({
			where: { id: attendanceRequest.employee_id },
			include: [
				{
					model: dbSAPPS.ShiftType,
					as: "shifttype", // pastikan alias relasi sesuai model Anda
					attributes: ["id", "late_time", "end_time"],
				},
			],
			transaction,
		});

		if (!employee) throw new Error("Employee not found!");
		const shiftType = employee.shifttype;

		// update attendanceRequest
		await attendanceRequest.update(
			{
				status: params.status,
				modified: params.modified,
				modified_by: params.modified_by,
				approver_date: new Date(),
			},
			{ transaction }
		);

		// Sinkronkan status ke Workflow dan WorkflowApprover agar History tab menampilkan approval dari card
		const workflowApprove = await dbSAPPS.Workflow.findOne({
			where: { doc_id: parseInt(id), doctype_id: ATTENDANCE_REQUEST_DOCTYPE_ID },
			transaction,
		});
		if (workflowApprove) {
			await workflowApprove.update(
				{
					status: params.status,
					modified: params.modified || new Date(),
					modified_by: params.modified_by != null ? parseInt(params.modified_by) : workflowApprove.modified_by,
				},
				{ transaction }
			);
			await dbSAPPS.WorkflowApprover.update(
				{
					status: params.status,
					modified: params.modified || new Date(),
					modified_by: params.modified_by != null ? parseInt(params.modified_by) : null,
				},
				{ where: { workflow_id: workflowApprove.id, status: "Need Approval" }, transaction }
			);
		}

		// create HistoryattendanceRequest
		await dbSAPPS.HistoryAttendanceRequest.create(
			{
				creation: new Date(),
				modified: params.modified,
				modified_by: params.modified_by,
				owner: attendanceRequest.owner,
				attendancerequest_id: attendanceRequest.id,
				action: params.status,
				date: new Date(),
			},
			{ transaction }
		);

		// update attendance
		// Ambil semua attendance yang berpotensi di update/create (termasuk flag FKA untuk validasi)
		const existingAttendances = await dbSAPPS.Attendance.findAll({
			where: {
				employee_id: attendanceRequest.employee_id,
				date: {
					[Op.eq]: params.date,
				},
			},
			attributes: [
				"id",
				"date",
				"absenceclarification_id",
				"is_absence_clarification",
			],
			transaction,
		});

		// Mapping attendance berdasarkan tanggal yyyy-MM-dd untuk akses cepat
		const attendanceMap = new Map();
		existingAttendances.forEach((att) => {
			const key = new Date(att.date).toISOString().split("T")[0]; // att.date.toISOString().split("T")[0];
			attendanceMap.set(key, att);
		});

		const datesToCreate = getDatesInRange(params.date, params.date);
		const attendanceRecordsToCreate = [];

		for (const date of datesToCreate) {
			const dateKey = new Date(date).toISOString().split("T")[0];

			const existingAttendance = attendanceMap.get(dateKey);

			if (existingAttendance) {
				// Block jika attendance tersebut dari Absence Clarification (FKA)
				const hasFKAId =
					existingAttendance.absenceclarification_id != null &&
					existingAttendance.absenceclarification_id !== 0 &&
					existingAttendance.absenceclarification_id !== "";
				const hasFKAFlag =
					existingAttendance.is_absence_clarification === true ||
					existingAttendance.is_absence_clarification === 1 ||
					existingAttendance.is_absence_clarification === "1";
				if (hasFKAId || hasFKAFlag) {
					throw new Error(
						`Tanggal ${dateKey} sudah tercatat dalam Absence Clarification (FKA). Tidak dapat menyetujui Attendance Request untuk tanggal yang sama.`
					);
				}
				// Update attendance jika half_day_date sama dan kondisi update terpenuhi
				// Jika ada attendance tapi bukan half_day_date, lempar error atau skip
				throw new Error(
					`Employee ${attendanceRequest?.employee_name} already has an attendance on ${dateKey}`
				);
			} else {
				// Buat attendance baru, isi in_time dan out_time dari shiftType jika ada
				// Jika tidak ada attendance, buat baru
				const newRecord = {
					creation: new Date(),
					modified: params.modified,
					modified_by: params.modified_by,
					owner: params.owner,
					date: dateKey,
					in_time: attendanceRequest.in_time || null,
					out_time: attendanceRequest.out_time || null,
					in_status: attendanceRequest.in_status || null,
					out_status: attendanceRequest.out_status || null,
					in_distance: null,
					out_distance: null,
					in_address: null,
					out_address: null,
					total_hour: attendanceRequest.total_hour || null,
					notes: null,
					company_id: attendanceRequest.company_id || null,
					employee_id: attendanceRequest.employee_id || null,
					shifttype_id: attendanceRequest?.shifttype_id || null,
					leavetype_id: attendanceRequest.leavetype_id || null,
					leaveapplication_id: null,
					attendancerequest_id: attendanceRequest.id,
					is_attendance_request: true,
					ids: null,
					import_from: null,
					in_latitude: null,
					out_latitude: null,
					in_longitude: null,
					out_longitude: null,
					absenceclarification_id: null,
					status_absenceclarification: null,
					status_working_in_dayoff: null,
					reason: attendanceRequest.reason || null,
					permitease_id: null,
					status_permitease: null,
					need_clarification: false,
				};
				attendanceRecordsToCreate.push(newRecord);
			}
		}

		if (attendanceRecordsToCreate.length > 0) {
			await dbSAPPS.Attendance.bulkCreate(attendanceRecordsToCreate, {
				transaction,
			});
		}

		// send email
		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,
		});

		// get email account
		if (
			emailAccount?.email &&
			emailAccount?.email_account &&
			attendanceRequest.follow_via_email === true
		) {
			// 1. Step 1 - Set konfigurasi email
			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,
				},
			});

			// 2. Step 2 - Set mail options
			const subject = `Attendance Request Approved (${
				attendanceRequest?.employee_name
			} - ${moment(attendanceRequest.date).format("yyyy-MM-DD")})`;

			const html = emailTemplateApproved.html(attendanceRequest);

			// 3. Send email and handle status
			let emailStatus = "sent";
			let errorMessage = null;

			try {
				await transporter.sendMail({
					from: emailAccount.email_account,
					to: attendanceRequest.employee_email,
					bcc: "it@sapharma.co.id",
					subject,
					html,
				});
			} catch (error) {
				console.error("Email sending failed:", error);
				emailStatus = "failed";
				errorMessage = error.message.substring(0, 255); // Truncate to fit in DB field
			}

			// 4. Create history email
			await dbSAPPS.HistoryEmail.create(
				{
					// main
					creation: new Date(),
					modified: new Date(),
					modified_by: params.modified_by,
					owner: params.owner,

					// extra
					company_id: attendanceRequest.company_id,
					employee_id: attendanceRequest.employee_id,
					recipient_email: attendanceRequest.employee_email,
					subject: subject,
					body: html,
					status: emailStatus,
					error_message: errorMessage,
					attendancerequest_id: attendanceRequest.id,
				},
				{ transaction }
			);
		}

		await transaction.commit();
		return {
			success: true,
			message: "Approved successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function revise(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const attendanceRequest = await dbSAPPS.AttendanceRequest.findByPk(id);
		if (!attendanceRequest) return { success: false, message: "ID not found!" };

		if (attendanceRequest.status === "Draft") throw new Error("Already draft!");

		// update attendanceRequest
		await attendanceRequest.update(
			{
				status: params.status,
				modified: params.modified,
				modified_by: params.modified_by,
				approver_date: new Date(),
			},
			{ transaction }
		);

		// Sinkronkan status ke Workflow dan WorkflowApprover agar History tab menampilkan approval dari card
		const workflowRevise = await dbSAPPS.Workflow.findOne({
			where: { doc_id: parseInt(id), doctype_id: ATTENDANCE_REQUEST_DOCTYPE_ID },
			transaction,
		});
		if (workflowRevise) {
			await workflowRevise.update(
				{
					status: params.status,
					modified: params.modified || new Date(),
					modified_by: params.modified_by != null ? parseInt(params.modified_by) : workflowRevise.modified_by,
				},
				{ transaction }
			);
			await dbSAPPS.WorkflowApprover.update(
				{
					status: params.status,
					modified: params.modified || new Date(),
					modified_by: params.modified_by != null ? parseInt(params.modified_by) : null,
				},
				{ where: { workflow_id: workflowRevise.id, status: "Need Approval" }, transaction }
			);
		}

		// create HistoryattendanceRequest
		await dbSAPPS.HistoryAttendanceRequest.create(
			{
				creation: new Date(),
				modified: params.modified,
				modified_by: params.modified_by,
				owner: attendanceRequest.owner,
				attendancerequest_id: attendanceRequest.id,
				action: params.status,
				date: new Date(),
			},
			{ transaction }
		);

		// send email
		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,
		});

		// get email account
		if (
			emailAccount?.email &&
			emailAccount?.email_account &&
			attendanceRequest.follow_via_email === true
		) {
			// 1. Step 1 - Set konfigurasi email
			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,
				},
			});

			// 2. Step 2 - Set mail options
			const subject = `Attendance Request Revision (${
				attendanceRequest?.employee_name
			} - ${moment(attendanceRequest.date).format("yyyy-MM-DD")})`;

			const html = emailTemplateRevised.html(attendanceRequest);

			// 3. Send email and handle status
			let emailStatus = "sent";
			let errorMessage = null;

			try {
				await transporter.sendMail({
					from: emailAccount.email_account,
					to: attendanceRequest.employee_email,
					bcc: "it@sapharma.co.id",
					subject,
					html,
				});
			} catch (error) {
				console.error("Email sending failed:", error);
				emailStatus = "failed";
				errorMessage = error.message.substring(0, 255); // Truncate to fit in DB field
			}

			// 4. Create history email
			await dbSAPPS.HistoryEmail.create(
				{
					// main
					creation: new Date(),
					modified: new Date(),
					modified_by: params.modified_by,
					owner: params.owner,

					// extra
					company_id: attendanceRequest.company_id,
					employee_id: attendanceRequest.employee_id,
					recipient_email: attendanceRequest.employee_email,
					subject: subject,
					body: html,
					status: emailStatus,
					error_message: errorMessage,
					attendancerequest_id: attendanceRequest.id,
				},
				{ transaction }
			);
		}

		await transaction.commit();
		return {
			success: true,
			message: "Revised successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function reject(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const attendanceRequest = await dbSAPPS.AttendanceRequest.findByPk(id);
		if (!attendanceRequest) return { success: false, message: "ID not found!" };

		if (attendanceRequest.status === "Rejected")
			throw new Error("Already reject!");

		if (attendanceRequest.status !== "Need Approval")
			throw new Error("You are not allowed to reject a attendance request!");

		// update attendanceRequest
		await attendanceRequest.update(
			{
				status: params.status,
				modified: params.modified,
				modified_by: params.modified_by,
				approver_date: new Date(),
			},
			{ transaction }
		);

		// Sinkronkan status ke Workflow dan WorkflowApprover agar History tab menampilkan approval dari card
		const workflowReject = await dbSAPPS.Workflow.findOne({
			where: { doc_id: parseInt(id), doctype_id: ATTENDANCE_REQUEST_DOCTYPE_ID },
			transaction,
		});
		if (workflowReject) {
			await workflowReject.update(
				{
					status: params.status,
					modified: params.modified || new Date(),
					modified_by: params.modified_by != null ? parseInt(params.modified_by) : workflowReject.modified_by,
				},
				{ transaction }
			);
			await dbSAPPS.WorkflowApprover.update(
				{
					status: params.status,
					modified: params.modified || new Date(),
					modified_by: params.modified_by != null ? parseInt(params.modified_by) : null,
				},
				{ where: { workflow_id: workflowReject.id, status: "Need Approval" }, transaction }
			);
		}

		// create HistoryattendanceRequest
		await dbSAPPS.HistoryAttendanceRequest.create(
			{
				creation: new Date(),
				modified: params.modified,
				modified_by: params.modified_by,
				owner: attendanceRequest.owner,
				attendancerequest_id: attendanceRequest.id,
				action: params.status,
				date: new Date(),
			},
			{ transaction }
		);

		// send email
		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,
		});

		// get email account
		if (
			emailAccount?.email &&
			emailAccount?.email_account &&
			attendanceRequest.follow_via_email === true
		) {
			// 1. Step 1 - Set konfigurasi email
			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,
				},
			});

			// 2. Step 2 - Set mail options
			const subject = `Attendance Request Rejected (${
				attendanceRequest?.employee_name
			} - ${moment(attendanceRequest.date).format("yyyy-MM-DD")})`;

			const html = emailTemplateRejected.html(attendanceRequest);

			// 3. Send email and handle status
			let emailStatus = "sent";
			let errorMessage = null;

			try {
				await transporter.sendMail({
					from: emailAccount.email_account,
					to: attendanceRequest.employee_email,
					bcc: "it@sapharma.co.id",
					subject,
					html,
				});
			} catch (error) {
				console.error("Email sending failed:", error);
				emailStatus = "failed";
				errorMessage = error.message.substring(0, 255); // Truncate to fit in DB field
			}

			// 4. Create history email
			await dbSAPPS.HistoryEmail.create(
				{
					// main
					creation: new Date(),
					modified: new Date(),
					modified_by: params.modified_by,
					owner: params.owner,

					// extra
					company_id: attendanceRequest.company_id,
					employee_id: attendanceRequest.employee_id,
					recipient_email: attendanceRequest.employee_email,
					subject: subject,
					body: html,
					status: emailStatus,
					error_message: errorMessage,
					attendancerequest_id: attendanceRequest.id,
				},
				{ transaction }
			);
		}

		await transaction.commit();
		return {
			success: true,
			message: "Rejected successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function _delete(id) {
	try {
		// validate existing
		const attendanceRequest = await dbSAPPS.AttendanceRequest.findByPk(id, {
			transaction,
		});
		if (!attendanceRequest) return { success: false, message: "ID not found!" };

		if (
			attendanceRequest.status === "Need Approval" ||
			attendanceRequest.status === "Approved" ||
			attendanceRequest.status === "Rejected" ||
			attendanceRequest.status === "Cancelled"
		) {
			throw new Error(
				`Cannot delete, attendance request is already ${attendanceRequest.status}!`
			);
		}

		// delete HistoryEmail
		await dbSAPPS.HistoryEmail.destroy({
			where: {
				attendancerequest_id: id,
			},
			transaction,
		});

		// delete HistoryattendanceRequest
		await dbSAPPS.HistoryAttendanceRequest.destroy({
			where: {
				attendancerequest_id: id,
			},
			transaction,
		});

		// delete
		await dbSAPPS.Attendance.destroy({
			where: {
				attendancerequest_id: id,
				[Op.or]: [{ in_time: null }, { out_time: null }],
			},
			transaction,
		});

		// delete
		await attendanceRequest.destroy({ transaction });

		await transaction.commit();
		return {
			success: true,
			message: "Deleted successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage =
			error?.message || "Failed to delete, please try again!";
		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) || ATTENDANCE_REQUEST_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) || ATTENDANCE_REQUEST_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) {
				await transaction.rollback();
				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) || ATTENDANCE_REQUEST_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) {
					await transaction.rollback();
					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,
				};

				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.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) {
		await transaction.rollback();
		const errorMessage = error.message || "Failed to create workflow.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}
