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

const path = require("path");
const fs = require("fs/promises"); // lebih enak pakai promises
const redisClient = require("../../../../redisClient");

// Choose case-insensitive operator when available (Postgres: ILIKE; others: LIKE)
const LikeOp = (() => {
	try {
		return dbSAPPS?.sequelize?.getDialect?.() === "postgres" ? Op.iLike : Op.like;
	} catch {
		return Op.like;
	}
})();

module.exports = {
	filter,
	getAll,
	getPlanByTerritoryForUC,
	getCallPlanById,
	getDestinationByCallPlanId,
	realizationByPlanId,
	generateDocumentNo,
	option,
	count,
	create,
	createRealization,
	createWorkflow,
	bulkCreateVisit,
	update,
	updateRealization,
	updateWorkflow,
	enabled,
	delete: _delete,
	deleteCallRealizaion: deleteCallRealizaion,
	resetVisitPlan,
};

const stringNameMessage = "Call Plan";

// Invalidate cached list responses after any mutation
async function invalidateCallPlanCaches() {
	try {
		await redisClient.delByPrefix("cp:list:v2:");
	} catch (e) {
		// do not block main flow on cache errors
	}
}

function getAllIds(arr) {
	let ids = [];
	const safeArr = Array.isArray(arr) ? arr : [];
	safeArr.forEach((item) => {
		// Add id from current object
		ids.push(item.id);
		// If object has children, recursive call to get all ids from children
		if (item?.children && item.children?.length > 0) {
			ids = ids.concat(getAllIds(item?.children));
		}
	});
	return ids;
}

// CALL PLAN
async function getAll(params) {
	const filterConditions = {};

	// Normalize query array params that may arrive as single value, CSV string, or `territoryIds[]`
	const normalizeIdList = (raw) => {
		if (raw === null || typeof raw === "undefined") return [];
		if (typeof raw === "string") {
			const trimmed = raw.trim();
			if (!trimmed || trimmed === "null" || trimmed === "undefined") return [];
		}
		const arr = Array.isArray(raw) ? raw : [raw];
		const flattened = arr.flatMap((v) => {
			if (typeof v === "string" && v.includes(",")) return v.split(",");
			return [v];
		});
		return flattened.map((v) => parseInt(String(v).trim(), 10)).filter((n) => Number.isFinite(n) && n > 0);
	};

	const territoryIds = normalizeIdList(params?.territoryIds ?? params?.["territoryIds[]"]);

	// Server-side pagination & sorting (robust defaults)
	const safeInt = (v, def) => {
		const n = parseInt(v, 10);
		return Number.isFinite(n) && n >= 0 ? n : def;
	};
	const pageIndex = safeInt(params?.pageIndex, 0);
	const pageSize = safeInt(params?.pageSize, 10);

	const SORT_WHITELIST = {
		modified: ["DESC", "ASC"],
		from_date: ["DESC", "ASC"],
		doc_no: ["ASC", "DESC"],
	};
	const sortBy = SORT_WHITELIST[params?.sortBy] ? params.sortBy : "modified";
	const sortDirRaw = (params?.sortDir || "DESC").toUpperCase();
	const sortDir = SORT_WHITELIST[sortBy].includes(sortDirRaw) ? sortDirRaw : "DESC";

	// untuk filter default load data 2 bulan
	const startDate = moment().subtract(1, "month").startOf("month").toDate();
	const endDate = moment().add(1, "month").endOf("month").toDate();
	if (startDate && endDate) {
		filterConditions.from_date = {
			[Op.gte]: startDate, // >= tanggal 1 bulan sebelumnya
		};
		filterConditions.to_date = {
			[Op.lte]: endDate, // <= tanggal terakhir bulan berikutnya
		};
	}

	// uuntuk filter custom
	if (params.valid_from && params.valid_to) {
		filterConditions.from_date = {
			[Op.lte]: params.valid_from, // >= tanggal 1 bulan sebelumnya
		};
		filterConditions.to_date = {
			[Op.gte]: params.valid_to, // <= tanggal terakhir bulan berikutnya
		};
	}

	if (params.doc_id) {
		filterConditions.id = {
			[Op.eq]: parseInt(params.doc_id),
		};
	} else {
		if (
			params.access_read_all_data === true ||
			params.access_read_all_data === "true" ||
			params.administrator_of_msd === true ||
			params.administrator_of_msd === "true"
		) {
			filterConditions.territory_id = {
				[Op.not]: null,
			};
		} else {
			if (territoryIds.length > 0) {
				filterConditions.territory_id = {
					[Op.in]: territoryIds,
				};
			} else {
				if (params.territory_id) {
					filterConditions.territory_id = {
						[Op.eq]: parseInt(params.territory_id),
					};
				} else {
					// For non-admin reads, missing territory filter should not crash or return huge datasets.
					return {
						callPlans: [],
						totalCount: 0,
						pageIndex,
						pageSize,
						sortBy,
						sortDir,
						success: true,
						message: "No territory filter provided.",
					};
				}
			}
		}
	}

	try {
		// Simple cache key based on request parameters
		const cacheKey = `cp:list:v2:${JSON.stringify({
			doc_id: params?.doc_id || null,
			access_read_all_data: params?.access_read_all_data || false,
			administrator_of_msd: params?.administrator_of_msd || false,
			territoryIds: territoryIds.length ? territoryIds : null,
			territory_id: params?.territory_id || null,
			valid_from: params?.valid_from || null,
			valid_to: params?.valid_to || null,
			search: params?.search || null,
			f_doc_no: params?.f_doc_no || null,
			f_territory: params?.f_territory || null,
			f_period: params?.f_period || null,
			f_company: params?.f_company || null,
			pageIndex,
			pageSize,
			sortBy,
			sortDir,
		})}`;

		const cached = await redisClient.get(cacheKey);
		if (cached) {
			return cached;
		}

		// Apply global search across doc_no, company, territory, and period names
		if (params?.search && String(params.search).trim().length > 0) {
			const rawTerm = String(params.search).trim();
			const term = rawTerm.length > 100 ? rawTerm.slice(0, 100) : rawTerm;
			const orSearch = [
				{ doc_no: { [LikeOp]: `%${term}%` } },
				{ "$company.display_name$": { [LikeOp]: `%${term}%` } },
				{ "$territory.display_name$": { [LikeOp]: `%${term}%` } },
				{ "$period.display_name$": { [LikeOp]: `%${term}%` } },
			];
			const existingAnd = filterConditions[Op.and] ? filterConditions[Op.and] : [];
			filterConditions[Op.and] = [...existingAnd, { [Op.or]: orSearch }];
		}

		// Apply per-column filters (AND semantics)
		const andFilters = [];
		if (params?.f_doc_no) andFilters.push({ doc_no: { [LikeOp]: `%${String(params.f_doc_no).trim()}%` } });
		if (params?.f_company) andFilters.push({ "$company.display_name$": { [LikeOp]: `%${String(params.f_company).trim()}%` } });
		if (params?.f_territory) andFilters.push({ "$territory.display_name$": { [LikeOp]: `%${String(params.f_territory).trim()}%` } });
		if (params?.f_period) andFilters.push({ "$period.display_name$": { [LikeOp]: `%${String(params.f_period).trim()}%` } });
		if (andFilters.length) {
			const existingAnd = filterConditions[Op.and] ? filterConditions[Op.and] : [];
			filterConditions[Op.and] = [...existingAnd, ...andFilters];
		}

		const plansResult = await dbSAPPS.CallPlan.findAndCountAll({
			order: [[sortBy, sortDir]],
			where: {
				...filterConditions,
			},
			attributes: [
				"id",
				"doc_no",
				"from_date",
				"to_date",
				"doc_status",
				"company_id",
				"territory_id",
				"year_id",
				"period_id",
				"enabled",
				"creation",
				"modified",
				"owner",
			],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Territory,
					as: "territory",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Period,
					as: "period",
					attributes: ["id", "display_name"],
				},
			],
			limit: pageSize,
			offset: pageIndex * pageSize,
			distinct: true,
			subQuery: false,
		});

		// Optimize: Batch fetch workflows, callplandestinations, and callplanvisits instead of N+1
		const planIds = plansResult.rows.map((p) => p.id);
		let workflows = [];
		let workflowUpcountries = [];

		let callplandestinations = [];
		let callplanvisits = [];
		let upcountries = [];

		if (planIds.length > 0) {
			[workflows, callplandestinations, callplanvisits, upcountries] = await Promise.all([
				dbSAPPS.Workflow.findAll({
					where: {
						doc_id: { [Op.in]: planIds },
						doctype_id: 22,
					},
					attributes: ["id", "doc_id", "doctype_id", "status"],
					include: [
						{
							model: dbSAPPS.WorkflowApprover,
							as: "workflowapprovers",
							attributes: ["id", "workflow_id", "territory_id", "status"],
							include: [
								{
									model: dbSAPPS.Territory,
									as: "territory",
									attributes: ["id", "name", "display_name"],
								},
							],
						},
					],
				}),
				dbSAPPS.CallPlanDestination.findAll({
					where: { callplan_id: { [Op.in]: planIds } },
					attributes: ["client_id", "outlet_id", "callplan_id"],
					include: [
						{
							model: dbSAPPS.Client,
							as: "client",
							attributes: ["id", "display_name", "priority"],
							include: [
								{ model: dbSAPPS.ClientType, as: "clienttype", attributes: ["id", "display_name"] },
								{ model: dbSAPPS.Specialist, as: "specialist", attributes: ["id", "display_name"] },
								{ model: dbSAPPS.SubSpecialist, as: "subspecialist", attributes: ["id", "display_name"] },
								{ model: dbSAPPS.ClientSubtype, as: "clientsubtype", attributes: ["id", "display_name"] },
							],
						},
						{
							model: dbSAPPS.Outlet,
							as: "outlet",
							attributes: ["id", "display_name"],
							include: [{ model: dbSAPPS.City, as: "city", attributes: ["id", "display_name"] }],
						},
					],
				}),
				dbSAPPS.CallPlanVisit.findAll({
					where: { callplan_id: { [Op.in]: planIds } },
					attributes: ["callplan_id", "client_id", "outlet_id", "visit_date"],
					include: [
						{ model: dbSAPPS.Client, as: "client", attributes: ["id", "display_name", "priority"] },
						{ model: dbSAPPS.Outlet, as: "outlet", attributes: ["id", "display_name"] },
					],
				}),
				dbSAPPS.UpCountry.findAll({
					where: { callplan_id: { [Op.in]: planIds } },
					attributes: ["id", "doc_no", "callplan_id"],
				}),
			]);

			// After fetching upcountries, load their workflows (doctype_id: 194) using upcountry IDs
			const upCountryIds = (upcountries || []).map((u) => u.id);
			if (upCountryIds.length > 0) {
				workflowUpcountries = await dbSAPPS.Workflow.findAll({
					where: { doc_id: { [Op.in]: upCountryIds }, doctype_id: 194 },
					attributes: ["id", "doc_id", "doctype_id", "status"],
				});
			} else {
				workflowUpcountries = [];
			}
		}

		const workflowMap = {};
		(workflows || []).forEach((w) => {
			workflowMap[w.doc_id] = w;
		});

		const destinationsMap = {};
		(callplandestinations || []).forEach((d) => {
			if (!destinationsMap[d.callplan_id]) {
				destinationsMap[d.callplan_id] = [];
			}
			destinationsMap[d.callplan_id].push(d);
		});

		const visitsMap = {};
		(callplanvisits || []).forEach((v) => {
			if (!visitsMap[v.callplan_id]) {
				visitsMap[v.callplan_id] = [];
			}
			visitsMap[v.callplan_id].push(v);
		});

		const upCountryMap = {};
		(upcountries || []).forEach((u) => {
			if (!upCountryMap[u.callplan_id]) upCountryMap[u.callplan_id] = [];
			upCountryMap[u.callplan_id].push(u);
		});

		// Map upcountry workflows (doctype_id: 194) and attach to upcountry entries
		const upCountryWorkflowMap = {};
		(workflowUpcountries || []).forEach((w) => {
			upCountryWorkflowMap[w.doc_id] = w;
		});

		const data = plansResult.rows.map((item) => {
			const ucArray = upCountryMap[item.id] || [];
			const ucWithWorkflow = ucArray.map((uc) => ({
				...uc.dataValues,
				workflow: upCountryWorkflowMap[uc.id] ? upCountryWorkflowMap[uc.id].dataValues : null,
			}));

			return {
				...item.dataValues,
				workflow: workflowMap[item.id] ? workflowMap[item.id] : null,
				callplandestinations: destinationsMap[item.id] || [],
				callplanvisits: visitsMap[item.id] || [],
				upcountries: ucWithWorkflow,
			};
		});

		const result = {
			callPlans: data,
			totalCount: plansResult.count,
			pageIndex,
			pageSize,
			sortBy,
			sortDir,
			success: true,
			message: `${stringNameMessage} loaded successfully!`,
		};

		// Cache short-lived list responses to accelerate repeated views
		await redisClient.setEx(cacheKey, 30, result);
		return result;
	} catch (error) {
		const errorMessage = error.message || `Failed to load ${stringNameMessage}!`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function getCallPlanById(id) {
	try {
		const plan = await dbSAPPS.CallPlan.findOne({
			order: [["modified", "DESC"]],
			where: {
				id: id,
			},
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Territory,
					as: "territory",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Period,
					as: "period",
					attributes: ["id", "display_name"],
				},
			],
		});

		// Optimize: Parallel fetch for independent related data
		const [callplandestinations, callplanvisits, callplanrealizations, workflow, workflowCallRealization, fetchUpCountries] = await Promise.all([
			dbSAPPS.CallPlanDestination.findAll({
				where: { callplan_id: plan?.id },
				attributes: ["client_id", "outlet_id", "callplan_id"],
				include: [
					{
						model: dbSAPPS.Client,
						as: "client",
						attributes: ["id", "display_name", "priority"],
						include: [
							{ model: dbSAPPS.ClientType, as: "clienttype", attributes: ["id", "display_name"] },
							{ model: dbSAPPS.Specialist, as: "specialist", attributes: ["id", "display_name"] },
							{ model: dbSAPPS.SubSpecialist, as: "subspecialist", attributes: ["id", "display_name"] },
							{ model: dbSAPPS.ClientSubtype, as: "clientsubtype", attributes: ["id", "display_name"] },
						],
					},
					{
						model: dbSAPPS.Outlet,
						as: "outlet",
						attributes: ["id", "display_name"],
						include: [{ model: dbSAPPS.City, as: "city", attributes: ["id", "display_name"] }],
					},
				],
			}),
			dbSAPPS.CallPlanVisit.findAll({
				where: { callplan_id: plan?.id },
				attributes: ["callplan_id", "client_id", "outlet_id", "visit_date"],
				include: [
					{ model: dbSAPPS.Client, as: "client", attributes: ["id", "display_name", "priority"] },
					{ model: dbSAPPS.Outlet, as: "outlet", attributes: ["id", "display_name"] },
				],
			}),
			dbSAPPS.CallPlanRealization.findAll({
				where: { callplan_id: plan?.id },
				include: [
					{ model: dbSAPPS.Client, as: "client", attributes: ["id", "display_name", "priority"] },
					{ model: dbSAPPS.Outlet, as: "outlet", attributes: ["id", "display_name"] },
				],
			}),
			dbSAPPS.Workflow.findOne({
				where: {
					doc_id: plan?.id,
					doctype_id: 22,
				},
				attributes: ["id", "doc_id", "doctype_id", "status"],
				include: [
					{
						model: dbSAPPS.WorkflowApprover,
						as: "workflowapprovers",
						attributes: ["id", "workflow_id", "territory_id", "status"],
						include: [{ model: dbSAPPS.Territory, as: "territory", attributes: ["id", "name", "display_name"] }],
					},
				],
			}),
			// workflowCallRealization
			dbSAPPS.Workflow.findAll({
				where: {
					doc_id: plan?.id,
					doctype_id: 93,
				},
				attributes: ["id", "doc_id", "doctype_id", "status"],
				include: [
					{
						model: dbSAPPS.WorkflowApprover,
						as: "workflowapprovers",
						attributes: ["id", "workflow_id", "territory_id", "status"],
						include: [{ model: dbSAPPS.Territory, as: "territory", attributes: ["id", "name", "display_name"] }],
					},
				],
			}),
			dbSAPPS.UpCountry.findAll({
				where: { callplan_id: plan?.id },
				attributes: ["id", "doc_no", "callplan_id"],
			}),
		]);

		// Struktur untuk menyimpan klien berdasarkan prioritas untuk setiap callPlan
		const clientsByPriority = callplandestinations.reduce(
			(acc, dest) => {
				const client = dest.client;
				if (client && client.priority) {
					acc[client.priority].push(client);
				}
				return acc;
			},
			{ A: [], B: [], C: [], D: [] },
		);

		// Optimize: Batch fetch workflows for realizations
		const realizationIds = callplanrealizations.map((r) => r.id);
		let realizationWorkflows = [];
		if (realizationIds.length > 0) {
			realizationWorkflows = await dbSAPPS.Workflow.findAll({
				where: {
					doc_id: { [Op.in]: realizationIds },
					doctype_id: 117,
				},
				attributes: ["id", "doc_id", "doctype_id", "status"],
				include: [
					{
						model: dbSAPPS.WorkflowApprover,
						as: "workflowapprovers",
						attributes: ["id", "workflow_id", "territory_id", "status"],
						include: [
							{
								model: dbSAPPS.Territory,
								as: "territory",
								attributes: ["id", "name", "display_name"],
							},
						],
					},
				],
			});
		}

		const realizationWorkflowMap = {};
		realizationWorkflows.forEach((w) => {
			realizationWorkflowMap[w.doc_id] = w;
		});

		const realizationWithWorkflow = callplanrealizations.map((realization) => ({
			...realization.dataValues,
			workflowRealizationPermission: realizationWorkflowMap[realization.id] || null,
		}));

		const upCountryIds = fetchUpCountries.map((u) => u.id);
		let upCountryWorkflows = [];
		if (upCountryIds.length > 0) {
			upCountryWorkflows = await dbSAPPS.Workflow.findAll({
				where: {
					doc_id: { [Op.in]: upCountryIds },
					doctype_id: 194,
				},
				attributes: ["id", "doc_id", "doctype_id", "status"],
			});
		}

		const upCountryWorkflowMap = {};
		upCountryWorkflows.forEach((w) => {
			upCountryWorkflowMap[w.doc_id] = w;
		});

		const upcountries = fetchUpCountries.map((upcountry) => {
			return {
				...upcountry.dataValues,
				workflow: upCountryWorkflowMap[upcountry.id] ? upCountryWorkflowMap[upcountry.id].dataValues : null,
			};
		});

		return {
			...plan?.dataValues,
			callplanrealizations: realizationWithWorkflow ? realizationWithWorkflow : null,
			clientsByPriority,
			callplandestinations,
			callplanvisits,
			workflow: workflow ? workflow?.dataValues : null,
			workflowCallRealization: workflowCallRealization ? workflowCallRealization[0] : null,
			upcountries,
			success: true,
			message: `${stringNameMessage} loaded successfully!`,
		};
	} catch (error) {
		const errorMessage = error.message || `Failed to load ${stringNameMessage}!`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function getDestinationByCallPlanId(id) {
	try {
		const destinations = await dbSAPPS.CallPlanDestination.findAll({
			order: [["modified", "DESC"]],
			where: {
				callplan_id: id,
			},
			attributes: ["client_id", "outlet_id", "callplan_id"],
			include: [
				{
					model: dbSAPPS.Client,
					as: "client",
					attributes: ["id", "display_name", "priority", "clienttype_id"],
					include: [
						{
							model: dbSAPPS.ClientType,
							as: "clienttype",
							attributes: ["display_name"],
						},
						{
							model: dbSAPPS.Specialist,
							as: "specialist",
							attributes: ["id", "display_name"],
						},
						{
							model: dbSAPPS.SubSpecialist,
							as: "subspecialist",
							attributes: ["id", "display_name"],
						},
						{
							model: dbSAPPS.ClientSubtype,
							as: "clientsubtype",
							attributes: ["id", "display_name"],
						},
					],
				},
				{
					model: dbSAPPS.Outlet,
					as: "outlet",
					attributes: ["id", "display_name"],
					include: [
						{
							model: dbSAPPS.City,
							as: "city",
							attributes: ["id", "display_name"],
						},
					],
				},
			],
		});

		return {
			destinations,
			success: true,
			message: `Destination plans loaded successfully!`,
		};
	} catch (error) {
		const errorMessage = error.message || `Failed to load Destination plans!`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

		// Server-side pagination & sorting (robust defaults)
		const safeInt = (v, def) => {
			const n = parseInt(v, 10);
			return Number.isFinite(n) && n >= 0 ? n : def;
		};
		const pageIndex = safeInt(params?.pageIndex, 0);
		const pageSize = safeInt(params?.pageSize, 10);

		const SORT_WHITELIST = {
			modified: ["DESC", "ASC"],
			from_date: ["DESC", "ASC"],
			doc_no: ["ASC", "DESC"],
		};
		const sortBy = SORT_WHITELIST[params?.sortBy] ? params.sortBy : "modified";
		const sortDirRaw = (params?.sortDir || "DESC").toUpperCase();
		const sortDir = SORT_WHITELIST[sortBy].includes(sortDirRaw) ? sortDirRaw : "DESC";

		// cari period berdaarkan period_id
		if (params.period_id) {
			const periodData = await dbSAPPS.Period.findOne({
				where: { id: params.period_id },
				attributes: ["id", "start_date", "end_date", "display_name", "year_id"],
			});

			if (periodData) {
				params.period_from = periodData.start_date;
				params.period_to = periodData.end_date;
			}
		}

		let territoryVersion = [];
		let territoryVersionChilds = [];
		let territories = [];
		let rentangPeriod;
		if (params && params.territory_id && params.period_from && params.period_to) {
			rentangPeriod = await dbSAPPS.Period.findAll({
				where: {
					[Op.and]: [{ start_date: { [Op.lte]: params.period_to } }, { end_date: { [Op.gte]: params.period_from } }],
				},
			});

			if (!rentangPeriod || rentangPeriod.length === 0) {
				throw new Error("Period not found!");
			}

			const firstPeriod = rentangPeriod[0];
			const start_date = firstPeriod.start_date;
			const end_date = rentangPeriod[rentangPeriod.length - 1].end_date;

			const getTerritoryVersion = await dbSAPPS.TerritoryVersion.findAll({
				attributes: ["id", "display_name", "valid_from", "valid_to", "enabled", "company_id"],
				where: {
					[Op.and]: [
						{ valid_from: { [Op.lte]: end_date } },
						{
							[Op.or]: [{ valid_to: { [Op.gte]: start_date } }, { valid_to: null }],
						},
					],
				},
				include: [
					{
						model: dbSAPPS.TerritoryVersionChild,
						as: "territoryversionchilds",
						attributes: ["id", "name", "parent_id", "territory_id"],
					},
				],
			});

			if (getTerritoryVersion && getTerritoryVersion.length > 0) {
				territoryVersion = getTerritoryVersion;
				territoryVersionChilds = getTerritoryVersion.reduce((acc, curr) => {
					return [...acc, ...curr.territoryversionchilds];
				}, []);
			}

			const hierarchyTerritoryVersionChilds = (parent_id = null) => {
				const children = territoryVersionChilds.filter((c) => c.parent_id === parent_id);
				const hierarchy = children.map((child) => ({
					id: child?.territory_id,
					name: child?.name,
					children: hierarchyTerritoryVersionChilds(child?.territory_id),
				}));
				return hierarchy;
			};

			const territoryDatas = [
				{
					id: parseInt(params?.territory_id),
					children: hierarchyTerritoryVersionChilds(parseInt(params?.territory_id)),
				},
			];
			territories = await getAllIds(territoryDatas && territoryDatas.length > 0 ? territoryDatas : []);
		}

		if (params.id) {
			filterConditions.id = params.id;
		}

		if (params.doc_no) {
			filterConditions.doc_no = params.doc_no;
		}

		if (Array.isArray(territories) && territories.length > 0) {
			filterConditions.territory_id = {
				[Op.in]: territories,
			};
		}

		if (params.year_id) {
			filterConditions.year_id = params.year_id;
		}
		if (params.period_id) {
			filterConditions.period_id = params.period_id;
		}
		if (params.from_date) {
			filterConditions.to_date = {
				[Op.gt]: moment(params.from_date).format("YYYY-MM-DD"), // lebih besar dari from_date for uc
			};
		}

		// Apply global search across doc_no, company, territory, and period names
		if (params?.search && String(params.search).trim().length > 0) {
			const rawTerm = String(params.search).trim();
			const term = rawTerm.length > 100 ? rawTerm.slice(0, 100) : rawTerm;
			const orSearch = [
				{ doc_no: { [LikeOp]: `%${term}%` } },
				{ "$company.display_name$": { [LikeOp]: `%${term}%` } },
				{ "$territory.display_name$": { [LikeOp]: `%${term}%` } },
				{ "$period.display_name$": { [LikeOp]: `%${term}%` } },
			];
			const existingAnd = filterConditions[Op.and] ? filterConditions[Op.and] : [];
			filterConditions[Op.and] = [...existingAnd, { [Op.or]: orSearch }];
		}

		// Apply per-column filters (AND semantics)
		const andFilters2 = [];
		if (params?.f_doc_no) andFilters2.push({ doc_no: { [LikeOp]: `%${String(params.f_doc_no).trim()}%` } });
		if (params?.f_company) andFilters2.push({ "$company.display_name$": { [LikeOp]: `%${String(params.f_company).trim()}%` } });
		if (params?.f_territory) andFilters2.push({ "$territory.display_name$": { [LikeOp]: `%${String(params.f_territory).trim()}%` } });
		if (params?.f_period) andFilters2.push({ "$period.display_name$": { [LikeOp]: `%${String(params.f_period).trim()}%` } });
		if (andFilters2.length) {
			const existingAnd = filterConditions[Op.and] ? filterConditions[Op.and] : [];
			filterConditions[Op.and] = [...existingAnd, ...andFilters2];
		}

		// Note: No duplicate simple-search override; keep a single, consistent OR search as above

		const plansResult = await dbSAPPS.CallPlan.findAndCountAll({
			order: [[sortBy, sortDir]],
			where: {
				...filterConditions,
			},
			attributes: [
				"id",
				"doc_no",
				"from_date",
				"to_date",
				"doc_status",
				"company_id",
				"territory_id",
				"year_id",
				"period_id",
				"enabled",
				"creation",
				"modified",
				"owner",
			],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Territory,
					as: "territory",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Period,
					as: "period",
					attributes: ["id", "display_name"],
				},
			],
			limit: pageSize,
			offset: pageIndex * pageSize,
			distinct: true,
			subQuery: false,
		});

		// Optimize: Batch fetch workflows only to match getAll performance
		const planIds = plansResult.rows.map((p) => p.id);
		let allWorkflows = [];

		if (planIds.length > 0) {
			allWorkflows = await dbSAPPS.Workflow.findAll({
				where: {
					doc_id: { [Op.in]: planIds },
					doctype_id: 22,
				},
				attributes: ["id", "doc_id", "doctype_id", "status"],
				include: [
					{
						model: dbSAPPS.WorkflowApprover,
						as: "workflowapprovers",
						attributes: ["id", "workflow_id", "territory_id", "status"],
						include: [
							{
								model: dbSAPPS.Territory,
								as: "territory",
								attributes: ["id", "name", "display_name"],
							},
						],
					},
				],
			});
		}

		const workflowMap = {};
		allWorkflows.forEach((w) => {
			workflowMap[w.doc_id] = w;
		});

		// Map data back to call plans
		const data = plansResult.rows.map((item) => {
			const callPlanId = item.id;
			const workflow = workflowMap[callPlanId] || null;

			return {
				...item.dataValues,
				workflow,
			};
		});

		return {
			callPlans: data,
			totalCount: plansResult.count,
			pageIndex,
			pageSize,
			sortBy,
			sortDir,
			success: true,
			message: `${stringNameMessage} loaded successfully!`,
		};
	} catch (error) {
		console.error("Error retrieving Call Plans: ", error);
		const errorMessage = error.message || "Failed to retrieve Call Plans. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

		if (params.from_date) {
			filterConditions.to_date = {
				[Op.gt]: moment(params.from_date).format("YYYY-MM-DD"), // lebih besar dari from_date for uc
			};
		}

		const plans = await dbSAPPS.CallPlan.findAll({
			order: [["modified", "DESC"]],
			where: {
				...filterConditions,
			},
			attributes: [
				"id",
				"doc_no",
				"from_date",
				"to_date",
				"doc_status",
				"company_id",
				"territory_id",
				"year_id",
				"period_id",
				"enabled",
				"creation",
				"modified",
				"owner",
			],
			include: [
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Territory,
					as: "territory",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Period,
					as: "period",
					attributes: ["id", "display_name"],
				},
			],
		});

		// Struktur untuk menyimpan klien berdasarkan prioritas untuk setiap callPlan
		// Optimize: Batch fetch workflows instead of N+1
		const planIds = plans.map((p) => p.id);
		let workflows = [];
		if (planIds.length > 0) {
			workflows = await dbSAPPS.Workflow.findAll({
				where: {
					doc_id: { [Op.in]: planIds },
					doctype_id: 22,
				},
				attributes: ["id", "doc_id", "doctype_id", "status"],
				include: [
					{
						model: dbSAPPS.WorkflowApprover,
						as: "workflowapprovers",
						attributes: ["id", "workflow_id", "territory_id", "status"],
						include: [
							{
								model: dbSAPPS.Territory,
								as: "territory",
								attributes: ["id", "name", "display_name"],
							},
						],
					},
				],
			});
		}

		const workflowMap = {};
		workflows.forEach((w) => {
			workflowMap[w.doc_id] = w;
		});

		const data = plans.map((item) => {
			return {
				...item.dataValues,
				workflow: workflowMap[item.id] ? workflowMap[item.id] : null,
			};
		});

		return {
			callPlans: data,
			success: true,
			message: `${stringNameMessage} loaded successfully!`,
		};
	} catch (error) {
		console.error("Error retrieving Call Plans: ", error);
		const errorMessage = error.message || "Failed to retrieve Call Plans. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function option() {
	try {
		const callPlans = await dbSAPPS.CallPlan.findAll({
			attributes: ["id", "doc_no", "company_id", "territory_id", "period_id"],
			where: { enabled: true },
			order: [["doc_no", "ASC"]],
			include: [
				{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] },
				{ model: dbSAPPS.Territory, as: "territory", attributes: ["id", "display_name"] },
				{ model: dbSAPPS.Period, as: "period", attributes: ["id", "display_name"] },
			],
		});

		return {
			success: true,
			callPlans,
			message: "Call Plans loaded successfully!",
		};
	} catch (error) {
		console.error("Error retrieving Call Plans: ", error);
		const errorMessage = error.message || "Failed to retrieve Call Plans. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function count() {
	try {
		const count = await dbSAPPS.CallPlan.count({
			where: { enabled: true },
		});

		return {
			success: true,
			count,
			message: "Call Plans counted successfully!",
		};
	} catch (error) {
		console.error("Error counting Call Plans: ", error);
		return {
			success: false,
			message: "Failed to retrieve Call Plans count. Please try again later.",
		};
	}
}

async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();

	try {
		// Pastikan parameter yang diperlukan ada
		if (!params.doc_no || !params.territory_id || !params.year_id || !params.period_id) {
			return { success: false, message: "Name, display name, valid from, valid to & company are required!" };
		}

		// periksa apakah data dengan doc_no, territory_id, year_id, period_id sudah ada di dalam database
		const existingData = await dbSAPPS.CallPlan.findOne({
			where: {
				doc_no: params.doc_no,
				territory_id: params.territory_id,
				year_id: params.year_id,
				period_id: params.period_id,
				company_id: params.company_id,
			},
		});
		if (existingData) {
			return { success: false, message: `Call Plan with doc_no ${params.doc_no} already exists.` };
		}

		// periksa apakah params.callplandestinations lenght nya > 0 atau tidak
		if (params.callplandestinations.length === 0) {
			return { success: false, message: `Please select callplandestinations berfore saving!` };
		}

		// Jika tidak ada nilai untuk beberapa field, set default value
		if (!params.creation) params.creation = Date.now();
		if (!params.modified) params.modified = Date.now();
		if (!params.modified_by) params.modified_by = 1; // Default jika tidak ada nilai
		if (!params.owner) params.owner = 1; // Default jika tidak ada nilai

		// Buat call plan baru
		const callPlan = await dbSAPPS.CallPlan.create(params, { transaction });

		// Cek dan proses jika ada callplandestinations
		if (params.callplandestinations && params.callplandestinations.length > 0) {
			// Pastikan `callplandestinations` adalah array yang valid
			if (typeof params.callplandestinations === "string") {
				try {
					params.callplandestinations = JSON.parse(params.callplandestinations);
				} catch (err) {
					return { success: false, message: "Invalid JSON format for territoryversionchilds" };
				}
			}

			// Pastikan bahwa `callplandestinations` adalah array yang valid dan memiliki data
			if (Array.isArray(params.callplandestinations) && params.callplandestinations.length > 0) {
				// Check unique data callplandestinations outlet_id dan client_id
				const duplicates = params.callplandestinations.filter(
					(item, index) => params.callplandestinations.findIndex((d) => d.outlet_id === item.outlet_id && d.client_id === item.client_id) !== index,
				);
				if (duplicates.length > 0) {
					await transaction.rollback();
					return {
						success: false,
						message: `Duplicate entries exist in callplandestinations for display names: ${duplicates.join(", ")}. Transaction rolled back.`,
					};
				}

				// Persiapkan data anak untuk disimpan
				const callplandestinationsData = params.callplandestinations.map((destination) => ({
					...destination,
					creation: Date.now(),
					modified: Date.now(),
					modified_by: params.modified_by || 1,
					owner: params.owner || 1,
					callplan_id: callPlan.id, // Assign ID dari callPlan yang baru saja dibuat
				}));

				// Insert data anak menggunakan bulkCreate
				await dbSAPPS.CallPlanDestination.bulkCreate(callplandestinationsData, { transaction });
			}
		}

		// buat workflow approval
		if (params?.workflowTerritories) {
			if (Array.isArray(params?.workflowTerritories) && params?.workflowTerritories.length > 0) {
				const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
					{
						where: {
							doctype_id: params.doctype_id,
							type: "Territory",
							enabled: true,
						},
					},
					{ transaction },
				);
				const limitWorkflow = workflowSettings[0]?.limit || 0;
				const workflowTerritories = params?.workflowTerritories.slice(0, limitWorkflow);

				if (!workflowTerritories || workflowTerritories.length === 0) {
					await transaction.rollback();
					return {
						success: false,
						message: `Workflow Territories is required. Transaction rolled back.`,
					};
				}

				const workflowData = {
					creation: Date.now(),
					modified: Date.now(),
					modified_by: params.modified_by || 1,
					owner: params.owner || 1,
					doctype_id: params.doctype_id,
					doc_id: callPlan?.id,
					status: "Draft",
				};

				const workflow = await dbSAPPS.Workflow.create(workflowData, {
					transaction,
					lock: transaction.LOCK.UPDATE,
				});

				if (workflow) {
					const workflowApproversData = workflowTerritories?.map((workflowTerritory) => ({
						creation: Date.now(),
						modified: Date.now(),
						modified_by: params.modified_by || 1,
						owner: params.owner || 1,
						workflow_id: workflow.id,
						territory_id: workflowTerritory?.territory_id,
						status: 0,
					}));

					const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, {
						transaction,
						lock: transaction.LOCK.UPDATE,
					});
					if (
						approvers?.length !== workflowTerritories.length ||
						!approvers.every((approver) => approver.workflow_id === workflow.id && approver.territory_id !== null)
					) {
						await transaction.rollback();
						return {
							success: false,
							message: "Failed to create workflow approvers. Please try again later.",
						};
					}
				}
			}
		}

		// Commit transaksi jika semua berhasil
		await transaction.commit();
		await invalidateCallPlanCaches();
		return {
			success: true,
			message: `${stringNameMessage} has been created!`,
		};
	} catch (error) {
		// Rollback transaksi jika ada error
		await transaction.rollback();
		const errorMessage = error.message || `An error occurred during creating ${stringNameMessage}.`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function update(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const callPlan = await dbSAPPS.CallPlan.findByPk(id);
		if (!callPlan)
			return {
				success: false,
				message: `${stringNameMessage} not found!`,
			};

		// Update Call Plan
		await callPlan.update(params, { transaction });

		// Pastikan `callplandestinations` adalah array yang valid
		if (params.callplandestinations) {
			// Jika formatnya string JSON, ubah menjadi array
			if (typeof params.callplandestinations === "string") {
				try {
					params.callplandestinations = JSON.parse(params.callplandestinations);
				} catch (err) {
					return {
						success: false,
						message: "Invalid JSON format for callplandestinations",
					};
				}
			}

			// Jika `callplandestinations` adalah array dan memiliki data
			if (Array.isArray(params.callplandestinations) && params.callplandestinations.length > 0) {
				const uniqueDestinations = new Set();
				const duplicateIds = [];

				for (const destination of params.callplandestinations) {
					const identifier = `${destination.client_id}-${destination.outlet_id}`;
					if (uniqueDestinations.has(identifier)) {
						duplicateIds.push(identifier);
					} else {
						uniqueDestinations.add(identifier);
					}
				}

				if (duplicateIds.length > 0) {
					await transaction.rollback();
					return {
						success: false,
						message: `Duplicate client_id and outlet_id pairs: ${duplicateIds.join(", ")}`,
					};
				}

				// Hapus data lama terlebih dahulu
				await dbSAPPS.CallPlanDestination.destroy({ where: { callplan_id: id }, transaction });

				// Persiapkan data baru untuk disimpan
				const callplandestinationsData = params.callplandestinations.map((destination) => ({
					...destination,
					creation: Date.now(),
					modified: Date.now(),
					modified_by: params.modified_by || 1,
					owner: params.owner || 1,
					callplan_id: parseInt(id),
				}));

				// Masukkan data anak yang baru
				try {
					await dbSAPPS.CallPlanDestination.bulkCreate(callplandestinationsData, { transaction });
				} catch (err) {
					await transaction.rollback();
					return {
						success: false,
						message: `Error saving callplandestinations: ${err.message || "Unknown error"}`,
					};
				}
			} else {
				// Jika tidak ada data, hapus semua child terkait
				await dbSAPPS.CallPlanDestination.destroy({ where: { callplan_id: id }, transaction });
			}

			if (params?.workflowTerritories) {
				if (Array.isArray(params?.workflowTerritories) && params?.workflowTerritories.length > 0) {
					const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
						{
							where: {
								doctype_id: params.doctype_id,
								type: "Territory",
								enabled: true,
							},
						},
						{ transaction },
					);
					const limitWorkflow = workflowSettings[0]?.limit || 0;
					const workflowTerritories = params?.workflowTerritories.slice(0, limitWorkflow);

					if (!workflowTerritories || workflowTerritories.length === 0) {
						await transaction.rollback();
						return {
							success: false,
							message: `Workflow Territories is required. Transaction rolled back.`,
						};
					}

					const workflowData = {
						creation: Date.now(),
						modified: Date.now(),
						modified_by: params.modified_by || 1,
						owner: params.owner || 1,
						doctype_id: params.doctype_id,
						doc_id: callPlan?.id,
						status: "Draft",
					};

					// saya mau update dulu
					// Cari dulu workflow sebelum update apakah sudah ada atau belum
					const existingWorkflow = await dbSAPPS.Workflow.findOne({
						where: {
							doctype_id: workflowData.doctype_id,
							doc_id: workflowData.doc_id,
						},
						transaction,
					});

					if (existingWorkflow) {
						// Jika workflow sudah ada, update status menjadi 'Draft'
						await existingWorkflow.update(
							{
								modified: Date.now(),
								modified_by: params.modified_by || 1,
								owner: params.owner || 1,
								status: "Draft",
							},
							{ transaction, lock: transaction.LOCK.UPDATE },
						);

						await dbSAPPS.WorkflowApprover.destroy(
							{
								where: {
									workflow_id: existingWorkflow.id,
								},
							},
							{ transaction, lock: transaction.LOCK.UPDATE },
						);

						const workflowApproversData = workflowTerritories?.map((workflowTerritory) => ({
							creation: Date.now(),
							modified: Date.now(),
							modified_by: params.modified_by || 1,
							owner: params.owner || 1,
							workflow_id: existingWorkflow.id,
							territory_id: workflowTerritory?.territory_id,
							status: null,
						}));

						const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(
							workflowApproversData.map((data) => ({
								...data,
								workflow_id: existingWorkflow.id,
							})),
							{ transaction, lock: transaction.LOCK.UPDATE },
						);

						if (
							approvers?.length !== workflowTerritories.length ||
							!approvers.every((approver) => approver.workflow_id === existingWorkflow.id && approver.territory_id !== null)
						) {
							await transaction.rollback();
							return {
								success: false,
								message: "Failed to create workflow approvers. Please try again later.",
							};
						}
					} else {
						// Jika workflow belum ada, create baru
						const workflow = await dbSAPPS.Workflow.create(workflowData, {
							transaction,
							lock: transaction.LOCK.UPDATE,
						});
						if (workflow) {
							const workflowApproversData = workflowTerritories?.map((workflowTerritory) => ({
								creation: Date.now(),
								modified: Date.now(),
								modified_by: params.modified_by || 1,
								owner: params.owner || 1,
								workflow_id: workflow.id,
								territory_id: workflowTerritory?.territory_id,
								status: null,
							}));

							const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, {
								transaction,
								lock: transaction.LOCK.UPDATE,
							});
							if (
								approvers?.length !== workflowTerritories.length ||
								!approvers.every((approver) => approver.workflow_id === workflow.id && approver.territory_id !== null)
							) {
								await transaction.rollback();
								return {
									success: false,
									message: "Failed to create workflow approvers. Please try again later.",
								};
							}
						} else {
							await transaction.rollback();
							return {
								success: false,
								message: `Failed to create workflow. Please try again later.`,
							};
						}
					}
				}
			}
		}

		// Commit transaksi jika semua sukses
		await transaction.commit();
		await invalidateCallPlanCaches();
		return {
			success: true,
			message: `${stringNameMessage} has been updated!`,
		};
	} catch (error) {
		// Rollback jika terjadi error
		await transaction.rollback();
		const errorMessage = error.message || `An error occurred during updating ${stringNameMessage}.`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const callplan = await dbSAPPS.CallPlan.findByPk(id, { transaction });
		if (!callplan) return { success: false, message: "Call Plan not found!" };
		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.eq]: callplan.id,
				},
				doctype_id: {
					//  [Op.eq]: callplan.doctype_id
					[Op.eq]: 22,
				},
			},
			attributes: ["id", "doc_id", "doctype_id", "status"],
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
						},
					],
				},
			],
		});
		if (!workflows) return { success: false, message: "Workflows calplan not found!" };

		for (const workflow of workflows) {
			if (workflow) {
				await dbSAPPS.WorkflowApprover.destroy({ where: { workflow_id: workflow.id }, transaction });
				await dbSAPPS.Workflow.destroy({ where: { id: workflow.id }, transaction });
			}
		}
		await new Promise((resolve) => setTimeout(resolve, 500));
		await callplan.destroy({ transaction });

		await transaction.commit();
		await invalidateCallPlanCaches();
		return {
			success: true,
			message: `${stringNameMessage} successfully deleted!`,
		};
	} catch (error) {
		const errorMessage = error.message || `Failed to delete ${stringNameMessage}!`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function enabled(id, params) {
	try {
		const callplan = await dbSAPPS.CallPlan.findByPk(id);
		if (!callplan)
			return {
				success: false,
				message: "Call Plan not found!",
			};

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

		await callplan.update(updateData);
		await invalidateCallPlanCaches();

		return {
			success: true,
			message: "Call Plan has been updated!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to Update Call Plan.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

//  CALL PLAN VISIT
async function bulkCreateVisit(params) {
	const transaction = await dbSAPPS.sequelize.transaction();

	try {
		// Hapus data yang berkaitan dengan callplan_id dulu
		await dbSAPPS.CallPlanVisit.destroy({ where: { callplan_id: params.callplan_id }, transaction });

		// Pastikan bahwa `callplanvisit` adalah array yang valid dan memiliki data
		if (Array.isArray(params.visit_plans) && params.visit_plans.length > 0) {
			// Persiapkan data anak untuk disimpan
			const callplanvisitData = params.visit_plans.map((visitData) => ({
				...visitData,
				creation: Date.now(),
				modified: Date.now(),
				modified_by: params.modified_by || 1,
				owner: params.owner || 1,
				callplan_id: params.callplan_id,
			}));

			// Insert data menggunakan bulkCreate
			try {
				await dbSAPPS.CallPlanVisit.bulkCreate(callplanvisitData, { transaction });
			} catch (err) {
				await transaction.rollback();
				return {
					success: false,
					message: `Error saving callplan visit: ${err.message || "Unknown error"}`,
				};
			}
		}

		// Commit transaksi jika semua berhasil
		await transaction.commit();
		await invalidateCallPlanCaches();
		return {
			success: true,
			message: `${stringNameMessage} Visit has been saved!`,
		};
	} catch (error) {
		// Rollback transaksi jika ada error
		await transaction.rollback();
		const errorMessage = error.message || `An error occurred during creating ${stringNameMessage} Visit.`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function resetVisitPlan(id) {
	try {
		const data = await dbSAPPS.CallPlanVisit.destroy({
			where: { callplan_id: id },
		});

		if (data === 0) return { success: false, message: `Visit Plan not found!` };
		await invalidateCallPlanCaches();

		return {
			success: true,
			message: `Visit Plan successfully reset!`,
		};
	} catch (error) {
		const errorMessage = error.message || `Failed to reset Visit Plan!`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

// CALL REALIZATION
async function realizationByPlanId(id) {
	try {
		const realization = await dbSAPPS.CallPlanRealization.findAll({
			where: {
				callplan_id: id,
			},
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.Client,
					as: "client",
					attributes: ["id", "display_name", "priority"],
				},
				{
					model: dbSAPPS.Outlet,
					as: "outlet",
					attributes: ["id", "display_name"],
				},
			],
		});

		// Optimize: Batch fetch workflows for realizations
		const realizationIds = realization.map((r) => r.id);
		let workflows = [];
		if (realizationIds.length > 0) {
			workflows = await dbSAPPS.Workflow.findAll({
				where: {
					doc_id: { [Op.in]: realizationIds },
					doctype_id: 117,
				},
				attributes: ["id", "doc_id", "doctype_id", "status"],
				include: [
					{
						model: dbSAPPS.WorkflowApprover,
						as: "workflowapprovers",
						attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
						include: [
							{
								model: dbSAPPS.Designation,
								as: "designation",
								attributes: ["id", "name", "display_name"],
							},
							{
								model: dbSAPPS.Territory,
								as: "territory",
								attributes: ["id", "name", "display_name"],
							},
						],
					},
				],
			});
		}

		// mendapatkan semua data realisasi status
		const realizationData = realization?.map((item) => {
			// if (item.data_source !== "up_country") {
			const clientDisplayName = item?.client ? item?.client?.display_name : "Unknown or Select your client";
			const clientPriority = item?.client ? item?.client?.priority : "Unknown Priority";
			const outletDisplayName = item.outlet ? item.outlet.display_name : "Unknown or Select Outlet";

			return {
				...item.toJSON(),
				title: `${clientDisplayName} (${clientPriority}) - ${outletDisplayName} (${item?.realization_status})`,
				start: moment(item.start_date).format("YYYY-MM-DD"),
				color: item.color,
				action: item.action ? item.action.split(", ") : [],
			};
		});

		// mendapatkan data realisasi permission
		const permissions = realization?.filter((item) => item.realization_status === "P");
		const permissionData = permissions?.map((item) => {
			const workflowPermission = workflows.find((workflow) => workflow.doc_id === item.id && workflow.doctype_id === 117);

			return {
				...item.toJSON(),
				display: "background",
				title: item.note,
				start: moment(item.start_date).format("YYYY-MM-DD"),
				end: moment(item.end_date).format("YYYY-MM-DD"),
				workflow: workflowPermission,
			};
		});

		// mendapatkan data yang bukan realisasi permission
		const onlyRealizationData = realizationData?.filter((item) => item.realization_status !== "P");
		const notFromPlanData = realizationData?.filter((item) => item.data_source === "not_from_plan");

		return {
			realizationData,
			onlyRealizationData,
			notFromPlanData,
			permissionData,
			success: true,
			message: `realization loaded successfully!`,
		};
	} catch (error) {
		const errorMessage = error.message || `Failed to load ${stringNameMessage}!`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function createRealization(params) {
	const transaction = await dbSAPPS.sequelize.transaction();

	try {
		// Pastikan parameter yang diperlukan ada
		if (!params.callplan_id) return { success: false, message: "Please make sure document call plan is created!" };

		// periksa apakah data dengan doc_no, territory_id, year_id, period_id sudah ada di dalam database untuk call realisasi biasa
		if (params?.realization_status !== "P" && params?.realization_status !== "UC" && params?.client_id && params?.outlet_id) {
			const existingData = await dbSAPPS.CallPlanRealization.findOne({
				where: {
					client_id: params.client_id,
					outlet_id: params.outlet_id,
					start_date: params.start_date,
					callplan_id: params.callplan_id,
				},
			});
			if (existingData) {
				return { success: false, message: "This client and outlet data already exists" };
			}
		}

		if (params?.realization_status === "P") {
			if (!params?.note) {
				return { success: false, message: "Please make sure provide reason note!" };
			}

			const existingData = await dbSAPPS.CallPlanRealization.findOne({
				where: { start_date: params.start_date, callplan_id: params.callplan_id, realization_status: "P" },
			});
			if (existingData) {
				return { success: false, message: "This permission data already exists" };
			}
		}

		if (params?.realization_status === "UC") {
			const existingData = await dbSAPPS.CallPlanRealization.findOne({
				where: {
					start_date: params.start_date,
					callplan_id: params.callplan_id,
					realization_status: "UC",
					uc_destination: params.uc_destination,
				},
			});
			if (existingData) {
				return { success: false, message: "This Up Country data already exists" };
			}
		}

		// Jika tidak ada nilai untuk beberapa field, set default value
		if (!params.creation) params.creation = Date.now();
		if (!params.modified) params.modified = Date.now();
		if (!params.modified_by) params.modified_by = 1; // Default jika tidak ada nilai
		if (!params.owner) params.owner = 1; // Default jika tidak ada nilai

		// Buat call realization baru
		const callRealization = await dbSAPPS.CallPlanRealization.create(params, { transaction });

		// jika call realization permission / realization status === P buat workflow
		if (params?.realization_status === "P") {
			if (Array.isArray(params?.workflowTerritories) && params?.workflowTerritories.length > 0) {
				const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
					{
						where: {
							doctype_id: params.doctype_id,
							type: "Territory",
							enabled: true,
						},
					},
					{ transaction },
				);
				const limitWorkflow = workflowSettings[0]?.limit || 0;
				const workflowTerritories = params?.workflowTerritories.slice(0, limitWorkflow);

				if (!workflowTerritories || workflowTerritories.length === 0) {
					await transaction.rollback();
					return {
						success: false,
						message: `Workflow Territories is required or Workflow Settings not found!. Transaction rolled back.`,
					};
				}

				const workflowData = {
					creation: Date.now(),
					modified: Date.now(),
					modified_by: params.modified_by || 1,
					owner: params.owner || 1,
					doctype_id: params.doctype_id,
					doc_id: callRealization?.id,
					status: "Need Approval",
				};

				const workflow = await dbSAPPS.Workflow.create(workflowData, { transaction, lock: transaction.LOCK.UPDATE });

				if (workflow) {
					const workflowApproversData = workflowTerritories?.map((workflowTerritory) => ({
						creation: Date.now(),
						modified: Date.now(),
						modified_by: params.modified_by || 1,
						owner: params.owner || 1,
						workflow_id: workflow.id,
						territory_id: workflowTerritory?.territory_id,
						status: "Need Approval",
					}));

					const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, {
						transaction,
						lock: transaction.LOCK.UPDATE,
					});
					if (
						approvers?.length !== workflowTerritories.length ||
						!approvers.every((approver) => approver.workflow_id === workflow.id && approver.territory_id !== null)
					) {
						await transaction.rollback();
						return {
							success: false,
							message: "Failed to create workflow approvers. Please try again later.",
						};
					}
				}
			}
		}

		// Commit transaksi jika semua berhasil
		await transaction.commit();
		await invalidateCallPlanCaches();

		return {
			success: true,
			message: `Realization has been created!`,
		};
	} catch (error) {
		// Rollback transaksi jika ada error
		await transaction.rollback();
		const errorMessage = error.message || `An error occurred during creating realization.`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function updateRealization(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const realization = await dbSAPPS.CallPlanRealization.findByPk(id);
		if (!realization)
			return {
				success: false,
				message: `Realization not found!`,
			};

		if (params?.realization_status !== "P" && params?.realization_status !== "UC" && params?.client_id && params?.outlet_id) {
			const existingData = await dbSAPPS.CallPlanRealization.findOne({
				where: {
					client_id: params.client_id,
					outlet_id: params.outlet_id,
					start_date: params.start_date,
					callplan_id: params.callplan_id,
				},
			});
			if (existingData) {
				return { success: false, message: "This client and outlet data already exists" };
			}
		}

		if (params.filename && realization.filename && params.filename !== realization.filename) {
			const oldFilePath = path.resolve(__dirname, "../../../../public/data/images/visitverify/", realization.filename);
			try {
				await fs.unlink(oldFilePath);
				console.warn("Deleted old file:", oldFilePath);
			} catch (err) {
				console.warn("Gagal hapus file lama:", err.message);
			}
		}

		// Update Call Realization
		await realization.update(params, { transaction });

		// Commit transaksi jika semua sukses
		await transaction.commit();
		await invalidateCallPlanCaches();
		return {
			success: true,
			message: `Realization has been updated!`,
		};
	} catch (error) {
		// Rollback jika terjadi error
		await transaction.rollback();
		const errorMessage = error.message || `An error occurred during updating realization.`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function deleteCallRealizaion(id) {
	try {
		const transaction = await dbSAPPS.sequelize.transaction();
		const callRealization = await dbSAPPS.CallPlanRealization.findByPk(id);
		if (!callRealization) return { success: false, message: `Realization not found!` };

		if (callRealization.filename) {
			const filePath = path.resolve(__dirname, "../../../../public/data/images/visitverify/", callRealization.filename);
			try {
				await fs.unlink(filePath);
				console.warn("Deleted file:", filePath);
			} catch (err) {
				console.warn("Gagal hapus file:", err.message);
			}
		}

		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.eq]: callRealization.id,
				},
				doctype_id: { [Op.in]: [117, 93] },
			},
			attributes: ["id", "doc_id", "doctype_id", "status"],
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
						},
					],
				},
			],
		});
		if (!workflows) return { success: false, message: "Workflows realization not found!" };

		for (const workflow of workflows) {
			if (workflow) {
				await dbSAPPS.WorkflowApprover.destroy({ where: { workflow_id: workflow.id }, transaction });
				await dbSAPPS.Workflow.destroy({ where: { id: workflow.id }, transaction });
			}
		}
		await new Promise((resolve) => setTimeout(resolve, 500));

		await callRealization.destroy({ transaction });
		await transaction.commit();
		await invalidateCallPlanCaches();

		return {
			success: true,
			message: `Realization successfully deleted!`,
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || `Failed to delete Realization!`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function generateDocumentNo(params) {
	try {
		const p = params.params;
		const documentNo = "CPR-" + p.territory + "-" + p.period_name;

		const lastDocumentNo = await dbSAPPS.CallPlan.findOne({
			where: {
				doc_no: {
					[Op.like]: `%${documentNo}%`,
				},
			},
		});

		if (lastDocumentNo) {
			throw new Error("Document No already exists!");
		}

		return {
			success: true,
			documentNo: documentNo,
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to generate document no.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

// buat call realization
async function createWorkflow(params) {
	const transaction = await dbSAPPS.sequelize.transaction();

	try {
		// Pastikan parameter yang diperlukan ada
		if (!params.id) return { success: false, message: "Please make sure document call plan is created!" };

		const callPlan = await dbSAPPS.CallPlan.findByPk(params.id);
		if (!callPlan) return { success: false, message: "Call plan not found!" };

		if (params.workflowTerritories) {
			if (Array.isArray(params.workflowTerritories) && params.workflowTerritories.length > 0) {
				const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
					{
						where: {
							doctype_id: params.doctype_id,
							type: "Territory",
							enabled: true,
						},
					},
					{ transaction },
				);
				const limitWorkflow = workflowSettings[0]?.limit || 0;
				const workflowTerritories = params.workflowTerritories.slice(0, limitWorkflow);

				const workflowData = {
					creation: Date.now(),
					modified: Date.now(),
					modified_by: params.modified_by || 1,
					owner: params.owner || 1,
					doctype_id: params.doctype_id,
					doc_id: callPlan.id,
					status: "Need Approval",
				};

				const workflow = await dbSAPPS.Workflow.create(workflowData, { transaction });
				if (workflow) {
					try {
						const workflowApproversData = workflowTerritories.map((wf) => ({
							creation: Date.now(),
							modified: Date.now(),
							modified_by: params.modified_by || 1,
							owner: params.owner || 1,
							workflow_id: workflow.id,
							territory_id: wf.territory_id,
							status: "Need Approval",
						}));
						await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, { transaction });
					} catch (error) {
						await transaction.rollback();
						throw error; // Propagate the error to be handled by the calling function
					}
				}
			}
		}

		await transaction.commit();
		await invalidateCallPlanCaches();
		return {
			success: true,
			message: `Workflow has been created!`,
		};
	} catch (error) {
		// Rollback transaksi jika ada error
		await transaction.rollback();
		const errorMessage = error.message || `An error occurred during creating workflow.`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function updateWorkflow(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();

	try {
		const workflow = await dbSAPPS.Workflow.findOne(
			{
				where: { id: id },
			},
			{ transaction },
		);

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

		await workflow.update(
			{
				modified: Date.now(),
				modified_by: params.modified_by || 1,
				owner: params.owner || 1,
				status: params.status,
				doctype_id: params.doctype_id,
			},
			{ transaction },
		);

		if (params.workflowTerritories) {
			if (Array.isArray(params.workflowTerritories) && params.workflowTerritories.length > 0) {
				await dbSAPPS.WorkflowApprover.destroy({ where: { workflow_id: id }, transaction });

				const workflowApproversData = params.workflowTerritories.map((wf) => ({
					creation: Date.now(),
					modified: Date.now(),
					modified_by: params.modified_by || 1,
					owner: params.owner || 1,
					workflow_id: id,
					territory_id: wf.territory_id,
					status: params.status,
				}));
				await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, { transaction });
			}
		} else if (params.status != null && params.status !== undefined) {
			// Saat hanya update status (tanpa kirim workflowTerritories), sinkronkan status ke semua WorkflowApprover
			await dbSAPPS.WorkflowApprover.update(
				{
					status: params.status,
					modified: Date.now(),
					modified_by: params.modified_by || 1,
				},
				{ where: { workflow_id: id }, transaction },
			);
		}

		await transaction.commit();
		await invalidateCallPlanCaches();
		return {
			success: true,
			message: `Workflow has been updated!`,
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || `An error occurred during updating workflow.`;
		return {
			success: false,
			message: errorMessage,
		};
	}
}
