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 WKHTML_PATH =
  os.platform() === "win32"
    ? "C:\\Program Files\\wkhtmltopdf\\bin\\wkhtmltopdf.exe"
    : "/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**",
      },
      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,
    };
  }
}
