const { Op, Sequelize } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const moment = require("moment");
const nodemailer = require("nodemailer");
const emailDistributor = require("./emailDistributor");
// const puppeteer = require("puppeteer"); // Disabled: Module not installed and code unused
const { spawn } = require("child_process");
const fs = require("fs");
const os = require("os");
const path = require("path");
const { getWkhtmltopdfPath } = require("../../../../utils/osCompatibility");

// Cross-platform wkhtmltopdf path detection
const WKHTML_PATH = getWkhtmltopdfPath({
	defaultWindowsPath: "C:\\Program Files\\wkhtmltopdf\\bin\\wkhtmltopdf.exe",
	defaultUnixPath: "/usr/local/bin/wkhtmltopdf",
});

// KFTD
const logoPath = path.join(process.cwd(), "public/data/logos/kf-logo.png");

let logoBase64 = null;

try {
	if (fs.existsSync(logoPath)) {
		logoBase64 = fs.readFileSync(logoPath).toString("base64");
	} else {
		console.warn("Logo not found:", logoPath);
	}
} catch (err) {
	console.error("Failed to load logo:", err.message);
}

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

// START HELPER
// 🔥 OPTIMIZED VERSION
// async function getHierarchy(territory_id, companyId) {
//   const today = new Date();

//   const territoryVersion = await dbSAPPS.TerritoryVersion.findOne({
//     where: {
//       company_id: companyId,
//       enabled: true,
//       valid_from: { [Op.lte]: today },
//       [Op.or]: [{ valid_to: { [Op.gte]: today } }, { valid_to: null }],
//     },
//     include: [
//       {
//         model: dbSAPPS.TerritoryVersionChild,
//         as: "territoryversionchilds",
//         attributes: ["territory_id", "parent_id", "display_name"],
//         include: [
//           {
//             model: dbSAPPS.Territory,
//             as: "territory",
//             attributes: ["id", "display_name"],
//             include: [
//               {
//                 model: dbSAPPS.Employee,
//                 as: "employees",
//                 attributes: ["id", "user_id"],
//                 include: [
//                   {
//                     model: dbSAPPS.User,
//                     as: "user",
//                     attributes: ["id", "email"],
//                   },
//                 ],
//               },
//             ],
//           },
//         ],
//       },
//     ],
//   });

//   if (!territoryVersion) {
//     return { hierarchy: [], hierarchyEmail: "" };
//   }

//   const childs = territoryVersion.territoryversionchilds;
//   const hierarchy = [];
//   const emailSet = new Set(); // 🔥 cegah duplikat

//   let current = childs.find((c) => c.territory_id === territory_id);

//   while (current) {
//     const email = current.territory?.employees?.[0]?.user?.email;

//     if (email) {
//       emailSet.add(email);
//     }

//     hierarchy.push({
//       id: current.territory_id,
//       name: current.display_name,
//       email: email || null,
//     });

//     if (!current.parent_id) break;
//     current = childs.find((c) => c.territory_id === current.parent_id);
//   }

//   return {
//     hierarchy,
//     hierarchyEmail: Array.from(emailSet).join("; "),
//   };
// }

// CUSTOM VERSION AGAR TAK SAMPAI SM DAN DIRECTUR
async function getHierarchy(territory_id, companyId) {
	const today = new Date();

	const territoryVersion = await dbSAPPS.TerritoryVersion.findOne({
		where: {
			company_id: companyId,
			enabled: true,
			valid_from: { [Op.lte]: today },
			[Op.or]: [{ valid_to: { [Op.gte]: today } }, { valid_to: null }],
		},
		include: [
			{
				model: dbSAPPS.TerritoryVersionChild,
				as: "territoryversionchilds",
				attributes: ["territory_id", "parent_id", "display_name"],
				include: [
					{
						model: dbSAPPS.Territory,
						as: "territory",
						attributes: ["id", "display_name"],
						include: [
							{
								model: dbSAPPS.Employee,
								as: "employees",
								attributes: ["id", "user_id"],
								include: [
									{
										model: dbSAPPS.User,
										as: "user",
										attributes: ["id", "email"],
									},
								],
							},
						],
					},
				],
			},
		],
	});

	if (!territoryVersion) {
		return { hierarchy: [], hierarchyEmail: "" };
	}

	const childs = territoryVersion.territoryversionchilds;
	const hierarchy = [];
	const emailSet = new Set();

	let current = childs.find((c) => c.territory_id === territory_id);

	while (current) {
		const email = current.territory?.employees?.[0]?.user?.email;

		if (email) {
			emailSet.add(email);
		}

		hierarchy.push({
			id: current.territory_id,
			name: current.display_name,
			email: email || null,
		});

		if (!current.parent_id) break;
		current = childs.find((c) => c.territory_id === current.parent_id);
	}

	// =========================
	// 🔥 LOGIKA BATAS EMAIL
	// =========================
	const emailArray = Array.from(emailSet);

	let limitedEmails = [];
	if (emailArray.length > 3) {
		limitedEmails = emailArray.slice(0, 3);
	} else if (emailArray.length > 0) {
		limitedEmails = emailArray.slice(0, 2);
	}

	return {
		hierarchy,
		hierarchyEmail: limitedEmails.join("; "),
	};
}

function getAllIds(arr) {
	let ids = [];
	arr.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;
}

// END HELPER

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

		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.administartor_of_msd === true ||
				params.administartor_of_msd === "true"
			) {
				filterConditions.territory_id = {
					[Op.not]: null,
				};
			} else {
				if (params.territoryIds && params.territoryIds.length > 0) {
					filterConditions.territory_id = {
						[Op.in]: params.territoryIds.map((item) => parseInt(item)),
					};
				} else {
					if (params.territory_id) {
						filterConditions.territory_id = {
							[Op.eq]: parseInt(params.territory_id),
						};
					}
				}
			}
		}

		const udp = await dbSAPPS.Udp.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: [
				{
					model: dbSAPPS.UdpProduct,
					as: "udpproducts",
					include: [
						{
							model: dbSAPPS.Item,
							as: "item",
							attributes: ["id", "display_name"],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Outlet,
					as: "outlet",
					attributes: ["id", "display_name", "code"],
					include: [
						{
							model: dbSAPPS.OutletType,
							as: "outlettype",
							attributes: ["id", "display_name"],
						},
					],
				},
				{
					model: dbSAPPS.Distributor,
					as: "distributor",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.DistributorBranch,
					as: "distributorbranch",
				},
				{
					model: dbSAPPS.Territory,
					as: "territory",
					attributes: ["id", "display_name"],
				},
			],
		});

		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.in]: udp.map((item) => item.id),
				},
				doctype_id: 24,
			},
			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 data = await Promise.all(
			udp.map(async (item) => {
				const plainItem = item.get({ plain: true });

				const workflow = workflows.find((wf) => wf.doc_id === item.id);

				const distributoroutlet = await dbSAPPS.DistributorOutlet.findOne({
					where: {
						distributor_id: item.distributor_id,
						outlet_id: item.outlet_id,
					},
					attributes: ["id", "display_name", "code", "distributor_id", "outlet_id", "address"],
				});

				// ✅ FIX UTAMA DI SINI
				plainItem.udpproducts = await Promise.all(
					plainItem.udpproducts.map(async (product) => {
						const distributoritem = await dbSAPPS.DistributorProduct.findOne({
							where: {
								item_id: product.item_id,
								distributor_id: item.distributor_id,
							},
						});

						return {
							...product,
							distributoritem: distributoritem ? distributoritem.get({ plain: true }) : null,
						};
					}),
				);

				const { hierarchy, hierarchyEmail } = await getHierarchy(item.territory_id, item.company_id);

				const dataCC = [item.distributorbranch?.email_address2, hierarchyEmail]
					.filter(Boolean)
					.flatMap((e) => e.split(";"))
					.map((e) => e.trim())
					.filter((v, i, a) => v && a.indexOf(v) === i)
					.join("; ");

				return {
					...plainItem,
					workflow: workflow ? workflow.get({ plain: true }) : null,
					distributoroutlet: distributoroutlet ? distributoroutlet.get({ plain: true }) : null,
					hierarchy: hierarchy,
					hierarchyEmail: hierarchyEmail,
					dataCC: dataCC,
				};
			}),
		);

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

// INI MENGGUNAKAN PUPPETEER UNTUK GENERATE PDF
// async function generatePdfFromTemplate(data) {
//   let browser = null;
//   try {
//     const formatNumberWithCommas = (number) => {
//       return number.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
//     };

//     const param = data;

//     // Path Chromium di VPS. Contoh: "/usr/bin/chromium" atau "/usr/bin/chromium-browser".
//     // Biarkan null agar Puppeteer pakai Chrome bundel (cocok untuk dev di Windows).
//     const CHROMIUM_PATH = null; // untuk VPS: isi mis. "/usr/bin/chromium"

//     const launchOptions = {
//       headless: true,
//       timeout: 60000, // 60s untuk VPS yang lambat
//       args: [
//         "--no-sandbox",
//         "--disable-setuid-sandbox",
//         "--disable-dev-shm-usage",
//         "--disable-gpu",
//         "--disable-software-rasterizer",
//         "--disable-extensions",
//         "--no-zygote",
//         "--disable-background-networking",
//         "--disable-default-apps",
//         "--disable-sync",
//         "--metrics-recording-only",
//         "--mute-audio",
//         "--no-first-run",
//       ],
//     };

//     if (CHROMIUM_PATH) {
//       launchOptions.executablePath = CHROMIUM_PATH;
//     }

//     browser = await puppeteer.launch(launchOptions);

//     const page = await browser.newPage();
//     page.setDefaultTimeout(30000);

//     const styles = {
//       thTopLeft: "border:2px solid #000000;",
//       thTop: "border-top:2px solid #000000;border-right:2px solid #000000;border-bottom:2px solid #000000;",
//       thLine: "border-right:2px solid #000000;border-bottom:2px solid #000000;",
//       tdLine: "border-right:2px solid #000000;border-bottom:2px solid #000000;",
//       tdLineLeft: "border-left:2px solid #000000;border-right:2px solid #000000;border-bottom:2px solid #000000;",
//       img: "width:100px;height:auto;",
//       title: "text-align:center;font-size:18px;font-weight:bold;text-decoration:underline;",
//       thLineLeft: "border-left:2px solid #000000;border-right:2px solid #000000;border-bottom:2px solid #000000;",
//     };

//     const html = `
//     <!DOCTYPE html>
// <html lang="id">
//   <head>
//     <meta charset="UTF-8" />
//     <meta name="viewport" content="width=device-width, initial-scale=1.0" />
//     <title>Surat Tugas Up Country</title>

//   </head>
//   <body>
//    <div style="
//       font-size:11px;
//       font-family:Arial, Helvetica, sans-serif;
//       line-height:1.3;
//     ">
//      <table width="100%" border="0" cellSpacing="1" cellPadding="1">
//                            <tr>
//                                <td>
//                                    <table width="100%" border="0" cellSpacing="1" cellPadding="3">
//                                        <tr>
//                                            <td colSpan="8">
//                                                <table width="100%" border="0" cellSpacing="1" cellPadding="3">
//                                                    <tr>
//                                                      <td align="left" style="width: 8%">
//                                                         ${
//                                                           logoBase64
//                                                             ? `<img src="data:image/png;base64,${logoBase64}" style="${styles.img}" />`
//                                                             : ""
//                                                         }
//                                                     </td>
//                                                     <td style="${styles.title}">
//                                                         <h3>USULAN DISKON PRODUK</h3>
//                                                         ${
//                                                           param && param?.workflow?.status === "Approved"
//                                                             ? `<h4>No Register : ${param.doc_no}</h4>`
//                                                             : `<h5>No Register :${param.doc_no} - (${param.workflow?.status})</h5>`
//                                                         }
//                                                     </td>
//                                                 </tr>
//                                                </table>
//                                            </td>
//                                        </tr>

//                                        <tr>
//                                            <td width="20px">&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td width="20px">&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td width="20px">&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td width="20px">&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td width="20px">&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td width="20px">&nbsp;</td>
//                                        </tr>

//                                        <tr>
//                                            <td>&nbsp;</td>
//                                            <td>Cabang</td>
//                                            <td>:</td>
//                                            <td>${param?.distributorbranch?.display_name}&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td rowSpan="4" valign="top">
//                                                &nbsp;
//                                            </td>
//                                            <td width="20px">&nbsp;</td>
//                                        </tr>
//                                        <tr>
//                                            <td>&nbsp;</td>
//                                            <td width="100px">Nama Pelanggan</td>
//                                            <td>:</td>
//                                            <td width="60%">${param?.outlet?.display_name}&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                        </tr>
//                                        <tr>
//                                            <td>&nbsp;</td>
//                                            <td>Kode Pelanggan</td>
//                                            <td>:</td>
//                                            <td>${
//                                              param?.outlet?.distributoroutlets?.length > 0
//                                                ? param?.outlet?.distributoroutlets[0]?.code
//                                                : "-"
//                                            }&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td rowSpan="4" valign="top">
//                                                &nbsp;
//                                            </td>
//                                            <td width="20px">&nbsp;</td>
//                                        </tr>
//                                        <tr>
//                                            <td>&nbsp;</td>
//                                            <td>Alamat Pelanggan</td>
//                                            <td>:</td>
//                                            <td>${
//                                              param?.outlet?.distributoroutlets?.length > 0
//                                                ? param?.outlet?.distributoroutlets[0]?.address
//                                                : "-"
//                                            }&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td rowSpan="4" valign="top">
//                                                &nbsp;
//                                            </td>
//                                            <td width="20px">&nbsp;</td>
//                                        </tr>

//                                        <tr>
//                                            <td>&nbsp;</td>
//                                            <td>Type</td>
//                                            <td>:</td>
//                                            <td>${param?.outlet?.outlettype?.display_name || "-"}&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td rowSpan="4" valign="top">
//                                                &nbsp;
//                                            </td>
//                                            <td width="20px">&nbsp;</td>
//                                        </tr>

//                                        <tr>
//                                            <td>&nbsp;</td>
//                                            <td>Periode Berlaku</td>
//                                            <td>:</td>
//                                            <td>
//                                                ${param?.from_date} s/d ${param?.to_date}&nbsp;
//                                            </td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                        </tr>
//                                        <tr>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td>&nbsp;</td>
//                                            <td width="20px">&nbsp;</td>
//                                        </tr>
//                                    </table>
//                                </td>
//                            </tr>
//                            <tr>
//                                <td>
//                                    <table width="100%" cellSpacing="0" cellPadding="3" border-collapse="collapse">
//                                        <tr>
//                                            <th style="${styles.thTopLeft}" rowSpan="2">
//                                                NO.
//                                            </th>
//                                            <th style="${styles.thTop}" colSpan="2" rowSpan="2">
//                                                NAMA ITEM PRODUK
//                                            </th>
//                                            <th style="${styles.thTop}" colSpan="3">
//                                                SALES
//                                            </th>

//                                            <th style="${styles.thTop}" rowSpan="2">
//                                                TOTAL DISKON %
//                                            </th>
//                                            <th style="${styles.thTop}" colSpan="2">
//                                                BEBAN DISKON
//                                            </th>
//                                        </tr>
//                                        <tr>
//                                            <th style="${styles.thLine}">UNIT SALES</th>
//                                            <th style="${styles.thLine}">HNA</th>
//                                            <th style="${styles.thLine}">Rp.</th>
//                                            <th style="${styles.thLine}">PT. KF</th>
//                                            <th style="${styles.thLine}">PT. SAP</th>
//                                        </tr>
//                                       ${param?.udpproducts
//                                         ?.map(
//                                           (item, index) => `
//                                             <tr>
//                                               <td style="${styles.tdLineLeft}" align="center">
//                                                 ${index + 1}.
//                                               </td>
//                                               <td style="${styles.tdLine}" colSpan="2">
//                                                 ${item?.item?.display_name || ""}
//                                               </td>
//                                               <td style="${styles.tdLine}">
//                                                 ${item?.stock_uom || ""}
//                                               </td>
//                                               <td style="${styles.tdLine}" align="right">
//                                                 ${formatNumberWithCommas(item?.item_price || 0)}
//                                               </td>
//                                               <td style="${styles.tdLine}" align="right">
//                                                 ${formatNumberWithCommas(item?.total || 0)}
//                                               </td>
//                                               <td style="${styles.tdLine}" align="center">
//                                                 ${
//                                                   (Number(item?.disc_distributor) || 0) +
//                                                   (Number(item?.disc_principal) || 0)
//                                                 }
//                                               </td>
//                                               <td style="${styles.tdLine}" align="center">
//                                                 ${item?.disc_distributor || 0}
//                                               </td>
//                                               <td style="${styles.tdLine}" align="center">
//                                                 ${item?.disc_principal || 0}
//                                               </td>
//                                             </tr>
//                                           `
//                                         )
//                                         .join("")}
//                                    </table>
//                                </td>
//                            </tr>

//                            <tr>
//                                <td>&nbsp;</td>
//                                <td>&nbsp;</td>
//                                <td>&nbsp;</td>
//                            </tr>
//                            <tr>
//                                <td>
//                                    <table width="100%" cellSpacing="0" cellPadding="3" border-collapse="collapse">
//                                        <tr>
//                                            <th style="${styles.thTopLeft}" colSpan="4">
//                                                DIAJUKAN.
//                                            </th>
//                                            <th style="${styles.thTop}" colSpan="4">
//                                                DISETUJUI
//                                            </th>
//                                        </tr>
//                                      <tr style="height: '80px';" align="center">
//                                            <td style="${styles.thLineLeft}" colSpan="2">&nbsp;</td>
//                                            <td style="${styles.thLine}" colSpan="2">Propose by ${
//       param?.territory?.display_name
//     } </td>
//                                            <td style="${styles.thLine}" colSpan="2">&nbsp;</td>
//                                            <td style="${styles.thLine}" colSpan="2"> ${
//       param?.workflow?.workflowapprovers[0]?.status === "Approved"
//         ? "Approved by " + param?.workflow?.workflowapprovers[0]?.territory?.employees[0]?.employee_name || "-"
//         : "Waiting for Approval " + param?.workflow?.workflowapprovers[0]?.territory?.display_name || "-"
//     } <br />
//                                                ${
//                                                  param?.workflow?.workflowapprovers[1]?.status === "Approved"
//                                                    ? "Approved by " +
//                                                        param?.workflow?.workflowapprovers[1]?.territory?.employees[0]
//                                                          ?.employee_name || "-"
//                                                    : "Waiting for Approval " +
//                                                        param?.workflow?.workflowapprovers[1]?.territory?.display_name ||
//                                                      "-"
//                                                } <br /></td>
//                                        </tr>
//                                      <tr style="height: '80px';" align="center">
//                                            <td style="${
//                                              styles.thLineLeft
//                                            }" colSpan="2" valign="bottom"> Kacab. PT Kimia Farma</td>
//                                            <td style="${
//                                              styles.thLine
//                                            }" colSpan="2" valign="bottom">MR / SPV PT. SAP (Cabang)</td>
//                                            <td style="${
//                                              styles.thLine
//                                            }" colSpan="2" valign="bottom">Manager Principal PT. KF</td>
//                                            <td style="${
//                                              styles.thLine
//                                            }" colSpan="2" valign="bottom">Sales Manager PT. SAP</td>
//                                        </tr>
//                                    </table>
//                                </td>
//                            </tr>
//                            <tr>
//                                <td>&nbsp;</td>
//                            </tr>
//                            <tr>
//                                <td>
//                                    <b>
//                                        <u>Keterangan</u> : ${param?.note || ""}
//                                    </b>
//                                </td>
//                            </tr>
//                            <tr>
//                                <td>&nbsp;</td>
//                            </tr>
//                        </table>
//     </div>
//   </body>
// </html>
//     `;

//     await page.setContent(html, {
//       waitUntil: "domcontentloaded",
//     });

//     await page.evaluate(async () => {
//       const img = document.querySelector("img");
//       if (img && !img.complete) {
//         await new Promise((resolve) => {
//           img.onload = resolve;
//           img.onerror = resolve;
//         });
//       }
//     });

//     const pdfBuffer = await page.pdf({
//       format: "A4",
//       printBackground: true,
//       landscape: true,
//       margin: { top: "10mm", right: "10mm", bottom: "10mm", left: "10mm" },
//     });

//     await browser.close();
//     browser = null;
//     return pdfBuffer;
//   } catch (err) {
//     if (browser) {
//       try {
//         await browser.close();
//       } catch (_) {}
//     }
//     // Log lengkap agar bisa debug di VPS (lihat log/console)
//     console.error("Error generate PDF:", err.message);
//     console.error("Stack:", err.stack || "(no stack)");
//     if (err.cause) console.error("Cause:", err.cause);
//     throw new Error("Failed to generate PDF: " + (err.message || String(err)));
//   }
// }

// INI MENGGUNAKAN WKHTMLTOPDF TO GENERATE PDF
async function generatePdfFromTemplate(data) {
	try {
		const formatNumberWithCommas = (number) => {
			return number.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
		};

		const param = data;

		const tmpDir = fs.mkdtempSync(path.join(os.tmpdir(), "wkhtml-"));
		const htmlPath = path.join(tmpDir, "doc.html");
		const pdfPath = path.join(tmpDir, "doc.pdf");

		// 🔥 HTML KAMU — COPY PERSIS TANPA DIUBAH
		const styles = {
			thTopLeft: "border:2px solid #000000;",
			thTop: "border-top:2px solid #000000;border-right:2px solid #000000;border-bottom:2px solid #000000;",
			thLine: "border-right:2px solid #000000;border-bottom:2px solid #000000;",
			tdLine: "border-right:2px solid #000000;border-bottom:2px solid #000000;",
			tdLineLeft: "border-left:2px solid #000000;border-right:2px solid #000000;border-bottom:2px solid #000000;",
			img: "width:100px;height:auto;",
			title: "text-align:center;font-size:18px;font-weight:bold;text-decoration:underline;",
			thLineLeft: "border-left:2px solid #000000;border-right:2px solid #000000;border-bottom:2px solid #000000;",
		};

		const html = `
          <!DOCTYPE html>
      <html lang="id">
        <head>
          <meta charset="UTF-8" />
          <meta name="viewport" content="width=device-width, initial-scale=1.0" />
          <title>Surat Tugas Up Country</title>
         
        </head>
        <body>
         <div style="
            font-size:11px;
            font-family:Arial, Helvetica, sans-serif;
            line-height:1.3;
          ">
           <table width="100%" border="0" cellSpacing="1" cellPadding="1">
                                 <tr>
                                     <td>
                                         <table width="100%" border="0" cellSpacing="1" cellPadding="3">
                                             <tr>
                                                 <td colSpan="8">
                                                     <table width="100%" border="0" cellSpacing="1" cellPadding="3">
                                                         <tr>
                                                           <td align="left" style="width: 8%">
                                                              ${logoBase64 ? `<img src="data:image/png;base64,${logoBase64}" style="${styles.img}" />` : ""}
                                                          </td>
                                                          <td style="${styles.title}">
                                                              <h3>USULAN DISKON PRODUK</h3>
                                                              ${
																																param && param?.workflow?.status === "Approved"
																																	? `<h4>No Register : ${param.doc_no}</h4>`
																																	: `<h5>No Register :${param.doc_no} - (${param.workflow?.status})</h5>`
																															}
                                                          </td>
                                                      </tr>
                                                     </table>
                                                 </td>
                                             </tr>
                 
                                             <tr>
                                                 <td width="20px">&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td width="20px">&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td width="20px">&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td width="20px">&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td width="20px">&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td width="20px">&nbsp;</td>
                                             </tr>
                 
                                             <tr>
                                                 <td>&nbsp;</td>
                                                 <td>Cabang</td>
                                                 <td>:</td>
                                                 <td>${param?.distributorbranch?.display_name}&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td rowSpan="4" valign="top">
                                                     &nbsp;
                                                 </td>
                                                 <td width="20px">&nbsp;</td>
                                             </tr>
                                             <tr>
                                                 <td>&nbsp;</td>
                                                 <td width="100px">Nama Pelanggan</td>
                                                 <td>:</td>
                                                 <td width="60%">${param?.outlet?.display_name}&nbsp;</td>
                                                 <td>&nbsp;</td>
                                             </tr>
                                             <tr>
                                                 <td>&nbsp;</td>
                                                 <td>Kode Pelanggan</td>
                                                 <td>:</td>
                                                 <td>${
																										param?.outlet?.distributoroutlets?.length > 0 ? param?.outlet?.distributoroutlets[0]?.code : "-"
																									}&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td rowSpan="4" valign="top">
                                                     &nbsp;
                                                 </td>
                                                 <td width="20px">&nbsp;</td>
                                             </tr>
                                             <tr>
                                                 <td>&nbsp;</td>
                                                 <td>Alamat Pelanggan</td>
                                                 <td>:</td>
                                                 <td>${
																										param?.outlet?.distributoroutlets?.length > 0 ? param?.outlet?.distributoroutlets[0]?.address : "-"
																									}&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td rowSpan="4" valign="top">
                                                     &nbsp;
                                                 </td>
                                                 <td width="20px">&nbsp;</td>
                                             </tr>
                 
                                             <tr>
                                                 <td>&nbsp;</td>
                                                 <td>Type</td>
                                                 <td>:</td>
                                                 <td>${param?.outlet?.outlettype?.display_name || "-"}&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td rowSpan="4" valign="top">
                                                     &nbsp;
                                                 </td>
                                                 <td width="20px">&nbsp;</td>
                                             </tr>
                 
                                             <tr>
                                                 <td>&nbsp;</td>
                                                 <td>Periode Berlaku</td>
                                                 <td>:</td>
                                                 <td>
                                                     ${param?.from_date} s/d ${param?.to_date}&nbsp;
                                                 </td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                             </tr>
                                             <tr>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td>&nbsp;</td>
                                                 <td width="20px">&nbsp;</td>
                                             </tr>
                                         </table>
                                     </td>
                                 </tr>
                                 <tr>
                                     <td>
                                         <table width="100%" cellSpacing="0" cellPadding="3" border-collapse="collapse">
                                             <tr>
                                                 <th style="${styles.thTopLeft}" rowSpan="2">
                                                     NO.
                                                 </th>
                                                 <th style="${styles.thTop}" colSpan="2" rowSpan="2">
                                                     NAMA ITEM PRODUK
                                                 </th>
                                                 <th style="${styles.thTop}" colSpan="3">
                                                     SALES
                                                 </th>
                 
                                                 <th style="${styles.thTop}" rowSpan="2">
                                                     TOTAL DISKON %
                                                 </th>
                                                 <th style="${styles.thTop}" colSpan="2">
                                                     BEBAN DISKON
                                                 </th>
                                             </tr>
                                             <tr>
                                                 <th style="${styles.thLine}">UNIT SALES</th>
                                                 <th style="${styles.thLine}">HNA</th>
                                                 <th style="${styles.thLine}">Rp.</th>
                                                 <th style="${styles.thLine}">PT. KF</th>
                                                 <th style="${styles.thLine}">PT. SAP</th>
                                             </tr>
                                            ${param?.udpproducts
																							?.map(
																								(item, index) => `
                                                  <tr>
                                                    <td style="${styles.tdLineLeft}" align="center">
                                                      ${index + 1}.
                                                    </td>
                                                    <td style="${styles.tdLine}" colSpan="2">
                                                      ${item?.item?.display_name || ""}
                                                    </td>
                                                    <td style="${styles.tdLine}">
                                                      ${item?.stock_uom || ""}
                                                    </td>
                                                    <td style="${styles.tdLine}" align="right">
                                                      ${formatNumberWithCommas(item?.item_price || 0)}
                                                    </td>
                                                    <td style="${styles.tdLine}" align="right">
                                                      ${formatNumberWithCommas(item?.total || 0)}
                                                    </td>
                                                    <td style="${styles.tdLine}" align="center">
                                                      ${(Number(item?.disc_distributor) || 0) + (Number(item?.disc_principal) || 0)}
                                                    </td>
                                                    <td style="${styles.tdLine}" align="center">
                                                      ${item?.disc_distributor || 0}
                                                    </td>
                                                    <td style="${styles.tdLine}" align="center">
                                                      ${item?.disc_principal || 0}
                                                    </td>
                                                  </tr>
                                                `,
																							)
																							.join("")}
                                         </table>
                                     </td>
                                 </tr>
                 
                                 <tr>
                                     <td>&nbsp;</td>
                                     <td>&nbsp;</td>
                                     <td>&nbsp;</td>
                                 </tr>
                                 <tr>
                                     <td>
                                         <table width="100%" cellSpacing="0" cellPadding="3" border-collapse="collapse">
                                             <tr>
                                                 <th style="${styles.thTopLeft}" colSpan="4">
                                                     DIAJUKAN.
                                                 </th>
                                                 <th style="${styles.thTop}" colSpan="4">
                                                     DISETUJUI
                                                 </th>
                                             </tr>
                                           <tr style="height: '80px';" align="center">
                                                 <td style="${styles.thLineLeft}" colSpan="2">&nbsp;</td>
                                                 <td style="${styles.thLine}" colSpan="2">Propose by ${param?.territory?.display_name} </td>
                                                 <td style="${styles.thLine}" colSpan="2">&nbsp;</td>
                                                 <td style="${styles.thLine}" colSpan="2"> ${
																										param?.workflow?.workflowapprovers[0]?.status === "Approved"
																											? "Approved by " + param?.workflow?.workflowapprovers[0]?.territory?.employees[0]?.employee_name || "-"
																											: "Waiting for Approval " + param?.workflow?.workflowapprovers[0]?.territory?.display_name || "-"
																									} <br />
                                                     ${
																												param?.workflow?.workflowapprovers[1]?.status === "Approved"
																													? "Approved by " + param?.workflow?.workflowapprovers[1]?.territory?.employees[0]?.employee_name ||
																														"-"
																													: "Waiting for Approval " + param?.workflow?.workflowapprovers[1]?.territory?.display_name || "-"
																											} <br /></td>
                                             </tr>
                                           <tr style="height: '80px';" align="center">
                                                 <td style="${styles.thLineLeft}" colSpan="2" valign="bottom"> Kacab. PT Kimia Farma</td>
                                                 <td style="${styles.thLine}" colSpan="2" valign="bottom">MR / SPV PT. SAP (Cabang)</td>
                                                 <td style="${styles.thLine}" colSpan="2" valign="bottom">Manager Principal PT. KF</td>
                                                 <td style="${styles.thLine}" colSpan="2" valign="bottom">Sales Manager PT. SAP</td>
                                             </tr>
                                         </table>
                                     </td>
                                 </tr>
                                 <tr>
                                     <td>&nbsp;</td>
                                 </tr>
                                 <tr>
                                     <td>
                                         <b>
                                             <u>Keterangan</u> : ${param?.note || ""}
                                         </b>
                                     </td>
                                 </tr>
                                 <tr>
                                     <td>&nbsp;</td>
                                 </tr>
                             </table>
          </div>
        </body>
      </html>
          `;

		fs.writeFileSync(htmlPath, html, "utf8");

		const args = [
			"--quiet",
			"--page-size",
			"A4",
			"--orientation",
			"Landscape",
			"--margin-top",
			"10mm",
			"--margin-right",
			"10mm",
			"--margin-bottom",
			"10mm",
			"--margin-left",
			"10mm",
			"--encoding",
			"UTF-8",
			"--enable-local-file-access",
			htmlPath,
			pdfPath,
		];

		await new Promise((resolve, reject) => {
			const proc = spawn(WKHTML_PATH, args);
			proc.on("error", reject);
			proc.on("close", (code) => {
				if (code !== 0) reject(new Error("wkhtmltopdf exit code " + code));
				else resolve();
			});
		});

		const buffer = fs.readFileSync(pdfPath);
		fs.rmSync(tmpDir, { recursive: true, force: true });

		return buffer;
	} catch (err) {
		console.error("WKHTMLTOPDF ERROR:", err);
		throw err;
	}
}

async function sendEmailDistributor(params) {
	try {
		const udp = await dbSAPPS.Udp.findOne({
			where: { id: params.id },
			include: [
				{
					model: dbSAPPS.UdpProduct,
					as: "udpproducts",
					include: [
						{
							model: dbSAPPS.Item,
							as: "item",
							attributes: ["id", "display_name", "price", "item_cor"],
						},
					],
				},
				{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] },
				{
					model: dbSAPPS.Outlet,
					as: "outlet",
					attributes: ["id", "display_name", "code"],
					include: [
						{
							model: dbSAPPS.OutletType,
							as: "outlettype",
							attributes: ["id", "display_name"],
						},
					],
				},
				{ model: dbSAPPS.Distributor, as: "distributor", attributes: ["id", "display_name", "code"] },
				{ model: dbSAPPS.DistributorBranch, as: "distributorbranch" },
				{ model: dbSAPPS.Territory, as: "territory", attributes: ["id", "display_name"] },
			],
		});

		if (!udp) throw new Error("Discount Product document not found");

		const workflow = await dbSAPPS.Workflow.findOne({
			where: {
				doc_id: {
					[Op.eq]: udp.id,
				},
				doctype_id: 24,
			},
			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 plainUdp = udp.get({ plain: true });

		// enrich udpproducts
		plainUdp.udpproducts = await Promise.all(
			plainUdp.udpproducts.map(async (product) => {
				const distributoritem = await dbSAPPS.DistributorProduct.findOne({
					where: {
						item_id: product.item_id,
						distributor_id: plainUdp.distributor_id,
					},
				});

				return {
					...product,
					distributoritem: distributoritem ? distributoritem.get({ plain: true }) : null,
				};
			}),
		);

		const distributoroutlet = await dbSAPPS.DistributorOutlet.findOne({
			where: {
				distributor_id: plainUdp.distributor_id,
				outlet_id: plainUdp.outlet_id,
			},
		});

		const { hierarchy, hierarchyEmail } = await getHierarchy(udp.territory_id, udp.company_id);

		const data = {
			...plainUdp,
			workflow: workflow ? workflow.get({ plain: true }) : null,
			distributoroutlet: distributoroutlet ? distributoroutlet.get({ plain: true }) : null,
			hierarchy: hierarchy ? hierarchy : null,
			hierarchyEmail: hierarchyEmail ? hierarchyEmail : null,
		};

		if (!data.distributorbranch?.email_address1) {
			throw new Error("Distributor email not found");
		}

		console.log("hierarchyEmail : ", hierarchyEmail);

		const transporter = nodemailer.createTransport({
			host: "mail.sapharma.co.id",
			port: 465,
			secure: true,
			auth: {
				user: "noreply-sapps@sapharma.co.id",
				pass: "noreply-sappss@p123**",
			},
			connectionTimeout: 30_000,
			greetingTimeout: 30_000,
			socketTimeout: 30_000,
			tls: { rejectUnauthorized: false },
		});

		const attachments = [];

		// KFTD
		if (data?.distributor_id === 5) {
			const attachment = await generatePdfFromTemplate(data);

			if (attachment) {
				attachments.push({
					filename: `${data.doc_no}.pdf`,
					content: attachment,
					contentType: "application/pdf",
				});
			}
		}

		const html = emailDistributor.html(data);
		const dataCC = [data?.distributorbranch?.email_address2, hierarchyEmail]
			.filter(Boolean)
			.flatMap((e) => e.split(";"))
			.map((e) => e.trim())
			.filter((v, i, a) => v && a.indexOf(v) === i);

		// const dataCC = data?.distributorbranch?.email_address2;

		console.log("dataCC : ", dataCC);

		const mailOptions = {
			from: "noreply-sapps@sapharma.co.id",
			to: data.distributorbranch.email_address1,
			subject: `Pengajuan Diskon - ${data.doc_no}`,
			html,
			attachments,
		};

		if (dataCC) {
			mailOptions.cc = dataCC;
		}

		try {
			const info = await transporter.sendMail(mailOptions);
			console.log("[EMAIL SENT]", info.messageId);
		} catch (err) {
			console.error("[EMAIL ERROR]", err.message);
		}

		return {
			success: true,
			message: "Email queued successfully",
		};
	} catch (error) {
		console.error("[SEND EMAIL ERROR]", error);
		return {
			success: false,
			message: error.message || "Failed to send email",
		};
	}
}

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

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

	try {
		const udks = await dbSAPPS.Udp.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]], // Opsional: Mengurutkan hasil berdasarkan kolom 'modified'
			include: [
				{
					model: dbSAPPS.UdpProduct,
					as: "udkproducts",
					attributes: [
						"id",
						"name",
						"item_code",
						"sku_code",
						"batch",
						"principal",
						"ed_stock",
						"stock_qty",
						"arrival_date",
						"price",
						"order_qty",
						"discount",
						"disc_elr",
						"discount_price",
						"total_product",
					],
				},
				{
					model: dbSAPPS.User,
					as: "user",
					attributes: ["id", "email", "full_name", "phone_number"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
							include: [
								{
									model: dbSAPPS.Designation,
									as: "designation",
									attributes: ["display_name"],
								},
							],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["name"],
				},
			],
		});

		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.in]: udks.map((item) => item.id),
				},
				doctype_id: 107,
			},
			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 data = udks.map((item) => {
			const workflow = workflows.find((wf) => wf.doc_id === item.id);
			return {
				...item.dataValues,
				workflow: workflow ? workflow.dataValues : null,
			};
		});

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

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

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

	try {
		const udks = await dbSAPPS.Udp.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]], // Opsional: Mengurutkan hasil berdasarkan kolom 'modified'
			include: [
				{
					model: dbSAPPS.UdpProduct,
					as: "udkproducts",
					attributes: [
						"id",
						"name",
						"item_code",
						"sku_code",
						"batch",
						"principal",
						"ed_stock",
						"stock_qty",
						"arrival_date",
						"price",
						"order_qty",
						"discount",
						"disc_elr",
						"discount_price",
						"total_product",
					],
				},
				{
					model: dbSAPPS.User,
					as: "user",
					attributes: ["id", "email", "full_name", "phone_number"],
					include: [
						{
							model: dbSAPPS.Employee,
							as: "employee",
							attributes: ["employee_name"],
							include: [
								{
									model: dbSAPPS.Designation,
									as: "designation",
									attributes: ["display_name"],
								},
							],
						},
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["name"],
				},
			],
		});

		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.in]: udks.map((item) => item.id),
				},
				doctype_id: 107,
			},
			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 data = udks.map((item) => {
			const workflow = workflows.find((wf) => wf.doc_id === item.id);
			return {
				...item.dataValues,
				workflow: workflow ? workflow.dataValues : null,
			};
		});

		// Filter data berdasarkan kondisi yang ditentukan
		const filteredData = data.filter((item) => {
			const isOwnerMatch = parseInt(item.owner) === parseInt(params.user_id);
			const hasDesignationMatch =
				item.workflow?.workflowapprovers?.some((approver) => {
					return parseInt(approver.territory_id) === parseInt(params.territory_id);
				}) || false;
			return isOwnerMatch || hasDesignationMatch;
		});

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

async function option() {
	try {
		const udks = await dbSAPPS.Udp.findAll({
			attributes: ["id", "name"],
			where: { enabled: true },
			order: [["name", "ASC"]],
			include: [
				{
					model: dbSAPPS.UdpProduct,
					as: "udkproducts",
					attributes: [
						"id",
						"name",
						"item_code",
						"sku_code",
						"batch",
						"principal",
						"ed_stock",
						"stock_qty",
						"arrival_date",
						"price",
						"order_qty",
						"discount",
						"disc_elr",
						"discount_price",
						"total_product",
					],
				},
				{
					model: dbSAPPS.Company,
					as: "company",
					attributes: ["name"],
				},
			],
		});

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

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

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

const toRoman = (month) => {
	const romans = ["I", "II", "III", "IV", "V", "VI", "VII", "VIII", "IX", "X", "XI", "XII"];
	return romans[month - 1];
};

async function generateDocNo({ salesManager, distributorCode, distributorBranchCode, transaction }) {
	const now = new Date();
	const monthRoman = toRoman(now.getMonth() + 1);
	const year = now.getFullYear();

	const lastDoc = await dbSAPPS.Udp.findOne({
		order: [["id", "DESC"]],
		lock: transaction.LOCK.UPDATE,
		transaction,
	});

	const lastNumber = lastDoc ? parseInt(lastDoc.doc_no.match(/\d+/)?.[0] || 0, 10) + 1 : 1;

	const seq = String(lastNumber).padStart(3, "0");

	return `No. ${seq}/${salesManager}/${distributorBranchCode}/${monthRoman}/${year}/${distributorCode}`;
}

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

	try {
		if (!params.company_id) {
			throw new Error("Company is required!");
		}

		/** =============================
     *  MASTER DATA
     ============================= */
		const [distributor, distributorBranch, territory] = await Promise.all([
			params.distributor_id ? dbSAPPS.Distributor.findByPk(params.distributor_id, { transaction }) : null,
			params.distributorbranch_id ? dbSAPPS.DistributorBranch.findByPk(params.distributorbranch_id, { transaction }) : null,
			params.territory_id ? dbSAPPS.Territory.findByPk(params.territory_id, { transaction }) : null,
		]);

		/** =============================
     *  WORKFLOW TERRITORIES
     ============================= */
		let workflowTerritories = [];
		let salesManager = "-";

		if (Array.isArray(params.workflowTerritories) && params.workflowTerritories.length) {
			const setting = await dbSAPPS.WorkflowSetting.findOne({
				where: {
					doctype_id: params.doctype_id,
					type: "Territory",
					enabled: true,
				},
				transaction,
			});

			const limit = setting?.limit || params.workflowTerritories.length;
			workflowTerritories = params.workflowTerritories.slice(0, limit);
			salesManager = workflowTerritories.at(-1)?.territory_name || "-";
		}

		/** =============================
     *  DOC NO
     ============================= */
		params.doc_no = await generateDocNo({
			salesManager,
			distributorCode: distributor?.code || "-",
			distributorBranchCode: distributorBranch?.code || "-",
			transaction,
		});

		// duplicate doc_no protection
		const exists = await dbSAPPS.Udp.findOne({
			where: { doc_no: params.doc_no },
			transaction,
		});
		if (exists) throw new Error(`Doc No "${params.doc_no}" already exists`);

		/** =============================
     *  CREATE UDP
     ============================= */
		const udp = await dbSAPPS.Udp.create({ ...params, enabled: params.enabled ?? true }, { transaction });

		/** =============================
     *  PRODUCTS
     ============================= */
		if (params.udpproducts) {
			const products = typeof params.udpproducts === "string" ? JSON.parse(params.udpproducts) : params.udpproducts;

			const productPayload = products.map((p) => {
				if (!p.item_id) throw new Error("Product item_id is required");
				return {
					...p,
					udp_id: udp.id,
					creation: new Date(),
					modified: new Date(),
					modified_by: params.modified_by,
					owner: params.owner,
				};
			});

			await dbSAPPS.UdpProduct.bulkCreate(productPayload, { transaction });
		}

		/** =============================
     *  WORKFLOW
     ============================= */
		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: udp?.id,
					status: "Draft",
				};

				// console.log('workflowData : ', workflowData);

				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.",
						};
					}
				}
			}
		}

		await transaction.commit();
		return { success: true, message: "Data UDP successfully created" };
	} catch (err) {
		await transaction.rollback();
		console.error(err);
		return { success: false, message: err.message };
	}
}

function isMasterChanged(oldUdp, params) {
	return (
		oldUdp.distributor_id !== params.distributor_id ||
		oldUdp.distributorbranch_id !== params.distributorbranch_id ||
		oldUdp.territory_id !== params.territory_id ||
		Array.isArray(params.workflowTerritories)
	);
}

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

	try {
		const udp = await dbSAPPS.Udp.findByPk(id, { transaction });
		if (!udp) throw new Error("UDP not found");

		/** =============================
     *  CEK PERUBAHAN MASTER
     ============================= */
		const needRegenerateDocNo = isMasterChanged(udp, params);

		let newDocNo = udp.doc_no;

		if (needRegenerateDocNo) {
			/** Ambil master terbaru */
			const [distributor, distributorBranch] = await Promise.all([
				params.distributor_id ? dbSAPPS.Distributor.findByPk(params.distributor_id, { transaction }) : null,
				params.distributorbranch_id ? dbSAPPS.DistributorBranch.findByPk(params.distributorbranch_id, { transaction }) : null,
			]);

			/** Workflow → ambil salesManager */
			let salesManager = "-";
			if (params.workflowTerritories?.length) {
				const setting = await dbSAPPS.WorkflowSetting.findOne({
					where: {
						doctype_id: params.doctype_id,
						type: "Territory",
						enabled: true,
					},
					transaction,
				});

				const limit = setting?.limit || params.workflowTerritories.length;
				salesManager = params.workflowTerritories.slice(0, limit).at(-1)?.territory_name || "-";
			}

			/** Generate doc_no baru */
			newDocNo = await generateDocNo({
				salesManager,
				distributorCode: distributor?.code || "-",
				distributorBranchCode: distributorBranch?.code || "-",
				transaction,
			});

			/** Anti duplicate */
			const exists = await dbSAPPS.Udp.findOne({
				where: {
					doc_no: newDocNo,
					id: { [Op.ne]: id },
				},
				transaction,
			});

			if (exists) {
				throw new Error(`Generated Doc No "${newDocNo}" already exists`);
			}
		}

		await udp.update(
			{
				...params,
				doc_no: newDocNo, // ← AUTO UPDATE
			},
			{ transaction },
		);
		/** PRODUCTS */
		if (params.udpproducts) {
			const products = typeof params.udpproducts === "string" ? JSON.parse(params.udpproducts) : params.udpproducts;

			await dbSAPPS.UdpProduct.destroy({ where: { udp_id: id }, transaction });

			await dbSAPPS.UdpProduct.bulkCreate(
				products.map((p) => ({
					...p,
					udp_id: id,
					creation: new Date(),
					modified: new Date(),
					modified_by: params.modified_by,
					owner: params.owner,
				})),
				{ transaction },
			);
		}

		/** WORKFLOW */
		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.length === 0) {
					await transaction.rollback();
					return {
						success: false,
						message: `Workflow Territories is required. Transaction rolled back.`,
					};
				}

				// Delete existing workflow approvers and create new ones
				const workflow = await dbSAPPS.Workflow.findOne({ where: { doc_id: udp.id }, transaction });
				if (workflow) {
					await dbSAPPS.WorkflowApprover.destroy({ where: { workflow_id: workflow.id }, transaction });
					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,
					}));
					await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, { transaction });
				}
			}
		}

		await transaction.commit();
		return { success: true, message: "Data UDP successfully updated" };
	} catch (err) {
		await transaction.rollback();
		console.error(err);
		return { success: false, message: err.message };
	}
}

async function enabled(id, params) {
	try {
		const udp = await dbSAPPS.Udp.findByPk(id);
		if (!udp)
			return {
				success: false,
				message: "udp 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 udp.update(updateData);

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

async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const udp = await dbSAPPS.Udp.findByPk(id, { transaction });
		if (!udp) return { success: false, message: "UDP not found!" };
		const workflows = await dbSAPPS.Workflow.findAll({
			where: {
				doc_id: {
					[Op.eq]: udp.id,
				},
				doctype_id: {
					[Op.eq]: 24,
				},
			},
			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 udp.destroy({ transaction });

		await transaction.commit();

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