const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const moment = require("moment");
const { getHolidayListItemByDate } = require("../holidayLists/holidayList.service");

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

function generateDateRange(startDate, endDate) {
	const dates = [];
	let currentDate = moment(startDate);
	const lastDate = moment(endDate);

	while (currentDate.isSameOrBefore(lastDate)) {
		dates.push(currentDate.format("YYYY-MM-DD"));
		currentDate.add(1, "day");
	}
	return dates;
}

async function getAll(params) {
	try {
		// Tentukan rentang tanggal filter
		let fromDate, toDate;
		if (params.from_date && params.to_date) {
			fromDate = moment(params.from_date).format("YYYY-MM-DD");
			toDate = moment(params.to_date).format("YYYY-MM-DD");
		} else {
			const today = moment();
			fromDate = today.clone().startOf("month").format("YYYY-MM-DD");
			toDate = moment().format("YYYY-MM-DD");
		}
		const datesInRange = generateDateRange(fromDate, toDate);

		// Ambil daftar company sesuai filter
		const companyFilter = {};
		if (params.companiesId) {
			companyFilter.id = { [Op.in]: params.companiesId };
		}
		const companies = await dbSAPPS.Company.findAll({
			where: companyFilter,
			attributes: ["id", "display_name"],
		});
		const companyMap = new Map();
		companies.forEach((c) => companyMap.set(c.id, c.display_name));

		// Ambil daftar employee sesuai filter
		const employeeFilter = { enabled: true };
		if (params.companiesId) employeeFilter.company_id = { [Op.in]: params.companiesId };
		if (params.employeesId) employeeFilter.id = { [Op.in]: params.employeesId };
		if (params.employee_id) employeeFilter.id = params.employee_id;

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

		// Ambil daftar shift type sesuai filter perusahaan (jika ada)
		const shiftTypeFilter = {};
		if (params.companiesId) shiftTypeFilter.company_id = { [Op.in]: params.companiesId };
		const shiftTypes = await dbSAPPS.ShiftType.findAll({
			where: shiftTypeFilter,
			attributes: [
				"id",
				"enabled",
				"name",
				"display_name",
				"code",
				"start_time",
				"end_time",
				"late_time",
				"early_time",
				"duration_break",
				"start_date_ramadhan",
				"end_date_ramadhan",
				"start_time_ramadhan",
				"end_time_ramadhan",
				"late_time_ramadhan",
				"early_time_ramadhan",
				"duration_break_ramadhan",
				"enable_auto_attendance",
				"location",
				"holidaygroup_id",
				"company_id",
			],
		});

		// Ambil data holiday list untuk rentang tanggal yang sama
		const holidayResponse = await getHolidayListItemByDate({ fromDate, toDate });
		const holidayListItems = holidayResponse.success ? holidayResponse.holidayListItems : [];
		const holidayMap = new Map();
		holidayListItems.forEach((h) => holidayMap.set(h.holiday_date, h));

		// Fungsi bantu cek hari libur
		function isHoliday(date) {
			return holidayMap.has(date);
		}

		// OPTIMASI: Fetch semua LeaveApplication sekali untuk employees dan date range (exact single-day match)
		// Filter: from_date = to_date (single day) dan employee_id in employees
		const employeeIds = employees.map((emp) => emp.id);
		const leaveApplicationFilter = {
			employee_id: { [Op.in]: employeeIds },
			from_date: { [Op.in]: datesInRange }, // Exact match from_date in range
			to_date: { [Op.in]: datesInRange }, // Exact match to_date in range (single day)
		};
		const allLeaveApplications = await dbSAPPS.LeaveApplication.findAll({
			where: leaveApplicationFilter,
			attributes: ["id", "employee_id", "from_date", "to_date"], // Hanya fields yang dibutuhkan untuk efisiensi
		});
		// Buat Map untuk lookup cepat: key = `${employee_id}_${from_date}`
		const leaveApplicationMap = new Map();
		allLeaveApplications.forEach((la) => {
			const key = `${la.employee_id}_${la.from_date}`;
			leaveApplicationMap.set(key, la);
		});
		// Fungsi bantu cek apakah leave application sudah ada atau belum (sekarang synchronous via Map)
		function isLeaveApplicationExist(employeeId, date) {
			if (!employeeId || !date) {
				console.warn("isLeaveApplicationExist: employeeId or date is required");
				return false;
			}
			const key = `${employeeId}_${date}`;
			return leaveApplicationMap.has(key);
		}

		// Pastikan employeeIds dan datesInRange didefinisikan (jika belum, tambahkan di atas)
		// Contoh: const employeeIds = employees.map(emp => emp.id);
		// Contoh: const datesInRange = generateDateRange(fromDate, toDate);  // Asumsi fungsi ini return array string 'YYYY-MM-DD'

		// Format datesInRange jika belum (untuk konsistensi, jika datesInRange berisi Date object)
		const formattedDatesInRange = datesInRange.map((date) => moment(date).format("YYYY-MM-DD"));

		// OPTIMASI: Fetch semua AttendanceRequest sekali untuk employees dan date range (exact single-day match)
		// Filter: date IN formattedDatesInRange (exact match) dan employee_id IN employeeIds
		// const employeeIds = employees.map((emp) => emp.id); // Pastikan dari employees

		// Deklarasi Map di luar (selalu ada, meskipun kosong)
		const attendanceRequestMap = new Map();

		if (employeeIds.length > 0 && formattedDatesInRange.length > 0) {
			const attendanceRequestFilter = {
				employee_id: { [Op.in]: employeeIds },
				date: { [Op.in]: formattedDatesInRange }, // Exact match IN datesInRange
			};

			try {
				const allAttendanceRequests = await dbSAPPS.AttendanceRequest.findAll({
					where: attendanceRequestFilter,
					attributes: ["id", "employee_id", "date"], // Hanya fields yang dibutuhkan untuk efisiensi
				});

				// Debug: Log jumlah data fetched (hapus di production)
				console.log(`Fetched ${allAttendanceRequests.length} AttendanceRequests for ${employeeIds.length} employees and ${formattedDatesInRange.length} dates`);

				// Buat Map untuk lookup cepat: key = `${employee_id}_${date}` (format date ke string)
				allAttendanceRequests.forEach((ar) => {
					const formattedDate = moment(ar.date).format("YYYY-MM-DD"); // Format ar.date ke string konsisten
					const key = `${ar.employee_id}_${formattedDate}`;
					attendanceRequestMap.set(key, ar);
				});

				// Debug: Log ukuran Map (hapus di production)
				console.log(`AttendanceRequest Map size: ${attendanceRequestMap.size}`);
			} catch (error) {
				console.error("Error fetching AttendanceRequests:", error);
				// Jika error, Map tetap kosong (return false di fungsi)
			}
		} else {
			console.warn("isAttendanceRequestExist: employeeIds or datesInRange is empty, Map will be empty");
		}

		// Fungsi bantu cek apakah attendance request sudah ada atau belum (sekarang synchronous via Map)
		function isAttendanceRequestExist(employeeId, date) {
			if (!employeeId || !date) {
				console.warn("isAttendanceRequestExist: employeeId or date is required");
				return false;
			}

			// Format date ke string 'YYYY-MM-DD' untuk konsistensi dengan Map key
			const formattedDate = moment(date).format("YYYY-MM-DD");
			if (!moment(formattedDate).isValid()) {
				console.warn(`isAttendanceRequestExist: Invalid date format for ${date}`);
				return false;
			}

			const key = `${employeeId}_${formattedDate}`;

			// Debug: Log key yang dicari (hapus di production, atau gunakan hanya saat testing)
			// console.log(`Checking AttendanceRequest for key: ${key}, exists: ${attendanceRequestMap.has(key)}`);

			// Selalu aman karena Map didefinisikan di atas
			return attendanceRequestMap.has(key);
		}

		// Fungsi bantu untuk dapatkan nama hari bahasa Indonesia
		function getDayName(date) {
			return moment(date).locale("id").format("dddd");
		}

		// Cek apakah filter status kosong/null
		const isInStatusEmpty = !params.inStatus;
		const isOutStatusEmpty = !params.outStatus;

		// Jika kedua status kosong/null, tampilkan semua data dengan kombinasi kosong lengkap
		if (isInStatusEmpty && isOutStatusEmpty) {
			const attendanceExistFilter = {
				date: { [Op.between]: [fromDate, toDate] },
			};
			if (params.companiesId) attendanceExistFilter.company_id = { [Op.in]: params.companiesId };
			if (params.employeesId) attendanceExistFilter.employee_id = { [Op.in]: params.employeesId };
			if (params.employee_id) attendanceExistFilter.employee_id = params.employee_id;

			const attendanceExistData = await dbSAPPS.Attendance.findAll({
				where: attendanceExistFilter,
				attributes: [
					"id",
					"date",
					"employee_id",
					"in_time",
					"in_status",
					"out_time",
					"out_status",
					"shifttype_id",
					"need_clarification",
					"in_address",
					"out_address",
					"in_latitude",
					"out_latitude",
					"in_longitude",
					"out_longitude",
					"total_hour",
					"company_id",
					"attendancerequest_id",
					"leaveapplication_id",
					"reason",
				],
				include: [
					{
						model: dbSAPPS.AttendanceRequest,
						as: "attendancerequest",
					},
					{
						model: dbSAPPS.LeaveApplication,
						as: "leaveapplication",
						include: [
							{
								model: dbSAPPS.LeaveType,
								as: "leavetype",
								attributes: ["id", "display_name"],
							},
						],
					},
					{
						model: dbSAPPS.User,
						as: "modifier",
						attributes: ["full_name"],
					},
				],
			});

			// Map attendance untuk lookup cepat
			const attendanceMap = new Map();
			attendanceExistData.forEach((att) => {
				const key = `${att.date}_${att.employee_id}_${att.shifttype_id}`;
				attendanceMap.set(key, att);
			});

			// Generate kombinasi lengkap tanggal × employee × shift type
			const fullAttendanceList = [];

			for (const date of datesInRange) {
				const dayName = getDayName(date);
				for (const emp of employees) {
					for (const shift of shiftTypes) {
						const attKey = `${date}_${emp.id}_${shift.id}`;
						const att = attendanceMap.get(attKey);

						// Tentukan need_clarification
						let needClarification = false;
						if (att) {
							if (att.need_clarification === true || att.need_clarification === "true" || att.need_clarification === "1" || att.need_clarification === 1) {
								needClarification = true;
							} else {
								// Cek kelengkapan data in/out
								const inTimeEmpty = !att.in_time || att.in_time === "";
								const outTimeEmpty = !att.out_time || att.out_time === "";
								const inStatusEmpty = !att.in_status || att.in_status === "";
								const outStatusEmpty = !att.out_status || att.out_status === "";

								if (inTimeEmpty || outTimeEmpty || inStatusEmpty || outStatusEmpty) {
									needClarification = true;
								} else {
									// Tambahan: Jika status Late atau LeftEarlier, set true
									if (
										(att.in_status === "Late" || att.out_status === "Left Earlier") &&
										att.attendancerequest_id === null &&
										att.leaveapplication_id === null
									) {
										needClarification = true;
									}
								}
							}
						} else {
							// Tidak ada attendance, cek hari libur
							needClarification = !isHoliday(date);
						}

						fullAttendanceList.push({
							id: att ? att.id : null,
							date: date,
							employee_id: emp.id,
							employee_name: emp.employee_name,
							shifttype_id: shift.id,
							shifttype: shiftTypes.find((s) => s.id === shift.id) || null,
							company_id: emp.company_id,
							company_name: companyMap.get(emp.company_id) || null,
							modifier: att ? (att.modifier ? att.modifier.full_name : null) : null,
							in_time: att ? att.in_time : null,
							out_time: att ? att.out_time : null,
							in_status: att ? att.in_status : null,
							out_status: att ? att.out_status : null,
							total_hour: att ? att.total_hour : 0,
							in_address: att ? att.in_address : null,
							out_address: att ? att.out_address : null,
							in_latitude: att ? att.in_latitude : null,
							out_latitude: att ? att.out_latitude : null,
							in_longitude: att ? att.in_longitude : null,
							out_longitude: att ? att.out_longitude : null,
							is_holiday: isHoliday(date),
							holiday: holidayMap.get(date) || null,
							holiday_name: holidayMap.get(date) ? holidayMap.get(date).display_name : null,
							need_clarification: needClarification,
							day_name: dayName,
							leavetype_name: att ? (att.leaveapplication ? att.leaveapplication.leavetype.display_name : null) : null,
							is_leave: isLeaveApplicationExist(emp.id, date), // Sekarang synchronous via Map
							is_attendance_request: isAttendanceRequestExist(emp.id, date), // Sekarang synchronous via Map
							attendancerequest_id: att ? (att.attendancerequest ? att.attendancerequest.id : null) : null,
							leaveapplication_id: att ? (att.leaveapplication ? att.leaveapplication.id : null) : null,
							reason: att ? (att.reason ? att.reason : null) : null,
						});
					}
				}
			}

			fullAttendanceList.sort((a, b) => b.date.localeCompare(a.date));

			return {
				attendances: fullAttendanceList,
				success: true,
				message: "Loaded successfully!",
			};
		}

		// Jika filter status "No In" atau "No Out"
		const isFilterNoIn = params.inStatus === "No In";
		const isFilterNoOut = params.outStatus === "No Out";

		if (isFilterNoIn || isFilterNoOut) {
			// Ambil semua attendance dalam rentang tanggal dan filter perusahaan/employee
			const attendanceExistFilter = {
				date: { [Op.between]: [fromDate, toDate] },
			};
			if (params.companiesId) attendanceExistFilter.company_id = { [Op.in]: params.companiesId };
			if (params.employeesId) attendanceExistFilter.employee_id = { [Op.in]: params.employeesId };
			if (params.employee_id) attendanceExistFilter.employee_id = params.employee_id;

			const attendanceExistData = await dbSAPPS.Attendance.findAll({
				where: attendanceExistFilter,
				attributes: [
					"id",
					"date",
					"employee_id",
					"in_time",
					"in_status",
					"out_time",
					"out_status",
					"shifttype_id",
					"need_clarification",
					"in_address",
					"out_address",
					"in_latitude",
					"out_latitude",
					"in_longitude",
					"out_longitude",
					"total_hour",
					"attendancerequest_id",
					"leaveapplication_id",
					"reason",
				],
				include: [
					{
						model: dbSAPPS.AttendanceRequest,
						as: "attendancerequest",
					},
					{
						model: dbSAPPS.LeaveApplication,
						as: "leaveapplication",
						include: [
							{
								model: dbSAPPS.LeaveType,
								as: "leavetype",
								attributes: ["id", "display_name"],
							},
						],
					},
					{
						model: dbSAPPS.User,
						as: "modifier",
						attributes: ["full_name"],
					},
				],
			});

			// Map attendance untuk lookup cepat
			const attendanceMap = new Map();
			attendanceExistData.forEach((att) => {
				const key = `${att.date}_${att.employee_id}_${att.shifttype_id}`;
				attendanceMap.set(key, att);
			});

			const filteredFullAttendanceList = [];

			for (const date of datesInRange) {
				const dayName = getDayName(date);
				const holiday = holidayMap.get(date);
				const isHolidayDay = !!holiday;

				for (const emp of employees) {
					for (const shift of shiftTypes) {
						const attKey = `${date}_${emp.id}_${shift.id}`;
						const att = attendanceMap.get(attKey);

						let needClarification = false;

						if (att) {
							if (att.need_clarification === true || att.need_clarification === "true" || att.need_clarification === "1" || att.need_clarification === 1) {
								needClarification = true;
							} else {
								// Cek kelengkapan data in/out
								const inTimeEmpty = !att.in_time || att.in_time === "";
								const outTimeEmpty = !att.out_time || att.out_time === "";
								const inStatusEmpty = !att.in_status || att.in_status === "";
								const outStatusEmpty = !att.out_status || att.out_status === "";

								if (inTimeEmpty || outTimeEmpty || inStatusEmpty || outStatusEmpty) {
									needClarification = true;
								} else {
									// Tambahan: Jika status Late atau LeftEarlier, set true
									if (
										(att.in_status === "Late" || att.out_status === "Left Earlier") &&
										att.attendancerequest_id === null &&
										att.leaveapplication_id === null
									) {
										needClarification = true;
									}
								}
							}
						} else {
							if (!isHolidayDay) {
								if (isFilterNoIn) {
									needClarification = true;
								} else if (isFilterNoOut) {
									needClarification = true;
								}
							} else {
								needClarification = false;
							}
						}

						filteredFullAttendanceList.push({
							id: att ? att.id : null,
							date: date,
							employee_id: emp.id,
							employee_name: emp.employee_name,
							shifttype_id: shift.id,
							shifttype: shiftTypes.find((s) => s.id === shift.id) || null,
							company_id: emp.company_id,
							company_name: companyMap.get(emp.company_id) || null,
							modifier: att ? (att.modifier ? att.modifier.full_name : null) : null,
							in_time: att ? att.in_time : null,
							out_time: att ? att.out_time : null,
							in_status: att ? att.in_status : null,
							out_status: att ? att.out_status : null,
							total_hour: att ? att.total_hour : 0,
							in_address: att ? att.in_address : null,
							out_address: att ? att.out_address : null,
							in_latitude: att ? att.in_latitude : null,
							out_latitude: att ? att.out_latitude : null,
							in_longitude: att ? att.in_longitude : null,
							out_longitude: att ? att.out_longitude : null,
							is_holiday: isHolidayDay,
							holiday: holiday || null,
							holiday_name: holiday ? holiday.display_name : null,
							need_clarification: needClarification,
							day_name: dayName,
							leavetype_name: att && att.leaveapplication ? att.leaveapplication.leavetype.display_name : null,
							is_leave: isLeaveApplicationExist(emp.id, date), // Sekarang synchronous via Map
							is_attendance_request: isAttendanceRequestExist(emp.id, date), // Sekarang synchronous via Map
							attendancerequest_id: att ? (att.attendancerequest ? att.attendancerequest.id : null) : null,
							leaveapplication_id: att ? (att.leaveapplication ? att.leaveapplication.id : null) : null,
							reason: att ? (att.reason ? att.reason : null) : null,
						});
					}
				}
			}

			filteredFullAttendanceList.sort((a, b) => b.date.localeCompare(a.date));

			return {
				attendances: filteredFullAttendanceList,
				success: true,
				message: "Loaded successfully!",
			};
		}

		// Filter status selain "No In" atau "No Out"
		const attendanceFilter = {
			date: { [Op.between]: [fromDate, toDate] },
		};
		if (params.companiesId) attendanceFilter.company_id = { [Op.in]: params.companiesId };
		if (params.employeesId) attendanceFilter.employee_id = { [Op.in]: params.employeesId };
		if (params.employee_id) attendanceFilter.employee_id = params.employee_id;

		if (params.inStatus && params.inStatus !== "No In") {
			attendanceFilter.in_status = { [Op.eq]: params.inStatus };
			attendanceFilter.in_time = { [Op.and]: [{ [Op.not]: null }, { [Op.ne]: "" }] };
		}
		if (params.outStatus && params.outStatus !== "No Out") {
			attendanceFilter.out_status = { [Op.eq]: params.outStatus };
			attendanceFilter.out_time = { [Op.and]: [{ [Op.not]: null }, { [Op.ne]: "" }] };
		}

		const attendances = await dbSAPPS.Attendance.findAll({
			where: attendanceFilter,
			order: [["date", "DESC"]],
			include: [
				{
					model: dbSAPPS.AttendanceRequest,
					as: "attendancerequest",
				},
				{
					model: dbSAPPS.LeaveApplication,
					as: "leaveapplication",
					include: [
						{
							model: dbSAPPS.LeaveType,
							as: "leavetype",
							attributes: ["id", "display_name"],
						},
					],
				},
				{
					model: dbSAPPS.User,
					as: "modifier",
					attributes: ["full_name"],
				},
			],
		});

		const attendanceList = attendances.map((att) => {
			const emp = employees.find((e) => e.id === att.employee_id);
			const dayName = getDayName(att.date);

			// Perbaikan need_clarification juga di sini
			let needClarification = false;
			if (att.need_clarification === true || att.need_clarification === "true" || att.need_clarification === "1" || att.need_clarification === 1) {
				needClarification = true;
			} else {
				const inTimeEmpty = !att.in_time || att.in_time === "";
				const outTimeEmpty = !att.out_time || att.out_time === "";
				const inStatusEmpty = !att.in_status || att.in_status === "";
				const outStatusEmpty = !att.out_status || att.out_status === "";

				if (inTimeEmpty || outTimeEmpty || inStatusEmpty || outStatusEmpty) {
					needClarification = true;
				} else {
					// Tambahan: Jika status Late atau LeftEarlier, set true
					if ((att.in_status === "Late" || att.out_status === "Left Earlier") && att.attendancerequest_id === null && att.leaveapplication_id === null) {
						needClarification = true;
					}
				}
			}

			return {
				...att.dataValues,
				company_name: companyMap.get(att.company_id) || null,
				employee_name: emp ? emp.employee_name : null,
				modifier: att.modifier ? att.modifier.full_name : null,
				shifttype: shiftTypes.find((s) => s.id === att.shifttype_id) || null,
				is_holiday: isHoliday(att.date),
				holiday: holidayMap.get(att.date) || null,
				need_clarification: needClarification,
				day_name: dayName,
				leavetype_name: att.leaveapplication ? att.leaveapplication.leavetype.display_name : null,
				is_leave: isLeaveApplicationExist(emp.id, att.date), // Sekarang synchronous via Map
				is_attendance_request: isAttendanceRequestExist(emp.id, att.date), // Sekarang synchronous via Map
				attendancerequest_id: att ? (att.attendancerequest ? att.attendancerequest.id : null) : null,
				leaveapplication_id: att ? (att.leaveapplication ? att.leaveapplication.id : null) : null,
				reason: att ? (att.reason ? att.reason : null) : null,
			};
		});

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

async function getByApprover(params) {
	try {
		const filterConditionsApprover = {};
		const filterConditionsApproverOr = [];

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

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

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

		const attendances = await dbSAPPS.Attendance.findAll({
			where: filterConditionsApprover,
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.ShiftType,
					as: "shifttype",
					attributes: ["id", "display_name"],
				},
			],
			order: [["modified", "DESC"]],
		});

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

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

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

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

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

		const attendances = await dbSAPPS.Attendance.findAll({
			where: filterConditions,
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.ShiftType,
					as: "shifttype",
					attributes: ["id", "display_name"],
				},
			],
			order: [["modified", "DESC"]],
		});

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

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

		filterConditionsEmployee.enabled = true;
		filterConditionsEmployee.shifttype_id = {
			[Op.not]: null,
		};

		const employees = await dbSAPPS.Employee.findAll({
			where: filterConditionsEmployee,
			attributes: ["id", "employee_name", "employee_number"],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.ShiftType,
					as: "shifttype",
					attributes: ["id", "display_name"],
				},
			],
			order: [["employee_name", "ASC"]],
		});

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

		const dates = [];
		if (params.from_date && params.to_date) {
			const fromDate = moment(params.from_date);
			const toDate = moment(params.to_date);
			while (fromDate.isSameOrBefore(toDate)) {
				dates.push(fromDate.format("YYYY-MM-DD"));
				fromDate.add(1, "days");
			}
		}

		const dataExportProcessing = await Promise.all(
			employees &&
				employees.map(async (employee) => {
					const dataWithDate = await Promise.all(
						dates.map(async (date) => {
							const matchedDataAttendance =
								attendances && attendances.filter((attendance) => attendance.employee_id === employee.id && attendance.date === date);
							const matchedDataHariLibur = holidays && holidays.filter((holiday) => holiday.holiday_date === date);
							const dayStatus = matchedDataHariLibur[0]?.is_national_holiday
								? "National Holiday"
								: moment(date).format("dddd") === "Saturday" || moment(date).format("dddd") === "Sunday"
								? "Day Off"
								: "Work Day";

							const foundAttendance = matchedDataAttendance && matchedDataAttendance.length > 0 ? true : false;
							const foundReason = matchedDataAttendance[0]?.reason ? true : false;
							const inTimeStatus = !matchedDataAttendance[0]?.in_time ? true : false;
							const outTimeStatus = !matchedDataAttendance[0]?.out_time ? true : false;
							const inTimeNullStatus = matchedDataAttendance[0]?.in_time === "00:00:00" ? true : false;
							const outTimeNullStatus = matchedDataAttendance[0]?.out_time === "00:00:00" ? true : false;
							const inStatus = matchedDataAttendance[0]?.in_status === "Late" ? true : false;
							const outStatus = matchedDataAttendance[0]?.out_status === "Left Earlier" ? true : false;
							const statusWorkingInDayoff =
								matchedDataAttendance[0]?.in_time &&
								matchedDataAttendance[0]?.in_time !== "00:00:00" &&
								matchedDataAttendance[0]?.out_time &&
								matchedDataAttendance[0]?.out_time !== "00:00:00" &&
								dayStatus === "Day Off"
									? true
									: false;
							const statusWorkingInNationalHoliday =
								matchedDataAttendance[0]?.in_time &&
								matchedDataAttendance[0]?.in_time !== "00:00:00" &&
								matchedDataAttendance[0]?.out_time &&
								matchedDataAttendance[0]?.out_time !== "00:00:00" &&
								dayStatus === "National Holiday"
									? true
									: false;

							const newDataExportProcessing = {
								employee_id: employee?.id,
								employee_name: employee?.employee_name,
								date: date,
								day: moment(date).format("dddd"),
								day_status: matchedDataHariLibur[0]?.is_national_holiday
									? "National Holiday"
									: moment(date).format("dddd") === "Saturday" || moment(date).format("dddd") === "Sunday"
									? "Day Off"
									: "Work Day",
								holiday_name: matchedDataHariLibur[0]?.display_name,
								attendance_id: matchedDataAttendance[0]?.id === 0 || !matchedDataAttendance[0]?.id ? "" : matchedDataAttendance[0]?.id,
								schedule_in: employee?.shifttype?.late_time ? employee?.shifttype?.late_time : "",
								schedule_out: employee?.shifttype?.ealy_time ? employee?.shifttype?.ealy_time : "",
								in_time: matchedDataAttendance[0]?.in_time ? matchedDataAttendance[0]?.in_time : "",
								out_time: matchedDataAttendance[0]?.out_time ? matchedDataAttendance[0]?.out_time : "",
								in_status: matchedDataAttendance[0]?.in_status ? matchedDataAttendance[0]?.in_status : "",
								out_status: matchedDataAttendance[0]?.out_status ? matchedDataAttendance[0]?.out_status : "",
								in_distance: matchedDataAttendance[0]?.in_distance ? matchedDataAttendance[0]?.in_distance : "",
								out_distance: matchedDataAttendance[0]?.out_distance ? matchedDataAttendance[0]?.out_distance : "",
								in_address: matchedDataAttendance[0]?.in_address ? matchedDataAttendance[0]?.in_address : "",
								out_address: matchedDataAttendance[0]?.out_address ? matchedDataAttendance[0]?.out_address : "",
								total_hour: matchedDataAttendance[0]?.total_hour ? matchedDataAttendance[0]?.total_hour : "",
								notes: matchedDataAttendance[0]?.notes ? matchedDataAttendance[0]?.notes : "",
								reason: matchedDataAttendance[0]?.reason ? matchedDataAttendance[0]?.reason : "",
								in_latitude: matchedDataAttendance[0]?.in_latitude ? matchedDataAttendance[0]?.in_latitude : "",
								out_latitude: matchedDataAttendance[0]?.out_latitude ? matchedDataAttendance[0]?.out_latitude : "",
								in_longitude: matchedDataAttendance[0]?.in_longitude ? matchedDataAttendance[0]?.in_longitude : "",
								out_longitude: matchedDataAttendance[0]?.out_longitude ? matchedDataAttendance[0]?.out_longitude : "",
								company: employee?.company?.display_name,
								company_id: employee?.company?.id,
								shifttype_id: employee?.shifttype?.id,
								status_absenceclarification:
									(inTimeStatus || outTimeStatus || inTimeNullStatus || outTimeNullStatus || inStatus || outStatus) &&
									dayStatus !== "Day Off" &&
									dayStatus !== "National Holiday" &&
									!foundReason
										? true
										: false,
								status_working_in_dayoff: statusWorkingInDayoff,
								status_working_in_national_holiday: statusWorkingInNationalHoliday,
								status_permitease: matchedDataAttendance[0]?.status_permitease ? matchedDataAttendance[0]?.status_permitease : false,
							};

							return newDataExportProcessing;
						})
					);

					return dataWithDate;
				})
		);

		const mergedArray = dataExportProcessing.flat();
		return {
			attendances: mergedArray,
			success: true,
			message: "attendance loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to load attendance!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

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

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

		// Filter status inStatus
		if (params.inStatus) {
			if (params.inStatus === "No In") {
				filterConditions.in_status = {
					[Op.or]: [{ [Op.is]: null }, { [Op.eq]: "" }],
				};
			} else {
				filterConditions.in_status = {
					[Op.eq]: params.inStatus,
				};
			}
		}

		// Filter status outStatus
		if (params.outStatus) {
			if (params.outStatus === "No Out") {
				filterConditions.out_status = {
					[Op.or]: [{ [Op.is]: null }, { [Op.eq]: "" }],
				};
			} else {
				filterConditions.out_status = {
					[Op.eq]: params.outStatus,
				};
			}
		}

		// Jika inStatus dan outStatus keduanya kosong/null, hapus filter status agar ambil semua data
		if (!params.inStatus && !params.outStatus) {
			delete filterConditions.in_status;
			delete filterConditions.out_status;
		}

		// Ambil data attendance sesuai filter
		let options = await dbSAPPS.Attendance.findAll({
			where: filterConditions,
			attributes: ["id", "employee_id", "date"],
			order: [["modified", "ASC"]],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "employee_number"],
				},
				{
					model: dbSAPPS.ShiftType,
					as: "shifttype",
					attributes: [
						"id",
						"enabled",
						"name",
						"display_name",
						"code",
						"start_time",
						"end_time",
						"late_time",
						"early_time",
						"duration_break",
						"start_date_ramadhan",
						"end_date_ramadhan",
						"start_time_ramadhan",
						"end_time_ramadhan",
						"late_time_ramadhan",
						"early_time_ramadhan",
						"duration_break_ramadhan",
						"enable_auto_attendance",
						"location",
						"holidaygroup_id",
						"company_id",
					],
				},
			],
		});

		// Map hasil untuk menambahkan company_name
		options = options.map((option) => ({
			...option.dataValues,
			company_name: option?.company?.display_name || null,
			employee_name: option?.employee?.employee_name || null,
			shifttype_name: option?.shifttype?.name || null,
		}));

		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() {
	try {
		// filter
		const filterConditions = {};

		const count = await dbSAPPS.Attendance.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();
	try {
		// validate existing
		const existing = await dbSAPPS.Attendance.findOne({
			where: {
				employee_id: params.employee_id,
				company_id: params.company_id,
				shifttype_id: params.shifttype_id,
				date: params.date,
			},
		});
		if (existing) {
			return {
				success: false,
				message: `There is already an attendance record on "${params.date}". Please try again with a different date or update the existing record if necessary.`,
			};
		}

		// save
		await dbSAPPS.Attendance.create(params, { 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 {
		const attendance = await dbSAPPS.Attendance.findByPk(id);
		if (!attendance) return { success: false, message: "ID not found!" };

		const existing = await dbSAPPS.Attendance.findOne({
			where: {
				employee_id: params.employee_id,
				company_id: params.company_id,
				shifttype_id: params.shifttype_id,
				date: params.date,
				id: {
					[Op.ne]: id,
				},
			},
			transaction,
		});
		if (existing) {
			return {
				success: false,
				message: `There is already an attendance record on "${params.date}". Please try again with a different date or update the existing record if necessary.`,
			};
		}

		// save
		await attendance.update(params, { 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 _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// Cari attendance dengan lock update untuk menghindari race condition
		const attendance = await dbSAPPS.Attendance.findByPk(id, {
			transaction,
			lock: transaction.LOCK.UPDATE,
		});

		if (!attendance) {
			await transaction.rollback();
			return { success: false, message: "ID not found!" };
		}

		// Cek apakah attendance digunakan di EmployeeCheckin
		const usedEmployeeCheckinCount = await dbSAPPS.EmployeeCheckin.count({
			where: {
				employee_id: attendance.employee_id,
				company_id: attendance.company_id,
				shifttype_id: attendance.shifttype_id,
				date: attendance.date,
			},
			transaction,
			lock: transaction.LOCK.UPDATE,
		});

		if (usedEmployeeCheckinCount > 0) {
			await transaction.rollback();
			throw new Error(`You cannot delete attendance because there are employee checkins on ${attendance.date}`);
		}

		// Hapus attendance
		await attendance.destroy({ transaction });

		await transaction.commit();
		return {
			success: true,
			message: "Deleted successfully!",
		};
	} catch (error) {
		try {
			await transaction.rollback();
		} catch (rollbackError) {
			console.error("Rollback error:", rollbackError);
		}
		return {
			success: false,
			message: error?.message || "Failed to delete, please try again!",
		};
	}
}
