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

module.exports = {
	getAll,
	filter,
	filterByYear,
	option,
	count,
	create,
	update,
	enabled,
	delete: _delete,
};

// Helper
// Alternative dengan Raw Query untuk performa maksimal
const generateNextNCRaw = async (transaction) => {
	const now = new Date();
	const currentMonth = now.getMonth() + 1;
	const currentYear = now.getFullYear();
	const month = currentMonth.toString().padStart(2, "0");

	const currentPrefix = `NC.${currentYear}${month}`;
	const yearPrefix = `NC.${currentYear}`;

	// Raw query untuk mendapatkan nomor urut terakhir dalam tahun ini
	const [results] = await dbSAPPS.sequelize.query(
		`
        SELECT nc_no 
        FROM ncproducts 
        WHERE nc_no LIKE :yearPrefix 
        ORDER BY CAST(RIGHT(nc_no, 4) AS UNSIGNED) DESC 
        LIMIT 1 
        FOR UPDATE
    `,
		{
			replacements: { yearPrefix: `${yearPrefix}%` },
			type: dbSAPPS.sequelize.QueryTypes.SELECT,
			transaction,
		}
	);

	let nextNumber = 1;

	if (results && results.nc_no) {
		// Extract nomor urut dari 4 digit terakhir
		const lastFourDigits = results.nc_no.slice(-4);
		const currentNumber = parseInt(lastFourDigits, 10);

		if (!isNaN(currentNumber)) {
			nextNumber = currentNumber + 1;
		}
	}

	// Format dengan padding 0 di depan
	const formattedNumber = nextNumber.toString().padStart(4, "0");

	return `${currentPrefix}${formattedNumber}`;
};

// Versi dengan optimasi untuk database besar
const generateNextNCOptimized = async (transaction) => {
	const now = new Date();
	const currentMonth = now.getMonth() + 1;
	const currentYear = now.getFullYear();
	const month = currentMonth.toString().padStart(2, "0");

	const currentPrefix = `NC.${currentYear}${month}`;
	const yearPrefix = `NC.${currentYear}`;

	// Gunakan MAX function untuk mendapatkan nomor urut tertinggi
	const [results] = await dbSAPPS.sequelize.query(
		`
        SELECT MAX(CAST(RIGHT(nc_no, 4) AS UNSIGNED)) as max_number
        FROM ncproducts 
        WHERE nc_no LIKE :yearPrefix 
        AND LENGTH(nc_no) = 12
        FOR UPDATE
    `,
		{
			replacements: { yearPrefix: `${yearPrefix}%` },
			type: dbSAPPS.sequelize.QueryTypes.SELECT,
			transaction,
		}
	);

	let nextNumber = 1;

	if (results && results.max_number !== null) {
		nextNumber = parseInt(results.max_number, 10) + 1;
	}

	// Format dengan padding 0 di depan
	const formattedNumber = nextNumber.toString().padStart(4, "0");

	return `${currentPrefix}${formattedNumber}`;
};

// Helper functions tetap sama...
async function processNCProductChildren(ncproductchilds, ncproductId, params, transaction) {
	try {
		let childrenData = typeof ncproductchilds === "string" ? JSON.parse(ncproductchilds) : ncproductchilds;

		if (!Array.isArray(childrenData) || childrenData.length === 0) {
			return;
		}

		const ncProductsChildData = childrenData.map((child, index) => {
			if (!child.product_name) {
				throw new Error(`Missing product_name in child item at index ${index}`);
			}

			return {
				...child,
				creation: Date.now(),
				modified: Date.now(),
				modified_by: params.modified_by,
				owner: params.owner,
				ncproduct_id: ncproductId,
			};
		});

		await dbSAPPS.NcProductChild.bulkCreate(ncProductsChildData, {
			transaction,
		});
	} catch (error) {
		throw new Error(`Failed to create NC product children: ${error.message}`);
	}
}

async function processNCProductFUs(ncproductfus, ncproductId, params, transaction) {
	try {
		let fuData = typeof ncproductfus === "string" ? JSON.parse(ncproductfus) : ncproductfus;

		if (!Array.isArray(fuData) || fuData.length === 0) {
			return;
		}

		const ncProductsFUData = fuData.map((fu, index) => {
			if (!fu.description) {
				throw new Error(`Missing description in FU item at index ${index}`);
			}

			return {
				...fu,
				creation: Date.now(),
				modified: Date.now(),
				modified_by: params.modified_by,
				owner: params.owner,
				ncproduct_id: ncproductId,
			};
		});

		await dbSAPPS.NcProductFU.bulkCreate(ncProductsFUData, {
			transaction,
		});
	} catch (error) {
		throw new Error(`Failed to create NC product FUs: ${error.message}`);
	}
}

async function processWorkflow(params, ncproductId, transaction) {
	try {
		if (params.workflowterritories && params.workflowterritories.length > 0) {
			await processWorkflowTerritories(params, ncproductId, transaction);
		} else if (params.workflowdesignations && params.workflowdesignations.length > 0) {
			await processWorkflowDesignations(params, ncproductId, transaction);
		}
	} catch (error) {
		throw new Error(`Failed to process workflow: ${error.message}`);
	}
}

async function processWorkflowTerritories(params, ncproductId, transaction) {
	let workflowterritories = typeof params.workflowterritories === "string" ? JSON.parse(params.workflowterritories) : params.workflowterritories;

	if (!Array.isArray(workflowterritories) || workflowterritories.length === 0) {
		return;
	}

	const workflowSettings = await dbSAPPS.WorkflowSetting.findOne({
		where: {
			doctype_id: parseInt(params.doctype_id),
			enabled: true,
		},
		transaction,
	});

	const limit = workflowSettings?.limit || workflowterritories.length;
	const limitedTerritories = workflowterritories.slice(0, limit);

	if (limitedTerritories.length === 0) {
		throw new Error("You must have at least one workflow territory.");
	}

	const workflow = await createWorkflow(params, ncproductId, transaction);

	const workflowApproversData = limitedTerritories.map((territory) => ({
		creation: Date.now(),
		modified: Date.now(),
		modified_by: parseInt(params.modified_by) || 1,
		owner: parseInt(params.owner) || 1,
		workflow_id: workflow.id,
		territory_id: parseInt(territory.territory_id),
		status: null,
	}));

	await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, {
		transaction,
	});
}

async function processWorkflowDesignations(params, ncproductId, transaction) {
	let workflowdesignations = typeof params.workflowdesignations === "string" ? JSON.parse(params.workflowdesignations) : params.workflowdesignations;

	if (!Array.isArray(workflowdesignations) || workflowdesignations.length === 0) {
		return;
	}

	const workflowSettings = await dbSAPPS.WorkflowSetting.findOne({
		where: {
			doctype_id: parseInt(params.doctype_id),
			type: "Designation",
			enabled: true,
		},
		transaction,
	});

	const limit = workflowSettings?.limit || workflowdesignations.length;
	const limitedDesignations = workflowdesignations.slice(0, limit);

	if (limitedDesignations.length === 0) {
		throw new Error("You must have at least one workflow designation.");
	}

	const workflow = await createWorkflow(params, ncproductId, transaction);

	const workflowApproversData = limitedDesignations.map((designation) => ({
		creation: Date.now(),
		modified: Date.now(),
		modified_by: parseInt(params.modified_by) || 1,
		owner: parseInt(params.owner) || 1,
		workflow_id: workflow.id,
		designation_id: parseInt(designation.designation_id),
		status: null,
	}));

	await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, {
		transaction,
	});
}

async function createWorkflow(params, ncproductId, transaction) {
	const workflowData = {
		creation: Date.now(),
		modified: Date.now(),
		modified_by: parseInt(params.modified_by) || 1,
		owner: parseInt(params.owner) || 1,
		doctype_id: parseInt(params.doctype_id),
		doc_id: ncproductId,
		status: "Draft",
	};

	return await dbSAPPS.Workflow.create(workflowData, {
		transaction,
	});
}

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

	if (params.doc_id) {
		filterConditions.id = params.doc_id;
	}
	try {
		const ncProducts = await dbSAPPS.NcProduct.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.NcProductChild,
					as: "ncproductchilds",
					attributes: ["id", "product_name", "quantity", "uom", "batch_no", "expiry_date", "noncomformity", "description", "status", "status_date"],
				},
				{
					model: dbSAPPS.NcProductFU,
					as: "ncproductfus",
					attributes: ["id", "date", "description", "pic_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
						},
					],
				},
				{
					model: dbSAPPS.User,
					as: "user",
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
							include: [
								{
									model: dbSAPPS.Designation,
									as: "designation",
									attributes: ["display_name"],
								},
							],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name", "name"],
				},
			],
		});

		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.in]: ncProducts.map((item) => item.id),
				},
				doctype_id: 149,
			},
			attributes: ["id", "doc_id", "doctype_id", "status", "modified_by", "modified"],
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: ["id", "workflow_id", "designation_id", "territory_id", "status", "modified"],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["id", "employee_name"],
								},
							],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employees",
									attributes: ["id", "employee_name"],
									include: [
										{
											model: dbSAPPS.Designation,
											as: "designation",
											attributes: ["display_name"],
										},
									],
								},
							],
						},
					],
				},
			],
		});

		// Get comments
		const comments = await dbSAPPS.Comment.findAll({
			where: {
				doc_id: {
					[Op.in]: ncProducts.map((item) => item.id),
				},
				doctype_id: 149,
			},
			order: [["creation", "DESC"]],
			attributes: ["id", "doc_id", "doctype_id", "edited", "comment", "modified_by", "modified", "owner"],
			include: [
				{
					model: dbSAPPS.User,
					as: "user",
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
							include: [
								{
									model: dbSAPPS.Designation,
									as: "designation",
									attributes: ["display_name"],
								},
							],
						},
					],
				},
			],
		});

		const data = ncProducts.map((item) => {
			const workflow = workflows.find((wf) => wf.doc_id === item.id);
			const comment = comments.filter((cmt) => cmt.doc_id === item.id);
			return {
				...item.dataValues,
				workflow: workflow ? workflow.dataValues : null,
				comments: comment ? comment.map((cmt) => cmt.dataValues) : null,
			};
		});

		return {
			ncProducts: data,
			success: true,
			message: "loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error || "Failed to load";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

	if (params.enabled) {
		filterConditions.enabled = {
			[Op.eq]: params.enabled === "1" ? true : false,
		};
	}

	try {
		const ncProducts = await dbSAPPS.NcProduct.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.NcProductChild,
					as: "ncproductchilds",
					attributes: ["id", "product_name", "quantity", "uom", "batch_no", "expiry_date", "noncomformity", "description", "status", "status_date"],
				},
				{
					model: dbSAPPS.NcProductFU,
					as: "ncproductfus",
					attributes: ["id", "date", "description", "pic_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
						},
					],
				},
				{
					model: dbSAPPS.User,
					as: "user",
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
							include: [
								{
									model: dbSAPPS.Designation,
									as: "designation",
									attributes: ["display_name"],
								},
							],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name", "name"],
				},
			],
		});

		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.in]: ncProducts.map((item) => item.id),
				},
				doctype_id: 149,
			},
			attributes: ["id", "doc_id", "doctype_id", "status", "modified_by", "modified"],
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: ["id", "workflow_id", "designation_id", "territory_id", "status", "modified"],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["id", "employee_name"],
								},
							],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employees",
									attributes: ["id", "employee_name"],
									include: [
										{
											model: dbSAPPS.Designation,
											as: "designation",
											attributes: ["display_name"],
										},
									],
								},
							],
						},
					],
				},
			],
		});

		// Get comments
		const comments = await dbSAPPS.Comment.findAll({
			where: {
				doc_id: {
					[Op.in]: ncProducts.map((item) => item.id),
				},
				doctype_id: 149,
			},
			order: [["creation", "DESC"]],
			attributes: ["id", "doc_id", "doctype_id", "edited", "comment", "modified_by", "modified", "owner"],
			include: [
				{
					model: dbSAPPS.User,
					as: "user",
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
							include: [
								{
									model: dbSAPPS.Designation,
									as: "designation",
									attributes: ["display_name"],
								},
							],
						},
					],
				},
			],
		});

		const data = ncProducts.map((item) => {
			const workflow = workflows.find((wf) => wf.doc_id === item.id);
			const comment = comments.filter((cmt) => cmt.doc_id === item.id);
			return {
				...item.dataValues,
				workflow: workflow ? workflow.dataValues : null,
				comments: comment ? comment.map((cmt) => cmt.dataValues) : null,
			};
		});

		return {
			success: true,
			ncProducts: data,
			message: "loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error || "Failed to retrieve. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function filterByYear(params) {
	let filterConditions = {};

	if (params.year || params.month) {
		filterConditions.nc_date = {};
		filterConditions.nc_date[Op.and] = [];

		if (params.year) {
			filterConditions.nc_date[Op.and].push(Sequelize.where(Sequelize.fn("YEAR", Sequelize.col("nc_date")), params.year));
		}

		if (params.month) {
			filterConditions.nc_date[Op.and].push(Sequelize.where(Sequelize.fn("MONTH", Sequelize.col("nc_date")), params.month));
		}

		// Kalau `Op.and` kosong, hapus dari kondisi
		if (filterConditions.nc_date[Op.and].length === 0) {
			delete filterConditions.nc_date[Op.and];
		}
	}

	try {
		const ncProducts = await dbSAPPS.NcProduct.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.NcProductChild,
					as: "ncproductchilds",
					attributes: ["id", "product_name", "quantity", "uom", "batch_no", "expiry_date", "noncomformity", "description", "status", "status_date"],
				},
				{
					model: dbSAPPS.NcProductFU,
					as: "ncproductfus",
					attributes: ["id", "date", "description", "pic_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
						},
					],
				},
				{
					model: dbSAPPS.User,
					as: "user",
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
							include: [
								{
									model: dbSAPPS.Designation,
									as: "designation",
									attributes: ["display_name"],
								},
							],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name", "name"],
				},
			],
		});

		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.in]: ncProducts.map((item) => item.id),
				},
				doctype_id: 149,
			},
			attributes: ["id", "doc_id", "doctype_id", "status", "modified_by", "modified"],
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: ["id", "workflow_id", "designation_id", "territory_id", "status", "modified"],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["id", "employee_name"],
								},
							],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employees",
									attributes: ["id", "employee_name"],
									include: [
										{
											model: dbSAPPS.Designation,
											as: "designation",
											attributes: ["display_name"],
										},
									],
								},
							],
						},
					],
				},
			],
		});

		const comments = await dbSAPPS.Comment.findAll({
			where: {
				doc_id: {
					[Op.in]: ncProducts.map((item) => item.id),
				},
				doctype_id: 149,
			},
			order: [["creation", "DESC"]],
			attributes: ["id", "doc_id", "doctype_id", "edited", "comment", "modified_by", "modified", "owner"],
			include: [
				{
					model: dbSAPPS.User,
					as: "user",
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
							include: [
								{
									model: dbSAPPS.Designation,
									as: "designation",
									attributes: ["display_name"],
								},
							],
						},
					],
				},
			],
		});

		const data = ncProducts.map((item) => {
			const workflow = workflows.find((wf) => wf.doc_id === item.id);
			const comment = comments.filter((cmt) => cmt.doc_id === item.id);
			return {
				...item.dataValues,
				workflow: workflow ? workflow.dataValues : null,
				comments: comment ? comment.map((cmt) => cmt.dataValues) : null,
			};
		});

		return {
			success: true,
			ncProducts: data,
			message: "NC Products export successfully!",
		};
	} catch (error) {
		console.error("Error retrieving NC Products: ", error);
		const errorMessage = error.message || "Failed to retrieve NC Products. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function option() {
	try {
		const ncProducts = await dbSAPPS.NcProduct.findAll({
			attributes: ["id", "nc_no"],
			where: { enabled: true },
			order: [["id", "ASC"]],
			include: [
				{
					model: dbSAPPS.NcProductChild,
					as: "ncproductchilds",
					attributes: ["id", "product_name", "quantity", "uom", "batch_no", "expiry_date", "noncomformity", "description", "status", "status_date"],
				},
				{
					model: dbSAPPS.NcProductFU,
					as: "ncproductfus",
					attributes: ["id", "date", "description", "pic_id"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
						},
					],
				},
				{
					model: dbSAPPS.User,
					as: "user",
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
							include: [
								{
									model: dbSAPPS.Designation,
									as: "designation",
									attributes: ["display_name"],
								},
							],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name", "name"],
				},
			],
		});

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

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

		return {
			count,
			success: true,
			message: "loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error || "Failed to counting. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

	try {
		// Utility function untuk format angka
		const toNumber = (num) => num.toString().padStart(2, "0");

		// Generate next NC number dengan urutan berlanjut sepanjang tahun
		const generateNextNC = async () => {
			const now = new Date();
			const currentMonth = now.getMonth() + 1;
			const currentYear = now.getFullYear();
			const month = toNumber(currentMonth);

			// Prefix untuk nomor yang akan dibuat (dengan bulan saat ini)
			const currentPrefix = `NC.${currentYear}${month}`;

			// Tapi untuk pencarian, gunakan prefix tahun saja untuk melanjutkan urutan
			const yearPrefix = `NC.${currentYear}`;

			// Ambil semua data dalam tahun yang sama untuk mendapatkan urutan terakhir
			const existingNCProducts = await dbSAPPS.NcProduct.findAll({
				where: {
					nc_no: {
						[dbSAPPS.Sequelize.Op.like]: `${yearPrefix}%`,
					},
				},
				attributes: ["nc_no"],
				order: [["nc_no", "DESC"]], // Urutkan descending untuk mendapatkan nomor terakhir
				transaction,
				lock: transaction.LOCK.UPDATE, // Lock untuk mencegah race condition
			});

			let nextNumber = 1;

			if (existingNCProducts && existingNCProducts.length > 0) {
				// Extract semua nomor urut yang ada dalam tahun ini
				const existingNumbers = [];
				const regex = new RegExp(`^NC\\.${currentYear}\\d{2}(\\d{4})$`); // Match NC.YYYY[MM]0001

				existingNCProducts.forEach((nc) => {
					const match = nc.nc_no.match(regex);
					if (match) {
						const numberPart = parseInt(match[1], 10);
						if (!isNaN(numberPart)) {
							existingNumbers.push(numberPart);
						}
					}
				});

				if (existingNumbers.length > 0) {
					// Sort nomor yang ada untuk mendapatkan yang terbesar
					existingNumbers.sort((a, b) => b - a);
					// Ambil nomor terbesar dan tambah 1
					nextNumber = existingNumbers[0] + 1;
				}
			}

			// Format nomor urut ke 4 digit dengan padding 0 di depan
			const formattedNumber = nextNumber.toString().padStart(4, "0");

			// Return dengan format NC.YYYYMM0001 tapi nomor urut berlanjut
			return `${currentPrefix}${formattedNumber}`;
		};

		// Generate NC number
		const newNCNumber = await generateNextNC();

		// Validasi input parameters
		if (!params || typeof params !== "object") {
			throw new Error("Invalid parameters provided");
		}

		const newParams = {
			...params,
			nc_no: newNCNumber,
			creation: Date.now(),
			modified: Date.now(),
		};

		// Create NC Product
		const newNCProduct = await dbSAPPS.NcProduct.create(newParams, {
			transaction,
		});

		// Process NC Product Children
		if (params.ncproductchilds) {
			await processNCProductChildren(params.ncproductchilds, newNCProduct.id, params, transaction);
		}

		// Process NC Product FUs
		if (params.ncproductfus) {
			await processNCProductFUs(params.ncproductfus, newNCProduct.id, params, transaction);
		}

		// Process Workflow
		await processWorkflow(params, newNCProduct.id, transaction);

		// Commit transaction
		await transaction.commit();

		return {
			success: true,
			message: "Data has been successfully added!",
			ncProducts: newNCProduct,
			nc_no: newNCNumber,
		};
	} catch (error) {
		await transaction.rollback();
		console.error("Error in create function:", error);

		return {
			success: false,
			message: error.message || "An error occurred during data creation.",
			error: process.env.NODE_ENV === "development" ? error.stack : undefined,
		};
	}
}

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

		await ncProduct.update(params);

		if (params.ncproductchilds) {
			// Pastikan data adalah array
			if (typeof params.ncproductchilds === "string") {
				params.ncproductchilds = JSON.parse(params.ncproductchilds);
			}

			// Hapus child yang ada sebelum menambahkan yang baru
			await dbSAPPS.NcProductChild.destroy({
				where: { ncproduct_id: id },
				transaction,
			});

			// Persiapkan data untuk bulkCreate
			const ncProductsChildData = params.ncproductchilds.map((child) => {
				// Validasi field yang diperlukan
				if (!child.product_name) {
					throw new Error("Missing required fields in data child.");
				}

				return {
					...child,
					creation: Date.now(),
					modified: Date.now(),
					modified_by: params.modified_by,
					owner: params.owner,
					ncproduct_id: id, // Mengaitkan dengan ID Delivery Trip yang diupdate
				};
			});

			// Lakukan bulkCreate untuk child yang baru
			await dbSAPPS.NcProductChild.bulkCreate(ncProductsChildData, {
				transaction,
			});
		}

		if (params.ncproductfus) {
			// Pastikan data adalah array
			if (typeof params.ncproductfus === "string") {
				params.ncproductfus = JSON.parse(params.ncproductfus);
			}

			// Hapus child yang ada sebelum menambahkan yang baru
			await dbSAPPS.NcProductFU.destroy({
				where: { ncproduct_id: id },
				transaction,
			});

			// Persiapkan data untuk bulkCreate
			const ncProductsFUData = params.ncproductfus.map((child) => {
				// Validasi field yang diperlukan
				if (!child.description) {
					throw new Error("Missing required fields in data child.");
				}

				return {
					...child,
					creation: Date.now(),
					modified: Date.now(),
					modified_by: params.modified_by,
					owner: params.owner,
					ncproduct_id: id,
				};
			});

			// Lakukan bulkCreate untuk child yang baru
			await dbSAPPS.NcProductFU.bulkCreate(ncProductsFUData, {
				transaction,
			});
		}

		// Commit transaksi jika semua berhasil
		await transaction.commit();
		return {
			success: true,
			message: "Your data has been saved!",
		};
	} catch (error) {
		const errorMessage = error || "Failed to update nc product data.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function enabled(id, params) {
	try {
		const ncProduct = await dbSAPPS.NcProduct.findByPk(id);
		if (!ncProduct)
			return {
				success: false,
				message: "Data 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 ncProduct.update(updateData);
		return {
			success: true,
			message: "Data has been updated!",
		};
	} catch (error) {
		const errorMessage = error || "Failed to update Data.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const ncProduct = await dbSAPPS.NcProduct.findByPk(id);
		if (!ncProduct) return { success: false, message: "nc product not found!" };
		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.eq]: ncProduct.id,
				},
				doctype_id: 149,
			},
			attributes: ["id", "doc_id", "doctype_id", "status", "modified_by", "modified"],
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: ["id", "workflow_id", "designation_id", "territory_id", "status", "modified"],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["id", "employee_name"],
								},
							],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employees",
									attributes: ["id", "employee_name"],
								},
							],
						},
					],
				},
			],
		});
		if (!workflows)
			return {
				success: false,
				message: "Workflows employee request 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 ncProduct.destroy();
		await transaction.commit();
		return {
			success: true,
			message: "NC Product successfully deleted!",
		};
	} catch (error) {
		const errorMessage = error || "Failed to delete nc product.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}
