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

module.exports = {
	create,
	getAll,
	option,
	getEmailsForGroup,
	update,
	updateEnabled,
	delete: _delete,
};

/**
 * Resolve email: prioritize company_email, fallback to personal_email.
 * Returns null if both are empty/null/whitespace.
 * Only processes enabled employees (filtered at query level).
 */
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() : "";
	// Prioritize company_email if it exists and is non-empty
	if (ce && ce.length > 0) return ce;
	// Fallback to personal_email if it exists and is non-empty
	if (pe && pe.length > 0) return pe;
	return null;
}

/**
 * Collect all employee ids for a group (recursive for aggregate groups; guards against circular refs).
 */
async function collectEmployeeIdsForGroup(groupId, visited = new Set()) {
	if (visited.has(Number(groupId))) return [];
	visited.add(Number(groupId));

	const group = await dbSAPPS.EmailGroup.findByPk(groupId, {
		attributes: ["id", "is_aggregate", "aggregate_of_group_ids"],
	});
	if (!group) return [];

	if (group.is_aggregate && Array.isArray(group.aggregate_of_group_ids) && group.aggregate_of_group_ids.length > 0) {
		const fromChildren = await Promise.all(group.aggregate_of_group_ids.map((gid) => collectEmployeeIdsForGroup(gid, visited)));
		return [...new Set(fromChildren.flat())];
	}

	const members = await dbSAPPS.EmailGroupMember.findAll({
		where: { email_group_id: groupId },
		attributes: ["employee_id"],
	});
	return members.map((m) => m.employee_id);
}

/**
 * Get list of resolved emails for a group (for copy/paste).
 * - Only employees with enabled=true.
 * - If group is aggregate, union member employee ids from child groups (recursive).
 * - Uses group's company_id if params.company_id is not provided (more robust).
 */
async function getEmailsForGroup(groupId, params = {}) {
	try {
		const group = await dbSAPPS.EmailGroup.findByPk(groupId, {
			include: [{ model: dbSAPPS.Company, as: "company", attributes: ["id", "name", "display_name"] }],
		});
		if (!group) return { success: false, message: "Group not found.", emails: [] };

		// Use company_id from params if provided, otherwise use group's company_id (more robust)
		const effectiveCompanyId = params.company_id ?? group.company_id;

		const employeeIds = [...new Set(await collectEmployeeIdsForGroup(groupId))];

		if (employeeIds.length === 0) {
			return {
				success: true,
				emails: [],
				group_name: group.display_name,
				message: "No members in this group.",
			};
		}

		// Build where clause: always filter by enabled=true, optionally by company_id
		const whereClause = {
			id: { [Op.in]: employeeIds },
			enabled: true,
		};
		if (effectiveCompanyId) {
			whereClause.company_id = effectiveCompanyId;
		}

		const employees = await dbSAPPS.Employee.findAll({
			where: whereClause,
			attributes: ["id", "employee_name", "personal_email", "company_email", "company_id", "enabled"],
		});

		// Resolve emails: prioritize company_email, fallback to personal_email
		const emails = [];
		const seen = new Set();
		for (const emp of employees) {
			const email = resolveEmployeeEmail(emp);
			if (email && !seen.has(email.toLowerCase())) {
				seen.add(email.toLowerCase());
				emails.push({ email, employee_name: emp.employee_name });
			}
		}

		// If no emails resolved, provide helpful message
		if (emails.length === 0) {
			return {
				success: true,
				emails: [],
				group_name: group.display_name,
				message: `Found ${employees.length} enabled employee(s) in group, but none have valid email addresses (company_email or personal_email).`,
			};
		}

		return {
			success: true,
			emails,
			group_name: group.display_name,
			email_list: emails.map((e) => e.email).join(", "),
		};
	} catch (error) {
		return {
			success: false,
			message: error?.message || "Failed to get emails.",
			emails: [],
		};
	}
}

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

async function getAll(params) {
	try {
		const filterConditions = {};
		if (params?.companiesId && params.companiesId.length) {
			// ROBUST: include global groups (company_id IS NULL) as valid options
			filterConditions[Op.or] = [{ company_id: { [Op.in]: params.companiesId } }, { company_id: null }];
		}

		let list = await dbSAPPS.EmailGroup.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{ model: dbSAPPS.Company, as: "company", attributes: ["id", "name", "display_name"] },
				{
					model: dbSAPPS.EmailGroupMember,
					as: "emailgroupmember",
					include: [{ model: dbSAPPS.Employee, as: "employee", attributes: ["id", "employee_name", "company_email", "personal_email"] }],
				},
			],
		});

		list = list.map((g) => ({
			...g.dataValues,
			company_name: g?.company?.display_name,
			members: (g?.emailgroupmember || []).map((m) => ({
				employee_id: m.employee_id,
				employee_name: m?.employee?.employee_name,
			})),
		}));
		list = await formatWithModifier(list);

		return { success: true, emailGroups: list, message: "Loaded successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load." };
	}
}

async function option(params) {
	try {
		const filterConditions = {};
		if (params?.companiesId && params.companiesId.length) {
			// ROBUST: include global groups (company_id IS NULL) as valid options
			filterConditions[Op.or] = [{ company_id: { [Op.in]: params.companiesId } }, { company_id: null }];
		}

		let options = await dbSAPPS.EmailGroup.findAll({
			attributes: ["id", "name", "display_name", "company_id", "is_aggregate", "aggregate_of_group_ids"],
			where: filterConditions,
			order: [["display_name", "ASC"]],
			include: [{ model: dbSAPPS.Company, as: "company", attributes: ["id", "name", "display_name"] }],
		});

		options = options.map((o) => ({
			...o.dataValues,
			company_name: o?.company?.display_name,
		}));

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

async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const existing = await dbSAPPS.EmailGroup.findOne({
			where: { display_name: params.display_name, company_id: params.company_id || null },
			transaction,
		});
		if (existing) {
			return { success: false, message: `"${params.display_name}" already exists for this company.` };
		}

		if (params.is_aggregate === true) {
			const ids = Array.isArray(params.aggregate_of_group_ids) ? params.aggregate_of_group_ids : [];
			if (ids.length === 0) {
				return { success: false, message: "Aggregate group must have at least one source group." };
			}
		}

		const emailGroup = await dbSAPPS.EmailGroup.create(
			{
				...params,
				enabled: params.enabled !== false,
				is_aggregate: params.is_aggregate === true,
				aggregate_of_group_ids: params.is_aggregate && Array.isArray(params.aggregate_of_group_ids) ? params.aggregate_of_group_ids : null,
			},
			{ transaction },
		);

		if (!params.is_aggregate && Array.isArray(params.members) && params.members.length > 0) {
			const membersData = params.members.map((m) => ({
				email_group_id: emailGroup.id,
				employee_id: m.employee_id,
				company_id: params.company_id,
				creation: params.creation,
				modified: params.modified,
				modified_by: params.modified_by,
				owner: params.owner,
			}));
			await dbSAPPS.EmailGroupMember.bulkCreate(membersData, { transaction });
		}

		await transaction.commit();
		return { success: true, message: "Saved successfully!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to save." };
	}
}

async function update(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const emailGroup = await dbSAPPS.EmailGroup.findByPk(id);
		if (!emailGroup) return { success: false, message: "Group not found." };

		const existing = await dbSAPPS.EmailGroup.findOne({
			where: {
				display_name: params.display_name,
				company_id: params.company_id ?? emailGroup.company_id,
				id: { [Op.ne]: id },
			},
			transaction,
		});
		if (existing) {
			return { success: false, message: `"${params.display_name}" already exists for this company.` };
		}

		const isAggregate = params.is_aggregate !== undefined ? params.is_aggregate : emailGroup.is_aggregate;
		let aggregateIds = emailGroup.aggregate_of_group_ids;
		if (params.aggregate_of_group_ids !== undefined && isAggregate && Array.isArray(params.aggregate_of_group_ids)) {
			aggregateIds = params.aggregate_of_group_ids.filter((gid) => Number(gid) !== Number(id));
			if (params.aggregate_of_group_ids.length !== aggregateIds.length) {
				return { success: false, message: "An aggregate group cannot include itself." };
			}
		}

		await emailGroup.update(
			{
				...params,
				enabled: params.enabled !== undefined ? params.enabled : emailGroup.enabled,
				is_aggregate: isAggregate,
				aggregate_of_group_ids:
					params.aggregate_of_group_ids !== undefined
						? isAggregate && Array.isArray(params.aggregate_of_group_ids)
							? aggregateIds
							: null
						: emailGroup.aggregate_of_group_ids,
			},
			{ transaction },
		);

		if (isAggregate) {
			await dbSAPPS.EmailGroupMember.destroy({ where: { email_group_id: id }, transaction });
		} else {
			await dbSAPPS.EmailGroupMember.destroy({ where: { email_group_id: id }, transaction });
			if (Array.isArray(params.members) && params.members.length > 0) {
				await dbSAPPS.EmailGroupMember.bulkCreate(
					params.members.map((m) => ({
						email_group_id: parseInt(id, 10),
						employee_id: m.employee_id,
						company_id: params.company_id ?? emailGroup.company_id,
						creation: emailGroup.creation,
						modified: params.modified,
						modified_by: params.modified_by,
						owner: emailGroup.owner,
					})),
					{ transaction },
				);
			}
		}

		await transaction.commit();
		return { success: true, message: "Saved successfully!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to save." };
	}
}

async function updateEnabled(id, params) {
	try {
		const emailGroup = await dbSAPPS.EmailGroup.findByPk(id);
		if (!emailGroup) return { success: false, message: "Group not found." };
		const enabled = params.enabled === true || params.enabled === "true" || params.enabled === 1;
		await emailGroup.update({
			enabled,
			modified: params.modified,
			modified_by: params.modified_by,
		});
		return { success: true, message: "Updated successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to update." };
	}
}

async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const emailGroup = await dbSAPPS.EmailGroup.findByPk(id);
		if (!emailGroup) return { success: false, message: "Group not found." };

		await dbSAPPS.EmailGroupMember.destroy({ where: { email_group_id: id }, transaction });
		await emailGroup.destroy({ transaction });
		await transaction.commit();
		return { success: true, message: "Deleted successfully!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to delete." };
	}
}
