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

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"],
				},
			],
		});

		attendanceRequests = attendanceRequests.map((attendanceRequest) => {
			return {
				...attendanceRequest.dataValues,
				company_name: attendanceRequest?.company?.display_name,
				employee_name: attendanceRequest?.employee?.employee_name,
				leavetype_name: attendanceRequest?.leavetype?.display_name,
				department_name: attendanceRequest?.department?.display_name,
				modifier: attendanceRequest?.modifier?.full_name,
				leaveapprover_name: attendanceRequest?.leaveapprover_name?.employee_name,
			};
		});

		return {
			attendanceRequests,
			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}.`,
			};
		}

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

		// save
		await attendanceRequest.update(params, { 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) {
					// 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.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.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: null,
						attendancerequest_id: attendanceRequest.id,
						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 cuti
			if (attendanceIdsToClear.length > 0) {
				await dbSAPPS.Attendance.update(
					{
						attendancerequest_id: null,
						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 }
		);

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

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

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