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

// Allowed columns for create/update to avoid client overwriting system fields (id, creation, owner on update)
const EMPLOYEE_COLUMNS = [
	"creation",
	"modified",
	"modified_by",
	"owner",
	"enabled",
	"image",
	"user_id",
	"employee_name",
	"employee_number",
	"job_role",
	"id_number",
	"tax_number",
	"npwp_number",
	"npwp_address",
	"npwp_city_id",
	"npwp_zip_code",
	"family_card_number",
	"personal_email",
	"company_email",
	"mobile_no",
	"place_of_birth",
	"date_of_birth",
	"current_address",
	"id_address",
	"emergency_phone_number",
	"family_background",
	"mother_maiden_name",
	"health_details",
	"date_of_joining",
	"scheduled_confirmation_date",
	"final_confirmation_date",
	"contract_end_date",
	"contract_status",
	"notice_number_of_days",
	"date_of_retirement",
	"resignation_letter_date",
	"relieving_date",
	"exit_clearance_date",
	"held_on",
	"health_insurance_number",
	"employment_insurance_number",
	"idx",
	"ids",
	"import_from",
	"holiday_list",
	"default_shift",
	"leave_approver",
	"expense_approver",
	"shift_request_approver",
	"religion",
	"gender",
	"shift_type",
	"status",
	"use_workflow",
	"eligible_for_wfh",
	"employementtype_id",
	"leaveapprover_id",
	"reports_to",
	"employeegrade_id",
	"jobrole_id",
	"employmentinsuranceprovider_id",
	"healthinsuranceprovider_id",
	"bloodgroup_id",
	"bankaccount_id",
	"bank_id",
	"maritalstatus_id",
	"ptkpstatus_id",
	"religion_id",
	"gender_id",
	"employeestatus_id",
	"education_level_id",
	"placement_area_id",
	"secondary_placement_area_id",
	"zone_id",
	"recruitment_city_id",
	"shifttype_id",
	"company_id",
	"department_id",
	"designation_id",
	"team_id",
];
const ALLOWED_CREATE_FIELDS = EMPLOYEE_COLUMNS; // id is auto-generated
const ALLOWED_UPDATE_FIELDS = EMPLOYEE_COLUMNS.filter((k) => k !== "creation" && k !== "owner"); // client cannot overwrite creation, owner

function pick(obj, keys) {
	if (!obj || typeof obj !== "object") return {};
	const out = {};
	for (const key of keys) {
		if (Object.prototype.hasOwnProperty.call(obj, key)) out[key] = obj[key];
	}
	return out;
}

/**
 * Validate optional master FK references when provided. Returns { success: false, message } on error, null when valid.
 * Does not fail when master data is missing for legacy rows; only validates when ID is explicitly provided.
 */
async function validateEmployeeMasterFks(params, options = {}) {
	const companyId = params.company_id ? Number(params.company_id) : null;

	if (params.education_level_id) {
		const el = await dbSAPPS.EducationLevel.findByPk(params.education_level_id, { attributes: ["id", "company_id"], ...options });
		if (!el) return { success: false, message: "Selected education level not found." };
		if (companyId != null && el.company_id != null && Number(el.company_id) !== companyId) {
			return { success: false, message: "Education level does not belong to selected company." };
		}
	}
	if (params.placement_area_id) {
		const pa = await dbSAPPS.PlacementArea.findByPk(params.placement_area_id, { attributes: ["id", "company_id"], ...options });
		if (!pa) return { success: false, message: "Selected placement area not found." };
		if (companyId != null && pa.company_id != null && Number(pa.company_id) !== companyId) {
			return { success: false, message: "Placement area does not belong to selected company." };
		}
	}
	if (params.secondary_placement_area_id) {
		const spa = await dbSAPPS.PlacementArea.findByPk(params.secondary_placement_area_id, { attributes: ["id", "company_id"], ...options });
		if (!spa) return { success: false, message: "Selected secondary placement area not found." };
		if (companyId != null && spa.company_id != null && Number(spa.company_id) !== companyId) {
			return { success: false, message: "Secondary placement area does not belong to selected company." };
		}
	}
	if (params.zone_id) {
		const z = await dbSAPPS.Zone.findByPk(params.zone_id, { attributes: ["id", "company_id"], ...options });
		if (!z) return { success: false, message: "Selected zone not found." };
		if (companyId != null && z.company_id != null && Number(z.company_id) !== companyId) {
			return { success: false, message: "Zone does not belong to selected company." };
		}
	}
	if (params.recruitment_city_id) {
		const rc = await dbSAPPS.City.findByPk(params.recruitment_city_id, { attributes: ["id"], ...options });
		if (!rc) return { success: false, message: "Selected recruitment city not found." };
	}
	if (params.npwp_city_id) {
		const nc = await dbSAPPS.City.findByPk(params.npwp_city_id, { attributes: ["id"], ...options });
		if (!nc) return { success: false, message: "Selected NPWP city not found." };
	}
	return null;
}

module.exports = {
	getAll,
	option,
	count,
	create,
	update,
	enabled,
	bulkUpdate,
	delete: _delete,
	importFromExcel,
	previewImportFromExcel,
	applyImportFromExcel,
};

function normalizeEmptyToNull(val) {
	if (val == null) return null;
	if (typeof val === "string") {
		const t = normalizeStr(val);
		return t ? t : null;
	}
	return val;
}

function isProbablyHeaderRow(row) {
	if (!row) return true;
	// Check if the values in key identification fields look like header labels
	const name = String(row.employee_name || row.name || "").trim().toLowerCase();
	const id = String(row.employee_number || row.employee_id || row["Emp ID"] || "").trim().toLowerCase();

	const headerLabels = [
		"nama",
		"name",
		"employee name",
		"nama lengkap",
		"employee_name",
		"emp id",
		"employee id",
		"employee_id",
		"employee number",
		"employee_number",
		"no karyawan",
		"nik",
	];

	if (headerLabels.includes(name) || headerLabels.includes(id)) return true;

	// If it's the literal mapping keys (e.g. employee_name: "employee_name")
	if (row.employee_name === "employee_name" || row.employee_number === "employee_number") return true;

	return false;
}

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;
	const iso = parseFlexibleDate(s);
	if (iso) return iso;
	const d = new Date(s);
	return isNaN(d.getTime()) ? null : d.toISOString().slice(0, 10);
}

function isBlankCell(val) {
	if (val == null) return true;
	if (typeof val === "string") return normalizeStr(val) === "";
	return false;
}

function equalNormalized(a, b, type) {
	if (type === "date") {
		return normalizeDateOnly(a) === normalizeDateOnly(b);
	}
	if (type === "string") {
		return normalizeEmptyToNull(a) === normalizeEmptyToNull(b);
	}
	if (type === "number") {
		if (a == null && b == null) return true;
		const na = a === "" ? null : a;
		const nb = b === "" ? null : b;
		return Number(na) === Number(nb);
	}
	// fallback
	return a === b;
}

async function findMasterScopedId(Model, displayName, companyId, transaction) {
	const d = normalizeStr(displayName);
	if (!d) return null;
	// MySQL collations are often case-insensitive; use LIKE for safety
	const existing = await Model.findOne({
		where: {
			company_id: companyId ?? null,
			[Op.or]: [{ display_name: { [Op.like]: d } }, { name: { [Op.like]: d } }],
		},
		attributes: ["id"],
		transaction,
	});
	return existing ? existing.id : null;
}

async function findCityId(displayName, transaction) {
	const d = normalizeStr(displayName);
	if (!d) return null;
	const existing = await dbSAPPS.City.findOne({
		where: { display_name: { [Op.like]: d } },
		attributes: ["id"],
		transaction,
	});
	return existing ? existing.id : null;
}

function buildImportPayloadFromRow(row, companyId) {
	const employeeName = normalizeStr(row.employee_name || row.name || row.nama_lengkap || row["Name"] || "");
	const employeeNumber = normalizeStr(row.employee_number || row["Emp ID"] || "");

	const statusKaryawanRaw = normalizeStr(row.employeestatus_display_name || row.status_karyawan || row["STATUS KARYAWAN"] || "");
	const { statusDisplayName: statusKaryawanNormalized, extractedDate: statusEffectiveDate } = normalizeStatusKaryawan(statusKaryawanRaw);

	const diangkatTetapDate =
		normalizeDateOnly(row["DIANGKAT KARYAWAN TETAP (Tanggal)"] || row.diangkat_karyawan_tetap || row.final_confirmation_date) ||
		(statusKaryawanNormalized === "TETAP" ? normalizeDateOnly(statusEffectiveDate) : null);
	const pkwtBerakhirDate = normalizeDateOnly(row["PKWT BERAKHIR"] || row.pkwt_berakhir || row.contract_end_date);
	const resignDate = normalizeDateOnly(row["Resign Date"] || row.resign_date || row.resignation_letter_date);

	return {
		employee_name: employeeName || null,
		employee_number: employeeNumber || null,
		job_role: normalizeEmptyToNull(row.job_role || row["Job Role"] || null),
		company_id: companyId,
		id_number: normalizeEmptyToNull(row.id_number || row.NIK || row.nik || null),
		npwp_number: normalizeEmptyToNull(row.npwp_number || row["Emp NPWP"] || row.emp_npwp || null),
		date_of_joining: normalizeDateOnly(row.date_of_joining || row["Join Date"]) || null,
		final_confirmation_date: diangkatTetapDate || null,
		contract_end_date: pkwtBerakhirDate || null,
		resignation_letter_date: resignDate || null,
		place_of_birth: normalizeEmptyToNull(row.place_of_birth || row.Birthplace || null),
		date_of_birth: normalizeDateOnly(row.date_of_birth || row.Birthdate) || null,
		personal_email: normalizeEmptyToNull(row.personal_email || row.Email || null),
		company_email: normalizeEmptyToNull(row.company_email || null),
		mobile_no: normalizeEmptyToNull(row.mobile_no || row.Phone || null),
		mother_maiden_name: normalizeEmptyToNull(row.mother_maiden_name || row["Nama Ibu Kandung"] || null),
		employment_insurance_number: normalizeEmptyToNull(row.employment_insurance_number || row["No KPJ"] || null),
		health_insurance_number: normalizeEmptyToNull(row.health_insurance_number || row["No BPJS kesehatan"] || null),
		npwp_address: normalizeEmptyToNull(row.npwp_address || row["Address NPWP"] || null),
		npwp_zip_code: normalizeEmptyToNull(row.npwp_zip_code || row["Zip Code NPWP"] || null),
		id_address: normalizeEmptyToNull(row.id_address || row["ADDRESS KTP"] || null),
		__bank_name: normalizeStr(row.bank_name || row.bank || row["BANK"] || row["Bank"] || row["NAMA BANK"] || ""),
		__bank_account_number: normalizeBankAccountNumber(
			row.account_number || row["ACCOUNT NUMBER"] || row["NO. REKENING"] || row["NO REKENING"] || row.no_rekening || "",
		),
		__bank_account_name: normalizeStr(
			row.bank_account_name || row["ACCOUNT NAME BANK"] || row["NAMA REKENING"] || row["NAMA PEMILIK REKENING"] || row["ACCOUNT NAME"] || "",
		),
		// Derived helpers
		__statusKaryawanNormalized: statusKaryawanNormalized,
		__statusEffectiveDate: statusEffectiveDate,
	};
}

function normalizeBankAccountNumber(val) {
	if (val == null) return null;
	const s = typeof val === "string" ? val : String(val);
	const t = s.trim();
	if (!t) return null;
	// keep digits, preserve leading zeros
	const digits = t.replace(/\D+/g, "");
	return digits ? digits : null;
}

async function findBankIdByNameOrCode(bankName, transaction) {
	const b = normalizeStr(bankName);
	if (!b) return null;
	const existing = await dbSAPPS.Bank.findOne({
		where: {
			[Op.or]: [{ display_name: { [Op.like]: b } }, { name: { [Op.like]: b } }, { code: { [Op.like]: b } }],
		},
		attributes: ["id"],
		transaction,
	});
	return existing ? existing.id : null;
}

async function ensureBank(bankName, opts) {
	const b = normalizeStr(bankName);
	if (!b) return { id: null, wouldCreate: false };
	const existingId = await findBankIdByNameOrCode(b, opts.transaction);
	if (existingId) return { id: existingId, wouldCreate: false };
	if (!opts.allowCreate) return { id: null, wouldCreate: true };

	let code = b
		.toUpperCase()
		.replace(/\s+/g, "_")
		.replace(/[^A-Z0-9_]/g, "");
	code = (code || "BANK").slice(0, 50);
	// If code already exists, attempt to reuse it rather than creating duplicate
	const existingByCode = await dbSAPPS.Bank.findOne({ where: { code }, attributes: ["id"], transaction: opts.transaction });
	if (existingByCode) return { id: existingByCode.id, wouldCreate: false };

	const now = new Date();
	const created = await dbSAPPS.Bank.create(
		{
			creation: now,
			modified: now,
			modified_by: opts.modifiedBy,
			owner: opts.owner,
			enabled: true,
			name: b.slice(0, 50),
			display_name: b.slice(0, 50),
			code,
		},
		{ transaction: opts.transaction },
	);
	return { id: created.id, wouldCreate: true };
}

async function findBankAccountByNumber(companyId, bankId, accountNumber, transaction) {
	const acc = normalizeBankAccountNumber(accountNumber);
	if (!companyId || !acc) return null;
	// Prefer reusing by company + account_number to avoid duplicates
	const base = await dbSAPPS.BankAccount.findOne({
		where: { company_id: companyId, account_number: acc },
		attributes: ["id", "bank_id", "account_number"],
		transaction,
	});
	if (base) return base;
	if (!bankId) return null;
	return dbSAPPS.BankAccount.findOne({
		where: { company_id: companyId, bank_id: bankId, account_number: acc },
		attributes: ["id", "bank_id", "account_number"],
		transaction,
	});
}

async function ensureBankAccount(params, opts) {
	const accountNumber = normalizeBankAccountNumber(params.accountNumber);
	if (!accountNumber) return { id: null, wouldCreate: false };
	if (!params.companyId || !params.bankId) return { id: null, wouldCreate: false };
	const existing = await findBankAccountByNumber(params.companyId, params.bankId, accountNumber, opts.transaction);
	if (existing) return { id: existing.id, bankIdUsed: existing.bank_id, wouldCreate: false };
	if (!opts.allowCreate) return { id: null, wouldCreate: true };

	const now = new Date();
	const displayName = normalizeStr(params.accountName) || `${params.employeeName || "Employee"} - ${accountNumber}`;
	const created = await dbSAPPS.BankAccount.create(
		{
			creation: now,
			modified: now,
			modified_by: opts.modifiedBy,
			owner: opts.owner,
			enabled: true,
			name: displayName,
			display_name: displayName,
			account_number: accountNumber,
			branch: null,
			description: null,
			type: "Employee",
			is_verified: null,
			bank_id: params.bankId,
			company_id: params.companyId,
			employee_id: params.employeeId ?? null,
			client_id: null,
		},
		{ transaction: opts.transaction },
	);
	return { id: created.id, bankIdUsed: params.bankId, wouldCreate: true };
}

async function resolveMastersForRow(row, companyId, transaction, allowCreate, modifiedBy, owner) {
	const opts = { modified_by: modifiedBy, owner, transaction };
	const out = { ids: {}, wouldCreate: [] };
	const getScoped = async (Model, value, label) => {
		const d = normalizeStr(value);
		if (!d) return null;
		const existingId = await findMasterScopedId(Model, d, companyId, transaction);
		if (existingId) return existingId;
		out.wouldCreate.push({ type: label, value: d });
		if (!allowCreate) return null;
		return findOrCreateMasterScoped(Model, d, companyId, opts);
	};
	const getCity = async (value, label) => {
		const d = normalizeStr(value);
		if (!d) return null;
		const existingId = await findCityId(d, transaction);
		if (existingId) return existingId;
		out.wouldCreate.push({ type: label, value: d });
		if (!allowCreate) return null;
		return findOrCreateCity(d, opts);
	};

	out.ids.designation_id = await getScoped(dbSAPPS.Designation, row.designation_display_name || row.job_title || row["Job Title"], "Designation");
	out.ids.jobrole_id = await getScoped(dbSAPPS.JobRole, row.jobrole_display_name || row.job_role || row["Job Role"] || row.job_title || row["Job Title"], "JobRole");
	out.ids.department_id = await getScoped(dbSAPPS.Department, row.department_display_name || row.department_name || row.Departmen || row.Departemen || row["Departemen"] || row.Divisi || row["Divisi"], "Department");
	out.ids.gender_id = await getScoped(dbSAPPS.Gender, row.gender_display_name || row.gender || row.Gender, "Gender");
	out.ids.religion_id = await getScoped(dbSAPPS.Religion, row.religion_display_name || row.religion || row.Religion, "Religion");
	out.ids.maritalstatus_id = await getScoped(
		dbSAPPS.MaritalStatus,
		row.maritalstatus_display_name || row.status_pernikahan || row["STATUS PERNIKAHAN"],
		"MaritalStatus",
	);
	out.ids.bloodgroup_id = await getScoped(dbSAPPS.BloodGroup, row.bloodgroup_display_name || row.golongan_darah || row["Golongan Darah"], "BloodGroup");
	out.ids.education_level_id = await getScoped(dbSAPPS.EducationLevel, row.education_level_display_name || row.pendidikan || row.PENDIDIKAN, "EducationLevel");
	out.ids.placement_area_id = await getScoped(
		dbSAPPS.PlacementArea,
		row.placement_area_display_name || row.area_penampatan || row["Area Penampatan"] || row.area_penempatan || row["Area Penempatan"],
		"PlacementArea",
	);
	out.ids.secondary_placement_area_id = await getScoped(
		dbSAPPS.PlacementArea,
		row.secondary_placement_area_display_name || row.area_sekunder || row["Area Sekunder"],
		"SecondaryPlacementArea",
	);
	out.ids.zone_id = await getScoped(dbSAPPS.Zone, row.zone_display_name || row.data_zona || row["Data Zona"], "Zone");
	out.ids.recruitment_city_id = await getCity(row.recruitment_city_display_name || row.recruit || row["Recruit"], "City");
	out.ids.npwp_city_id = await getCity(row.npwp_city_display_name || row["City NPWP"], "City");
	out.ids.shifttype_id = await getScoped(dbSAPPS.ShiftType, row.shifttype_display_name || row.shift_type, "ShiftType");
	out.ids.ptkpstatus_id = await getScoped(dbSAPPS.PtkpStatus, row.ptkpstatus_display_name || row.status_pajak || row["Status Pajak"], "PtkpStatus");
	out.ids.team_id = await getScoped(dbSAPPS.Team, row.team_id || row.team_display_name || row.team || row.Team || row.Tim || row["Tim"] || row["Team"], "Team");

	// Employee status normalization
	const statusKaryawanRaw = normalizeStr(row.employeestatus_display_name || row.status_karyawan || row["STATUS KARYAWAN"] || "");
	const { statusDisplayName: statusKaryawanNormalized } = normalizeStatusKaryawan(statusKaryawanRaw);
	out.ids.employeestatus_id = statusKaryawanNormalized ? await getScoped(dbSAPPS.EmployeeStatus, statusKaryawanNormalized, "EmployeeStatus") : null;
	if (statusKaryawanNormalized === "PKWT") out.ids.contract_status = "PKWT";
	if (statusKaryawanNormalized === "TETAP") out.ids.contract_status = "PKWTT";

	return out;
}

async function previewImportFromExcel(params) {
	try {
		const rows = Array.isArray(params.rows) ? params.rows : [];
		const defaultCompanyId = params.defaultCompanyId ? Number(params.defaultCompanyId) : null;
		const modifiedBy = params.modified_by ? Number(params.modified_by) : 1;
		const owner = params.owner ? Number(params.owner) : modifiedBy;
		const allowCreateMasters = params.create_missing_masters !== false; // default true for smoother import
		const keyMode = params.key_mode === "name" ? "name" : "employee_number";

		const getJobRoleDisplayNameById = async (id, transaction) => {
			if (id == null || id === "") return null;
			const jobRole = await dbSAPPS.JobRole.findByPk(id, {
				attributes: ["id", "display_name", "name"],
				transaction,
			});
			return jobRole?.display_name || jobRole?.name || null;
		};

		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 companyId = await resolveCompanyId(row, defaultCompanyId, transaction);
				if (!companyId) {
					items.push({ row: rowNum, action: "error", message: "Company not found and no default company." });
					errors++;
					await transaction.rollback();
					continue;
				}

				if (isProbablyHeaderRow(row)) {
					skips++;
					items.push({ row: rowNum, action: "skip", message: "Header row skipped" });
					await transaction.rollback();
					continue;
				}

				const base = buildImportPayloadFromRow(row, companyId);
				if (!base.employee_name && !base.employee_number) {
					items.push({ row: rowNum, action: "error", message: "Emp ID or Name is required." });
					errors++;
					await transaction.rollback();
					continue;
				}

				const resolved = await resolveMastersForRow(row, companyId, transaction, false, modifiedBy, owner);
				const desired = {
					...base,
					...resolved.ids,
					enabled: true,
				};
				delete desired.__statusKaryawanNormalized;
				delete desired.__statusEffectiveDate;
				const bankName = desired.__bank_name;
				const bankAccountNumber = desired.__bank_account_number;
				const bankAccountName = desired.__bank_account_name;
				delete desired.__bank_name;
				delete desired.__bank_account_number;
				delete desired.__bank_account_name;

				const lookupWhere = { company_id: companyId };
				if (base.employee_number) {
					lookupWhere[Op.or] = [
						{ employee_number: base.employee_number },
						{ employee_name: base.employee_name },
					];
				} else {
					lookupWhere.employee_name = base.employee_name;
				}
				const existing = await dbSAPPS.Employee.findOne({ where: lookupWhere, transaction });

				if (!existing) {
					creates++;
					let bankPreview = null;
					if (bankAccountNumber) {
						const bankIdExisting = await findBankIdByNameOrCode(bankName, transaction);
						const bankWouldCreate = !!bankName && !bankIdExisting;
						const bankAccountExisting = await findBankAccountByNumber(companyId, bankIdExisting, bankAccountNumber, transaction);
						const bankAccountWouldCreate = !bankAccountExisting;
						bankPreview = {
							bank_name: bankName || null,
							account_number: bankAccountNumber,
							account_name: bankAccountName || null,
							wouldCreateBank: bankWouldCreate,
							wouldCreateBankAccount: bankAccountWouldCreate,
						};
					}
					items.push({
						row: rowNum,
						action: "create",
						key: lookupWhere,
						employee: { employee_name: base.employee_name, employee_number: base.employee_number, company_id: companyId },
						wouldCreateMasters: resolved.wouldCreate,
						bank: bankPreview,
					});
					await transaction.rollback();
					continue;
				}

				const changes = [];
				const updatePayload = {};
				const fields = [
					"employee_name",
					"job_role",
					"jobrole_id",
					"designation_id",
					"department_id",
					"employeestatus_id",
					"gender_id",
					"religion_id",
					"maritalstatus_id",
					"bloodgroup_id",
					"education_level_id",
					"placement_area_id",
					"secondary_placement_area_id",
					"zone_id",
					"recruitment_city_id",
					"npwp_city_id",
					"shifttype_id",
					"ptkpstatus_id",
					"date_of_joining",
					"final_confirmation_date",
					"contract_end_date",
					"contract_status",
					"resignation_letter_date",
					"place_of_birth",
					"date_of_birth",
					"personal_email",
					"company_email",
					"mobile_no",
					"mother_maiden_name",
					"employment_insurance_number",
					"health_insurance_number",
					"npwp_address",
					"npwp_zip_code",
					"id_address",
					"id_number",
					"npwp_number",
					"bank_id",
					"bankaccount_id",
					"team_id",
				];

				// bank preview for existing employee
				let bankPreview = null;
				if (bankAccountNumber) {
					const bankIdExisting = await findBankIdByNameOrCode(bankName, transaction);
					const bankWouldCreate = !!bankName && !bankIdExisting;
					const bankAccountExisting = await findBankAccountByNumber(companyId, bankIdExisting, bankAccountNumber, transaction);
					const bankAccountWouldCreate = !bankAccountExisting;
					bankPreview = {
						bank_name: bankName || null,
						account_number: bankAccountNumber,
						account_name: bankAccountName || null,
						wouldCreateBank: bankWouldCreate,
						wouldCreateBankAccount: bankAccountWouldCreate,
					};

					if (bankAccountExisting) {
						if (!equalNormalized(bankAccountExisting.bank_id, existing.get("bank_id"), "number")) {
							updatePayload.bank_id = bankAccountExisting.bank_id;
							changes.push({ field: "bank_id", from: existing.get("bank_id"), to: bankAccountExisting.bank_id });
						}
						if (!equalNormalized(bankAccountExisting.id, existing.get("bankaccount_id"), "number")) {
							updatePayload.bankaccount_id = bankAccountExisting.id;
							changes.push({ field: "bankaccount_id", from: existing.get("bankaccount_id"), to: bankAccountExisting.id });
						}
					} else if (bankName) {
						// Will create + link during apply
						changes.push({ field: "bank_id", from: existing.get("bank_id"), to: "(create)" });
						changes.push({ field: "bankaccount_id", from: existing.get("bankaccount_id"), to: "(create)" });
					}
				}

				for (const f of fields) {
					const incoming = desired[f];
					if (isBlankCell(incoming)) continue; // do not overwrite with blank
					const existingVal = existing.get(f);
					const type = f.includes("date") ? "date" : typeof incoming === "string" ? "string" : "fallback";
					if (!equalNormalized(incoming, existingVal, type)) {
						updatePayload[f] = incoming;
						changes.push({ field: f, from: existingVal, to: incoming });
					}
				}

				if (changes.length === 0) {
					skips++;
					items.push({
						row: rowNum,
						action: "skip",
						key: lookupWhere,
						existing: { id: existing.id, employee_name: existing.employee_name, employee_number: existing.employee_number, company_id: existing.company_id },
						bank: bankPreview,
					});
					await transaction.rollback();
					continue;
				}

				// Enrich specific FK diffs with human-readable names for a better preview UX
				for (const ch of changes) {
					if (ch?.field === "jobrole_id") {
						try {
							ch.from_display_name = await getJobRoleDisplayNameById(ch.from, transaction);
							ch.to_display_name = await getJobRoleDisplayNameById(ch.to, transaction);
						} catch (e) {
							ch.from_display_name = null;
							ch.to_display_name = null;
						}
					}
				}

				updates++;
				items.push({
					row: rowNum,
					action: "update",
					key: lookupWhere,
					existing: { id: existing.id, employee_name: existing.employee_name, employee_number: existing.employee_number, company_id: existing.company_id },
					changes,
					wouldCreateMasters: resolved.wouldCreate,
					bank: bankPreview,
				});
				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,
			options: { key_mode: keyMode, create_missing_masters: allowCreateMasters },
		};
	} catch (error) {
		return { success: false, message: error?.message || "Failed to preview import" };
	}
}

async function applyImportFromExcel(params) {
	try {
		const rows = Array.isArray(params.rows) ? params.rows : [];
		const defaultCompanyId = params.defaultCompanyId ? Number(params.defaultCompanyId) : null;
		const modifiedBy = params.modified_by ? Number(params.modified_by) : 1;
		const owner = params.owner ? Number(params.owner) : modifiedBy;
		const allowCreateMasters = params.create_missing_masters !== false; // default true
		const keyMode = params.key_mode === "name" ? "name" : "employee_number";
		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 companyId = await resolveCompanyId(row, defaultCompanyId, transaction);
				if (!companyId) {
					results.errors.push({ row: rowNum, message: "Company not found and no default company." });
					await transaction.rollback();
					continue;
				}

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

				const base = buildImportPayloadFromRow(row, companyId);
				const bankName = base.__bank_name;
				const bankAccountNumber = base.__bank_account_number;
				const bankAccountName = base.__bank_account_name;
				delete base.__bank_name;
				delete base.__bank_account_number;
				delete base.__bank_account_name;
				if (!base.employee_name && !base.employee_number) {
					results.errors.push({ row: rowNum, message: "Emp ID or Name is required." });
					await transaction.rollback();
					continue;
				}

				const resolved = await resolveMastersForRow(row, companyId, transaction, allowCreateMasters, modifiedBy, owner);
				const desired = {
					...base,
					...resolved.ids,
					enabled: true,
					modified: new Date(),
					modified_by: modifiedBy,
				};
				delete desired.__statusKaryawanNormalized;
				delete desired.__statusEffectiveDate;

				const lookupWhere = { company_id: companyId };
				if (base.employee_number) {
					lookupWhere[Op.or] = [
						{ employee_number: base.employee_number },
						{ employee_name: base.employee_name },
					];
				} else {
					lookupWhere.employee_name = base.employee_name;
				}
				const existing = await dbSAPPS.Employee.findOne({ where: lookupWhere, transaction });

				if (!existing) {
					const now = new Date();
					const createPayload = pick(
						{
							...desired,
							creation: now,
							owner,
						},
						ALLOWED_CREATE_FIELDS,
					);
					const fkError = await validateEmployeeMasterFks(createPayload, { transaction });
					if (fkError) {
						results.errors.push({ row: rowNum, message: fkError.message });
						await transaction.rollback();
						continue;
					}
					const createdEmployee = await dbSAPPS.Employee.create(createPayload, { transaction });
					// Link bank account if provided
					if (bankAccountNumber && bankName) {
						const bank = await ensureBank(bankName, {
							transaction,
							allowCreate: allowCreateMasters,
							modifiedBy,
							owner,
						});
						if (bank.id) {
							const ba = await ensureBankAccount(
								{
									companyId,
									bankId: bank.id,
									accountNumber: bankAccountNumber,
									accountName: bankAccountName,
									employeeId: createdEmployee.id,
									employeeName: createdEmployee.employee_name,
								},
								{ transaction, allowCreate: allowCreateMasters, modifiedBy, owner },
							);
							const bankPatch = {};
							const bankIdToUse = ba.bankIdUsed || bank.id;
							if (bankIdToUse && createdEmployee.bank_id !== bankIdToUse) bankPatch.bank_id = bankIdToUse;
							if (ba.id && createdEmployee.bankaccount_id !== ba.id) bankPatch.bankaccount_id = ba.id;
							if (Object.keys(bankPatch).length > 0) await createdEmployee.update(bankPatch, { transaction });
						}
					}
					results.created++;
					await transaction.commit();
					continue;
				}

				const updatePayload = {};
				for (const [k, v] of Object.entries(desired)) {
					if (!ALLOWED_UPDATE_FIELDS.includes(k)) continue;
					if (isBlankCell(v)) continue;
					const existingVal = existing.get(k);
					const type = k.includes("date") ? "date" : typeof v === "string" ? "string" : "fallback";
					if (!equalNormalized(v, existingVal, type)) {
						updatePayload[k] = v;
					}
				}

				if (Object.keys(updatePayload).length === 0) {
					results.skipped++;
					await transaction.rollback();
					continue;
				}
				await existing.update(updatePayload, { transaction });
				// Bank account linkage (safe)
				if (bankAccountNumber && bankName) {
					const bank = await ensureBank(bankName, {
						transaction,
						allowCreate: allowCreateMasters,
						modifiedBy,
						owner,
					});
					if (bank.id) {
						const ba = await ensureBankAccount(
							{
								companyId,
								bankId: bank.id,
								accountNumber: bankAccountNumber,
								accountName: bankAccountName,
								employeeId: existing.id,
								employeeName: existing.employee_name,
							},
							{ transaction, allowCreate: allowCreateMasters, modifiedBy, owner },
						);
						const bankPatch = {};
						const bankIdToUse = ba.bankIdUsed || bank.id;
						if (bankIdToUse && existing.bank_id !== bankIdToUse) bankPatch.bank_id = bankIdToUse;
						if (ba.id && existing.bankaccount_id !== ba.id) bankPatch.bankaccount_id = ba.id;
						if (Object.keys(bankPatch).length > 0) await existing.update(bankPatch, { transaction });
					}
				}
				results.updated++;
				await transaction.commit();
			} catch (err) {
				await transaction.rollback();
				results.errors.push({ row: rowNum, message: err?.message || "Unknown error" });
			}
		}

		return {
			success: true,
			message: `Import applied: ${results.created} created, ${results.updated} updated, ${results.skipped} skipped.`,
			created: results.created,
			updated: results.updated,
			skipped: results.skipped,
			errors: results.errors,
		};
	} catch (error) {
		return { success: false, message: error?.message || "Failed to apply import" };
	}
}

const formatWithModifier = async (employees) => {
	return Promise.all(
		employees.map(async (employee) => {
			const modifier = await dbSAPPS.User.findOne({
				where: {
					id: employee.modified_by,
				},
				attributes: ["full_name"],
			});

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

async function getAll(params) {
	try {
		// filter
		const filterConditions = {};
		const companyIdParamRaw = params?.company_id;
		const companyIdParam =
			companyIdParamRaw !== undefined && companyIdParamRaw !== null && String(companyIdParamRaw).trim() !== "" ? Number(companyIdParamRaw) : null;
		const companiesIdParam = Array.isArray(params?.companiesId) ? params.companiesId : null;
		const effectiveCompaniesId =
			companyIdParam != null && !Number.isNaN(companyIdParam) ? [companyIdParam] : companiesIdParam && companiesIdParam.length > 0 ? companiesIdParam : null;

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

		// Company precedence: company_id (single) wins over companiesId (scope)
		if (companyIdParam != null && !Number.isNaN(companyIdParam)) {
			filterConditions.company_id = {
				[Op.eq]: companyIdParam,
			};
		} else if (companiesIdParam && companiesIdParam.length > 0) {
			filterConditions.company_id = {
				[Op.in]: companiesIdParam,
			};
		}

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

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

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

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

		let order = [["modified", "DESC"]];
		if (params?.pkwt_control) {
			// Prefer explicit contract_status = 'PKWT'; fallback to employeestatus name containing PKWT (legacy)
			const pkwtBase = {
				[Op.or]: [{ display_name: { [Op.like]: "%PKWT%" } }, { name: { [Op.like]: "%pkwt%" } }],
				enabled: true,
			};
			const pkwtWhere =
				effectiveCompaniesId && Array.isArray(effectiveCompaniesId) && effectiveCompaniesId.length > 0
					? {
						[Op.and]: [pkwtBase, { [Op.or]: [{ company_id: { [Op.in]: effectiveCompaniesId } }, { company_id: null }] }],
					}
					: pkwtBase;
			const pkwtStatuses = await dbSAPPS.EmployeeStatus.findAll({
				where: pkwtWhere,
				attributes: ["id"],
			});
			const pkwtIds = (pkwtStatuses || []).map((s) => s.id).filter((id) => id != null);
			filterConditions.contract_end_date = { [Op.ne]: null };
			// Include rows with contract_status = 'PKWT' OR (legacy) employeestatus_id in PKWT list
			if (pkwtIds.length > 0) {
				filterConditions[Op.and] = [
					...(filterConditions[Op.and] || []),
					{
						[Op.or]: [{ contract_status: "PKWT" }, { employeestatus_id: { [Op.in]: pkwtIds } }],
					},
				];
			} else {
				filterConditions[Op.and] = [...(filterConditions[Op.and] || []), { [Op.or]: [{ contract_status: "PKWT" }, { id: { [Op.eq]: -1 } }] }];
			}
			if (params?.pkwt_expiring_within_days != null && Number(params.pkwt_expiring_within_days) >= 0) {
				const n = Number(params.pkwt_expiring_within_days);
				const today = new Date();
				const pad = (num) => String(num).padStart(2, "0");
				const todayStr = `${today.getFullYear()}-${pad(today.getMonth() + 1)}-${pad(today.getDate())}`;
				const end = new Date(today.getFullYear(), today.getMonth(), today.getDate() + n);
				const endStr = `${end.getFullYear()}-${pad(end.getMonth() + 1)}-${pad(end.getDate())}`;
				filterConditions.contract_end_date = {
					[Op.and]: [{ [Op.ne]: null }, { [Op.gte]: todayStr }, { [Op.lte]: endStr }],
				};
			}
			order = [["contract_end_date", "ASC"]];
		}

		let employees = [];
		employees = await dbSAPPS.Employee.findAll({
			where: filterConditions,
			order,
			include: [
				{
					model: dbSAPPS.User,
					as: "user",
					attributes: ["id", "email", "full_name"],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.JobRole,
					as: "jobrole",
					attributes: ["id", "display_name", "name"],
					required: false,
				},
				{
					model: dbSAPPS.Department,
					as: "department",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Designation,
					as: "designation",
					attributes: ["id", "display_name"],
				},
				{
					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",
					],
				},
				{
					model: dbSAPPS.EducationLevel,
					as: "education_level",
					attributes: ["id", "display_name"],
					required: false,
				},
				{
					model: dbSAPPS.PlacementArea,
					as: "placement_area",
					attributes: ["id", "display_name"],
					required: false,
				},
				{
					model: dbSAPPS.PlacementArea,
					as: "secondary_placement_area",
					attributes: ["id", "display_name"],
					required: false,
				},
				{
					model: dbSAPPS.Zone,
					as: "zone",
					attributes: ["id", "display_name"],
					required: false,
				},
				{
					model: dbSAPPS.City,
					as: "recruitment_city",
					attributes: ["id", "display_name"],
					required: false,
				},
				{
					model: dbSAPPS.City,
					as: "npwp_city",
					attributes: ["id", "display_name"],
					required: false,
				},
				{
					model: dbSAPPS.User,
					as: "modifier",
					attributes: ["full_name"],
				},
				{
					model: dbSAPPS.EmployeeStatus,
					as: "employeestatus",
					attributes: ["id", "display_name", "name"],
					required: false,
				},
			],
		});

		// update relation
		employees = employees.map((employee) => {
			return {
				...employee.dataValues,
				user_name: employee?.user?.full_name,
				shifttype_name: employee?.shifttype?.display_name,
				company_name: employee?.company?.display_name,
				jobrole_name: employee?.jobrole?.name,
				jobrole_display_name: employee?.jobrole?.display_name || employee?.jobrole?.name,
				department_name: employee?.department?.display_name,
				designation_name: employee?.designation?.display_name,
				modifier: employee?.modifier?.full_name,
				email: employee?.user?.email,
				education_level_name: employee?.education_level?.display_name,
				placement_area_name: employee?.placement_area?.display_name,
				secondary_placement_area_name: employee?.secondary_placement_area?.display_name,
				zone_name: employee?.zone?.display_name,
				recruitment_city_name: employee?.recruitment_city?.display_name,
				npwp_city_name: employee?.npwp_city?.display_name,
				employeestatus_name: employee?.employeestatus?.display_name || employee?.employeestatus?.name,
			};
		});

		// modifier
		// employees = await formatWithModifier(employees);

		return {
			employees,
			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?.usersId) {
			filterConditions.user_id = {
				[Op.in]: params?.usersId,
			};
		}

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

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

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

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

		// must be enabled
		filterConditions.enabled = {
			[Op.eq]: true,
		};

		let options = [];
		options = await dbSAPPS.Employee.findAll({
			attributes: ["id", "employee_name", "employee_number", "user_id", "company_id", "department_id", "designation_id", "shifttype_id", "use_workflow"],
			where: filterConditions,
			order: [["employee_name", "ASC"]],
			include: [
				{
					model: dbSAPPS.User,
					as: "user",
					attributes: ["id", "email", "full_name"],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "name", "display_name"],
				},
				{
					model: dbSAPPS.Department,
					as: "department",
					attributes: ["id", "name", "display_name"],
				},
				{
					model: dbSAPPS.Designation,
					as: "designation",
					attributes: ["id", "name", "display_name"],
				},
				{
					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",
					],
				},
			],
		});

		const territories = await dbSAPPS.EmployeeTerritory.findAll({
			where: {
				employee_id: {
					[Op.in]: options.map((option) => option.id),
				},
			},
			include: [
				{
					model: dbSAPPS.Territory,
					as: "territory",
					attributes: ["id", "name", "display_name"],
				},
			],
			attributes: ["employee_id", "territory_id"],
		});

		// update relation
		options = options.map((option) => {
			return {
				...option.dataValues,
				company_name: option?.company?.display_name,
				user_id: option?.user?.id,
				user_full_name: option?.user?.full_name,
				shifttype_name: option?.shifttype?.display_name,
				department_name: option?.department?.display_name,
				designation_name: option?.designation?.display_name,
				email: option?.user?.email,
				shifttype_id: option?.shifttype?.id,
				department_id: option?.department?.id,
				designation_id: option?.designation?.id,
				territory_name: territories
					.filter((territory) => territory.employee_id === option.id)
					.map((territory) => territory.territory.display_name)
					.join(", "),
			};
		});

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

/**
 * Count all enabled employees
 *
 * @returns {Promise<{
 *   success: boolean,
 *   count: number,
 *   message: string
 * }>}
 */
async function count(params) {
	try {
		// filter
		const filterConditions = {};

		// must be enabled
		filterConditions.enabled = {
			[Op.eq]: true,
		};

		const count = await dbSAPPS.Employee.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 existingName = await dbSAPPS.Employee.findOne({
			where: {
				employee_name: params.employee_name,
				company_id: params.company_id,
			},
			transaction,
		});
		if (existingName) {
			return {
				success: false,
				message: `"${params.employee_name}" is already taken.`,
			};
		}

		if (params.employee_number && params.employee_number !== "") {
			const existingNumber = await dbSAPPS.Employee.findOne({
				where: {
					employee_number: params.employee_number,
					company_id: params.company_id,
				},
				transaction,
			});
			if (existingNumber) {
				return {
					success: false,
					message: `"${params.employee_number}" is already taken.`,
				};
			}
		}

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

		if (params.user_id) {
			const existingUserCompany = await dbSAPPS.UserCompany.findOne({
				where: {
					user_id: params.user_id,
					company_id: params.company_id,
				},
				transaction,
			});
			if (!existingUserCompany) {
				await dbSAPPS.UserCompany.create(
					{
						user_id: params.user_id,
						company_id: params.company_id,
					},
					{ transaction },
				);
			}
		}

		// Validate optional FKs when provided (do not break when master data missing)
		const fkError = await validateEmployeeMasterFks(params, { transaction });
		if (fkError) {
			return fkError;
		}

		// save: only allowed fields to avoid client overwriting system data
		const createData = pick(params, ALLOWED_CREATE_FIELDS);
		const created = await dbSAPPS.Employee.create(createData, { transaction });

		await transaction.commit();
		return {
			success: true,
			message: "Saved successfully!",
			employee_id: created.id,
		};
	} 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 employee = await dbSAPPS.Employee.findByPk(id);
		if (!employee) return { success: false, message: "ID not found!" };

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

		if (params.employee_number && params.employee_number !== "") {
			const existingNumber = await dbSAPPS.Employee.findOne({
				where: {
					employee_number: params.employee_number,
					company_id: params.company_id,
					id: {
						[Op.ne]: id,
					},
				},
				transaction,
			});
			if (existingNumber) {
				return {
					success: false,
					message: `"${params.employee_number}" is already taken.`,
				};
			}
		}

		if (params.user_id) {
			const existingUserCompany = await dbSAPPS.UserCompany.findOne({
				where: {
					user_id: params.user_id,
					company_id: params.company_id,
				},
				transaction,
			});
			if (!existingUserCompany) {
				await dbSAPPS.UserCompany.create(
					{
						user_id: params.user_id,
						company_id: params.company_id,
					},
					{ transaction },
				);
			}
		}

		// Validate optional FKs when provided
		const fkError = await validateEmployeeMasterFks(params, { transaction });
		if (fkError) {
			return fkError;
		}

		// save: only allowed fields so client cannot overwrite id, creation, owner
		const updateData = pick(params, ALLOWED_UPDATE_FIELDS);
		await employee.update(updateData, { 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 enabled(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const employee = await dbSAPPS.Employee.findByPk(id);
		if (!employee) return { success: false, message: "ID not found!" };

		// update params
		const updateData = {};
		updateData.enabled = params.enabled;
		if (params.modified_by) updateData.modified_by = params.modified_by;
		if (params.modified) updateData.modified = params.modified;

		// save
		await employee.update(updateData, { 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,
		};
	}
}

/**
 * Bulk update employees - update company and/or shiftType for multiple employees
 * @param {Object} params - Bulk update parameters
 * @param {number[]} params.employee_ids - Array of employee IDs to update
 * @param {number} [params.company_id] - New company ID (optional)
 * @param {number} [params.shifttype_id] - New shiftType ID (optional)
 * @param {number} params.modified_by - User ID who made the change
 * @param {string} params.modified - Modification timestamp
 * @param {number} params.user_id - User ID for permission check
 * @returns {Promise<{success: boolean, message: string, updated: number, failed: number}>}
 */
async function bulkUpdate(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// Validasi permission: hanya user dengan access_read_all_data atau administrator_of_hr yang bisa bulk update
		if (params.user_id) {
			const user = await dbSAPPS.User.findByPk(params.user_id, {
				include: [
					{
						model: dbSAPPS.Role,
						as: "role",
						attributes: ["id", "access_read_all_data", "administrator_of_hr"],
					},
				],
				attributes: ["id", "role_id"],
				transaction,
			});

			if (!user || !user.role) {
				await transaction.rollback();
				return {
					success: false,
					message: "User role not found!",
				};
			}

			const hasAccess =
				user.role.access_read_all_data === true ||
				user.role.access_read_all_data === "true" ||
				user.role.administrator_of_hr === true ||
				user.role.administrator_of_hr === "true";

			if (!hasAccess) {
				await transaction.rollback();
				return {
					success: false,
					message: "You don't have permission to perform bulk update. Only users with 'access_read_all_data' or 'administrator_of_hr' can perform this action.",
				};
			}
		}

		// Validasi wajib
		if (!params.employee_ids || !Array.isArray(params.employee_ids) || params.employee_ids.length === 0) {
			await transaction.rollback();
			return {
				success: false,
				message: "Employee IDs are required and must be an array!",
			};
		}

		// Validasi minimal satu field yang diubah
		const hasCompanyChange = params.company_id !== null && params.company_id !== undefined;
		const hasShiftTypeChange = params.shifttype_id !== null && params.shifttype_id !== undefined;
		const hasEnabledChange = params.enabled !== null && params.enabled !== undefined;

		if (!hasCompanyChange && !hasShiftTypeChange && !hasEnabledChange) {
			await transaction.rollback();
			return {
				success: false,
				message: "At least one field (company_id, shifttype_id, or enabled) must be provided!",
			};
		}

		// Validasi: jika shiftType dipilih, company wajib dipilih
		if (hasShiftTypeChange && !hasCompanyChange) {
			await transaction.rollback();
			return {
				success: false,
				message: "Company must be selected when updating ShiftType!",
			};
		}

		// Validasi shiftType jika company juga diubah
		if (hasCompanyChange && hasShiftTypeChange) {
			const shiftType = await dbSAPPS.ShiftType.findByPk(params.shifttype_id, {
				attributes: ["id", "company_id", "enabled"],
				transaction,
			});

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

			// Validasi shiftType harus sesuai dengan company
			if (shiftType.company_id && Number(shiftType.company_id) !== Number(params.company_id)) {
				await transaction.rollback();
				return {
					success: false,
					message: "ShiftType must belong to the selected company!",
				};
			}

			// Validasi shiftType harus enabled
			if (!shiftType.enabled) {
				await transaction.rollback();
				return {
					success: false,
					message: "Selected ShiftType is disabled!",
				};
			}
		}

		// Validasi shiftType jika hanya shiftType yang diubah (tidak boleh karena company wajib)
		// Ini sudah di-handle di validasi di atas, tapi tetap kita validasi shiftType enabled
		if (hasShiftTypeChange) {
			const shiftType = await dbSAPPS.ShiftType.findByPk(params.shifttype_id, {
				attributes: ["id", "company_id", "enabled"],
				transaction,
			});

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

			if (!shiftType.enabled) {
				await transaction.rollback();
				return {
					success: false,
					message: "Selected ShiftType is disabled!",
				};
			}
		}

		// Validasi company jika hanya company yang diubah
		if (params.company_id && !params.shifttype_id) {
			const company = await dbSAPPS.Company.findByPk(params.company_id, {
				attributes: ["id"],
				transaction,
			});

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

		// Cari semua employees yang akan diupdate
		const employees = await dbSAPPS.Employee.findAll({
			where: {
				id: {
					[Op.in]: params.employee_ids,
				},
			},
			transaction,
		});

		if (employees.length === 0) {
			await transaction.rollback();
			return {
				success: false,
				message: "No employees found with the provided IDs!",
			};
		}

		// Siapkan data update
		const updateData = {};
		if (hasCompanyChange) {
			updateData.company_id = params.company_id;
		}
		if (hasShiftTypeChange) {
			updateData.shifttype_id = params.shifttype_id;
		}
		if (hasEnabledChange) {
			updateData.enabled = params.enabled === true || params.enabled === "true" || params.enabled === 1;
		}
		updateData.modified = params.modified;
		updateData.modified_by = params.modified_by;

		// Update employees
		let updatedCount = 0;
		let failedCount = 0;
		const errors = [];

		for (const employee of employees) {
			try {
				// Jika company diubah, validasi user_id dan buat UserCompany jika perlu
				if (params.company_id && employee.user_id) {
					const existingUserCompany = await dbSAPPS.UserCompany.findOne({
						where: {
							user_id: employee.user_id,
							company_id: params.company_id,
						},
						transaction,
					});

					if (!existingUserCompany) {
						await dbSAPPS.UserCompany.create(
							{
								user_id: employee.user_id,
								company_id: params.company_id,
							},
							{ transaction },
						);
					}
				}

				// Jika shiftType diubah, validasi shiftType sesuai dengan company
				if (params.shifttype_id) {
					const newCompanyId = params.company_id || employee.company_id;
					const shiftType = await dbSAPPS.ShiftType.findByPk(params.shifttype_id, {
						attributes: ["id", "company_id", "enabled"],
						transaction,
					});

					if (shiftType) {
						// Validasi shiftType harus sesuai dengan company
						if (shiftType.company_id && Number(shiftType.company_id) !== Number(newCompanyId)) {
							failedCount++;
							errors.push(`Employee ${employee.employee_name || employee.id}: ShiftType does not match company`);
							continue;
						}

						if (!shiftType.enabled) {
							failedCount++;
							errors.push(`Employee ${employee.employee_name || employee.id}: ShiftType is disabled`);
							continue;
						}
					}
				}

				// Update employee
				await employee.update(updateData, { transaction });
				updatedCount++;
			} catch (error) {
				failedCount++;
				errors.push(`Employee ${employee.employee_name || employee.id}: ${error.message}`);
			}
		}

		if (updatedCount === 0) {
			await transaction.rollback();
			return {
				success: false,
				message: `Failed to update all employees. Errors: ${errors.join("; ")}`,
				updated: 0,
				failed: failedCount,
			};
		}

		await transaction.commit();

		const message =
			updatedCount === employees.length
				? `Successfully updated ${updatedCount} employee(s)!`
				: `Partially updated: ${updatedCount} succeeded, ${failedCount} failed. Errors: ${errors.join("; ")}`;

		return {
			success: true,
			message,
			updated: updatedCount,
			failed: failedCount,
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to bulk update employees, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

		// validate used in attendance
		const usedInAttendance = await dbSAPPS.Attendance.count({
			where: {
				employee_id: id,
			},
			transaction,
		});
		if (usedInAttendance > 0) {
			throw new Error(`This employee is being used in Attendance, cannot be deleted!`);
		}

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

// --- Excel Import: find-or-create masters, then create/update employee by employee_name + company_id ---
function normalizeStr(val) {
	if (val == null || typeof val !== "string") return "";
	return val.trim().replace(/\s+/g, " ");
}

function slugForName(s) {
	if (!s || typeof s !== "string") return "";
	return s
		.trim()
		.toLowerCase()
		.replace(/\s+/g, "-")
		.replace(/[^a-z0-9-]/g, "");
}

/**
 * Normalize STATUS KARYAWAN from Excel: "TETAP (1 Sep'17)", "Tetap (Efektif 01 Nov 2023)", "PKWT" → base status for master lookup and optional date.
 * Returns { statusDisplayName: "TETAP"|"PKWT"|null, extractedDate: Date|null }.
 */
function normalizeStatusKaryawan(raw) {
	const s = normalizeStr(raw);
	if (!s) return { statusDisplayName: null, extractedDate: null };
	const upper = s.toUpperCase();
	const isPkwt = upper.startsWith("PKWT");
	const isTetap = upper.startsWith("TETAP");
	const statusDisplayName = isPkwt ? "PKWT" : isTetap ? "TETAP" : null;
	let extractedDate = null;
	const parenMatch = s.match(/\((?:per|efektif)?\s*([^)]+)\)/i);
	if (parenMatch && parenMatch[1]) {
		extractedDate = parseFlexibleDate(parenMatch[1].trim());
	}
	return { statusDisplayName, extractedDate };
}

/**
 * Parse date from various Excel/Indonesian formats. Returns ISO date string (YYYY-MM-DD) or null.
 */
function parseFlexibleDate(s) {
	if (!s || typeof s !== "string") return null;
	const trimmed = s.trim();
	if (!trimmed) return null;
	const d = new Date(trimmed);
	if (!isNaN(d.getTime())) return d.toISOString().slice(0, 10);
	const dm = trimmed.match(/^(\d{1,2})[\/\-\.](\d{1,2})[\/\-\.](\d{2,4})$/);
	if (dm) {
		const y = dm[3].length === 2 ? 2000 + parseInt(dm[3], 10) : parseInt(dm[3], 10);
		const m = parseInt(dm[2], 10) - 1;
		const day = parseInt(dm[1], 10);
		const d2 = new Date(y, m, day);
		if (!isNaN(d2.getTime())) return d2.toISOString().slice(0, 10);
	}
	const ef = trimmed.replace(/^(per|efektif)\s+/i, "").trim();
	const d3 = new Date(ef);
	if (!isNaN(d3.getTime())) return d3.toISOString().slice(0, 10);
	return null;
}

/**
 * Find or create a company-scoped master by display_name. Returns id or null if displayName empty.
 */
async function findOrCreateMasterScoped(Model, displayName, companyId, opts) {
	const { modified_by, owner = modified_by, transaction } = opts || {};
	const d = normalizeStr(displayName);
	if (!d) return null;
	const where = {
		[Op.or]: [{ display_name: { [Op.iLike]: d } }, { name: { [Op.iLike]: d } }],
	};
	where.company_id = companyId ?? null;
	const existing = await Model.findOne({ where, transaction });
	if (existing) return existing.id;
	const now = new Date();
	const name = slugForName(d) || d.substring(0, 50);
	const created = await Model.create(
		{
			display_name: d,
			name: name.substring(0, 100),
			company_id: companyId ?? null,
			enabled: true,
			creation: now,
			modified: now,
			modified_by: modified_by || 1,
			owner: owner || 1,
		},
		{ transaction },
	);
	return created ? created.id : null;
}

/**
 * Find or create City by display_name (no company_id). Returns id or null.
 */
async function findOrCreateCity(displayName, opts) {
	const { modified_by, owner = modified_by, transaction } = opts || {};
	const d = normalizeStr(displayName);
	if (!d) return null;
	const existing = await dbSAPPS.City.findOne({
		where: { display_name: { [Op.iLike]: d } },
		transaction,
	});
	if (existing) return existing.id;
	const now = new Date();
	const created = await dbSAPPS.City.create(
		{
			display_name: d,
			enabled: true,
			creation: now,
			modified: now,
			modified_by: modified_by || 1,
			owner: owner || 1,
		},
		{ transaction },
	);
	return created ? created.id : null;
}

async function resolveCompanyId(row, defaultCompanyId, transaction) {
	if (row.company_id && Number(row.company_id)) return Number(row.company_id);
	const name = normalizeStr(row.company_name || row.company_display_name || "");
	if (!name) return defaultCompanyId || null;
	const c = await dbSAPPS.Company.findOne({
		where: {
			[Op.or]: [{ display_name: { [Op.iLike]: name } }, { name: { [Op.iLike]: name } }],
		},
		attributes: ["id"],
		transaction,
	});
	return c ? c.id : defaultCompanyId || null;
}

/**
 * Import employees from Excel-like rows.
 * Key: employee_name (nama lengkap) + company_id → update if exists, else create.
 * Masters (designation, department, employeestatus, gender, religion, maritalstatus, bloodgroup, education_level, placement_area, zone, recruitment_city, shifttype): find by display_name + company_id; if not found create then use new id.
 * Body: { rows: Array<Object>, defaultCompanyId: number, modified_by: number }
 */
async function importFromExcel(params) {
	const rows = Array.isArray(params.rows) ? params.rows : [];
	const defaultCompanyId = params.defaultCompanyId ? Number(params.defaultCompanyId) : 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, 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 companyId = await resolveCompanyId(row, defaultCompanyId, transaction);
			if (!companyId) {
				results.errors.push({ row: rowNum, message: "Company not found and no default company." });
				await transaction.rollback();
				continue;
			}

			const employeeName = normalizeStr(row.employee_name || row.name || row.nama_lengkap || row["ACCOUNT NAME BANK"] || "");
			if (!employeeName) {
				results.errors.push({ row: rowNum, message: "Employee name (nama lengkap) is required." });
				await transaction.rollback();
				continue;
			}

			const opts = { modified_by: modifiedBy, owner, transaction };

			const designation_id = await findOrCreateMasterScoped(
				dbSAPPS.Designation,
				row.designation_display_name || row.job_title || row["Job Title"],
				companyId,
				opts,
			);
			const department_id = await findOrCreateMasterScoped(
				dbSAPPS.Department,
				row.department_display_name || row.department_name || row.Departmen,
				companyId,
				opts,
			);
			const gender_id = await findOrCreateMasterScoped(dbSAPPS.Gender, row.gender_display_name || row.gender || row.Gender, companyId, opts);
			const religion_id = await findOrCreateMasterScoped(dbSAPPS.Religion, row.religion_display_name || row.religion || row.Religion, companyId, opts);
			const maritalstatus_id = await findOrCreateMasterScoped(
				dbSAPPS.MaritalStatus,
				row.maritalstatus_display_name || row.status_pernikahan || row["STATUS PERNIKAHAN"],
				companyId,
				opts,
			);
			const bloodgroup_id = await findOrCreateMasterScoped(
				dbSAPPS.BloodGroup,
				row.bloodgroup_display_name || row.golongan_darah || row["Golongan Darah"],
				companyId,
				opts,
			);
			const education_level_id = await findOrCreateMasterScoped(
				dbSAPPS.EducationLevel,
				row.education_level_display_name || row.pendidikan || row.PENDIDIKAN,
				companyId,
				opts,
			);
			const placement_area_id = await findOrCreateMasterScoped(
				dbSAPPS.PlacementArea,
				row.placement_area_display_name || row.area_penampatan || row["Area Penampatan"],
				companyId,
				opts,
			);
			const secondary_placement_area_id = await findOrCreateMasterScoped(
				dbSAPPS.PlacementArea,
				row.secondary_placement_area_display_name || row.area_sekunder || row["Area Sekunder"],
				companyId,
				opts,
			);
			const zone_id = await findOrCreateMasterScoped(dbSAPPS.Zone, row.zone_display_name || row.data_zona, companyId, opts);
			const recruitment_city_id = await findOrCreateCity(
				row.recruitment_city_display_name || row.recruit || row["Recruit"] || row.city_npwp || row["City NPWP"],
				opts,
			);
			const npwp_city_id = await findOrCreateCity(row.npwp_city_display_name || row["City NPWP"], opts);
			const shifttype_id = await findOrCreateMasterScoped(dbSAPPS.ShiftType, row.shifttype_display_name || row.shift_type, companyId, opts);

			// Normalize STATUS KARYAWAN: "TETAP (1 Sep'17)" / "Tetap (Efektif 01 Nov 2023)" → base "TETAP" for master; optionally parse date
			const statusKaryawanRaw = normalizeStr(row.employeestatus_display_name || row.status_karyawan || row["STATUS KARYAWAN"] || "");
			const { statusDisplayName: statusKaryawanNormalized, extractedDate: statusEffectiveDate } = normalizeStatusKaryawan(statusKaryawanRaw);
			const employeestatus_id_resolved = statusKaryawanNormalized
				? await findOrCreateMasterScoped(dbSAPPS.EmployeeStatus, statusKaryawanNormalized, companyId, opts)
				: employeestatus_id;

			const now = new Date();
			const isoDate = (d) => {
				if (!d) return null;
				if (d instanceof Date) return d.toISOString().slice(0, 10);
				const s = String(d).trim();
				if (!s) return null;
				const parsed = new Date(s);
				return isNaN(parsed.getTime()) ? null : parsed.toISOString().slice(0, 10);
			};
			const flexibleDate = (d) => isoDate(d) || (d && parseFlexibleDate(String(d).trim()));

			const diangkatTetapDate =
				flexibleDate(row["DIANGKAT KARYAWAN TETAP (Tanggal)"] || row.diangkat_karyawan_tetap || row.final_confirmation_date) ||
				(statusKaryawanNormalized === "TETAP" ? flexibleDate(statusEffectiveDate) : null);
			const pkwtBerakhirDate = flexibleDate(row["PKWT BERAKHIR"] || row.pkwt_berakhir || row.contract_end_date);
			const resignDate = flexibleDate(row["Resign Date"] || row.resign_date || row.resignation_letter_date);

			const payload = {
				employee_name: employeeName,
				company_id: companyId,
				employee_number: normalizeStr(row.employee_number || row["Emp ID"] || "") || null,
				job_role: normalizeStr(row.job_role || row["Job Role"] || "") || null,
				designation_id: designation_id ?? null,
				department_id: department_id ?? null,
				employeestatus_id: employeestatus_id_resolved ?? null,
				gender_id: gender_id ?? null,
				religion_id: religion_id ?? null,
				maritalstatus_id: maritalstatus_id ?? null,
				bloodgroup_id: bloodgroup_id ?? null,
				education_level_id: education_level_id ?? null,
				placement_area_id: placement_area_id ?? null,
				secondary_placement_area_id: secondary_placement_area_id ?? null,
				zone_id: zone_id ?? null,
				recruitment_city_id: recruitment_city_id ?? null,
				npwp_city_id: npwp_city_id ?? null,
				shifttype_id: shifttype_id ?? null,
				date_of_joining: isoDate(row.date_of_joining || row["Join Date"]) || null,
				final_confirmation_date: diangkatTetapDate || null,
				contract_end_date: pkwtBerakhirDate || null,
				resignation_letter_date: resignDate || null,
				place_of_birth: normalizeStr(row.place_of_birth || row.Birthplace) || null,
				date_of_birth: isoDate(row.date_of_birth || row.Birthdate) || null,
				personal_email: normalizeStr(row.personal_email || row.Email) || null,
				company_email: normalizeStr(row.company_email) || null,
				mobile_no: normalizeStr(row.mobile_no || row.Phone) || null,
				mother_maiden_name: normalizeStr(row.mother_maiden_name || row["Nama Ibu Kandung"]) || null,
				employment_insurance_number: normalizeStr(row.employment_insurance_number || row["No KPJ"] || "") || null,
				health_insurance_number: normalizeStr(row.health_insurance_number || row["No BPJS kesehatan"] || "") || null,
				npwp_address: normalizeStr(row.npwp_address || row["Address NPWP"]) || null,
				npwp_zip_code: normalizeStr(row.npwp_zip_code || row["Zip Code NPWP"]) || null,
				id_address: normalizeStr(row.id_address || row["ADDRESS KTP"]) || null,
				enabled: true,
				modified: now,
				modified_by: modifiedBy,
			};

			if (row.id_number != null && row.id_number !== "") payload.id_number = row.id_number;
			if (row.npwp_number != null && row.npwp_number !== "") payload.npwp_number = row.npwp_number;
			if (row.tax_number != null && row.tax_number !== "") payload.tax_number = row.tax_number;

			const existing = await dbSAPPS.Employee.findOne({
				where: { employee_name: employeeName, company_id: companyId },
				transaction,
			});

			if (existing) {
				const updateData = pick(payload, ALLOWED_UPDATE_FIELDS);
				updateData.modified = now;
				updateData.modified_by = modifiedBy;
				await existing.update(updateData, { transaction });
				results.updated++;
			} else {
				payload.creation = now;
				payload.owner = owner;
				const createData = pick({ ...payload }, ALLOWED_CREATE_FIELDS);
				const fkError = await validateEmployeeMasterFks(createData, { transaction });
				if (fkError) {
					results.errors.push({ row: rowNum, message: fkError.message });
					await transaction.rollback();
					continue;
				}
				await dbSAPPS.Employee.create(createData, { transaction });
				results.created++;
			}
			await transaction.commit();
		} catch (err) {
			await transaction.rollback();
			results.errors.push({ row: rowNum, message: err?.message || "Unknown error" });
		}
	}

	return {
		success: true,
		message: `Import completed: ${results.created} created, ${results.updated} updated.`,
		created: results.created,
		updated: results.updated,
		errors: results.errors,
	};
}
