const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
require("dotenv").config();

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

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

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

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

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

		let leaveAllocations = [];
		leaveAllocations = await dbSAPPS.LeaveAllocation.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name", "name"],
				},
				{
					model: dbSAPPS.LeaveType,
					as: "leavetype",
					attributes: ["id", "name", "display_name"],
				},
				{
					model: dbSAPPS.LeavePeriod,
					as: "leaveperiod",
					attributes: ["id", "name", "display_name", "from_date", "to_date", "is_active"],
				},
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_number", "employee_name", "company_id"],
				},
				{
					model: dbSAPPS.User,
					as: "modifier", // pastikan alias sesuai relasi di model Attendance
					attributes: ["full_name"],
				},
			],
		});

		leaveAllocations = leaveAllocations.map((leaveAllocation) => {
			return {
				...leaveAllocation.dataValues,
				company_name: leaveAllocation?.company?.display_name,
				leavetype_name: leaveAllocation?.leavetype?.display_name,
				leaveperiod_name: leaveAllocation?.leaveperiod?.display_name,
				employee_name: leaveAllocation?.employee?.employee_name,
				modifier: leaveAllocation?.modifier?.full_name,
			};
		});

		leaveAllocations = await formatWithModifier(leaveAllocations);

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

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

		let options = [];
		options = await dbSAPPS.LeaveAllocation.findAll({
			attributes: [
				"id",
				"name",
				"display_name",
				"leavetype_id",
				"leaveperiod_id",
				"company_id",
				"employee_id",
				"new_leaves_allocated",
				"total_leaves_allocated",
				"unused_leaves",
				"used_leaves",
				"expired_leaves",
				"leaves_pending_approval",
			],
			where: filterConditions,
			order: [["name", "ASC"]],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name", "name"],
				},
				{
					model: dbSAPPS.LeaveType,
					as: "leavetype",
					attributes: ["id", "name", "display_name"],
				},
				{
					model: dbSAPPS.LeavePeriod,
					as: "leaveperiod",
					attributes: ["id", "name", "display_name", "from_date", "to_date", "is_active"],
				},
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_number", "employee_name", "company_id"],
				},
			],
		});

		options = options.map((option) => {
			return {
				...option.dataValues,
				company_name: option?.company?.display_name,
				employee_name: option?.employee?.employee_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.LeaveAllocation.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.LeaveAllocation.findOne({
			where: {
				display_name: params.display_name,
				name: params.name,
				company_id: params.company_id,
			},
			transaction,
		});
		if (existing) {
			return {
				success: false,
				message: `"${params.display_name}" is already taken.`,
			};
		}

		if (!params.enabled) params.enabled = true;

		// Validate LeavePeriod exists and is active
		if (params.leaveperiod_id) {
			const leavePeriod = await dbSAPPS.LeavePeriod.findOne({
				where: { id: params.leaveperiod_id },
				transaction,
			});
			if (!leavePeriod) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Period dengan ID ${params.leaveperiod_id} tidak ditemukan.`,
				};
			}
			if (leavePeriod.company_id !== parseInt(params.company_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Period tidak sesuai dengan company yang dipilih.`,
				};
			}
			// Validate dates are within leave period range
			const periodFromDate = new Date(leavePeriod.from_date);
			const periodToDate = new Date(leavePeriod.to_date);
			const allocFromDate = new Date(params.from_date);
			const allocToDate = new Date(params.to_date);
			if (allocFromDate < periodFromDate || allocToDate > periodToDate) {
				await transaction.rollback();
				return {
					success: false,
					message: `Tanggal Leave Allocation (${params.from_date} - ${params.to_date}) harus berada dalam periode Leave Period (${leavePeriod.from_date} - ${leavePeriod.to_date}).`,
				};
			}
		}

		// Validate LeaveType exists and matches company
		if (params.leavetype_id) {
			const leaveType = await dbSAPPS.LeaveType.findOne({
				where: { id: params.leavetype_id },
				transaction,
			});
			if (!leaveType) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Type dengan ID ${params.leavetype_id} tidak ditemukan.`,
				};
			}
			if (leaveType.company_id !== parseInt(params.company_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Type tidak sesuai dengan company yang dipilih.`,
				};
			}
		}

		// Validate Employee exists and matches company
		if (params.employee_id) {
			const employee = await dbSAPPS.Employee.findOne({
				where: { id: params.employee_id },
				transaction,
			});
			if (!employee) {
				await transaction.rollback();
				return {
					success: false,
					message: `Employee dengan ID ${params.employee_id} tidak ditemukan.`,
				};
			}
			if (employee.company_id !== parseInt(params.company_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Employee tidak sesuai dengan company yang dipilih.`,
				};
			}
		}

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

		// validate existing
		const existing = await dbSAPPS.LeaveAllocation.findOne({
			where: {
				display_name: params.display_name,
				name: params.name,
				company_id: params.company_id,
				id: {
					[Op.ne]: id,
				},
			},
			transaction,
		});
		if (existing) {
			return {
				success: false,
				message: `"${params.display_name}" is already taken.`,
			};
		}

		// Validate LeavePeriod exists and is active (if being changed)
		if (params.leaveperiod_id && params.leaveperiod_id !== leaveAllocation.leaveperiod_id) {
			const leavePeriod = await dbSAPPS.LeavePeriod.findOne({
				where: { id: params.leaveperiod_id },
				transaction,
			});
			if (!leavePeriod) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Period dengan ID ${params.leaveperiod_id} tidak ditemukan.`,
				};
			}
			if (leavePeriod.company_id !== parseInt(params.company_id || leaveAllocation.company_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Period tidak sesuai dengan company yang dipilih.`,
				};
			}
			// Validate dates are within leave period range
			const periodFromDate = new Date(leavePeriod.from_date);
			const periodToDate = new Date(leavePeriod.to_date);
			const allocFromDate = new Date(params.from_date || leaveAllocation.from_date);
			const allocToDate = new Date(params.to_date || leaveAllocation.to_date);
			if (allocFromDate < periodFromDate || allocToDate > periodToDate) {
				await transaction.rollback();
				return {
					success: false,
					message: `Tanggal Leave Allocation (${allocFromDate.toISOString().split('T')[0]} - ${allocToDate.toISOString().split('T')[0]}) harus berada dalam periode Leave Period (${leavePeriod.from_date} - ${leavePeriod.to_date}).`,
				};
			}
		}

		// Validate LeaveType exists and matches company (if being changed)
		if (params.leavetype_id && params.leavetype_id !== leaveAllocation.leavetype_id) {
			const leaveType = await dbSAPPS.LeaveType.findOne({
				where: { id: params.leavetype_id },
				transaction,
			});
			if (!leaveType) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Type dengan ID ${params.leavetype_id} tidak ditemukan.`,
				};
			}
			if (leaveType.company_id !== parseInt(params.company_id || leaveAllocation.company_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Leave Type tidak sesuai dengan company yang dipilih.`,
				};
			}
		}

		// Validate Employee exists and matches company (if being changed)
		if (params.employee_id && params.employee_id !== leaveAllocation.employee_id) {
			const employee = await dbSAPPS.Employee.findOne({
				where: { id: params.employee_id },
				transaction,
			});
			if (!employee) {
				await transaction.rollback();
				return {
					success: false,
					message: `Employee dengan ID ${params.employee_id} tidak ditemukan.`,
				};
			}
			if (employee.company_id !== parseInt(params.company_id || leaveAllocation.company_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: `Employee tidak sesuai dengan company yang dipilih.`,
				};
			}
		}

		// save
		await leaveAllocation.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 {
		// validate existing
		const leaveAllocation = await dbSAPPS.LeaveAllocation.findByPk(id, {
			transaction,
		});
		if (!leaveAllocation) return { success: false, message: "ID not found!" };

		// validate used in leaveApplication
		const usedInLeaveApplication = await dbSAPPS.LeaveApplication.count({
			where: {
				leaveallocation_id: id,
			},
			transaction,
		});
		if (usedInLeaveApplication > 0) {
			throw new Error(`This leave allocation is being used in Leave Application, cannot be deleted!`);
		}

		// delete
		await leaveAllocation.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,
		};
	}
}

// ─── Import Leave Allocations ────────────────────────────────────────────────

function normalizeStr(val) {
	if (val == null) return "";
	return String(val).trim();
}

function normalizeDateOnly(val) {
	if (!val) return null;
	if (val instanceof Date && !isNaN(val.getTime())) return val.toISOString().slice(0, 10);
	const s = typeof val === "string" ? val.trim() : String(val).trim();
	if (!s) return null;
	// Try parsing common date formats
	// DD-Mon-YY, DD/MM/YYYY, YYYY-MM-DD, D-MMM-YY, etc.
	const months = { jan: 1, feb: 2, mar: 3, apr: 4, may: 5, jun: 6, jul: 7, aug: 8, sep: 9, oct: 10, nov: 11, dec: 12 };
	// Try D-Mon-YY or DD-Mon-YY (e.g. 1-Aug-11, 12-Sep-11)
	const m1 = s.match(/^(\d{1,2})[-\/](\w{3})[-\/](\d{2,4})$/);
	if (m1) {
		const day = parseInt(m1[1], 10);
		const mon = months[m1[2].toLowerCase().slice(0, 3)];
		let year = parseInt(m1[3], 10);
		if (year < 100) year += year < 50 ? 2000 : 1900;
		if (mon && day >= 1 && day <= 31) {
			return `${year}-${String(mon).padStart(2, "0")}-${String(day).padStart(2, "0")}`;
		}
	}
	// Try DD/MM/YYYY or DD-MM-YYYY
	const m2 = s.match(/^(\d{1,2})[-\/](\d{1,2})[-\/](\d{4})$/);
	if (m2) {
		const day = parseInt(m2[1], 10);
		const mon = parseInt(m2[2], 10);
		const year = parseInt(m2[3], 10);
		if (mon >= 1 && mon <= 12 && day >= 1 && day <= 31) {
			return `${year}-${String(mon).padStart(2, "0")}-${String(day).padStart(2, "0")}`;
		}
	}
	// Try Excel serial number
	const num = Number(s);
	if (!isNaN(num) && num > 30000 && num < 70000) {
		// Excel serial to date
		const d = new Date((num - 25569) * 86400 * 1000);
		if (!isNaN(d.getTime())) return d.toISOString().slice(0, 10);
	}
	// Fallback: native Date parse
	const d = new Date(s);
	return isNaN(d.getTime()) ? null : d.toISOString().slice(0, 10);
}

function parseFloat2(val) {
	if (val == null || val === "") return null;
	const n = parseFloat(val);
	return isNaN(n) ? null : n;
}

async function resolveEmployee(empId, empName, companyId, transaction) {
	const sId = empId ? normalizeStr(empId) : null;
	const sName = empName ? normalizeStr(empName) : null;

	// 1. Try by employee_number (EMP ID) first if provided
	if (sId && !["emp id", "employee id", "employee number", "nik", "no karyawan"].includes(sId.toLowerCase())) {
		const byNumber = await dbSAPPS.Employee.findOne({
			where: { employee_number: sId, company_id: companyId },
			attributes: ["id", "employee_name", "employee_number", "company_id", "date_of_joining"],
			transaction,
		});
		if (byNumber) return byNumber;
	}

	// 2. Try by employee_name (Name) if number didn't match or wasn't provided
	if (sName && !["name", "employee name", "nama", "nama karyawan"].includes(sName.toLowerCase())) {
		const byName = await dbSAPPS.Employee.findOne({
			where: { employee_name: { [Op.like]: sName }, company_id: companyId },
			attributes: ["id", "employee_name", "employee_number", "company_id", "date_of_joining"],
			transaction,
		});
		if (byName) return byName;
	}

	return null;
}

async function resolveLeaveType(value, companyId, transaction) {
	const s = normalizeStr(value);
	if (!s) return null;
	const lt = await dbSAPPS.LeaveType.findOne({
		where: {
			company_id: companyId,
			[Op.or]: [
				{ display_name: { [Op.like]: s } },
				{ name: { [Op.like]: s } },
				{ code: { [Op.like]: s } },
			],
		},
		attributes: ["id", "display_name", "name"],
		transaction,
	});
	return lt;
}

async function resolveLeavePeriod(value, companyId, transaction) {
	const s = normalizeStr(value);
	if (!s) return null;
	const lp = await dbSAPPS.LeavePeriod.findOne({
		where: {
			company_id: companyId,
			[Op.or]: [
				{ display_name: { [Op.like]: s } },
				{ name: { [Op.like]: s } },
			],
		},
		attributes: ["id", "display_name", "name", "from_date", "to_date", "is_active"],
		transaction,
	});
	return lp;
}

async function findActiveLeavePeriod(companyId, terbitHakDate, transaction) {
	if (!terbitHakDate) return null;
	const lp = await dbSAPPS.LeavePeriod.findOne({
		where: {
			company_id: companyId,
			from_date: { [Op.lte]: terbitHakDate },
			to_date: { [Op.gte]: terbitHakDate },
		},
		attributes: ["id", "display_name", "name", "from_date", "to_date", "is_active"],
		transaction,
	});
	if (lp) return lp;
	// Fallback: find active leave period
	const activeLp = await dbSAPPS.LeavePeriod.findOne({
		where: { company_id: companyId, is_active: true },
		attributes: ["id", "display_name", "name", "from_date", "to_date", "is_active"],
		transaction,
	});
	return activeLp;
}

function isProbablyHeaderRow(row) {
	if (!row) return true;
	const empId = String(row.emp_id || row.employee_number || "").trim().toLowerCase();
	const empName = String(row.name || row.employee_name || "").trim().toLowerCase();

	const headerLabels = [
		"emp id",
		"employee_number",
		"employee number",
		"no karyawan",
		"nik",
		"nik karyawan",
		"name",
		"employee_name",
		"employee name",
		"nama",
		"nama_lengkap",
		"nama lengkap",
		"hak_cuti",
		"hak cuti",
		"saldo cuti",
	];

	if (headerLabels.includes(empId) || headerLabels.includes(empName)) return true;

	// Check if all fields look like their own keys
	if (row.emp_id === "emp_id" || row.hak_cuti === "hak_cuti") return true;

	return false;
}

async function previewImportLeaveAllocations(params) {
	try {
		const rows = Array.isArray(params.rows) ? params.rows : [];
		const defaultCompanyId = params.defaultCompanyId ? Number(params.defaultCompanyId) : null;
		const defaultLeaveTypeId = params.defaultLeaveTypeId ? Number(params.defaultLeaveTypeId) : null;
		const defaultLeavePeriodId = params.defaultLeavePeriodId ? Number(params.defaultLeavePeriodId) : null;
		const modifiedBy = params.modified_by ? Number(params.modified_by) : 1;

		const items = [];
		let creates = 0;
		let updates = 0;
		let skips = 0;
		let errors = 0;

		for (let i = 0; i < rows.length; i++) {
			const row = rows[i] || {};
			const rowNum = i + 1;
			const transaction = await dbSAPPS.sequelize.transaction();
			try {
				const empId = normalizeStr(row.emp_id || row.employee_number || "");
				const empName = normalizeStr(row.name || row.employee_name || "");

				if (!empId && !empName) {
					items.push({ row: rowNum, action: "error", message: "Emp ID atau Name wajib diisi." });
					errors++;
					await transaction.rollback();
					continue;
				}

				const companyId = defaultCompanyId;
				if (!companyId) {
					items.push({ row: rowNum, action: "error", message: "Company default belum dipilih." });
					errors++;
					await transaction.rollback();
					continue;
				}

				if (isProbablyHeaderRow(row)) {
					skips++;
					await transaction.rollback();
					continue;
				}

				// Resolve employee
				const employee = await resolveEmployee(empId, empName, companyId, transaction);
				if (!employee) {

					items.push({ row: rowNum, action: "error", message: `Employee "${empId || empName}" tidak ditemukan di company ini.` });
					errors++;
					await transaction.rollback();
					continue;
				}

				// Resolve leave type
				let leaveTypeId = defaultLeaveTypeId;
				let leaveTypeName = "(default)";
				if (row.leave_type) {
					const lt = await resolveLeaveType(row.leave_type, companyId, transaction);
					if (lt) {
						leaveTypeId = lt.id;
						leaveTypeName = lt.display_name || lt.name;
					} else {
						items.push({ row: rowNum, action: "error", message: `Leave Type "${row.leave_type}" tidak ditemukan.` });
						errors++;
						await transaction.rollback();
						continue;
					}
				} else if (leaveTypeId) {
					const ltDefault = await dbSAPPS.LeaveType.findByPk(leaveTypeId, { attributes: ["id", "display_name", "name"], transaction });
					leaveTypeName = ltDefault?.display_name || ltDefault?.name || "(default)";
				}

				if (!leaveTypeId) {
					items.push({ row: rowNum, action: "error", message: "Leave Type belum ditentukan." });
					errors++;
					await transaction.rollback();
					continue;
				}

				// Parse dates
				const joinDate = normalizeDateOnly(row.join_date || employee.date_of_joining);
				const terbitHak = normalizeDateOnly(row.terbit_hak);
				const hakCuti = parseFloat2(row.hak_cuti);
				const cutiDigunakan = parseFloat2(row.cuti_digunakan) || 0;
				const sisaCuti = parseFloat2(row.sisa_cuti);
				const masaKerja = parseFloat2(row.masa_kerja);

				if (hakCuti == null) {
					items.push({ row: rowNum, action: "error", message: "Hak Cuti wajib diisi." });
					errors++;
					await transaction.rollback();
					continue;
				}

				// Resolve leave period
				let leavePeriodId = defaultLeavePeriodId;
				let leavePeriodName = "(default)";
				let periodFromDate = null;
				let periodToDate = null;

				if (row.leave_period) {
					const lp = await resolveLeavePeriod(row.leave_period, companyId, transaction);
					if (lp) {
						leavePeriodId = lp.id;
						leavePeriodName = lp.display_name || lp.name;
						periodFromDate = lp.from_date;
						periodToDate = lp.to_date;
					}
				}

				if (!leavePeriodId && terbitHak) {
					// Auto-find leave period based on terbit hak
					const lpAuto = await findActiveLeavePeriod(companyId, terbitHak, transaction);
					if (lpAuto) {
						leavePeriodId = lpAuto.id;
						leavePeriodName = lpAuto.display_name || lpAuto.name;
						periodFromDate = lpAuto.from_date;
						periodToDate = lpAuto.to_date;
					}
				}

				if (!leavePeriodId) {
					// Still try default
					const lpDefault = defaultLeavePeriodId
						? await dbSAPPS.LeavePeriod.findByPk(defaultLeavePeriodId, {
							attributes: ["id", "display_name", "name", "from_date", "to_date"],
							transaction,
						})
						: null;
					if (lpDefault) {
						leavePeriodId = lpDefault.id;
						leavePeriodName = lpDefault.display_name || lpDefault.name;
						periodFromDate = lpDefault.from_date;
						periodToDate = lpDefault.to_date;
					}
				}

				if (!leavePeriodId) {
					items.push({ row: rowNum, action: "error", message: "Leave Period tidak ditemukan. Pilih default Leave Period atau isi kolom Leave Period / Terbit Hak." });
					errors++;
					await transaction.rollback();
					continue;
				}

				// Determine allocation dates
				const allocFromDate = terbitHak || periodFromDate;
				let allocToDate = periodToDate;

				// Fallback: If Terbit Hak exists but no periodToDate, set allocToDate to 1 year minus 1 day from Terbit Hak
				if (!allocToDate && terbitHak) {
					const [y, m, d] = terbitHak.split("-");
					const tDate = new Date(parseInt(y), parseInt(m) - 1, parseInt(d));
					tDate.setFullYear(tDate.getFullYear() + 1);
					tDate.setDate(tDate.getDate() - 1);
					allocToDate = tDate.toISOString().slice(0, 10);
				}

				if (!allocFromDate || !allocToDate) {
					items.push({ row: rowNum, action: "error", message: "Tanggal alokasi tidak bisa ditentukan. Pastikan Terbit Hak tersedia jika Leave Period tidak ditemukan." });
					errors++;
					await transaction.rollback();
					continue;
				}

				// Generate History Reason if any cuti digunakan
				let historicalReason = "";
				if (cutiDigunakan > 0) {
					const months = [
						{ key: 'jan', label: 'Jan' }, { key: 'feb', label: 'Feb' }, { key: 'mar', label: 'Mar' },
						{ key: 'apr', label: 'Apr' }, { key: 'may', label: 'Mei' }, { key: 'jun', label: 'Jun' },
						{ key: 'jul', label: 'Jul' }, { key: 'aug', label: 'Ags' }, { key: 'sep', label: 'Sep' },
						{ key: 'oct', label: 'Okt' }, { key: 'nov', label: 'Nov' }, { key: 'dec', label: 'Des' }
					];

					const monthlyDetails = [];
					for (const m of months) {
						const val = parseFloat2(row[m.key]);
						if (val > 0) {
							monthlyDetails.push(`${m.label}: ${val}`);
						}
					}

					const ket = normalizeStr(row.keterangan);
					const notes = ket ? ` | Keterangan: ${ket}` : "";

					historicalReason = `[Riwayat Excel] ${monthlyDetails.length > 0 ? monthlyDetails.join(", ") : "Total: " + cutiDigunakan}${notes}`;
				}

				// Check existing allocation
				const existingAllocation = await dbSAPPS.LeaveAllocation.findOne({
					where: {
						employee_id: employee.id,
						leavetype_id: leaveTypeId,
						leaveperiod_id: leavePeriodId,
						company_id: companyId,
					},
					transaction,
				});

				const computedSisa = sisaCuti != null ? sisaCuti : hakCuti - cutiDigunakan;
				const allocationName = `${employee.employee_name} - ${leaveTypeName}`;

				if (!existingAllocation) {
					creates++;
					items.push({
						row: rowNum,
						action: "create",
						employee: { id: employee.id, name: employee.employee_name, number: employee.employee_number },
						leave_type: leaveTypeName,
						leave_period: leavePeriodName,
						hak_cuti: hakCuti,
						cuti_digunakan: cutiDigunakan,
						sisa_cuti: computedSisa,
						historical_reason: historicalReason,
						masa_kerja: masaKerja,
						join_date: joinDate,
						terbit_hak: terbitHak,
						from_date: allocFromDate,
						to_date: allocToDate,
					});
				} else {
					// Determine if there are real changes
					const changes = [];
					if (existingAllocation.new_leaves_allocated !== hakCuti) {
						changes.push({ field: "new_leaves_allocated", from: existingAllocation.new_leaves_allocated, to: hakCuti });
					}
					if (existingAllocation.used_leaves !== cutiDigunakan) {
						changes.push({ field: "used_leaves", from: existingAllocation.used_leaves, to: cutiDigunakan });
					}
					if (existingAllocation.unused_leaves !== computedSisa) {
						changes.push({ field: "unused_leaves", from: existingAllocation.unused_leaves, to: computedSisa });
					}
					const totalLeavesVal = hakCuti;
					if (existingAllocation.total_leaves_allocated !== totalLeavesVal) {
						changes.push({ field: "total_leaves_allocated", from: existingAllocation.total_leaves_allocated, to: totalLeavesVal });
					}

					if (changes.length === 0) {
						skips++;
						items.push({
							row: rowNum,
							action: "skip",
							employee: { id: employee.id, name: employee.employee_name, number: employee.employee_number },
							leave_type: leaveTypeName,
							leave_period: leavePeriodName,
							message: "Tidak ada perubahan.",
						});
					} else {
						updates++;
						items.push({
							row: rowNum,
							action: "update",
							employee: { id: employee.id, name: employee.employee_name, number: employee.employee_number },
							leave_type: leaveTypeName,
							leave_period: leavePeriodName,
							hak_cuti: hakCuti,
							cuti_digunakan: cutiDigunakan,
							sisa_cuti: computedSisa,
							historical_reason: historicalReason,
							changes,
							existing_id: existingAllocation.id,
						});
					}
				}

				await transaction.rollback();
			} catch (err) {
				await transaction.rollback();
				items.push({ row: rowNum, action: "error", message: err?.message || "Unknown error" });
				errors++;
			}
		}

		return {
			success: true,
			message: "Preview generated",
			summary: { creates, updates, skips, errors },
			items,
		};
	} catch (error) {
		return { success: false, message: error?.message || "Failed to preview import" };
	}
}

async function applyImportLeaveAllocations(params) {
	try {
		const rows = Array.isArray(params.rows) ? params.rows : [];
		const defaultCompanyId = params.defaultCompanyId ? Number(params.defaultCompanyId) : null;
		const defaultLeaveTypeId = params.defaultLeaveTypeId ? Number(params.defaultLeaveTypeId) : null;
		const defaultLeavePeriodId = params.defaultLeavePeriodId ? Number(params.defaultLeavePeriodId) : null;
		const modifiedBy = params.modified_by ? Number(params.modified_by) : 1;
		const owner = params.owner ? Number(params.owner) : modifiedBy;

		const results = { created: 0, updated: 0, skipped: 0, errors: [] };

		for (let i = 0; i < rows.length; i++) {
			const row = rows[i] || {};
			const rowNum = i + 1;
			const transaction = await dbSAPPS.sequelize.transaction();
			try {
				const empId = normalizeStr(row.emp_id || row.employee_number || "");
				const empName = normalizeStr(row.name || row.employee_name || "");
				const companyId = defaultCompanyId;

				if (!empId && !empName) {
					results.errors.push({ row: rowNum, message: "Emp ID atau Name wajib diisi." });
					await transaction.rollback();
					continue;
				}
				if (!companyId) {
					results.errors.push({ row: rowNum, message: "Company default belum dipilih." });
					await transaction.rollback();
					continue;
				}

				if (isProbablyHeaderRow(row)) {
					results.skipped++;
					await transaction.rollback();
					continue;
				}

				// Resolve employee
				const employee = await resolveEmployee(empId, empName, companyId, transaction);
				if (!employee) {

					results.errors.push({ row: rowNum, message: `Employee "${empId || empName}" tidak ditemukan.` });
					await transaction.rollback();
					continue;
				}

				// Resolve leave type
				let leaveTypeId = defaultLeaveTypeId;
				let leaveTypeName = "(default)";
				if (row.leave_type) {
					const lt = await resolveLeaveType(row.leave_type, companyId, transaction);
					if (lt) {
						leaveTypeId = lt.id;
						leaveTypeName = lt.display_name || lt.name;
					} else {
						results.errors.push({ row: rowNum, message: `Leave Type "${row.leave_type}" tidak ditemukan.` });
						await transaction.rollback();
						continue;
					}
				} else if (leaveTypeId) {
					const ltDefault = await dbSAPPS.LeaveType.findByPk(leaveTypeId, { attributes: ["id", "display_name", "name"], transaction });
					leaveTypeName = ltDefault?.display_name || ltDefault?.name || "(default)";
				}

				if (!leaveTypeId) {
					results.errors.push({ row: rowNum, message: "Leave Type belum ditentukan." });
					await transaction.rollback();
					continue;
				}

				const joinDate = normalizeDateOnly(row.join_date || employee.date_of_joining);
				const terbitHak = normalizeDateOnly(row.terbit_hak);
				const hakCuti = parseFloat2(row.hak_cuti);
				const cutiDigunakan = parseFloat2(row.cuti_digunakan) || 0;
				const sisaCuti = parseFloat2(row.sisa_cuti);
				const masaKerja = parseFloat2(row.masa_kerja);

				if (hakCuti == null) {
					results.errors.push({ row: rowNum, message: "Hak Cuti wajib diisi." });
					await transaction.rollback();
					continue;
				}

				// Resolve leave period
				let leavePeriodId = defaultLeavePeriodId;
				let periodFromDate = null;
				let periodToDate = null;

				if (row.leave_period) {
					const lp = await resolveLeavePeriod(row.leave_period, companyId, transaction);
					if (lp) {
						leavePeriodId = lp.id;
						periodFromDate = lp.from_date;
						periodToDate = lp.to_date;
					}
				}

				if (!leavePeriodId && terbitHak) {
					const lpAuto = await findActiveLeavePeriod(companyId, terbitHak, transaction);
					if (lpAuto) {
						leavePeriodId = lpAuto.id;
						periodFromDate = lpAuto.from_date;
						periodToDate = lpAuto.to_date;
					}
				}

				if (!leavePeriodId) {
					if (defaultLeavePeriodId) {
						const lpDefault = await dbSAPPS.LeavePeriod.findByPk(defaultLeavePeriodId, { attributes: ["id", "from_date", "to_date"], transaction });
						if (lpDefault) {
							leavePeriodId = lpDefault.id;
							periodFromDate = lpDefault.from_date;
							periodToDate = lpDefault.to_date;
						}
					}
				}

				if (!leavePeriodId) {
					results.errors.push({ row: rowNum, message: "Leave Period tidak ditemukan." });
					await transaction.rollback();
					continue;
				}

				const allocFromDate = terbitHak || periodFromDate;
				let allocToDate = periodToDate;

				// Fallback: If Terbit Hak exists but no periodToDate, set allocToDate to 1 year minus 1 day from Terbit Hak
				if (!allocToDate && terbitHak) {
					const [y, m, d] = terbitHak.split("-");
					const tDate = new Date(parseInt(y), parseInt(m) - 1, parseInt(d));
					tDate.setFullYear(tDate.getFullYear() + 1);
					tDate.setDate(tDate.getDate() - 1);
					allocToDate = tDate.toISOString().slice(0, 10);
				}

				if (!allocFromDate || !allocToDate) {
					results.errors.push({ row: rowNum, message: "Tanggal alokasi tidak bisa ditentukan. Pastikan Terbit Hak tersedia." });
					await transaction.rollback();
					continue;
				}

				// Generate History Reason if any cuti digunakan
				let historicalReason = "";
				if (cutiDigunakan > 0) {
					const months = [
						{ key: 'jan', label: 'Jan' }, { key: 'feb', label: 'Feb' }, { key: 'mar', label: 'Mar' },
						{ key: 'apr', label: 'Apr' }, { key: 'may', label: 'Mei' }, { key: 'jun', label: 'Jun' },
						{ key: 'jul', label: 'Jul' }, { key: 'aug', label: 'Ags' }, { key: 'sep', label: 'Sep' },
						{ key: 'oct', label: 'Okt' }, { key: 'nov', label: 'Nov' }, { key: 'dec', label: 'Des' }
					];

					const monthlyDetails = [];
					for (const m of months) {
						const val = parseFloat2(row[m.key]);
						if (val > 0) {
							monthlyDetails.push(`${m.label}: ${val}`);
						}
					}

					const ket = normalizeStr(row.keterangan);
					const notes = ket ? ` | Keterangan: ${ket}` : "";

					historicalReason = `[Riwayat Excel] ${monthlyDetails.length > 0 ? monthlyDetails.join(", ") : "Total: " + cutiDigunakan}${notes}`;
				}

				const computedSisa = sisaCuti != null ? sisaCuti : hakCuti - cutiDigunakan;
				const allocationName = `${employee.employee_name} - ${leaveTypeName}`;
				const now = new Date();

				// Check existing
				const existingAllocation = await dbSAPPS.LeaveAllocation.findOne({
					where: {
						employee_id: employee.id,
						leavetype_id: leaveTypeId,
						leaveperiod_id: leavePeriodId,
						company_id: companyId,
					},
					transaction,
				});

				let allocationRecordId = null;

				if (!existingAllocation) {
					const newAlloc = await dbSAPPS.LeaveAllocation.create(
						{
							creation: now,
							modified: now,
							modified_by: modifiedBy,
							owner: owner,
							name: allocationName,
							display_name: allocationName,
							description: `Import saldo cuti - Masa kerja: ${masaKerja || "-"} tahun`,
							from_date: allocFromDate,
							to_date: allocToDate,
							new_leaves_allocated: hakCuti,
							total_leaves_allocated: hakCuti,
							carry_forward: false,
							unused_leaves: computedSisa,
							used_leaves: cutiDigunakan,
							expired_leaves: 0,
							expired: false,
							leaves_pending_approval: 0,
							leaveperiod_id: leavePeriodId,
							leavetype_id: leaveTypeId,
							employee_id: employee.id,
							company_id: companyId,
						},
						{ transaction },
					);
					allocationRecordId = newAlloc.id;
					results.created++;
				} else {
					allocationRecordId = existingAllocation.id;
					const updatePayload = {
						modified: now,
						modified_by: modifiedBy,
						new_leaves_allocated: hakCuti,
						total_leaves_allocated: hakCuti,
						used_leaves: cutiDigunakan,
						unused_leaves: computedSisa,
					};
					// Check if there are actual changes
					const hasChanges =
						existingAllocation.new_leaves_allocated !== hakCuti ||
						existingAllocation.used_leaves !== cutiDigunakan ||
						existingAllocation.unused_leaves !== computedSisa ||
						existingAllocation.total_leaves_allocated !== hakCuti;

					if (hasChanges) {
						await existingAllocation.update(updatePayload, { transaction });
						results.updated++;
					} else {
						// Even if skipped, we still might need to generate/update the LeaveApplication later downstream
						results.skipped++;
					}
				}

				// Side effect: Handle Historical Leave Application if cutiDigunakan > 0
				if (cutiDigunakan > 0 && allocationRecordId) {
					const existingApp = await dbSAPPS.LeaveApplication.findOne({
						where: {
							employee_id: employee.id,
							leavetype_id: leaveTypeId,
							leaveallocation_id: allocationRecordId,
							reason: {
								[dbSAPPS.Sequelize.Op.like]: '%[Riwayat Excel]%',
							}
						},
						transaction
					});

					if (!existingApp) {
						const nextId = (await dbSAPPS.LeaveApplication.max("id", { transaction })) || 0;
						const appName = `LA-${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, '0')}-${String(nextId + 1).padStart(5, '0')}`;

						const newApp = await dbSAPPS.LeaveApplication.create({
							creation: now,
							modified: now,
							modified_by: modifiedBy,
							owner: owner,
							name: appName,
							display_name: appName,
							from_date: allocFromDate,
							to_date: allocToDate,
							half_day: false,
							total_leave_days: cutiDigunakan,
							leave_balance: computedSisa + cutiDigunakan, // Total capacity before usage
							estimated_leave_balance: computedSisa,
							reason: historicalReason,
							status: "Approved",
							employee_name: employee.employee_name,
							leavetype_name: leaveTypeName,
							employee_id: employee.id,
							company_id: companyId,
							leavetype_id: leaveTypeId,
							leaveallocation_id: allocationRecordId,
							posting_date: now,
							approver_date: now,
						}, { transaction });

						await dbSAPPS.HistoryLeaveApplication.create({
							creation: now,
							modified: now,
							modified_by: modifiedBy,
							owner: owner,
							leaveapplication_id: newApp.id,
							action: "Approved",
							date: now,
						}, { transaction });
					} else {
						// Update existing app if total days or reason changed
						if (existingApp.total_leave_days !== cutiDigunakan || existingApp.reason !== historicalReason) {
							await existingApp.update({
								modified: now,
								modified_by: modifiedBy,
								total_leave_days: cutiDigunakan,
								leave_balance: computedSisa + cutiDigunakan,
								estimated_leave_balance: computedSisa,
								reason: historicalReason
							}, { transaction });

							await dbSAPPS.HistoryLeaveApplication.create({
								creation: now,
								modified: now,
								modified_by: modifiedBy,
								owner: owner,
								leaveapplication_id: existingApp.id,
								action: "Approved",
								date: now,
							}, { transaction });
						}
					}
				}

				await transaction.commit();
			} catch (err) {
				await transaction.rollback();
				results.errors.push({ row: rowNum, message: err?.message || "Unknown error" });
			}
		}

		return {
			success: true,
			message: `Import selesai: ${results.created} dibuat, ${results.updated} diperbarui, ${results.skipped} dilewati${results.errors.length > 0 ? `, ${results.errors.length} error` : ""}.`,
			created: results.created,
			updated: results.updated,
			skipped: results.skipped,
			errors: results.errors,
		};
	} catch (error) {
		return { success: false, message: error?.message || "Failed to apply import" };
	}
}
