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

/**
 * Format an array of leaveApplications by appending a modifier field to each company.
 * The modifier field is the full name of the user who last modified the company.
 */
const formatWithModifier = async (leaveApplications) => {
	return Promise.all(
		leaveApplications.map(async (leaveApplication) => {
			const modifier = await dbSAPPS.User.findOne({
				where: {
					id: leaveApplication.modified_by,
				},
				attributes: ["full_name"],
			});

			return {
				...leaveApplication,
				modifier: modifier?.full_name,
			};
		})
	);
};

const formatWithApprover = async (leaveApplications) => {
	return Promise.all(
		leaveApplications.map(async (leaveApplication) => {
			const approver = await dbSAPPS.Employee.findOne({
				where: {
					id: leaveApplication.leave_approver,
				},
				attributes: ["employee_name"],
			});

			return {
				...leaveApplication,
				leaveapprover_name: approver?.employee_name,
			};
		})
	);
};

// Doctype ID for Leave Application - should match doctype in database
// Note: This should be configured based on actual doctype ID in your database
// For now using a placeholder - update this with actual doctype ID
const LEAVE_APPLICATION_DOCTYPE_ID = 111; // TODO: Update with actual doctype ID from database

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.from_date = {
				[Op.lte]: params.from_date,
			};
			filterConditions.to_date = {
				[Op.gte]: params.to_date,
			};
		}

		let leaveApplications = [];
		leaveApplications = await dbSAPPS.LeaveApplication.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.LeaveType,
					as: "leavetype",
					attributes: ["id", "display_name"],
				},
				{
					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 LeaveApplication
					attributes: ["employee_name"],
				},
			],
		});

		// Fetch workflows for leave applications (similar to employee request pattern)
		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.in]: leaveApplications.map((item) => item.id),
				},
				doctype_id: LEAVE_APPLICATION_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 = leaveApplications.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 {
			leaveApplications: 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.from_date = {
				[Op.lte]: params.from_date,
			};
			filterConditions.to_date = {
				[Op.gte]: params.to_date,
			};
		}

		let options = [];
		options = await dbSAPPS.LeaveApplication.findAll({
			attributes: [
				"id",
				"name",
				"display_name",
				"leave_balance",
				"estimated_leave_balance",
				"total_leave_days",
				"company_id",
				"department_id",
				"employee_id",
				"leavetype_id",
				"from_date",
				"to_date",
				"status",
				"half_day",
				"half_day_date",
				"reason",
				"use_doctor_letter",
				"filename",
				"posting_date",
				"approver_date",
				"is_lwp",
				"is_sl",
			],
			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.LeaveType,
					as: "leavetype",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Department,
					as: "department",
					attributes: ["id", "display_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.LeaveApplication.count({
			where: filterConditions,
		});

		return {
			count,
			success: true,
			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();

	// warning karena pakai formData maka semua value jadi string
	try {
		// validate existing
		const existing = await dbSAPPS.LeaveApplication.findOne({
			where: {
				employee_id: params.employee_id,
				company_id: params.company_id,
				status: {
					[Op.in]: ["Approved", "Need Approval", "Draft"],
				},
				[Op.or]: [
					{
						from_date: {
							[Op.between]: [params.from_date, params.to_date], // Cuti baru mulai di dalam rentang cuti yang sudah ada
						},
					},
					{
						to_date: {
							[Op.between]: [params.from_date, params.to_date], // Cuti baru selesai di dalam rentang cuti yang sudah ada
						},
					},
					{
						from_date: {
							[Op.lte]: params.from_date,
							[Op.gte]: params.to_date, // Cuti baru sepenuhnya di dalam rentang cuti yang sudah ada
						},
					},
				],
			},
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
				{
					model: dbSAPPS.LeaveType,
					as: "leavetype",
					attributes: ["id", "display_name"],
				},
			],
			transaction,
		});
		if (existing) {
			return {
				success: false,
				message: `Employee ${existing.employee?.employee_name} has already applied for ${existing.leavetype?.display_name} between ${existing.from_date} and ${existing.to_date}.`,
			};
		}

		// Block if any date in range already has Absence Clarification (FKA)
		const attendanceWithFKA = await dbSAPPS.Attendance.findOne({
			where: {
				employee_id: params.employee_id,
				date: { [Op.between]: [params.from_date, params.to_date] },
				[Op.or]: [
					{ absenceclarification_id: { [Op.ne]: null } },
					{ is_absence_clarification: { [Op.in]: [true, 1, "1"] } },
				],
			},
			attributes: ["id", "date"],
			transaction,
		});
		if (attendanceWithFKA) {
			await transaction.rollback();
			return {
				success: false,
				message: `Tanggal ${attendanceWithFKA.date} dalam rentang cuti sudah tercatat dalam Absence Clarification (FKA). Tidak dapat membuat Leave Application yang bertumpuk dengan tanggal yang sudah diklarifikasi.`,
			};
		}

		// Validate leaveallocation_id if provided
		if (params.leaveallocation_id) {
			const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
				where: {
					id: params.leaveallocation_id,
				},
				include: [
					{
						model: dbSAPPS.LeavePeriod,
						as: "leaveperiod",
						attributes: ["id", "from_date", "to_date", "is_active"],
					},
				],
				transaction,
			});

			if (!leaveAllocation) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Allocation dengan ID ${params.leaveallocation_id} tidak ditemukan.`,
				};
			}

			// Validate consistency: employee_id, leavetype_id, company_id must match
			if (leaveAllocation.employee_id !== parseInt(params.employee_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Allocation tidak sesuai dengan employee yang dipilih.`,
				};
			}

			if (leaveAllocation.leavetype_id !== parseInt(params.leavetype_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Allocation tidak sesuai dengan leave type yang dipilih.`,
				};
			}

			if (leaveAllocation.company_id !== parseInt(params.company_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Allocation tidak sesuai dengan company yang dipilih.`,
				};
			}

			// Validate that leave period is active and dates are within range
			if (leaveAllocation.leaveperiod && !leaveAllocation.leaveperiod.is_active) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Period untuk Leave Allocation ini tidak aktif.`,
				};
			}

			if (leaveAllocation.leaveperiod) {
				const periodFromDate = new Date(leaveAllocation.leaveperiod.from_date);
				const periodToDate = new Date(leaveAllocation.leaveperiod.to_date);
				const appFromDate = new Date(params.from_date);
				const appToDate = new Date(params.to_date);

				if (appFromDate < periodFromDate || appToDate > periodToDate) {
					await transaction.rollback();
					return {
						success: false,
						message: `Tanggal cuti (${params.from_date} - ${params.to_date}) berada di luar periode Leave Allocation (${leaveAllocation.leaveperiod.from_date} - ${leaveAllocation.leaveperiod.to_date}).`,
					};
				}
			}
		}

		if (!params.status) params.status = "Draft";
		if (!params.total_leave_days) params.total_leave_days = 0;
		if (!params.leave_balance) params.leave_balance = 0;
		if (!params.estimated_leave_balance) params.estimated_leave_balance = 0;

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

		// create HistoryLeaveApplication
		await dbSAPPS.HistoryLeaveApplication.create(
			{
				creation: new Date(),
				modified: params.modified,
				modified_by: params.modified_by,
				owner: leaveApplication.owner,
				leaveapplication_id: leaveApplication.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();

	// warning karena pakai formData maka semua value jadi string
	try {
		// validate existing
		const leaveApplication = await dbSAPPS.LeaveApplication.findByPk(id);
		if (!leaveApplication) return { success: false, message: "ID not found!" };

		// validate existing
		const existing = await dbSAPPS.LeaveApplication.findOne({
			where: {
				employee_id: params.employee_id,
				company_id: params.company_id,
				status: {
					[Op.in]: ["Approved", "Need Approval", "Draft"],
				},
				[Op.or]: [
					{
						from_date: {
							[Op.between]: [params.from_date, params.to_date], // Cuti baru mulai di dalam rentang cuti yang sudah ada
						},
					},
					{
						to_date: {
							[Op.between]: [params.from_date, params.to_date], // Cuti baru selesai di dalam rentang cuti yang sudah ada
						},
					},
					{
						from_date: {
							[Op.lte]: params.from_date,
							[Op.gte]: params.to_date, // Cuti baru sepenuhnya di dalam rentang cuti yang sudah ada
						},
					},
				],
				id: {
					[Op.ne]: id,
				},
			},
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
				{
					model: dbSAPPS.LeaveType,
					as: "leavetype",
					attributes: ["id", "display_name"],
				},
			],
			transaction,
		});
		if (existing) {
			return {
				success: false,
				message: `Employee ${existing.employee?.employee_name} has already applied for ${existing.leavetype?.display_name} between ${existing.from_date} and ${existing.to_date}.`,
			};
		}

		// Block if any date in range already has Absence Clarification (FKA)
		const attendanceWithFKA = await dbSAPPS.Attendance.findOne({
			where: {
				employee_id: params.employee_id,
				date: { [Op.between]: [params.from_date, params.to_date] },
				[Op.or]: [
					{ absenceclarification_id: { [Op.ne]: null } },
					{ is_absence_clarification: { [Op.in]: [true, 1, "1"] } },
				],
			},
			attributes: ["id", "date"],
			transaction,
		});
		if (attendanceWithFKA) {
			await transaction.rollback();
			return {
				success: false,
				message: `Tanggal ${attendanceWithFKA.date} dalam rentang cuti sudah tercatat dalam Absence Clarification (FKA). Tidak dapat mengubah Leave Application yang bertumpuk dengan tanggal yang sudah diklarifikasi.`,
			};
		}

		// Validate leaveallocation_id if provided
		// Validate if it's being changed OR if employee/leavetype/company/dates are being changed
		const isLeaveAllocationChanged = params.leaveallocation_id && params.leaveallocation_id !== leaveApplication.leaveallocation_id;
		const isRelatedDataChanged = 
			params.employee_id !== leaveApplication.employee_id ||
			params.leavetype_id !== leaveApplication.leavetype_id ||
			params.company_id !== leaveApplication.company_id ||
			params.from_date !== leaveApplication.from_date ||
			params.to_date !== leaveApplication.to_date;

		if (params.leaveallocation_id && (isLeaveAllocationChanged || isRelatedDataChanged)) {
			const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
				where: {
					id: params.leaveallocation_id,
				},
				include: [
					{
						model: dbSAPPS.LeavePeriod,
						as: "leaveperiod",
						attributes: ["id", "from_date", "to_date", "is_active"],
					},
				],
				transaction,
			});

			if (!leaveAllocation) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Allocation dengan ID ${params.leaveallocation_id} tidak ditemukan.`,
				};
			}

			// Validate consistency: employee_id, leavetype_id, company_id must match
			if (leaveAllocation.employee_id !== parseInt(params.employee_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Allocation tidak sesuai dengan employee yang dipilih.`,
				};
			}

			if (leaveAllocation.leavetype_id !== parseInt(params.leavetype_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Allocation tidak sesuai dengan leave type yang dipilih.`,
				};
			}

			if (leaveAllocation.company_id !== parseInt(params.company_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Allocation tidak sesuai dengan company yang dipilih.`,
				};
			}

			// Validate that leave period is active and dates are within range
			if (leaveAllocation.leaveperiod && !leaveAllocation.leaveperiod.is_active) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Period untuk Leave Allocation ini tidak aktif.`,
				};
			}

			if (leaveAllocation.leaveperiod) {
				const periodFromDate = new Date(leaveAllocation.leaveperiod.from_date);
				const periodToDate = new Date(leaveAllocation.leaveperiod.to_date);
				const appFromDate = new Date(params.from_date);
				const appToDate = new Date(params.to_date);

				if (appFromDate < periodFromDate || appToDate > periodToDate) {
					await transaction.rollback();
					return {
						success: false,
						message: `Tanggal cuti (${params.from_date} - ${params.to_date}) berada di luar periode Leave Allocation (${leaveAllocation.leaveperiod.from_date} - ${leaveAllocation.leaveperiod.to_date}).`,
					};
				}
			}
		}

		// save
		await leaveApplication.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: LEAVE_APPLICATION_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(
			leaveApplication.company_id,
			{
				attributes: ["id", "display_name", "website"],
			}
		);
		if (!company) {
			return {
				success: false,
				message: "Company not found!",
			};
		}

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

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

		// Draft
		if (
			params.status === "Draft" &&
			(params.is_save === false || params.is_save === "false")
		) {
			// update leave allocation
			const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
				where: {
					id: leaveApplication.leaveallocation_id,
				},
				transaction,
			});

			if (leaveAllocation) {
				const updateLeaveAllocation = {
					...leaveAllocation,
					leaves_pending_approval:
						(parseFloat(leaveAllocation.leaves_pending_approval) || 0) -
						(parseFloat(leaveApplication.total_leave_days) || 0),
				};

				await leaveAllocation.update(updateLeaveAllocation, { transaction });
			}

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

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				leaveApplication.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 = `Leave Application Revision (${
					leaveApplication?.leavetype_name
				}) - ${leaveApplication?.employee_name} - (${moment(
					leaveApplication.from_date
				).format("yyyy-MM-DD")} to ${moment(leaveApplication.to_date).format(
					"yyyy-MM-DD"
				)})`;

				const html = emailTemplateRevised.html(leaveApplication);

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

				try {
					await transporter.sendMail({
						from: emailAccount.email_account,
						to: leaveApplication.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: leaveApplication.company_id,
						employee_id: leaveApplication.employee_id,
						recipient_email: leaveApplication.employee_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						leaveapplication_id: leaveApplication.id,
					},
					{ transaction }
				);
			}
			// Reject
		} else if (
			params.status === "Rejected" &&
			(params.is_save === false || params.is_save === "false")
		) {
			// update leave allocation
			const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
				where: {
					id: leaveApplication.leaveallocation_id,
				},
				transaction,
			});

			if (leaveAllocation) {
				const updateLeaveAllocation = {
					...leaveAllocation,
					leaves_pending_approval:
						(parseFloat(leaveAllocation.leaves_pending_approval) || 0) -
						(parseFloat(leaveApplication.total_leave_days) || 0),
				};

				await leaveAllocation.update(updateLeaveAllocation, { transaction });
			}

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

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				leaveApplication.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 = `Leave Application Rejected (${
					leaveApplication?.leavetype_name
				}) - ${leaveApplication?.employee_name} - (${moment(
					leaveApplication.from_date
				).format("yyyy-MM-DD")} to ${moment(leaveApplication.to_date).format(
					"yyyy-MM-DD"
				)})`;

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

				const html = emailTemplateRejected.html(leaveApplication);

				try {
					await transporter.sendMail({
						from: emailAccount.email_account,
						to: leaveApplication.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: leaveApplication.company_id,
						employee_id: leaveApplication.employee_id,
						recipient_email: leaveApplication.employee_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						leaveapplication_id: leaveApplication.id,
					},
					{ transaction }
				);
			}

			// Need Approval
		} else if (params.status === "Need Approval") {
			// update leave allocation
			const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
				where: {
					id: leaveApplication.leaveallocation_id,
				},
				transaction,
			});

			if (leaveAllocation) {
				const updateLeaveAllocation = {
					...leaveAllocation,
					leaves_pending_approval:
						(parseFloat(leaveAllocation.leaves_pending_approval) || 0) +
						(parseFloat(leaveApplication.total_leave_days) || 0),
				};

				await leaveAllocation.update(updateLeaveAllocation, { transaction });
			}

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

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				leaveApplication.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 = `Leave Request Approval (${
					leaveApplication?.leavetype_name
				}) - ${leaveApplication?.employee_name} - (${moment(
					leaveApplication.from_date
				).format("yyyy-MM-DD")} to ${moment(leaveApplication.to_date).format(
					"yyyy-MM-DD"
				)})`;

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

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

				try {
					await transporter.sendMail({
						from: `${leaveApplication?.employee_name} <${emailAccount.email_account}>`,
						to: leaveApplication.leave_approver_email,
						bcc: "it@sapharma.co.id",
						subject,
						html,
						attachments: leaveApplication.filename
							? [
									{
										filename: leaveApplication.filename,
										path: `./public/data/documents/leaveapplications/${leaveApplication.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: leaveApplication.company_id,
						employee_id: leaveApplication.employee_id,
						recipient_email: leaveApplication.leave_approver_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						leaveapplication_id: leaveApplication.id,
					},
					{ transaction }
				);
			}

			// Approved
		} else if (
			params.status === "Approved" &&
			(params.is_save === false || params.is_save === "false")
		) {
			// update attendance
			// Ambil employee beserta shiftType relasi
			const employee = await dbSAPPS.Employee.findOne({
				where: { id: leaveApplication.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;

			// Ambil semua attendance yang berpotensi di update/create
			const existingAttendances = await dbSAPPS.Attendance.findAll({
				where: {
					employee_id: leaveApplication.employee_id,
					date: {
						[Op.between]: [params.from_date, params.to_date],
					},
				},
				transaction,
			});

			// Block jika ada attendance yang sudah dari Absence Clarification (FKA)
			const isAttFKA = (att) =>
				(att.absenceclarification_id != null &&
					att.absenceclarification_id !== 0 &&
					att.absenceclarification_id !== "") ||
				att.is_absence_clarification === true ||
				att.is_absence_clarification === 1 ||
				att.is_absence_clarification === "1";
			const hasFKA = existingAttendances.some(isAttFKA);
			if (hasFKA) {
				const fkaAtt = existingAttendances.find(isAttFKA);
				throw new Error(
					`Tanggal ${fkaAtt.date} sudah tercatat dalam Absence Clarification (FKA). Tidak dapat menyetujui Leave Application yang bertumpuk dengan tanggal yang sudah diklarifikasi.`
				);
			}
			const hasAR = existingAttendances.some(
				(att) => att.attendancerequest_id != null && att.attendancerequest_id !== 0
			);
			if (hasAR) {
				const arAtt = existingAttendances.find(
					(att) => att.attendancerequest_id != null && att.attendancerequest_id !== 0
				);
				throw new Error(
					`Tanggal ${arAtt.date} sudah tercatat sebagai Attendance Request. Tidak dapat menyetujui Leave Application yang bertumpuk dengan tanggal yang sama.`
				);
			}
			const hasPE = existingAttendances.some(
				(att) =>
					(att.permitease_id != null && att.permitease_id !== 0) ||
					att.status_permitease === true ||
					att.status_permitease === 1 ||
					att.status_permitease === "true"
			);
			if (hasPE) {
				const peAtt = existingAttendances.find(
					(att) =>
						(att.permitease_id != null && att.permitease_id !== 0) ||
						att.status_permitease === true ||
						att.status_permitease === 1 ||
						att.status_permitease === "true"
				);
				throw new Error(
					`Tanggal ${peAtt.date} sudah tercatat sebagai Permit Ease (Izin). Tidak dapat menyetujui Leave Application yang bertumpuk dengan tanggal yang sama.`
				);
			}

			// 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.from_date, params.to_date);
			const attendanceRecordsToCreate = [];

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

				const isHalfDayDate =
					leaveApplication.half_day &&
					dateKey ===
						new Date(leaveApplication.half_day_date)
							.toISOString()
							.split("T")[0];

				const existingAttendance = attendanceMap.get(dateKey);

				if (existingAttendance) {
					// Update attendance jika half_day_date sama dan kondisi update terpenuhi
					if (isHalfDayDate) {
						// 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.leavetype_id = leaveApplication.leavetype_id;
							existingAttendance.leaveapplication_id = leaveApplication.id;
							existingAttendance.attendancerequest_id = null;
							existingAttendance.permitease_id = null;
							existingAttendance.status_permitease = null;
							existingAttendance.reason = leaveApplication.reason;
							existingAttendance.half_day = true;
							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 to half day.`
							);
						} else {
							// Kondisi lain, misal belum ada waktu sama sekali (tidak punya in_time/out_time)
							// Bisa logika lain sesuai kebutuhan, atau skip
						}
					} else {
						// Jika ada attendance tapi bukan half_day_date, lempar error atau skip
						throw new Error(
							`Employee ${leaveApplication?.employee_name} already has an attendance on ${dateKey}`
						);
					}
				} else {
					let inTime = null;
					let outTime = null;
					let inStatus = null;
					let outStatus = null;

					if (shiftType) {
						// Asumsikan late_time dan end_time berupa string waktu, misal "08:30:00"
						// Anda bisa sesuaikan format sesuai kebutuhan model Attendance
						inTime = shiftType.late_time || null;
						outTime = shiftType.end_time || null;
						inStatus = "On TIme";
						outStatus = "On TIme";
					}

					// 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: inTime,
						out_time: outTime,
						in_status: inStatus,
						out_status: outStatus,
						in_distance: null,
						out_distance: null,
						in_address: null,
						out_address: null,
						total_hour: leaveApplication.half_day && isHalfDayDate ? 4 : 8,
						notes: null,
						company_id: leaveApplication.company_id || null,
						employee_id: leaveApplication.employee_id || null,
						shifttype_id: leaveApplication?.shifttype_id || null,
						leavetype_id: leaveApplication.leavetype_id || null,
						leaveapplication_id: leaveApplication.id,
						is_leave: true,
						attendancerequest_id: null,
						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: leaveApplication.reason || null,
						permitease_id: null,
						status_permitease: null,
						half_day: leaveApplication.half_day && isHalfDayDate,
						need_clarification: false,
					};
					attendanceRecordsToCreate.push(newRecord);
				}
			}

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

			// 4. update leave allocation
			const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
				where: {
					id: leaveApplication.leaveallocation_id,
				},
				transaction,
			});

			if (leaveAllocation) {
				const updateLeaveAllocation = {
					...leaveAllocation,
					leaves_pending_approval:
						(parseFloat(leaveAllocation.leaves_pending_approval) || 0) -
						(parseFloat(leaveApplication.total_leave_days) || 0),
					used_leaves:
						parseFloat(leaveAllocation.used_leaves) +
						parseFloat(leaveApplication.total_leave_days),
					unused_leaves: parseFloat(leaveApplication.estimated_leave_balance),
				};

				await leaveAllocation.update(updateLeaveAllocation, { transaction });
			}

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

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				leaveApplication.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 = `Leave Application Approved (${
					leaveApplication?.leavetype_name
				}) - ${leaveApplication?.employee_name} - ${moment(
					leaveApplication.from_date
				).format("yyyy-MM-DD")} to ${moment(leaveApplication.to_date).format(
					"yyyy-MM-DD"
				)}`;

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

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

				try {
					await transporter.sendMail({
						from: emailAccount.email_account,
						to: leaveApplication.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: leaveApplication.company_id,
						employee_id: leaveApplication.employee_id,
						recipient_email: leaveApplication.employee_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						leaveapplication_id: leaveApplication.id,
					},
					{ transaction }
				);
			}

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

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

			for (const att of leaveAttendances) {
				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 cuti
			if (attendanceIdsToClear.length > 0) {
				await dbSAPPS.Attendance.update(
					{
						half_day: false,
						leavetype_id: null,
						leaveapplication_id: null,
						is_leave: false,
						reason: null,
					},
					{
						where: { id: attendanceIdsToClear },
						transaction,
					}
				);
			}

			// update leave allocation
			const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
				where: {
					id: leaveApplication.leaveallocation_id,
				},
				transaction,
			});

			if (leaveAllocation) {
				const updateLeaveAllocation = {
					...leaveAllocation,
					used_leaves: parseFloat(
						(
							leaveAllocation.used_leaves - leaveApplication.total_leave_days
						).toFixed(2)
					),
					unused_leaves: parseFloat(
						(
							leaveAllocation.unused_leaves -
							leaveApplication.estimated_leave_balance
						).toFixed(2)
					),
				};

				await leaveAllocation.update(updateLeaveAllocation, { transaction });
			}

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

			// get email account
			if (
				emailAccount?.email &&
				emailAccount?.email_account &&
				leaveApplication.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 = `Leave Application Cancelled (${
					leaveApplication?.leavetype_name
				}) - ${leaveApplication?.employee_name} - (${moment(
					leaveApplication.from_date
				).format("yyyy-MM-DD")} - ${moment(leaveApplication.to_date).format(
					"yyyy-MM-DD"
				)})`;

				const html = emailTemplateCancelled.html(leaveApplication);

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

				try {
					await transporter.sendMail({
						from: emailAccount.email_account,
						to: leaveApplication.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: leaveApplication.company_id,
						employee_id: leaveApplication.employee_id,
						recipient_email: leaveApplication.employee_email,
						subject: subject,
						body: html,
						status: emailStatus,
						error_message: errorMessage,
						leaveapplication_id: leaveApplication.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 leaveApplication = await dbSAPPS.LeaveApplication.findByPk(id);
		if (!leaveApplication) return { success: false, message: "ID not found!" };

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

		// Ambil employee beserta shiftType relasi
		const employee = await dbSAPPS.Employee.findOne({
			where: { id: leaveApplication.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 leaveApplication
		await leaveApplication.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: LEAVE_APPLICATION_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 HistoryLeaveApplication
		await dbSAPPS.HistoryLeaveApplication.create(
			{
				creation: new Date(),
				modified: params.modified,
				modified_by: params.modified_by,
				owner: leaveApplication.owner,
				leaveapplication_id: leaveApplication.id,
				action: params.status,
				date: new Date(),
			},
			{ transaction }
		);

		// update attendance
		// Ambil semua attendance yang berpotensi di update/create
		const existingAttendances = await dbSAPPS.Attendance.findAll({
			where: {
				employee_id: leaveApplication.employee_id,
				date: {
					[Op.between]: [params.from_date, params.to_date],
				},
			},
			transaction,
		});

		// Block jika ada attendance yang sudah dari Absence Clarification (FKA)
		const isAttFKAApprove = (att) =>
			(att.absenceclarification_id != null &&
				att.absenceclarification_id !== 0 &&
				att.absenceclarification_id !== "") ||
			att.is_absence_clarification === true ||
			att.is_absence_clarification === 1 ||
			att.is_absence_clarification === "1";
		const hasFKA = existingAttendances.some(isAttFKAApprove);
		if (hasFKA) {
			const fkaAtt = existingAttendances.find(isAttFKAApprove);
			throw new Error(
				`Tanggal ${fkaAtt.date} sudah tercatat dalam Absence Clarification (FKA). Tidak dapat menyetujui Leave Application yang bertumpuk dengan tanggal yang sudah diklarifikasi.`
			);
		}
		const hasAR = existingAttendances.some(
			(att) => att.attendancerequest_id != null && att.attendancerequest_id !== 0
		);
		if (hasAR) {
			const arAtt = existingAttendances.find(
				(att) => att.attendancerequest_id != null && att.attendancerequest_id !== 0
			);
			throw new Error(
				`Tanggal ${arAtt.date} sudah tercatat sebagai Attendance Request. Tidak dapat menyetujui Leave Application yang bertumpuk dengan tanggal yang sama.`
			);
		}
		const hasPE = existingAttendances.some(
			(att) =>
				(att.permitease_id != null && att.permitease_id !== 0) ||
				att.status_permitease === true ||
				att.status_permitease === 1 ||
				att.status_permitease === "true"
		);
		if (hasPE) {
			const peAtt = existingAttendances.find(
				(att) =>
					(att.permitease_id != null && att.permitease_id !== 0) ||
					att.status_permitease === true ||
					att.status_permitease === 1 ||
					att.status_permitease === "true"
			);
			throw new Error(
				`Tanggal ${peAtt.date} sudah tercatat sebagai Permit Ease (Izin). Tidak dapat menyetujui Leave Application yang bertumpuk dengan tanggal yang sama.`
			);
		}

		// 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.from_date, params.to_date);
		const attendanceRecordsToCreate = [];

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

			const isHalfDayDate =
				leaveApplication.half_day &&
				dateKey ===
					new Date(leaveApplication.half_day_date).toISOString().split("T")[0];

			const existingAttendance = attendanceMap.get(dateKey);

			if (existingAttendance) {
				// Update attendance jika half_day_date sama dan kondisi update terpenuhi
				if (isHalfDayDate) {
					// 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.leavetype_id = leaveApplication.leavetype_id;
						existingAttendance.leaveapplication_id = leaveApplication.id;
						existingAttendance.attendancerequest_id = null;
						existingAttendance.permitease_id = null;
						existingAttendance.status_permitease = null;
						existingAttendance.reason = leaveApplication.reason;
						existingAttendance.half_day = true;
						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 to half day.`
						);
					} else {
						// Kondisi lain, misal belum ada waktu sama sekali (tidak punya in_time/out_time)
						// Bisa logika lain sesuai kebutuhan, atau skip
					}
				} else {
					// Jika ada attendance tapi bukan half_day_date, lempar error atau skip
					throw new Error(
						`Employee ${leaveApplication?.employee_name} already has an attendance on ${dateKey}`
					);
				}
			} else {
				// Buat attendance baru, isi in_time dan out_time dari shiftType jika ada
				let inTime = null;
				let outTime = null;
				let inStatus = null;
				let outStatus = null;

				if (shiftType) {
					// Asumsikan late_time dan end_time berupa string waktu, misal "08:30:00"
					// Anda bisa sesuaikan format sesuai kebutuhan model Attendance
					inTime = shiftType.late_time || null;
					outTime = shiftType.end_time || null;
					inStatus = "On TIme";
					outStatus = "On TIme";
				}

				// 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: inTime,
					out_time: outTime,
					in_status: inStatus,
					out_status: outStatus,
					in_distance: null,
					out_distance: null,
					in_address: null,
					out_address: null,
					total_hour: leaveApplication.half_day && isHalfDayDate ? 4 : 8,
					notes: null,
					company_id: leaveApplication.company_id || null,
					employee_id: leaveApplication.employee_id || null,
					shifttype_id: leaveApplication?.shifttype_id || null,
					leavetype_id: leaveApplication.leavetype_id || null,
					leaveapplication_id: leaveApplication.id,
					is_leave: true,
					attendancerequest_id: null,
					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: leaveApplication.reason || null,
					permitease_id: null,
					status_permitease: null,
					half_day: leaveApplication.half_day && isHalfDayDate,
					need_clarification: false,
				};
				attendanceRecordsToCreate.push(newRecord);
			}
		}

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

		// 4. update leave allocation
		const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
			where: {
				id: leaveApplication.leaveallocation_id,
			},
			transaction,
		});

		if (leaveAllocation) {
			const updateLeaveAllocation = {
				...leaveAllocation,
				leaves_pending_approval:
					(parseFloat(leaveAllocation.leaves_pending_approval) || 0) -
					(parseFloat(leaveApplication.total_leave_days) || 0),
				used_leaves:
					parseFloat(leaveAllocation.used_leaves) +
					parseFloat(leaveApplication.total_leave_days),
				unused_leaves: parseFloat(leaveApplication.estimated_leave_balance),
			};

			await leaveAllocation.update(updateLeaveAllocation, { transaction });
		}

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

		// get email account
		if (
			emailAccount?.email &&
			emailAccount?.email_account &&
			leaveApplication.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 = `Leave Application Approved (${
				leaveApplication?.leavetype_name
			}) - ${leaveApplication?.employee_name} - ${moment(
				leaveApplication.from_date
			).format("yyyy-MM-DD")} to ${moment(leaveApplication.to_date).format(
				"yyyy-MM-DD"
			)}`;

			const html = emailTemplateApproved.html(leaveApplication);

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

			try {
				await transporter.sendMail({
					from: emailAccount.email_account,
					to: leaveApplication.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: leaveApplication.company_id,
					employee_id: leaveApplication.employee_id,
					recipient_email: leaveApplication.employee_email,
					subject: subject,
					body: html,
					status: emailStatus,
					error_message: errorMessage,
					leaveapplication_id: leaveApplication.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 leaveApplication = await dbSAPPS.LeaveApplication.findByPk(id);
		if (!leaveApplication) return { success: false, message: "ID not found!" };

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

		// update leaveApplication
		await leaveApplication.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: LEAVE_APPLICATION_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 HistoryLeaveApplication
		await dbSAPPS.HistoryLeaveApplication.create(
			{
				creation: new Date(),
				modified: params.modified,
				modified_by: params.modified_by,
				owner: leaveApplication.owner,
				leaveapplication_id: leaveApplication.id,
				action: params.status,
				date: new Date(),
			},
			{ transaction }
		);

		// update leave allocation
		const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
			where: {
				id: leaveApplication.leaveallocation_id,
			},
			transaction,
		});

		if (leaveAllocation) {
			const updateLeaveAllocation = {
				...leaveAllocation,
				leaves_pending_approval:
					(parseFloat(leaveAllocation.leaves_pending_approval) || 0) -
					(parseFloat(leaveApplication.total_leave_days) || 0),
			};

			await leaveAllocation.update(updateLeaveAllocation, { transaction });
		}

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

		// get email account
		if (
			emailAccount?.email &&
			emailAccount?.email_account &&
			leaveApplication.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 = `Leave Application Revision (${
				leaveApplication?.leavetype_name
			}) - ${leaveApplication?.employee_name} - (${moment(
				leaveApplication.from_date
			).format("yyyy-MM-DD")} to ${moment(leaveApplication.to_date).format(
				"yyyy-MM-DD"
			)})`;

			const html = emailTemplateRevised.html(leaveApplication);

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

			try {
				await transporter.sendMail({
					from: emailAccount.email_account,
					to: leaveApplication.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: leaveApplication.company_id,
					employee_id: leaveApplication.employee_id,
					recipient_email: leaveApplication.employee_email,
					subject: subject,
					body: html,
					status: emailStatus,
					error_message: errorMessage,
					leaveapplication_id: leaveApplication.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 leaveApplication = await dbSAPPS.LeaveApplication.findByPk(id);
		if (!leaveApplication) return { success: false, message: "ID not found!" };

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

		if (leaveApplication.status !== "Need Approval")
			throw new Error("You are not allowed to reject a leave application!");

		// update leaveApplication
		await leaveApplication.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: LEAVE_APPLICATION_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 HistoryLeaveApplication
		await dbSAPPS.HistoryLeaveApplication.create(
			{
				creation: new Date(),
				modified: params.modified,
				modified_by: params.modified_by,
				owner: leaveApplication.owner,
				leaveapplication_id: leaveApplication.id,
				action: params.status,
				date: new Date(),
			},
			{ transaction }
		);

		// update leave allocation
		const leaveAllocation = await dbSAPPS.LeaveAllocation.findOne({
			where: {
				id: leaveApplication.leaveallocation_id,
			},
			transaction,
		});

		if (leaveAllocation) {
			const updateLeaveAllocation = {
				...leaveAllocation,
				leaves_pending_approval:
					(parseFloat(leaveAllocation.leaves_pending_approval) || 0) -
					(parseFloat(leaveApplication.total_leave_days) || 0),
			};

			await leaveAllocation.update(updateLeaveAllocation, { transaction });
		}

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

		// get email account
		if (
			emailAccount?.email &&
			emailAccount?.email_account &&
			leaveApplication.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 = `Leave Application Rejected (${
				leaveApplication?.leavetype_name
			}) - ${leaveApplication?.employee_name} - (${moment(
				leaveApplication.from_date
			).format("yyyy-MM-DD")} to ${moment(leaveApplication.to_date).format(
				"yyyy-MM-DD"
			)})`;

			const html = emailTemplateRejected.html(leaveApplication);

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

			try {
				await transporter.sendMail({
					from: emailAccount.email_account,
					to: leaveApplication.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: leaveApplication.company_id,
					employee_id: leaveApplication.employee_id,
					recipient_email: leaveApplication.employee_email,
					subject: subject,
					body: html,
					status: emailStatus,
					error_message: errorMessage,
					leaveapplication_id: leaveApplication.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) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const leaveApplication = await dbSAPPS.LeaveApplication.findByPk(id, {
			transaction,
		});
		if (!leaveApplication) return { success: false, message: "ID not found!" };

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

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

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

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

		// delete
		await leaveApplication.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) || LEAVE_APPLICATION_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) || LEAVE_APPLICATION_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) || LEAVE_APPLICATION_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,
		};
	}
}
