const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
const emailNotificationService = require("./emailNotification.service");
const pkwtScheduler = require("./pkwtNotificationScheduler");
const emailGroupService = require("../emailGroups/emailGroup.service");
const birthdayAnnouncementEmailTemplate = require("./emailTemplates/email.birthdayAnnouncement");
require("dotenv").config();

const PKWT_EMAIL_SEND_LEASE_MINUTES = 10;

module.exports = {
	getAll,
	getByEmployee,
	create,
	markAsRead,
	markAsSent,
	delete: _delete,
	generatePKWTNotifications,
	previewBirthdayNotifications,
	getBirthdaySummary,
	getBirthdayCompanySettings,
	updateBirthdayCompanySettings,
	generateBirthdayNotifications,
	sendEmailForNotification,
	sendBirthdayTestEmail,
};

const BIRTHDAY_DOCTYPE_NAME = "birthday_notifications";
const BIRTHDAY_DOCTYPE_DISPLAY_NAME = "Birthday Notifications";
const BIRTHDAY_NOTIFICATION_TYPE = "BIRTHDAY_ANNOUNCEMENT";
const BIRTHDAY_LAST_EMAIL_SENT_KEY = "birthday_notification_last_email_sent_date";
const BIRTHDAY_EMAIL_SEND_LOCK_KEY = "birthday_notification_email_send_lock";
const BIRTHDAY_EMAIL_SEND_LOCK_TTL_MINUTES = 10;

function formatLocalYmd(date) {
	const d = date instanceof Date ? date : new Date(date);
	const y = d.getFullYear();
	const m = String(d.getMonth() + 1).padStart(2, "0");
	const day = String(d.getDate()).padStart(2, "0");
	return `${y}-${m}-${day}`;
}

function toLocalDateOnly(dateLike) {
	if (!dateLike) return null;
	const d = dateLike instanceof Date ? new Date(dateLike) : new Date(dateLike);
	if (Number.isNaN(d.getTime())) return null;
	d.setHours(0, 0, 0, 0);
	return d;
}

function isActiveEmailLock(lockValue, now = new Date(), ttlMinutes = BIRTHDAY_EMAIL_SEND_LOCK_TTL_MINUTES) {
	if (!lockValue) return false;
	const lockDate = new Date(lockValue);
	if (Number.isNaN(lockDate.getTime())) return false;
	const ageMs = now.getTime() - lockDate.getTime();
	return ageMs >= 0 && ageMs < Math.max(1, ttlMinutes) * 60 * 1000;
}

function isTruthy(val) {
	return val === true || val === "true" || val === 1 || val === "1";
}

function safeJsonParse(val, fallback) {
	if (val == null) return fallback;
	if (typeof val === "object") return val;
	try {
		return JSON.parse(val);
	} catch {
		return fallback;
	}
}

function normalizeEmailList(list) {
	const arr = Array.isArray(list) ? list : list ? [list] : [];
	const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
	const seen = new Set();
	const out = [];
	for (const raw of arr) {
		if (!raw || typeof raw !== "string") continue;
		const email = raw.trim();
		if (!emailRegex.test(email)) continue;
		const key = email.toLowerCase();
		if (seen.has(key)) continue;
		seen.add(key);
		out.push(email);
	}
	return out;
}

function resolveEmployeeEmail(emp) {
	if (!emp) return null;
	const ce = emp.company_email ? String(emp.company_email).trim() : "";
	const pe = emp.personal_email ? String(emp.personal_email).trim() : "";
	if (ce) return ce;
	if (pe) return pe;
	return null;
}

async function tryAcquireNotificationEmailLease(notificationId, params = {}, options = {}) {
	const leaseMinutes = Number(options.leaseMinutes || PKWT_EMAIL_SEND_LEASE_MINUTES);
	const now = new Date();
	const cutoff = new Date(now.getTime() - Math.max(1, leaseMinutes) * 60 * 1000);

	const [affected] = await dbSAPPS.Notification.update(
		{
			sentAt: now,
			modified: now,
			modified_by: params.modified_by || 1,
		},
		{
			where: {
				id: notificationId,
				is_sent: false,
				[Op.or]: [{ sentAt: { [Op.is]: null } }, { sentAt: { [Op.lt]: cutoff } }],
			},
		},
	);

	return Number(affected || 0) > 0;
}

async function releaseNotificationEmailLease(notificationId, params = {}) {
	try {
		await dbSAPPS.Notification.update(
			{
				sentAt: null,
				modified: new Date(),
				modified_by: params.modified_by || 1,
			},
			{
				where: {
					id: notificationId,
					is_sent: false,
				},
			},
		);
	} catch {
		// best-effort
	}
}

async function getOrCreateBirthdayDocType(modified_by, owner, transaction) {
	let dt = await dbSAPPS.DocType.findOne({
		where: { name: BIRTHDAY_DOCTYPE_NAME },
		transaction,
	});
	if (dt) return dt;

	// Also try by display_name (legacy)
	dt = await dbSAPPS.DocType.findOne({
		where: { display_name: BIRTHDAY_DOCTYPE_DISPLAY_NAME },
		transaction,
	});
	if (dt) return dt;

	return dbSAPPS.DocType.create(
		{
			creation: new Date(),
			modified: new Date(),
			modified_by: modified_by || 1,
			owner: owner || 1,
			enabled: true,
			name: BIRTHDAY_DOCTYPE_NAME,
			display_name: BIRTHDAY_DOCTYPE_DISPLAY_NAME,
			description: "System doctype for birthday announcement notifications",
		},
		{ transaction },
	);
}

async function getCompanySettingsMap(companyId, keys, transaction) {
	const rows = await dbSAPPS.CompanySetting.findAll({
		where: { company_id: companyId, key: { [Op.in]: keys } },
		attributes: ["key", "value"],
		transaction,
	});
	const out = {};
	for (const r of rows || []) {
		out[r.key] = r.value;
	}
	return out;
}

async function upsertCompanySetting(companyId, key, value, modified_by, owner, transaction) {
	const existing = await dbSAPPS.CompanySetting.findOne({
		where: { company_id: companyId, key },
		transaction,
	});
	const now = new Date();
	const payload = {
		modified: now,
		modified_by: modified_by || 1,
		owner: owner || 1,
		value: value != null ? String(value) : "",
	};
	if (existing) {
		await existing.update(payload, { transaction });
		return existing;
	}
	return dbSAPPS.CompanySetting.create(
		{
			company_id: companyId,
			key,
			creation: now,
			...payload,
		},
		{ transaction },
	);
}

function buildBirthdayEmailTemplate({ companyId, companyDisplayName, birthdayEmployees, templateId, eventDate }) {
	const built = birthdayAnnouncementEmailTemplate.build({
		company_id: companyId,
		companyName: companyDisplayName,
		birthdayEmployees,
		templateId,
		eventDate,
	});
	return {
		subject: built.subject,
		html: built.html,
	};
}

function buildBirthdaySystemMessage({ birthdayEmployees }) {
	const names = (birthdayEmployees || []).map((e) => e.employee_name).filter(Boolean);
	if (names.length === 0) return "Birthday announcement.";
	if (names.length === 1) return `🎂 Today is ${names[0]}'s birthday. Send your wishes!`;
	return `🎂 Birthdays today: ${names.join(", ")}. Please send your wishes!`;
}

async function resolveRecipientEmailsForCompany({ companyId, emailGroupIds, additionalRecipients }) {
	const groupIds = Array.isArray(emailGroupIds) ? emailGroupIds : [];
	let fromGroups = [];
	if (groupIds.length > 0) {
		const groupResults = await Promise.all(groupIds.map((gid) => emailGroupService.getEmailsForGroup(gid, { company_id: companyId })));
		fromGroups = groupResults.filter((r) => r && r.success).flatMap((r) => (r.emails || []).map((e) => e.email));
	}

	let fromAllEmployees = [];
	if (fromGroups.length === 0) {
		const employees = await dbSAPPS.Employee.findAll({
			where: { company_id: companyId, enabled: true },
			attributes: ["id", "employee_name", "company_email", "personal_email"],
		});
		fromAllEmployees = (employees || []).map(resolveEmployeeEmail).filter(Boolean);
	}

	return normalizeEmailList([...(fromGroups || []), ...(fromAllEmployees || []), ...(additionalRecipients || [])]);
}

async function getBirthdayCompanySettingsInternal(companyId) {
	const keys = [
		"birthday_notification_enabled",
		"birthday_notification_send_email",
		"birthday_notification_send_system",
		"birthday_notification_email_group_ids",
		"birthday_notification_additional_recipients",
		"birthday_notification_template_id",
	];
	const map = await getCompanySettingsMap(companyId, keys);
	return {
		enabled: isTruthy(map.birthday_notification_enabled),
		send_email: map.birthday_notification_send_email == null ? true : isTruthy(map.birthday_notification_send_email),
		send_system: map.birthday_notification_send_system == null ? true : isTruthy(map.birthday_notification_send_system),
		email_group_ids: safeJsonParse(map.birthday_notification_email_group_ids, []),
		additional_recipients: safeJsonParse(map.birthday_notification_additional_recipients, []),
		template_id: map.birthday_notification_template_id || "classic",
	};
}

async function getBirthdayCompanySettings(params = {}) {
	try {
		const companyId = params.company_id ? Number(params.company_id) : null;
		if (!companyId) return { success: false, message: "company_id is required" };
		const settings = await getBirthdayCompanySettingsInternal(companyId);
		return { success: true, message: "Loaded successfully", settings };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load settings" };
	}
}

async function updateBirthdayCompanySettings(params = {}) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const companyId = params.company_id ? Number(params.company_id) : null;
		if (!companyId) {
			await transaction.rollback();
			return { success: false, message: "company_id is required" };
		}
		const modified_by = params.modified_by || 1;
		const owner = params.owner || 1;

		await upsertCompanySetting(companyId, "birthday_notification_enabled", params.enabled ? "true" : "false", modified_by, owner, transaction);
		await upsertCompanySetting(companyId, "birthday_notification_send_email", params.send_email === false ? "false" : "true", modified_by, owner, transaction);
		await upsertCompanySetting(
			companyId,
			"birthday_notification_send_system",
			params.send_system === false ? "false" : "true",
			modified_by,
			owner,
			transaction,
		);
		await upsertCompanySetting(
			companyId,
			"birthday_notification_email_group_ids",
			JSON.stringify(Array.isArray(params.email_group_ids) ? params.email_group_ids : []),
			modified_by,
			owner,
			transaction,
		);
		await upsertCompanySetting(
			companyId,
			"birthday_notification_additional_recipients",
			JSON.stringify(normalizeEmailList(params.additional_recipients || [])),
			modified_by,
			owner,
			transaction,
		);
		await upsertCompanySetting(companyId, "birthday_notification_template_id", params.template_id || "classic", modified_by, owner, transaction);

		await transaction.commit();
		const settings = await getBirthdayCompanySettingsInternal(companyId);
		return { success: true, message: "Settings saved", settings };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to save settings" };
	}
}

async function previewBirthdayNotifications(params = {}) {
	try {
		const companyId = params.company_id ? Number(params.company_id) : null;
		if (!companyId) return { success: false, message: "company_id is required" };
		const settings = await getBirthdayCompanySettingsInternal(companyId);

		// Allow the caller to override the template for a live preview (e.g. from the UI picker)
		const effectiveTemplateId = params.template_id || settings.template_id || "classic";

		const now = new Date();
		const mm = String(now.getMonth() + 1).padStart(2, "0");
		const dd = String(now.getDate()).padStart(2, "0");
		const mmdd = `${mm}-${dd}`;

		const birthdayEmployees = await dbSAPPS.Employee.findAll({
			where: {
				company_id: companyId,
				enabled: true,
				date_of_birth: { [Op.ne]: null },
				[Op.and]: dbSAPPS.sequelize.where(dbSAPPS.sequelize.fn("DATE_FORMAT", dbSAPPS.sequelize.col("date_of_birth"), "%m-%d"), mmdd),
			},
			attributes: ["id", "employee_name", "company_id"],
		});

		const additionalRecipients = normalizeEmailList(settings.additional_recipients || []);
		const recipientEmails = await resolveRecipientEmailsForCompany({
			companyId,
			emailGroupIds: Array.isArray(settings.email_group_ids) ? settings.email_group_ids : [],
			additionalRecipients,
		});

		const companyRow = await dbSAPPS.Company.findByPk(companyId, { attributes: ["id", "display_name", "name"] });
		const tpl = buildBirthdayEmailTemplate({
			companyId,
			companyDisplayName: companyRow?.display_name || companyRow?.name,
			birthdayEmployees: birthdayEmployees || [],
			templateId: effectiveTemplateId,
			eventDate: now.toISOString().slice(0, 10),
		});
		const sysMsg = buildBirthdaySystemMessage({ birthdayEmployees: birthdayEmployees || [] });

		return {
			success: true,
			message: "Preview loaded",
			settings,
			birthdayEmployees: (birthdayEmployees || []).map((e) => ({
				id: e.id,
				employee_name: e.employee_name,
				department_name: null,
			})),
			recipients: {
				email_count: recipientEmails.length,
				emails_sample: recipientEmails.slice(0, 10),
			},
			emailPreview: tpl,
			systemMessagePreview: sysMsg,
		};
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load preview" };
	}
}

async function getBirthdaySummary(params = {}) {
	try {
		const companyId = params.company_id ? Number(params.company_id) : null;
		if (!companyId) return { success: false, message: "company_id is required" };

		const baseDate = toLocalDateOnly(params.base_date ? new Date(params.base_date) : new Date()) || toLocalDateOnly(new Date());
		const yesterday = new Date(baseDate);
		yesterday.setDate(yesterday.getDate() - 1);
		const tomorrow = new Date(baseDate);
		tomorrow.setDate(tomorrow.getDate() + 1);

		const mmddOf = (d) => {
			const mm = String(d.getMonth() + 1).padStart(2, "0");
			const dd = String(d.getDate()).padStart(2, "0");
			return `${mm}-${dd}`;
		};

		const mmddMap = {
			yesterday: mmddOf(yesterday),
			today: mmddOf(baseDate),
			tomorrow: mmddOf(tomorrow),
		};

		const mmddValues = [mmddMap.yesterday, mmddMap.today, mmddMap.tomorrow];
		const whereAnyMmdd = mmddValues.map((mmdd) =>
			dbSAPPS.sequelize.where(dbSAPPS.sequelize.fn("DATE_FORMAT", dbSAPPS.sequelize.col("date_of_birth"), "%m-%d"), mmdd),
		);

		const rows = await dbSAPPS.Employee.findAll({
			where: {
				company_id: companyId,
				enabled: true,
				date_of_birth: { [Op.ne]: null },
				[Op.or]: whereAnyMmdd,
			},
			attributes: ["id", "employee_name", "date_of_birth", "company_id"],
			order: [["employee_name", "ASC"]],
		});

		const buckets = {
			yesterday: [],
			today: [],
			tomorrow: [],
		};

		for (const r of rows || []) {
			const rawDob = r?.date_of_birth;
			const dobStr = rawDob == null ? "" : String(rawDob);
			// DATEONLY commonly returns YYYY-MM-DD; avoid timezone by slicing
			const mmdd = dobStr.length >= 10 ? dobStr.slice(5, 10).replace("-", "-") : "";
			const emp = { id: r.id, employee_name: r.employee_name };
			if (mmdd === mmddMap.yesterday.replace("-", "-")) buckets.yesterday.push(emp);
			else if (mmdd === mmddMap.today.replace("-", "-")) buckets.today.push(emp);
			else if (mmdd === mmddMap.tomorrow.replace("-", "-")) buckets.tomorrow.push(emp);
		}

		return {
			success: true,
			message: "Loaded birthday summary",
			company_id: companyId,
			dates: {
				yesterday: formatLocalYmd(yesterday),
				today: formatLocalYmd(baseDate),
				tomorrow: formatLocalYmd(tomorrow),
			},
			birthdays: buckets,
			counts: {
				yesterday: buckets.yesterday.length,
				today: buckets.today.length,
				tomorrow: buckets.tomorrow.length,
			},
		};
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load birthday summary" };
	}
}

async function generateBirthdayNotifications(params = {}) {
	try {
		const modified_by = params.modified_by || 1;
		const owner = params.owner || 1;
		const companyId = params.company_id ? Number(params.company_id) : null;
		const companiesId = Array.isArray(params.companiesId) ? params.companiesId : null;
		const companiesToProcess = companyId ? [companyId] : companiesId && companiesId.length ? companiesId : null;
		if (!companiesToProcess || companiesToProcess.length === 0) {
			return { success: false, message: "company_id or companiesId is required" };
		}

		const dt = await getOrCreateBirthdayDocType(modified_by, owner);
		const today = new Date();
		const start = new Date(today);
		start.setHours(0, 0, 0, 0);
		const end = new Date(start);
		end.setDate(end.getDate() + 1);
		const mm = String(start.getMonth() + 1).padStart(2, "0");
		const dd = String(start.getDate()).padStart(2, "0");
		const mmdd = `${mm}-${dd}`;

		let totalBirthdays = 0;
		let totalNotificationsCreated = 0;
		let totalEmailsSent = 0;
		const errors = [];

		for (const cidRaw of companiesToProcess) {
			const cid = Number(cidRaw);
			if (!cid || Number.isNaN(cid)) continue;
			const settings = await getBirthdayCompanySettingsInternal(cid);
			const enabled = params.enabled != null ? isTruthy(params.enabled) : settings.enabled;
			if (!enabled) continue;
			const sendEmail = params.send_email != null ? isTruthy(params.send_email) : settings.send_email;
			const sendSystem = params.send_system != null ? isTruthy(params.send_system) : settings.send_system;
			const templateId = params.template_id || settings.template_id;
			const emailGroupIds = Array.isArray(params.email_group_ids)
				? params.email_group_ids
				: Array.isArray(settings.email_group_ids)
					? settings.email_group_ids
					: [];
			const additionalRecipients = normalizeEmailList(
				Array.isArray(params.additional_recipients) ? params.additional_recipients : settings.additional_recipients || [],
			);

			// Fetch birthday employees (today)
			const birthdayEmployees = await dbSAPPS.Employee.findAll({
				where: {
					company_id: cid,
					enabled: true,
					date_of_birth: { [Op.ne]: null },
					[Op.and]: dbSAPPS.sequelize.where(dbSAPPS.sequelize.fn("DATE_FORMAT", dbSAPPS.sequelize.col("date_of_birth"), "%m-%d"), mmdd),
				},
				attributes: ["id", "employee_name", "company_id"],
			});

			if (!birthdayEmployees || birthdayEmployees.length === 0) continue;
			totalBirthdays += birthdayEmployees.length;

			// Recipient employees for system notifications (all enabled employees in company)
			let recipientEmployees = [];
			if (sendSystem) {
				recipientEmployees = await dbSAPPS.Employee.findAll({
					where: { company_id: cid, enabled: true },
					attributes: ["id"],
				});
			}

			// Create notifications per birthday employee, per recipient (DB transaction only)
			if (sendSystem) {
				const t = await dbSAPPS.sequelize.transaction();
				try {
					for (const bEmp of birthdayEmployees) {
						// Ensure workflow exists for this birthday employee today
						let workflow = await dbSAPPS.Workflow.findOne({
							where: {
								doctype_id: dt.id,
								doc_id: bEmp.id,
								creation: { [Op.gte]: start, [Op.lt]: end },
							},
							transaction: t,
						});
						if (!workflow) {
							workflow = await dbSAPPS.Workflow.create(
								{
									creation: new Date(),
									modified: new Date(),
									modified_by,
									owner,
									doctype_id: dt.id,
									doc_id: bEmp.id,
									status: "Draft",
								},
								{ transaction: t },
							);
						}

						const recipientIds = (recipientEmployees || []).map((r) => r.id).filter((id) => id != null);
						if (recipientIds.length === 0) continue;

						// Avoid duplicates: check existing notifications for this workflow + recipients created today
						const existing = await dbSAPPS.Notification.findAll({
							where: {
								workflow_id: workflow.id,
								employee_id: { [Op.in]: recipientIds },
								notification_type: BIRTHDAY_NOTIFICATION_TYPE,
								creation: { [Op.gte]: start, [Op.lt]: end },
							},
							attributes: ["employee_id"],
							transaction: t,
						});
						const existingSet = new Set((existing || []).map((n) => n.employee_id));
						const toCreate = [];
						for (const rid of recipientIds) {
							if (existingSet.has(rid)) continue;
							toCreate.push({
								creation: new Date(),
								modified: new Date(),
								modified_by,
								owner,
								workflow_id: workflow.id,
								employee_id: rid,
								notification_type: BIRTHDAY_NOTIFICATION_TYPE,
								recipient_emails: null,
								additional_recipients: null,
								is_sent: false,
								is_read: false,
								sentAt: null,
							});
						}
						if (toCreate.length > 0) {
							await dbSAPPS.Notification.bulkCreate(toCreate, { transaction: t });
							totalNotificationsCreated += toCreate.length;
						}
					}
					await t.commit();
				} catch (err) {
					await t.rollback();
					errors.push({ company_id: cid, error: err.message });
				}
			}

			// Email broadcast (once per company)
			if (sendEmail) {
				try {
					const todayKey = formatLocalYmd(start);
					const canForce = isTruthy(params.force_email);
					const lastMap = await getCompanySettingsMap(cid, [BIRTHDAY_LAST_EMAIL_SENT_KEY, BIRTHDAY_EMAIL_SEND_LOCK_KEY]);
					const lastSent = lastMap && lastMap[BIRTHDAY_LAST_EMAIL_SENT_KEY] != null ? String(lastMap[BIRTHDAY_LAST_EMAIL_SENT_KEY]) : null;
					const lockVal = lastMap && lastMap[BIRTHDAY_EMAIL_SEND_LOCK_KEY] != null ? String(lastMap[BIRTHDAY_EMAIL_SEND_LOCK_KEY]) : null;
					if (lastSent === todayKey && !canForce) {
						continue;
					}
					if (!canForce && isActiveEmailLock(lockVal)) {
						errors.push({ company_id: cid, error: "Birthday email send is locked (another run in progress)" });
						continue;
					}

					// Best-effort lock to reduce duplicate sends across parallel triggers
					await upsertCompanySetting(cid, BIRTHDAY_EMAIL_SEND_LOCK_KEY, new Date().toISOString(), modified_by, owner);

					const companyRow = await dbSAPPS.Company.findByPk(cid, { attributes: ["id", "display_name", "name"] });
					const recipientEmails = await resolveRecipientEmailsForCompany({
						companyId: cid,
						emailGroupIds,
						additionalRecipients,
					});
					if (recipientEmails.length > 0) {
						const tpl = buildBirthdayEmailTemplate({
							companyId: cid,
							companyDisplayName: companyRow?.display_name || companyRow?.name,
							birthdayEmployees,
							templateId,
							eventDate: start.toISOString().slice(0, 10),
						});
						const emailRes = await emailNotificationService.sendEmailNotification({
							company_id: cid,
							to: recipientEmails,
							subject: tpl.subject,
							html: tpl.html,
							modified_by,
							owner,
						});
						if (emailRes?.success) {
							await upsertCompanySetting(cid, BIRTHDAY_LAST_EMAIL_SENT_KEY, todayKey, modified_by, owner);
							await upsertCompanySetting(cid, BIRTHDAY_EMAIL_SEND_LOCK_KEY, "", modified_by, owner);
							totalEmailsSent += 1;
						} else {
							await upsertCompanySetting(cid, BIRTHDAY_EMAIL_SEND_LOCK_KEY, "", modified_by, owner);
							errors.push({ company_id: cid, error: emailRes?.message || "Failed to send birthday email" });
						}
					}
				} catch (err) {
					try {
						await upsertCompanySetting(cid, BIRTHDAY_EMAIL_SEND_LOCK_KEY, "", modified_by, owner);
					} catch {
						// ignore lock release failures
					}
					errors.push({ company_id: cid, error: err.message });
				}
			}
		}
		return {
			success: true,
			message: "Birthday notifications processed",
			birthdays: totalBirthdays,
			notificationsCreated: totalNotificationsCreated,
			emailsSent: totalEmailsSent,
			errors: errors.length ? errors : undefined,
		};
	} catch (error) {
		return { success: false, message: error?.message || "Failed to generate birthday notifications" };
	}
}

/**
 * Get all notifications with permission-based filtering
 * @param {Object} params - Query parameters
 * @param {Number} params.user_employee_id - Employee ID of the current user (for filtering own notifications)
 * @param {Boolean} params.access_read_all_data - User has access_read_all_data permission
 * @param {Boolean} params.administrator_of_hr - User has administrator_of_hr permission
 * @param {String} params.notification_type - Filter by notification type (e.g., 'PKWT_EXPIRATION')
 * @param {Number} params.employee_id - Filter by specific employee_id (only if has permission)
 * @param {Boolean} params.is_read - Filter by read status
 * @param {Boolean} params.is_sent - Filter by sent status
 * @returns {Promise<Object>} - Result object with notifications array
 */
async function getAll(params) {
	try {
		const filterConditions = {};
		const userEmployeeId = params.user_employee_id ? parseInt(params.user_employee_id) : null;
		const hasReadAllData = params.access_read_all_data === true || params.access_read_all_data === "true";
		const hasAdminHR = params.administrator_of_hr === true || params.administrator_of_hr === "true";

		// Permission-based filtering for PKWT notifications
		// Only show PKWT notifications to:
		// 1. Users with access_read_all_data
		// 2. Users with administrator_of_hr
		// 3. The employee who owns the PKWT contract
		if (params.notification_type === "PKWT_EXPIRATION" || !params.notification_type) {
			if (hasReadAllData || hasAdminHR) {
				// Users with read_all_data or admin_hr can see all PKWT notifications
				// No additional filter needed
			} else if (userEmployeeId) {
				// Regular users can only see their own PKWT notifications
				filterConditions.employee_id = {
					[Op.eq]: userEmployeeId,
				};
			} else {
				// No user_employee_id provided and no permission = return empty
				return {
					notifications: [],
					success: true,
					message: "Notifications loaded successfully!",
				};
			}
		}

		// Additional filters
		if (params.employee_id && (hasReadAllData || hasAdminHR)) {
			// Only allow filtering by employee_id if user has permission
			filterConditions.employee_id = {
				[Op.eq]: parseInt(params.employee_id),
			};
		}

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

		if (params.is_read !== undefined) {
			filterConditions.is_read = {
				[Op.eq]: params.is_read === true || params.is_read === "true" || params.is_read === 1,
			};
		}

		if (params.is_sent !== undefined) {
			filterConditions.is_sent = {
				[Op.eq]: params.is_sent === true || params.is_sent === "true" || params.is_sent === 1,
			};
		}

		const notifications = await dbSAPPS.Notification.findAll({
			where: filterConditions,
			order: [["creation", "DESC"]],
			include: [
				{
					model: dbSAPPS.Workflow,
					as: "workflow",
					attributes: ["id", "doc_id", "doctype_id", "status", "modified"],
					required: false, // LEFT JOIN untuk handle notification tanpa workflow
					include: [
						{
							model: dbSAPPS.DocType,
							as: "doctype",
							attributes: ["id", "name", "display_name"],
							required: false,
						},
					],
				},
				{
					model: dbSAPPS.Employee,
					as: "recipient",
					attributes: ["id", "employee_name", "employee_number", "email", "contract_end_date", "contract_status"],
					required: false,
				},
			],
		});

		const formattedNotifications = notifications.map((notification) => {
			// Format untuk PKWT notifications
			const isPKWTNotification = notification.notification_type === "PKWT_EXPIRATION";
			const employee = notification.recipient;
			const contractEndDate = employee?.contract_end_date || null;

			// Calculate days until expiration
			let daysUntilExpiration = null;
			if (contractEndDate) {
				const today = new Date();
				today.setHours(0, 0, 0, 0);
				const endDate = new Date(contractEndDate);
				endDate.setHours(0, 0, 0, 0);
				const diffTime = endDate - today;
				daysUntilExpiration = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
			}

			return {
				...notification.dataValues,
				id: notification.id,
				workflow: notification.workflow
					? {
							id: notification.workflow.id,
							doc_id: notification.workflow.doc_id,
							doctype_id: notification.workflow.doctype_id,
							status: notification.workflow.status || "Draft",
							modified: notification.workflow.modified,
							doctype: notification.workflow.doctype
								? {
										id: notification.workflow.doctype.id,
										name: notification.workflow.doctype.name,
										display_name: notification.workflow.doctype.display_name,
									}
								: null,
						}
					: null,
				recipient: employee
					? {
							id: employee.id,
							employee_name: employee.employee_name,
							employee_number: employee.employee_number,
							email: employee.email,
							contract_end_date: contractEndDate,
							contract_status: employee.contract_status,
							days_until_expiration: daysUntilExpiration,
						}
					: null,
				// Additional fields for frontend compatibility
				workflow_name: isPKWTNotification
					? "PKWT Expiration"
					: notification.workflow?.doctype?.display_name || notification.workflow?.doctype?.name || "Notification",
				created_at: notification.creation || notification.created_at,
				description:
					isPKWTNotification && employee
						? `Kontrak PKWT ${employee.employee_name} akan berakhir pada ${contractEndDate ? new Date(contractEndDate).toLocaleDateString("id-ID") : "N/A"}${daysUntilExpiration !== null ? ` (${daysUntilExpiration} hari lagi)` : ""}`
						: notification.description || "Notification",
				feature: isPKWTNotification ? "PKWT Expiration" : null,
			};
		});

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

/**
 * Get notifications by employee with permission-based filtering
 * @param {Object} params - Query parameters with employee_id
 * @param {Number} params.user_employee_id - Employee ID of the current user (for permission check)
 * @param {Boolean} params.access_read_all_data - User has access_read_all_data permission
 * @param {Boolean} params.administrator_of_hr - User has administrator_of_hr permission
 * @returns {Promise<Object>} - Result object with notifications array
 */
async function getByEmployee(params) {
	try {
		if (!params.employee_id) {
			return {
				success: false,
				message: "employee_id is required!",
			};
		}

		const userEmployeeId = params.user_employee_id ? parseInt(params.user_employee_id) : null;
		const hasReadAllData = params.access_read_all_data === true || params.access_read_all_data === "true";
		const hasAdminHR = params.administrator_of_hr === true || params.administrator_of_hr === "true";
		const requestedEmployeeId = parseInt(params.employee_id);

		// Permission check: user can only see their own notifications unless they have permission
		if (!hasReadAllData && !hasAdminHR && userEmployeeId !== requestedEmployeeId) {
			return {
				success: false,
				message: "You don't have permission to view notifications for this employee.",
			};
		}

		const filterConditions = {
			employee_id: {
				[Op.eq]: requestedEmployeeId,
			},
		};

		if (params.is_read !== undefined) {
			filterConditions.is_read = {
				[Op.eq]: params.is_read === true || params.is_read === "true" || params.is_read === 1,
			};
		}

		const notifications = await dbSAPPS.Notification.findAll({
			where: filterConditions,
			order: [["creation", "DESC"]],
			include: [
				{
					model: dbSAPPS.Workflow,
					as: "workflow",
					attributes: ["id", "doc_id", "doctype_id", "status", "modified"],
					required: false,
					include: [
						{
							model: dbSAPPS.DocType,
							as: "doctype",
							attributes: ["id", "name", "display_name"],
							required: false,
						},
					],
				},
				{
					model: dbSAPPS.Employee,
					as: "recipient",
					attributes: ["id", "employee_name", "employee_number", "email", "contract_end_date", "contract_status"],
					required: false,
				},
			],
		});

		const formattedNotifications = notifications.map((notification) => {
			const isPKWTNotification = notification.notification_type === "PKWT_EXPIRATION";
			const employee = notification.recipient;
			const contractEndDate = employee?.contract_end_date || null;

			let daysUntilExpiration = null;
			if (contractEndDate) {
				const today = new Date();
				today.setHours(0, 0, 0, 0);
				const endDate = new Date(contractEndDate);
				endDate.setHours(0, 0, 0, 0);
				const diffTime = endDate - today;
				daysUntilExpiration = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
			}

			return {
				...notification.dataValues,
				id: notification.id,
				workflow: notification.workflow
					? {
							id: notification.workflow.id,
							doc_id: notification.workflow.doc_id,
							doctype_id: notification.workflow.doctype_id,
							status: notification.workflow.status || "Draft",
							modified: notification.workflow.modified,
							doctype: notification.workflow.doctype
								? {
										id: notification.workflow.doctype.id,
										name: notification.workflow.doctype.name,
										display_name: notification.workflow.doctype.display_name,
									}
								: null,
						}
					: null,
				recipient: employee
					? {
							id: employee.id,
							employee_name: employee.employee_name,
							employee_number: employee.employee_number,
							email: employee.email,
							contract_end_date: contractEndDate,
							contract_status: employee.contract_status,
							days_until_expiration: daysUntilExpiration,
						}
					: null,
				workflow_name: isPKWTNotification
					? "PKWT Expiration"
					: notification.workflow?.doctype?.display_name || notification.workflow?.doctype?.name || "Notification",
				created_at: notification.creation || notification.created_at,
				description:
					isPKWTNotification && employee
						? `Kontrak PKWT ${employee.employee_name} akan berakhir pada ${contractEndDate ? new Date(contractEndDate).toLocaleDateString("id-ID") : "N/A"}${daysUntilExpiration !== null ? ` (${daysUntilExpiration} hari lagi)` : ""}`
						: notification.description || "Notification",
				feature: isPKWTNotification ? "PKWT Expiration" : null,
			};
		});

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

/**
 * Create a notification
 * @param {Object} params - Notification parameters
 * @returns {Promise<Object>} - Result object
 */
async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// Validate required fields
		if (!params.workflow_id) {
			await transaction.rollback();
			return {
				success: false,
				message: "workflow_id is required!",
			};
		}

		if (!params.employee_id) {
			await transaction.rollback();
			return {
				success: false,
				message: "employee_id is required!",
			};
		}

		if (!params.notification_type) {
			await transaction.rollback();
			return {
				success: false,
				message: "notification_type is required!",
			};
		}

		// Validate workflow exists
		const workflow = await dbSAPPS.Workflow.findOne({
			where: { id: params.workflow_id },
			transaction,
		});

		if (!workflow) {
			await transaction.rollback();
			return {
				success: false,
				message: `Workflow with ID ${params.workflow_id} not found!`,
			};
		}

		// Validate employee exists
		const employee = await dbSAPPS.Employee.findOne({
			where: { id: params.employee_id },
			transaction,
		});

		if (!employee) {
			await transaction.rollback();
			return {
				success: false,
				message: `Employee with ID ${params.employee_id} not found!`,
			};
		}

		// Check if notification already exists for this workflow and employee
		const existing = await dbSAPPS.Notification.findOne({
			where: {
				workflow_id: params.workflow_id,
				employee_id: params.employee_id,
				notification_type: params.notification_type,
			},
			transaction,
		});

		if (existing) {
			await transaction.rollback();
			return {
				success: false,
				message: "Notification already exists for this workflow and employee!",
			};
		}

		// Create notification
		const notification = await dbSAPPS.Notification.create(
			{
				creation: new Date(),
				modified: new Date(),
				modified_by: params.modified_by || 1,
				owner: params.owner || 1,
				workflow_id: params.workflow_id,
				employee_id: params.employee_id,
				notification_type: params.notification_type,
				recipient_emails: params.recipient_emails || null,
				additional_recipients: params.additional_recipients || null,
				is_sent: params.is_sent || false,
				is_read: params.is_read || false,
				sentAt: params.sentAt || null,
			},
			{ transaction },
		);

		await transaction.commit();
		return {
			success: true,
			message: "Notification created successfully!",
			notification: notification,
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to create notification, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Mark notification as read
 * @param {Number} id - Notification ID
 * @param {Object} params - Update parameters
 * @returns {Promise<Object>} - Result object
 */
async function markAsRead(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const notification = await dbSAPPS.Notification.findByPk(id, { transaction });
		if (!notification) {
			await transaction.rollback();
			return {
				success: false,
				message: "Notification not found!",
			};
		}

		await notification.update(
			{
				is_read: true,
				modified: new Date(),
				modified_by: params.modified_by || notification.modified_by,
			},
			{ transaction },
		);

		await transaction.commit();
		return {
			success: true,
			message: "Notification marked as read!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to update notification, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Mark notification as sent
 * @param {Number} id - Notification ID
 * @param {Object} params - Update parameters
 * @returns {Promise<Object>} - Result object
 */
async function markAsSent(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const notification = await dbSAPPS.Notification.findByPk(id, { transaction });
		if (!notification) {
			await transaction.rollback();
			return {
				success: false,
				message: "Notification not found!",
			};
		}

		await notification.update(
			{
				is_sent: true,
				sentAt: new Date(),
				modified: new Date(),
				modified_by: params.modified_by || notification.modified_by,
			},
			{ transaction },
		);

		await transaction.commit();
		return {
			success: true,
			message: "Notification marked as sent!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to update notification, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Delete notification
 * @param {Number} id - Notification ID
 * @returns {Promise<Object>} - Result object
 */
async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const notification = await dbSAPPS.Notification.findByPk(id, { transaction });
		if (!notification) {
			await transaction.rollback();
			return {
				success: false,
				message: "Notification not found!",
			};
		}

		await notification.destroy({ transaction });
		await transaction.commit();
		return {
			success: true,
			message: "Notification deleted successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to delete notification, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Generate PKWT notifications per company with company-specific configuration
 * @param {Array} companyIds - Array of company IDs to process
 * @param {Number} defaultDaysBeforeExpiration - Default days if company setting not found
 * @param {Number} modified_by - User ID
 * @param {Number} owner - Owner ID
 * @param {Object} transaction - Database transaction
 * @param {Number} doctypeId - DocType ID
 * @returns {Promise<Object>} - Result object
 */
async function generatePKWTNotificationsPerCompany(companyIds, defaultDaysBeforeExpiration, modified_by, owner, transaction, doctypeId) {
	let totalNotificationsCreated = 0;
	let totalEmployeesChecked = 0;
	const allErrors = [];

	for (const companyId of companyIds) {
		try {
			// Get company-specific scheduler configuration
			const companyConfig = await pkwtScheduler.getCompanySchedulerConfig(companyId);

			// Skip if scheduler is disabled for this company
			if (!companyConfig.enabled) {
				console.log(`[PKWT Scheduler] Skipping company ${companyId}: scheduler disabled`);
				continue;
			}

			const daysBeforeExpiration = companyConfig.daysBeforeExpiration || defaultDaysBeforeExpiration;

			// Calculate date range for this company
			const today = new Date();
			today.setHours(0, 0, 0, 0);
			const expirationDate = new Date(today);
			expirationDate.setDate(today.getDate() + daysBeforeExpiration);

			const todayStr = today.toISOString().split("T")[0];
			const expirationDateStr = expirationDate.toISOString().split("T")[0];

			// Build filter conditions for PKWT employees in this company
			const filterConditions = {
				company_id: companyId,
				contract_end_date: {
					[Op.and]: [{ [Op.ne]: null }, { [Op.gte]: todayStr }, { [Op.lte]: expirationDateStr }],
				},
			};

			// Filter by contract_status = 'PKWT' or employeestatus containing PKWT
			const pkwtBase = {
				[Op.or]: [{ display_name: { [Op.like]: "%PKWT%" } }, { name: { [Op.like]: "%pkwt%" } }],
				enabled: true,
			};

			const pkwtStatuses = await dbSAPPS.EmployeeStatus.findAll({
				where: pkwtBase,
				attributes: ["id"],
				transaction,
			});

			const pkwtIds = (pkwtStatuses || []).map((s) => s.id).filter((id) => id != null);

			if (pkwtIds.length > 0) {
				filterConditions[Op.or] = [{ contract_status: "PKWT" }, { employeestatus_id: { [Op.in]: pkwtIds } }];
			} else {
				filterConditions.contract_status = "PKWT";
			}

			// Find employees with expiring PKWT contracts for this company
			const employees = await dbSAPPS.Employee.findAll({
				where: filterConditions,
				attributes: ["id", "employee_name", "employee_number", "email", "contract_end_date", "contract_status", "company_id"],
				include: [
					{
						model: dbSAPPS.Company,
						as: "company",
						attributes: ["id", "display_name", "name"],
					},
				],
				transaction,
			});

			if (!employees || employees.length === 0) {
				continue;
			}

			totalEmployeesChecked += employees.length;

			// Process employees for this company
			const result = await processEmployeesForNotifications(employees, companyId, daysBeforeExpiration, modified_by, owner, doctypeId, transaction);

			totalNotificationsCreated += result.notificationsCreated;
			if (result.errors && result.errors.length > 0) {
				allErrors.push(...result.errors);
			}
		} catch (error) {
			console.error(`Error processing company ${companyId}:`, error);
			allErrors.push({
				company_id: companyId,
				error: error.message,
			});
		}
	}

	return {
		success: true,
		message: `Successfully created ${totalNotificationsCreated} PKWT expiration notifications.`,
		notificationsCreated: totalNotificationsCreated,
		employeesChecked: totalEmployeesChecked,
		errors: allErrors.length > 0 ? allErrors : undefined,
	};
}

/**
 * Process employees and create notifications (helper function)
 * @param {Array} employees - Array of employee objects
 * @param {Number} companyId - Company ID
 * @param {Number} modified_by - User ID
 * @param {Number} owner - Owner ID
 * @param {Number} doctypeId - DocType ID
 * @param {Object} transaction - Database transaction
 * @returns {Promise<Object>} - Result object
 */
async function processEmployeesForNotifications(employees, companyId, daysBeforeExpiration, modified_by, owner, doctypeId, transaction) {
	const PKWT_NOTIFICATION_DOCTYPE_ID = doctypeId;
	let notificationsCreated = 0;
	const errors = [];

	for (const employee of employees) {
		try {
			// Check if notification already exists (and is still relevant to the current contract cycle)
			const existingNotification = await dbSAPPS.Notification.findOne({
				where: {
					employee_id: employee.id,
					notification_type: "PKWT_EXPIRATION",
				},
				order: [["creation", "DESC"]],
				include: [
					{
						model: dbSAPPS.Workflow,
						as: "workflow",
						required: false,
						where: PKWT_NOTIFICATION_DOCTYPE_ID
							? {
									doc_id: employee.id,
									doctype_id: PKWT_NOTIFICATION_DOCTYPE_ID,
								}
							: undefined,
					},
				],
				transaction,
			});

			if (existingNotification) {
				const endDate = toLocalDateOnly(employee.contract_end_date);
				if (endDate && daysBeforeExpiration != null) {
					const windowStart = new Date(endDate);
					windowStart.setDate(windowStart.getDate() - Number(daysBeforeExpiration));
					windowStart.setHours(0, 0, 0, 0);

					const existingCreated = toLocalDateOnly(existingNotification.creation);
					// If the existing notification was created within the current contract window, keep it.
					if (existingCreated && existingCreated.getTime() >= windowStart.getTime()) {
						continue;
					}

					// Otherwise treat it as stale (previous contract cycle) and allow creating a new notification.
				} else {
					// No contract_end_date available, keep the existing notification to avoid duplicates.
					continue;
				}
			}

			// Create workflow
			let workflow;
			if (PKWT_NOTIFICATION_DOCTYPE_ID) {
				workflow = await dbSAPPS.Workflow.create(
					{
						creation: new Date(),
						modified: new Date(),
						modified_by: modified_by,
						owner: owner,
						doctype_id: PKWT_NOTIFICATION_DOCTYPE_ID,
						doc_id: employee.id,
						status: "Draft",
					},
					{ transaction },
				);
			} else {
				console.warn(`Skipping notification creation for employee ${employee.id}: PKWT_NOTIFICATION_DOCTYPE_ID not configured`);
				continue;
			}

			// Prepare recipient emails
			const recipientEmails = [];
			if (employee.email) {
				recipientEmails.push(employee.email);
			}

			// Get additional recipients from CompanySetting
			let additionalRecipients = null;
			try {
				const companySetting = await dbSAPPS.CompanySetting.findOne({
					where: {
						company_id: companyId,
						key: "pkwt_notification_additional_recipients",
					},
					transaction,
				});

				if (companySetting && companySetting.value) {
					try {
						const parsedValue = JSON.parse(companySetting.value);
						if (Array.isArray(parsedValue) && parsedValue.length > 0) {
							additionalRecipients = parsedValue.filter((email) => email && typeof email === "string" && email.trim().length > 0);
						}
					} catch (parseError) {
						console.warn(`Failed to parse PKWT additional recipients for company ${companyId}:`, parseError);
					}
				}
			} catch (error) {
				console.warn(`Failed to get PKWT additional recipients from CompanySetting for company ${companyId}:`, error);
			}

			// Create notification
			await dbSAPPS.Notification.create(
				{
					creation: new Date(),
					modified: new Date(),
					modified_by: modified_by,
					owner: owner,
					workflow_id: workflow.id,
					employee_id: employee.id,
					notification_type: "PKWT_EXPIRATION",
					recipient_emails: recipientEmails.length > 0 ? recipientEmails : null,
					additional_recipients: additionalRecipients && additionalRecipients.length > 0 ? additionalRecipients : null,
					is_sent: false,
					is_read: false,
					sentAt: null,
				},
				{ transaction },
			);

			notificationsCreated++;
		} catch (error) {
			errors.push({
				employee_id: employee.id,
				employee_name: employee.employee_name,
				error: error.message,
			});
			console.error(`Error creating notification for employee ${employee.id}:`, error);
		}
	}

	return {
		notificationsCreated,
		errors,
	};
}

/**
 * Generate PKWT expiration notifications
 * This function checks for employees with PKWT contracts expiring within specified days
 * and creates notifications for them
 * @param {Object} params - Parameters for notification generation
 * @param {Number} params.daysBeforeExpiration - Number of days before expiration to notify (default: 30, or from company settings)
 * @param {Number} params.modified_by - User ID who triggers this (default: 1)
 * @param {Number} params.owner - Owner ID (default: 1)
 * @param {Array} params.companiesId - Optional: filter by company IDs
 * @param {Boolean} params.useCompanySettings - Use company-specific settings (default: true)
 * @returns {Promise<Object>} - Result object with created notifications count
 */
async function generatePKWTNotifications(params = {}) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const useCompanySettings = params.useCompanySettings !== false; // Default: true
		const defaultDaysBeforeExpiration = params.daysBeforeExpiration || 30;
		const modified_by = params.modified_by || 1;
		const owner = params.owner || 1;

		// If using company settings, we need to process per company
		// Otherwise, use the provided daysBeforeExpiration for all companies
		let companiesToProcess = params.companiesId || null;

		if (useCompanySettings && companiesToProcess && Array.isArray(companiesToProcess) && companiesToProcess.length > 0) {
			// Process each company with its own configuration
			const result = await generatePKWTNotificationsPerCompany(
				companiesToProcess,
				defaultDaysBeforeExpiration,
				modified_by,
				owner,
				transaction,
				params.doctype_id || null,
			);

			await transaction.commit();

			// Optionally send emails after creating notifications
			if (params.sendEmail !== false && result.notificationsCreated > 0) {
				sendEmailsForPKWTNotifications({ ...params, modified_by, owner }).catch((err) => {
					console.error("Error sending emails for PKWT notifications:", err);
				});
			}

			return result;
		}

		// Use single daysBeforeExpiration for all (backward compatibility or when useCompanySettings is false)
		const daysBeforeExpiration = defaultDaysBeforeExpiration;

		// Calculate date range
		const today = new Date();
		today.setHours(0, 0, 0, 0);
		const expirationDate = new Date(today);
		expirationDate.setDate(today.getDate() + daysBeforeExpiration);

		const todayStr = today.toISOString().split("T")[0];
		const expirationDateStr = expirationDate.toISOString().split("T")[0];

		// Build filter conditions for PKWT employees
		const filterConditions = {
			contract_end_date: {
				[Op.and]: [{ [Op.ne]: null }, { [Op.gte]: todayStr }, { [Op.lte]: expirationDateStr }],
			},
		};

		// Filter by contract_status = 'PKWT' or employeestatus containing PKWT
		const pkwtBase = {
			[Op.or]: [{ display_name: { [Op.like]: "%PKWT%" } }, { name: { [Op.like]: "%pkwt%" } }],
			enabled: true,
		};

		const pkwtStatuses = await dbSAPPS.EmployeeStatus.findAll({
			where: pkwtBase,
			attributes: ["id"],
		});

		const pkwtIds = (pkwtStatuses || []).map((s) => s.id).filter((id) => id != null);

		if (pkwtIds.length > 0) {
			filterConditions[Op.or] = [{ contract_status: "PKWT" }, { employeestatus_id: { [Op.in]: pkwtIds } }];
		} else {
			filterConditions.contract_status = "PKWT";
		}

		// Filter by company if provided
		if (params.companiesId && Array.isArray(params.companiesId) && params.companiesId.length > 0) {
			filterConditions.company_id = {
				[Op.in]: params.companiesId,
			};
		}

		// Find employees with expiring PKWT contracts
		const employees = await dbSAPPS.Employee.findAll({
			where: filterConditions,
			attributes: ["id", "employee_name", "employee_number", "email", "contract_end_date", "contract_status", "company_id"],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name", "name"],
				},
			],
			transaction,
		});

		if (!employees || employees.length === 0) {
			await transaction.commit();
			return {
				success: true,
				message: "No employees with expiring PKWT contracts found.",
				notificationsCreated: 0,
			};
		}

		// Get or create DocType for PKWT Notification
		// Note: You may need to create this doctype in your database first
		// For now, we'll use a placeholder doctype_id or create a workflow without doctype
		// You should configure the actual doctype_id for PKWT notifications
		const PKWT_NOTIFICATION_DOCTYPE_ID = params.doctype_id || null; // Set this to actual doctype ID

		let notificationsCreated = 0;
		const errors = [];

		for (const employee of employees) {
			try {
				// Check if notification already exists for this employee and PKWT expiration
				// Check by employee_id and notification_type, and also check if workflow exists with same doc_id
				const existingNotification = await dbSAPPS.Notification.findOne({
					where: {
						employee_id: employee.id,
						notification_type: "PKWT_EXPIRATION",
					},
					order: [["creation", "DESC"]],
					include: [
						{
							model: dbSAPPS.Workflow,
							as: "workflow",
							required: false,
							where: PKWT_NOTIFICATION_DOCTYPE_ID
								? {
										doc_id: employee.id,
										doctype_id: PKWT_NOTIFICATION_DOCTYPE_ID,
									}
								: undefined,
						},
					],
					transaction,
				});

				if (existingNotification) {
					const endDate = toLocalDateOnly(employee.contract_end_date);
					if (endDate && daysBeforeExpiration != null) {
						const windowStart = new Date(endDate);
						windowStart.setDate(windowStart.getDate() - Number(daysBeforeExpiration));
						windowStart.setHours(0, 0, 0, 0);

						const existingCreated = toLocalDateOnly(existingNotification.creation);
						if (existingCreated && existingCreated.getTime() >= windowStart.getTime()) {
							// Notification already exists for this contract window
							continue;
						}

						// Stale notification from previous contract cycle; allow creating a new one (non-destructive).
					} else {
						// If no contract_end_date, keep existing to avoid duplicates
						continue;
					}
				}

				// Create workflow for this notification
				// If doctype_id is not provided, we'll create a minimal workflow
				let workflow;
				if (PKWT_NOTIFICATION_DOCTYPE_ID) {
					workflow = await dbSAPPS.Workflow.create(
						{
							creation: new Date(),
							modified: new Date(),
							modified_by: modified_by,
							owner: owner,
							doctype_id: PKWT_NOTIFICATION_DOCTYPE_ID,
							doc_id: employee.id,
							status: "Draft",
						},
						{ transaction },
					);
				} else {
					// Create a workflow without doctype (if your system allows)
					// Or use a default doctype_id
					// For now, we'll skip workflow creation if doctype_id is not provided
					// You should configure the doctype_id for PKWT notifications
					console.warn(`Skipping notification creation for employee ${employee.id}: PKWT_NOTIFICATION_DOCTYPE_ID not configured`);
					continue;
				}

				// Prepare recipient emails
				const recipientEmails = [];
				if (employee.email) {
					recipientEmails.push(employee.email);
				}

				// Get additional recipients from params or CompanySetting
				let additionalRecipients = null;

				// First, try to get from params
				if (params.additional_recipients) {
					// Handle both array and JSON string formats
					if (Array.isArray(params.additional_recipients)) {
						additionalRecipients = params.additional_recipients.filter((email) => email && typeof email === "string" && email.trim().length > 0);
					} else if (typeof params.additional_recipients === "string") {
						try {
							const parsed = JSON.parse(params.additional_recipients);
							if (Array.isArray(parsed)) {
								additionalRecipients = parsed.filter((email) => email && typeof email === "string" && email.trim().length > 0);
							}
						} catch (parseError) {
							console.warn(`Failed to parse additional_recipients from params:`, parseError);
						}
					}
				}

				// If not provided in params or empty, try to get from CompanySetting
				if ((!additionalRecipients || additionalRecipients.length === 0) && employee.company_id) {
					try {
						const companySetting = await dbSAPPS.CompanySetting.findOne({
							where: {
								company_id: employee.company_id,
								key: "pkwt_notification_additional_recipients",
							},
							transaction,
						});

						if (companySetting && companySetting.value) {
							try {
								// Parse JSON value from CompanySetting
								const parsedValue = JSON.parse(companySetting.value);
								if (Array.isArray(parsedValue) && parsedValue.length > 0) {
									additionalRecipients = parsedValue.filter((email) => email && typeof email === "string" && email.trim().length > 0);
								}
							} catch (parseError) {
								console.warn(`Failed to parse PKWT additional recipients for company ${employee.company_id}:`, parseError);
							}
						}
					} catch (error) {
						console.warn(`Failed to get PKWT additional recipients from CompanySetting for company ${employee.company_id}:`, error);
					}
				}

				// Create notification
				// Store additional_recipients as array (Sequelize JSON type will handle serialization)
				const notification = await dbSAPPS.Notification.create(
					{
						creation: new Date(),
						modified: new Date(),
						modified_by: modified_by,
						owner: owner,
						workflow_id: workflow.id,
						employee_id: employee.id,
						notification_type: "PKWT_EXPIRATION",
						recipient_emails: recipientEmails.length > 0 ? recipientEmails : null,
						additional_recipients: additionalRecipients && additionalRecipients.length > 0 ? additionalRecipients : null,
						is_sent: false,
						is_read: false,
						sentAt: null,
					},
					{ transaction },
				);

				notificationsCreated++;
			} catch (error) {
				errors.push({
					employee_id: employee.id,
					employee_name: employee.employee_name,
					error: error.message,
				});
				console.error(`Error creating notification for employee ${employee.id}:`, error);
			}
		}

		await transaction.commit();

		// Optionally send emails after creating notifications
		// This can be done asynchronously to not block the notification creation
		if (params.sendEmail !== false && notificationsCreated > 0) {
			// Send emails asynchronously (don't await to avoid blocking)
			sendEmailsForPKWTNotifications({ ...params, modified_by, owner }).catch((err) => {
				console.error("Error sending emails for PKWT notifications:", err);
			});
		}

		return {
			success: true,
			message: `Successfully created ${notificationsCreated} PKWT expiration notifications.`,
			notificationsCreated: notificationsCreated,
			employeesChecked: employees.length,
			errors: errors.length > 0 ? errors : undefined,
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to generate PKWT notifications, please try again!";
		console.error("Error generating PKWT notifications:", error);
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Send email for a specific notification
 * @param {Number} notificationId - Notification ID
 * @param {Object} params - Additional parameters
 * @returns {Promise<Object>} - Result object
 */
async function sendEmailForNotification(notificationId, params = {}) {
	try {
		const notification = await dbSAPPS.Notification.findByPk(notificationId, {
			include: [
				{
					model: dbSAPPS.Employee,
					as: "recipient",
					attributes: ["id", "employee_name", "employee_number", "email", "contract_end_date", "contract_status", "company_id"],
					include: [
						{
							model: dbSAPPS.Company,
							as: "company",
							attributes: ["id", "display_name", "name"],
						},
					],
				},
			],
		});

		if (!notification) {
			return {
				success: false,
				message: "Notification not found!",
			};
		}

		if (notification.is_sent === true || notification.is_sent === 1 || notification.is_sent === "1") {
			return {
				success: true,
				message: "Email already sent for this notification.",
			};
		}

		const acquired = await tryAcquireNotificationEmailLease(notification.id, params);
		if (!acquired) {
			return {
				success: false,
				message: "Email sending is already in progress for this notification. Please try again later.",
			};
		}

		// Only send email for PKWT_EXPIRATION notifications
		if (notification.notification_type !== "PKWT_EXPIRATION") {
			return {
				success: false,
				message: `Email sending not implemented for notification type: ${notification.notification_type}`,
			};
		}

		const employee = notification.recipient;
		if (!employee) {
			return {
				success: false,
				message: "Employee data not found for notification!",
			};
		}

		// Calculate days until expiration
		let daysUntilExpiration = null;
		if (employee.contract_end_date) {
			const today = new Date();
			today.setHours(0, 0, 0, 0);
			const endDate = new Date(employee.contract_end_date);
			endDate.setHours(0, 0, 0, 0);
			const diffTime = endDate - today;
			daysUntilExpiration = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
		}

		// Get additional recipients from notification or CompanySetting
		// Handle both array and JSON string formats
		let additionalRecipients = [];

		// First, try to get from notification.additional_recipients
		if (notification.additional_recipients) {
			try {
				// If it's a string, parse it; if it's already an array, use it directly
				if (typeof notification.additional_recipients === "string") {
					const parsed = JSON.parse(notification.additional_recipients);
					if (Array.isArray(parsed)) {
						additionalRecipients = parsed.filter((email) => email && typeof email === "string" && email.trim().length > 0);
					}
				} else if (Array.isArray(notification.additional_recipients)) {
					additionalRecipients = notification.additional_recipients.filter((email) => email && typeof email === "string" && email.trim().length > 0);
				}
			} catch (parseError) {
				console.warn(`Failed to parse additional_recipients from notification ${notification.id}:`, parseError);
			}
		}

		// If not set in notification or empty, try to get from CompanySetting
		if (additionalRecipients.length === 0 && employee.company_id) {
			try {
				const companySetting = await dbSAPPS.CompanySetting.findOne({
					where: {
						company_id: employee.company_id,
						key: "pkwt_notification_additional_recipients",
					},
				});

				if (companySetting && companySetting.value) {
					try {
						// Parse JSON value from CompanySetting
						const parsedValue = JSON.parse(companySetting.value);
						if (Array.isArray(parsedValue) && parsedValue.length > 0) {
							additionalRecipients = parsedValue.filter((email) => email && typeof email === "string" && email.trim().length > 0);
						}
					} catch (parseError) {
						console.warn(`Failed to parse PKWT additional recipients for company ${employee.company_id}:`, parseError);
					}
				}
			} catch (error) {
				console.warn(`Failed to get PKWT additional recipients from CompanySetting for company ${employee.company_id}:`, error);
			}
		}

		// Send PKWT expiration email
		const emailResult = await emailNotificationService.sendPKWTExpirationEmail({
			employee: {
				...employee.dataValues,
				company: employee.company,
			},
			company_id: employee.company_id,
			additionalRecipients: additionalRecipients,
			notification_id: notification.id,
			daysUntilExpiration: daysUntilExpiration,
			modified_by: params.modified_by || notification.modified_by,
			owner: params.owner || notification.owner,
		});

		if (!emailResult?.success) {
			await releaseNotificationEmailLease(notification.id, params);
		}

		return emailResult;
	} catch (error) {
		console.error("Error in sendEmailForNotification:", error);
		try {
			await releaseNotificationEmailLease(Number(notificationId), params);
		} catch {
			// ignore
		}
		return {
			success: false,
			message: error?.message || "Failed to send email for notification",
		};
	}
}

/**
 * Send emails for all PKWT notifications that haven't been sent yet
 * This is called asynchronously after generating notifications
 * @param {Object} params - Parameters
 * @returns {Promise<Object>} - Result object
 */
async function sendEmailsForPKWTNotifications(params = {}) {
	try {
		// Find all unsent PKWT notifications
		const notifications = await dbSAPPS.Notification.findAll({
			where: {
				notification_type: "PKWT_EXPIRATION",
				is_sent: false,
			},
			include: [
				{
					model: dbSAPPS.Employee,
					as: "recipient",
					attributes: ["id", "employee_name", "employee_number", "email", "contract_end_date", "contract_status", "company_id"],
					include: [
						{
							model: dbSAPPS.Company,
							as: "company",
							attributes: ["id", "display_name", "name"],
						},
					],
				},
			],
			limit: 100, // Process in batches to avoid overwhelming the system
		});

		if (!notifications || notifications.length === 0) {
			return {
				success: true,
				message: "No unsent PKWT notifications found.",
				emailsSent: 0,
			};
		}

		let emailsSent = 0;
		let emailsFailed = 0;
		const errors = [];

		for (const notification of notifications) {
			try {
				const acquired = await tryAcquireNotificationEmailLease(notification.id, params);
				if (!acquired) {
					continue;
				}

				const employee = notification.recipient;
				if (!employee) {
					await releaseNotificationEmailLease(notification.id, params);
					emailsFailed++;
					errors.push({
						notification_id: notification.id,
						error: "Employee data not found",
					});
					continue;
				}

				// Calculate days until expiration
				let daysUntilExpiration = null;
				if (employee.contract_end_date) {
					const today = new Date();
					today.setHours(0, 0, 0, 0);
					const endDate = new Date(employee.contract_end_date);
					endDate.setHours(0, 0, 0, 0);
					const diffTime = endDate - today;
					daysUntilExpiration = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
				}

				// Get additional recipients from notification or CompanySetting
				// Handle both array and JSON string formats
				let additionalRecipients = [];

				// First, try to get from notification.additional_recipients
				if (notification.additional_recipients) {
					try {
						// If it's a string, parse it; if it's already an array, use it directly
						if (typeof notification.additional_recipients === "string") {
							const parsed = JSON.parse(notification.additional_recipients);
							if (Array.isArray(parsed)) {
								additionalRecipients = parsed.filter((email) => email && typeof email === "string" && email.trim().length > 0);
							}
						} else if (Array.isArray(notification.additional_recipients)) {
							additionalRecipients = notification.additional_recipients.filter((email) => email && typeof email === "string" && email.trim().length > 0);
						}
					} catch (parseError) {
						console.warn(`Failed to parse additional_recipients from notification ${notification.id}:`, parseError);
					}
				}

				// If not set in notification or empty, try to get from CompanySetting
				if (additionalRecipients.length === 0 && employee.company_id) {
					try {
						const companySetting = await dbSAPPS.CompanySetting.findOne({
							where: {
								company_id: employee.company_id,
								key: "pkwt_notification_additional_recipients",
							},
						});

						if (companySetting && companySetting.value) {
							try {
								// Parse JSON value from CompanySetting
								const parsedValue = JSON.parse(companySetting.value);
								if (Array.isArray(parsedValue) && parsedValue.length > 0) {
									additionalRecipients = parsedValue.filter((email) => email && typeof email === "string" && email.trim().length > 0);
								}
							} catch (parseError) {
								console.warn(`Failed to parse PKWT additional recipients for company ${employee.company_id}:`, parseError);
							}
						}
					} catch (error) {
						console.warn(`Failed to get PKWT additional recipients from CompanySetting for company ${employee.company_id}:`, error);
					}
				}

				// Send email
				const emailResult = await emailNotificationService.sendPKWTExpirationEmail({
					employee: {
						...employee.dataValues,
						company: employee.company,
					},
					company_id: employee.company_id,
					additionalRecipients: additionalRecipients,
					notification_id: notification.id,
					daysUntilExpiration: daysUntilExpiration,
					modified_by: params.modified_by || notification.modified_by,
					owner: params.owner || notification.owner,
				});

				if (emailResult.success) {
					emailsSent++;
				} else {
					await releaseNotificationEmailLease(notification.id, params);
					emailsFailed++;
					errors.push({
						notification_id: notification.id,
						employee_id: employee.id,
						error: emailResult.message,
					});
				}

				// Small delay to avoid overwhelming email server
				await new Promise((resolve) => setTimeout(resolve, 500));
			} catch (error) {
				await releaseNotificationEmailLease(notification.id, params);
				emailsFailed++;
				errors.push({
					notification_id: notification.id,
					error: error.message,
				});
				console.error(`Error sending email for notification ${notification.id}:`, error);
			}
		}

		return {
			success: true,
			message: `Sent ${emailsSent} emails, ${emailsFailed} failed.`,
			emailsSent: emailsSent,
			emailsFailed: emailsFailed,
			errors: errors.length > 0 ? errors : undefined,
		};
	} catch (error) {
		console.error("Error in sendEmailsForPKWTNotifications:", error);
		return {
			success: false,
			message: error?.message || "Failed to send emails for PKWT notifications",
		};
	}
}

/**
 * Send a birthday test email directly to any recipient address.
 * Uses the same birthday announcement template as the real notification.
 *
 * SMTP resolution strategy (robust):
 *   1. Try the company's own EmailAccount first.
 *   2. If not configured, fall back to ANY email account available in the system.
 *      This allows test emails to be sent for companies that haven't set up their own
 *      SMTP yet, using another company's account as the sender.
 *
 * @param {Object} params
 * @param {Number}         params.company_id   - Company context (for name and preferred SMTP)
 * @param {String}         params.template_id  - Template variant (classic|confetti|…|random)
 * @param {String|Array}   params.to           - Recipient address(es) — can be ANY email
 * @param {Number}         params.modified_by  - User triggering the test (for audit)
 * @param {Number}         params.owner        - Owner (for audit)
 */
async function sendBirthdayTestEmail(params = {}) {
	try {
		const { company_id, template_id = "classic", to, modified_by = 1, owner = 1 } = params;

		if (!company_id) return { success: false, message: "company_id is required" };

		const toList = Array.isArray(to) ? to : to ? [to] : [];
		if (toList.length === 0) return { success: false, message: "Recipient (to) is required" };

		// ── 1. Company name (best-effort) ──────────────────────────────────────
		let companyName = "Your Company";
		try {
			const companyRow = await dbSAPPS.Company.findByPk(Number(company_id), {
				attributes: ["id", "name", "display_name"],
			});
			if (companyRow) companyName = companyRow.display_name || companyRow.name || companyName;
		} catch (_) { /* not critical */ }

		// ── 2. Resolve birthday employees for preview ──────────────────────────
		//    Priority:
		//    a) Caller passes specific employee_ids → fetch those employees
		//    b) Caller passes employee_names (array of strings) → use as-is
		//    c) Fallback: fetch up to 3 active employees from the company
		//    d) Last resort: placeholder names
		let birthdayEmployees = [];
		try {
			const employeeIds = Array.isArray(params.employee_ids)
				? params.employee_ids.map((id) => Number(id)).filter(Boolean)
				: params.employee_id
					? [Number(params.employee_id)]
					: [];

			if (employeeIds.length > 0) {
				// (a) Specific employees requested by the caller
				const rows = await dbSAPPS.Employee.findAll({
					where: { id: employeeIds, enabled: true },
					attributes: ["id", "employee_name"],
				});
				birthdayEmployees = rows.map((r) => ({ id: r.id, employee_name: r.employee_name }));
			} else if (Array.isArray(params.employee_names) && params.employee_names.length > 0) {
				// (b) Name strings provided directly
				birthdayEmployees = params.employee_names.map((n) => ({ employee_name: String(n) }));
			} else {
				// (c) No selection → pick up to 3 random active employees from the company
				const rows = await dbSAPPS.Employee.findAll({
					where: { company_id: Number(company_id), enabled: true },
					attributes: ["id", "employee_name"],
					order: dbSAPPS.sequelize.random(),
					limit: 3,
				});
				birthdayEmployees = rows.map((r) => ({ id: r.id, employee_name: r.employee_name }));
			}
		} catch (empError) {
			console.warn("[BirthdayTestEmail] Could not fetch employees, using placeholders:", empError.message);
		}

		// (d) Last resort fallback
		if (birthdayEmployees.length === 0) {
			birthdayEmployees = [{ employee_name: "Sample Employee A" }, { employee_name: "Sample Employee B" }];
		}

		const { build } = require("./emailTemplates/email.birthdayAnnouncement");
		const { subject, html } = build({
			company_id,
			companyName,
			birthdayEmployees,
			templateId: template_id,
			eventDate: new Date().toISOString().slice(0, 10),
		});

		// ── 3. Resolve SMTP company (company-first → system-wide fallback) ─────
		let smtpCompanyId = Number(company_id);

		// Check whether the target company has a configured, usable email account
		try {
			const ownAccount = await dbSAPPS.EmailAccount.findOne({
				where: { company_id: smtpCompanyId },
				include: [{ model: dbSAPPS.Email, as: "email", required: true }],
				attributes: ["id"],
			});

			if (!ownAccount) {
				// Fall back to the first email account that exists anywhere in the system
				const fallback = await dbSAPPS.EmailAccount.findOne({
					include: [{ model: dbSAPPS.Email, as: "email", required: true }],
					attributes: ["id", "company_id"],
				});

				if (!fallback) {
					return {
						success: false,
						message:
							"No email account is configured in the system. " +
							"Please set up at least one Email Account (Company Settings → Email Accounts) before sending test emails.",
					};
				}

				smtpCompanyId = fallback.company_id;
				console.log(
					`[BirthdayTestEmail] Company ${company_id} has no email account; using company ${smtpCompanyId} as fallback SMTP.`
				);
			}
		} catch (lookupError) {
			console.warn("[BirthdayTestEmail] Error looking up email account, proceeding with original company:", lookupError.message);
			// continue with original company_id and let sendEmailNotification surface the error
		}

		// ── 4. Send ────────────────────────────────────────────────────────────
		const result = await emailNotificationService.sendEmailNotification({
			company_id: smtpCompanyId,
			to: toList,
			subject: `[TEST] ${subject}`,
			html,
			fromName: "SAPPS Birthday Notifications (Test)",
			modified_by,
			owner,
			// No notification_id → does not touch the notifications table
		});

		return {
			...result,
			templateUsed: template_id,
			smtpCompanyId,
		};
	} catch (error) {
		console.error("Error in sendBirthdayTestEmail:", error);
		return {
			success: false,
			message: error?.message || "Failed to send birthday test email",
		};
	}
}

