const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const { Op, where } = require("sequelize");
const nodemailer = require("nodemailer");
const moment = require("moment");
const emailNotification = require("./emailNotification");
const fs = require("fs");
const path = require("path");

module.exports = {
  generateDocumentNo,
  create,
  getAll,
  option,
  count,
  update,
  bulkUpdateReceiver,
  sendEmailNotification,
  enabled,
  delete: _delete,
};

/**
 * Format an array of gimmickRequests by appending a modifier field to each company.
 * The modifier field is the full name of the user who last modified the company.
 * @param {Object[]} gimmickRequests - The array of gimmickRequests to format.
 * @returns {Promise<Object[]>} - The formatted array of gimmickRequests.
 */
const formatWithModifier = async (gimmickRequests) => {
  return Promise.all(
    gimmickRequests.map(async (gimmickRequest) => {
      const modifier = await dbSAPPS.User.findOne({
        where: {
          id: gimmickRequest.modified_by,
        },
        attributes: ["full_name"],
      });

      return {
        ...gimmickRequest,
        modifier: modifier?.full_name,
      };
    }),
  );
};

// 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: [],
      mailTo: null,
      hierarchyEmail: "",
    };
  }

  const childs = territoryVersion.territoryversionchilds;
  const hierarchy = [];

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

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

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

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

  // =========================
  // 🔥 MAIL LOGIC
  // =========================
  const mailTo = hierarchy[0]?.email || null;

  const ccEmails = hierarchy
    .slice(1) // buang pembuat
    .map((h) => h.email)
    .filter(Boolean); // buang null

  const limitedCC = ccEmails.length > 3 ? ccEmails.slice(0, 3) : ccEmails.slice(0, 2);

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

async function sendEmailNotification(params) {
  try {
    const gimmickrequest = await dbSAPPS.GimmickRequest.findOne({
      where: { id: params.id },
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.GimmickReqType,
          as: "gimmickrequesttype",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.GimmickReqSubType,
          as: "gimmickrequestsubtype",
          attributes: ["id", "display_name"],
        },
      ],
    });

    if (!gimmickrequest) throw new Error("Gimmick Request document not found");

    const workflow = await dbSAPPS.Workflow.findOne({
      where: {
        doc_id: {
          [Op.eq]: gimmickrequest.id,
        },
        doctype_id: 176,
      },
      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 allocationData = await dbSAPPS.GimmickAllocation.findAll({
      where: {
        gimmickreq_id: gimmickrequest.id,
      },
      include: [
        {
          model: dbSAPPS.Item,
          as: "item",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.GimmickAllocationOutlet,
          as: "gimmickallocationoutlets",
          attributes: ["outlet_id", "gimmickallocation_id"],
          include: [
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              attributes: ["id", "display_name"],
            },
          ],
        },
      ],
    });

    const receiverData = await dbSAPPS.GimmickReceiver.findAll({
      where: {
        gimmickreq_id: gimmickrequest.id,
      },
      include: [
        {
          model: dbSAPPS.BankAccount,
          as: "bankaccount",
          attributes: [
            "id",
            "display_name",
            "account_number",
            "branch",
            "description",
            "employee_id",
            "client_id",
            "company_id",
            "type",
            "bank_id",
            "name",
            "is_verified",
          ],
          include: [
            {
              model: dbSAPPS.Bank,
              as: "bank",
              attributes: ["id", "name", "display_name", "code"],
            },
          ],
        },
      ],
    });

    const client = await dbSAPPS.Client.findOne({
      where: {
        id: gimmickrequest.client_id,
      },
      attributes: ["id", "display_name"],
      include: [
        {
          model: dbSAPPS.BankAccount,
          as: "bankaccounts",
          attributes: [
            "id",
            "display_name",
            "account_number",
            "branch",
            "bank_id",
            "type",
            "client_id",
            "name",
            "description",
            "enabled",
          ],
          include: [
            {
              model: dbSAPPS.Bank,
              as: "bank",
              attributes: ["id", "display_name", "code", "enabled"],
              where: { enabled: true },
            },
          ],
        },
      ],
    });

    const accountEntries = await dbSAPPS.AccountEntries.findAll({
      where: {
        gimmickreq_id: gimmickrequest.id,
        type: "credit",
        enabled: true,
      },
      attributes: ["id", "gimmickreq_id", "item_id", "client_id", "territory_id", "kerja_sama", "quantity", "status"],
      include: [
        {
          model: dbSAPPS.Item,
          as: "item",
          attributes: ["id", "display_name", "price"],
        },
        {
          model: dbSAPPS.Client,
          as: "client",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
      ],
    });

    const employee = await dbSAPPS.Employee.findOne({
      where: {
        id: gimmickrequest.employee_id,
      },
      attributes: ["id", "employee_name"],
    });

    const territoryItemgroup = await dbSAPPS.TerritoryItemGroup.findOne({
      where: {
        territory_id: gimmickrequest.territory_id,
      },
      attributes: ["itemgroup_id"],
      include: [
        {
          model: dbSAPPS.ItemGroup,
          as: "itemgroup",
          attributes: ["id", "display_name"],
        },
      ],
    });

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

    const data = {
      ...gimmickrequest.get({ plain: true }),
      doc_no: gimmickrequest.doc_no,
      creation: gimmickrequest.creation,
      accountentries: accountEntries ? accountEntries.map((accountEntry) => accountEntry.get({ plain: true })) : null,
      employee: employee ? employee.get({ plain: true }) : null,
      client: client ? client.get({ plain: true }) : null,
      client_name: client?.display_name || null,
      territoryitemgroup: territoryItemgroup ? territoryItemgroup.get({ plain: true }) : null,
      allocation: allocationData ? allocationData.map((allocation) => allocation.get({ plain: true })) : null,
      receiver: receiverData ? receiverData.map((receiver) => receiver.get({ plain: true })) : null,
      workflow: workflow ? workflow.get({ plain: true }) : null,
      hierarchy: hierarchy ? hierarchy : null,
      hierarchyEmail: hierarchyEmail ? hierarchyEmail : null,
      mailTo: mailTo ? mailTo : null,
    };

    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 = [];

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

    const mailOptions = {
      from: "noreply-sapps@sapharma.co.id",
      to: data.mailTo,
      cc: dataCC,
      subject: `Gimmick Transfer Realization Notification - ${data.doc_no}`,
      html,
      attachments,
    };

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

/**
 * Get all gimmickRequests with their respective companies.
 * @param {Object} params - An object that contains parameters to filter the gimmickRequests.
 * @param {Array} params.companiesId - Array of company IDs to filter by.
 * @returns {Promise<Object>} Resolves with an object that contains the gimmickRequests and a success flag.
 * @example
 * const result = await getAll({ companiesId: [1, 2] });
 * console.log(result);
 */
async function getAll(params) {
  try {
    // filter
    const filterConditions = {};

    if (params?.is_workflow_direct === true || params?.is_workflow_direct === "true") {
      if (params.doc_id) {
        filterConditions.id = {
          [Op.eq]: parseInt(params.doc_id),
        };
      }
    } else {
      if (
        params.access_read_all_data === true ||
        params.access_read_all_data === "true" ||
        params.administrator_of_msd === true ||
        params.administrator_of_msd === "true" ||
        params.administrator_of_finance === true ||
        params.administrator_of_finance === "true"
      ) {
        filterConditions.territory_id = {
          [Op.not]: null,
        };
      } else {
        if (params.territory_id && params.territory_id.length > 0) {
          filterConditions.territory_id = {
            [Op.in]: params?.territory_id?.map((id) => parseInt(id)) || [],
          };
        } else {
          if (params.territory_id) {
            filterConditions.territory_id = {
              [Op.eq]: parseInt(params.territory_id),
            };
          }
        }
      }
    }

    let gimmickRequests = [];
    gimmickRequests = await dbSAPPS.GimmickRequest.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.GimmickReqType,
          as: "gimmickrequesttype",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.GimmickReqSubType,
          as: "gimmickrequestsubtype",
          attributes: ["id", "display_name"],
        },
      ],
    });

    gimmickRequests = await Promise.all(
      gimmickRequests.map(async (gimmickRequest) => {
        const workflow = await dbSAPPS.Workflow.findOne({
          where: {
            doc_id: gimmickRequest.id,
            doctype_id: 176,
            ...(params?.administrator_of_finance === true ? { status: "Approved" } : {}),
          },
          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 allocationData = await dbSAPPS.GimmickAllocation.findAll({
          where: {
            gimmickreq_id: gimmickRequest.id,
          },
          include: [
            {
              model: dbSAPPS.Item,
              as: "item",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.GimmickAllocationOutlet,
              as: "gimmickallocationoutlets",
              attributes: ["outlet_id", "gimmickallocation_id"],
              include: [
                {
                  model: dbSAPPS.Outlet,
                  as: "outlet",
                  attributes: ["id", "display_name"],
                },
              ],
            },
          ],
        });

        const receiverData = await dbSAPPS.GimmickReceiver.findAll({
          where: {
            gimmickreq_id: gimmickRequest.id,
          },
          include: [
            {
              model: dbSAPPS.BankAccount,
              as: "bankaccount",
              attributes: [
                "id",
                "display_name",
                "account_number",
                "branch",
                "description",
                "employee_id",
                "client_id",
                "company_id",
                "type",
                "bank_id",
                "name",
                "is_verified",
              ],
              include: [
                {
                  model: dbSAPPS.Bank,
                  as: "bank",
                  attributes: ["id", "name", "display_name", "code"],
                },
              ],
            },
          ],
        });

        const client = await dbSAPPS.Client.findOne({
          where: {
            id: gimmickRequest.client_id,
          },
          attributes: ["id", "display_name"],
          include: [
            {
              model: dbSAPPS.BankAccount,
              as: "bankaccounts",
              attributes: [
                "id",
                "display_name",
                "account_number",
                "branch",
                "bank_id",
                "type",
                "client_id",
                "name",
                "description",
                "enabled",
              ],
              include: [
                {
                  model: dbSAPPS.Bank,
                  as: "bank",
                  attributes: ["id", "display_name", "code", "enabled"],
                  where: { enabled: true },
                },
              ],
            },
          ],
        });

        const accountEntries = await dbSAPPS.AccountEntries.findAll({
          where: {
            gimmickreq_id: gimmickRequest.id,
            type: "credit",
            enabled: true,
          },
          attributes: [
            "id",
            "gimmickreq_id",
            "item_id",
            "client_id",
            "territory_id",
            "kerja_sama",
            "quantity",
            "status",
          ],
          include: [
            {
              model: dbSAPPS.Item,
              as: "item",
              attributes: ["id", "display_name", "price"],
            },
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "display_name"],
            },
          ],
        });

        const employee = await dbSAPPS.Employee.findOne({
          where: {
            id: gimmickRequest.employee_id,
          },
          attributes: ["id", "employee_name"],
        });

        const territoryItemgroup = await dbSAPPS.TerritoryItemGroup.findOne({
          where: {
            territory_id: gimmickRequest.territory_id,
          },
          attributes: ["itemgroup_id"],
          include: [
            {
              model: dbSAPPS.ItemGroup,
              as: "itemgroup",
              attributes: ["id", "display_name"],
            },
          ],
        });

        return {
          ...gimmickRequest.dataValues,
          accountentries: accountEntries ? accountEntries.map((accountEntry) => accountEntry.dataValues) : null,
          company_name: gimmickRequest?.company?.display_name,
          territory_name: gimmickRequest?.territory?.display_name,
          gimmickrequesttype_name: gimmickRequest?.gimmickrequesttype?.display_name,
          gimmickrequestsubtype_name: gimmickRequest?.gimmickrequestsubtype?.display_name,
          client: client ? client : null,
          client_name: client?.display_name || null,
          workflow: workflow ? workflow.dataValues : null,
          allocation: allocationData ? allocationData.map((allocation) => allocation.dataValues) : null,
          receiver: receiverData ? receiverData.map((receiver) => receiver.dataValues) : null,
          employee_name: employee ? employee.employee_name : null,
          territoryitemgroup: territoryItemgroup ? territoryItemgroup.dataValues : null,
        };
      }),
    );

    gimmickRequests = await formatWithModifier(gimmickRequests);

    const search = (params?.search || "").toString().trim().toLowerCase();
    const filterTerritoryId = params?.f_territory_id ? parseInt(params.f_territory_id, 10) : null;
    const filterTerritoryName = (params?.f_territory_name || "").toString().trim().toLowerCase();
    const filterYear = params?.f_year ? parseInt(params.f_year, 10) : null;
    const filterPeriodId = params?.f_period_id ? parseInt(params.f_period_id, 10) : null;
    const filterPeriodName = (params?.f_period_name || "").toString().trim().toLowerCase();

    let filteredRows = gimmickRequests.filter((row) => {
      const sourceDate = row?.periode || row?.request_date;
      const sourceDateObj = sourceDate ? new Date(sourceDate) : null;
      const rowYear = sourceDateObj && !Number.isNaN(sourceDateObj.getTime()) ? sourceDateObj.getFullYear() : null;

      const rowPeriodName =
        sourceDateObj && !Number.isNaN(sourceDateObj.getTime())
          ? moment(sourceDateObj).format("MMM-YYYY").toLowerCase()
          : null;

      const rowPeriodeNumeric = Number(row?.periode);

      const matchTerritory = filterTerritoryName
        ? (row?.territory_name || "").toString().toLowerCase() === filterTerritoryName
        : true;
      const matchTerritoryId = filterTerritoryId ? parseInt(row?.territory_id, 10) === filterTerritoryId : true;
      const matchYear = filterYear && !filterPeriodId ? rowYear === filterYear : true;
      const matchPeriodId = filterPeriodId ? rowPeriodeNumeric === filterPeriodId : true;
      const matchPeriodName = filterPeriodName && !filterPeriodId ? rowPeriodName === filterPeriodName : true;

      if (!search) {
        return matchTerritory && matchTerritoryId && matchYear && matchPeriodId && matchPeriodName;
      }

      const searchableText = [
        row?.doc_no,
        row?.client_name,
        row?.territory_name,
        row?.company_name,
        row?.gimmickrequesttype_name,
        row?.gimmickrequestsubtype_name,
        row?.workflow?.status,
      ]
        .filter(Boolean)
        .join(" ")
        .toLowerCase();

      return matchTerritory && matchTerritoryId && matchYear && matchPeriodId && matchPeriodName && searchableText.includes(search);
    });

    const sortBy = (params?.sortBy || "modified").toString();
    const sortDir = (params?.sortDir || "DESC").toString().toUpperCase() === "ASC" ? "ASC" : "DESC";
    const sortFactor = sortDir === "ASC" ? 1 : -1;

    filteredRows = filteredRows.sort((a, b) => {
      const getSortValue = (row) => {
        if (sortBy === "workflow.status") return row?.workflow?.status || "";
        return row?.[sortBy] ?? "";
      };

      const av = getSortValue(a);
      const bv = getSortValue(b);

      const ad = new Date(av);
      const bd = new Date(bv);
      const adValid = !Number.isNaN(ad.getTime());
      const bdValid = !Number.isNaN(bd.getTime());

      if (adValid && bdValid) {
        return (ad.getTime() - bd.getTime()) * sortFactor;
      }

      if (typeof av === "number" && typeof bv === "number") {
        return (av - bv) * sortFactor;
      }

      return av.toString().localeCompare(bv.toString()) * sortFactor;
    });

    const statusCounts = filteredRows.reduce(
      (acc, row) => {
        const status = row?.workflow?.status;
        if (status === "Approved") acc.approved += 1;
        else if (status === "Draft") acc.draft += 1;
        else if (status === "Rejected") acc.rejected += 1;
        else if (status === "Pending" || status === "Need Approval" || status === "On Progress") acc.pending += 1;
        return acc;
      },
      { approved: 0, draft: 0, rejected: 0, pending: 0 },
    );

    const totalCount = filteredRows.length;
    const pageIndex = Number.isFinite(Number(params?.pageIndex)) ? parseInt(params.pageIndex, 10) : null;
    const pageSize = Number.isFinite(Number(params?.pageSize)) ? parseInt(params.pageSize, 10) : null;

    const pagedRows =
      pageIndex !== null && pageSize !== null && pageSize > 0
        ? filteredRows.slice(pageIndex * pageSize, pageIndex * pageSize + pageSize)
        : filteredRows;

    return {
      gimmickRequests: pagedRows,
      totalCount,
      statusCounts: {
        total: totalCount,
        approved: statusCounts.approved,
        draft: statusCounts.draft,
        rejected: statusCounts.rejected,
        pending: statusCounts.pending,
      },
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Get gimmickRequests with their respective settings.
 * @param {Object} params - An object that contains parameters to filter the gimmickRequests.
 * @param {Array} params.companiesId - Array of company IDs to filter by.
 * @returns {Promise<Object>} Resolves with an object that contains the gimmickRequests and a success flag.
 * @example
 * const result = await option({ companiesId: [1, 2] });
 * console.log(result);
 */
async function option(params) {
  try {
    // filter
    const filterConditions = {};

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

    // must be enabled
    filterConditions.enabled = {
      [Op.eq]: true,
    };

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

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

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

/**
 * Count active gimmickRequests
 *
 * @returns {Promise<{
 *   count: number,
 *   success: boolean,
 *   message: string
 * }>}
 */
async function count(params) {
  try {
    // filter
    const filterConditions = {};

    // must be enabled
    filterConditions.enabled = {
      [Op.eq]: true,
    };

    const count = await dbSAPPS.GimmickRequest.count({
      where: filterConditions,
    });

    return {
      count,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Creates a new gimmickRequest
 *
 * @param {object} params - gimmickRequest data to be created.
 * @param {string} params.name - gimmickRequest name.
 * @param {string} params.display_name - gimmickRequest display name.
 * @param {boolean} [params.enabled=true] - gimmickRequest enabled status.
 * @param {number} [params.creation=Date.now()] - gimmickRequest creation timestamp.
 * @param {number} [params.modified=Date.now()] - gimmickRequest last modification timestamp.
 * @param {number} [params.owner=1] - User ID of the gimmickRequest owner.
 * @param {number} [params.modified_by=1] - User ID of the last modifier of the gimmickRequest.
 *
 * @returns {Promise<{success: boolean, message: string}>} Result of the gimmickRequest creation process.
 */
async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const existingDocNo = await dbSAPPS.GimmickRequest.findOne({
      where: { doc_no: params.doc_no, company_id: params.company_id },
      transaction,
    });
    if (existingDocNo) {
      return {
        success: false,
        message: `"${params.doc_no}" is already taken.`,
      };
    }

    // save gimmick request
    const gimmickRequest = await dbSAPPS.GimmickRequest.create(params, { transaction });

    // save allocation data
    if (params?.allocationData && params.allocationData.length > 0) {
      if (typeof params.allocationData === "string") {
        params.allocationData = JSON.parse(params.allocationData);
      }

      // Validasi dan persiapkan data untuk bulkCreate
      const allocationData = params.allocationData.map((ad) => {
        // Validasi field yang diperlukan
        if (!ad.item_id) {
          throw new Error("Missing required fields in product list.");
        }

        return {
          ...ad, // Menggunakan ad bukan allocationData (yang belum didefinisikan)
          creation: Date.now(),
          modified: Date.now(),
          modified_by: params.modified_by,
          owner: params.owner,
          gimmickreq_id: gimmickRequest.id,
          enabled: true,
        };
      });

      // Lakukan bulkCreate untuk GimmickAllocation
      const createdAllocations = await dbSAPPS.GimmickAllocation.bulkCreate(allocationData, { transaction });

      // Persiapkan data untuk GimmickAllocationOutlet
      const outletData = [];

      createdAllocations.forEach((ca, index) => {
        if (params.allocationData[index].outlets && params.allocationData[index].outlets.length > 0) {
          params.allocationData[index].outlets.forEach((outlet) => {
            outletData.push({
              gimmickallocation_id: ca.id, // Menggunakan ID yang baru dibuat
              outlet_id: outlet.outlet_id,
              outlet_name: outlet.outlet_name,
              creation: Date.now(),
              modified: Date.now(),
              modified_by: params.modified_by,
              owner: params.owner,
              enabled: true,
            });
          });
        }
      });

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

      // Lakukan bulkCreate untuk GimmickAllocationOutlet
      if (outletData.length > 0) {
        await dbSAPPS.GimmickAllocationOutlet.bulkCreate(outletData, { transaction });
      }
    }

    // save receiver data
    if (params?.receiverData && params.receiverData.length > 0) {
      if (typeof params.receiverData === "string") {
        params.receiverData = JSON.parse(params.receiverData);
      }
      const receiverData = params?.receiverData.map((rd) => {
        // Validasi field yang diperlukan
        if (!rd.bankaccount_id) {
          throw new Error("Missing required fields in bank account id.");
        }

        return {
          ...rd,
          creation: Date.now(),
          modified: Date.now(),
          modified_by: params.modified_by,
          owner: params.owner,
          gimmickreq_id: gimmickRequest.id,
        };
      });

      // Lakukan bulkCreate untuk GimmickAllocation
      const createReceivers = await dbSAPPS.GimmickReceiver.bulkCreate(receiverData, { transaction });
    }

    // save
    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: gimmickRequest?.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: "Saved successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to save, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Updates an existing gimmickRequest
 *
 * @param {number} id - gimmickRequest ID to be updated.
 * @param {object} params - gimmickRequest data to be updated.
 * @param {string} [params.name] - gimmickRequest name.
 * @param {string} [params.display_name] - gimmickRequest display name.
 * @param {boolean} [params.enabled] - gimmickRequest enabled status.
 * @param {number} [params.creation] - gimmickRequest creation timestamp.
 * @param {number} [params.modified] - gimmickRequest last modification timestamp.
 * @param {number} [params.owner] - User ID of the gimmickRequest owner.
 * @param {number} [params.modified_by] - User ID of the last modifier of the gimmickRequest.
 *
 * @returns {Promise<{success: boolean, message: string}>} Result of the gimmickRequest update process.
 */

async function update(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Validate existing doc_no
    const existingDocNo = await dbSAPPS.GimmickRequest.findOne({
      where: {
        doc_no: params.doc_no,
        company_id: params.company_id,
        id: { [Op.ne]: id },
      },
      transaction,
    });

    if (existingDocNo) {
      await transaction.rollback();
      return {
        success: false,
        message: `"${params.doc_no}" is already taken.`,
      };
    }

    // Find and update gimmick request
    const gimmickRequest = await dbSAPPS.GimmickRequest.findByPk(id, { transaction });
    if (!gimmickRequest) {
      await transaction.rollback();
      return {
        success: false,
        message: `Gimmick Request with ID ${id} not found.`,
      };
    }

    await gimmickRequest.update(params, { transaction });

    // Process allocation data if exists
    if (params?.allocationData && params.allocationData.length > 0) {
      // Parse if allocationData is string
      if (typeof params.allocationData === "string") {
        try {
          params.allocationData = JSON.parse(params.allocationData);
        } catch (e) {
          await transaction.rollback();
          return {
            success: false,
            message: "Invalid allocationData format",
          };
        }
      }

      // Process each allocation item
      for (const allocationItem of params.allocationData) {
        // Validate required fields
        if (!allocationItem.item_id) {
          await transaction.rollback();
          return {
            success: false,
            message: "Missing item_id in allocation data",
          };
        }

        // Find or create allocation
        let allocation;
        const existingAllocation = await dbSAPPS.GimmickAllocation.findOne({
          where: {
            gimmickreq_id: gimmickRequest.id,
            item_id: allocationItem.item_id,
          },
          transaction,
        });

        if (existingAllocation) {
          // Update existing allocation
          allocation = await existingAllocation.update(
            {
              ...allocationItem,
              modified: Date.now(),
              modified_by: params.modified_by,
            },
            { transaction },
          );
        } else {
          // Create new allocation
          allocation = await dbSAPPS.GimmickAllocation.create(
            {
              ...allocationItem,
              creation: Date.now(),
              modified: Date.now(),
              modified_by: params.modified_by,
              owner: params.owner,
              gimmickreq_id: gimmickRequest.id,
              enabled: true,
            },
            { transaction },
          );
        }

        // Process outlets for this allocation
        if (allocationItem.outlets && allocationItem.outlets.length > 0) {
          // Delete existing outlets for this allocation
          await dbSAPPS.GimmickAllocationOutlet.destroy({
            where: { gimmickallocation_id: allocation.id },
            transaction,
          });

          // Prepare new outlet data
          const outletData = allocationItem.outlets.map((outlet) => ({
            gimmickallocation_id: allocation.id,
            outlet_id: outlet.outlet_id,
            outlet_name: outlet.outlet_name || null,
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by,
            owner: params.owner,
            enabled: true,
          }));

          // Create new outlets
          await dbSAPPS.GimmickAllocationOutlet.bulkCreate(outletData, { transaction });
        }
      }
    }

    // Process receiver data if exists
    if (params?.receiverData && params.receiverData.length > 0) {
      // Parse if receiverData is string
      if (typeof params.receiverData === "string") {
        try {
          params.receiverData = JSON.parse(params.receiverData);
        } catch (e) {
          await transaction.rollback();
          return {
            success: false,
            message: "Invalid receiverData format",
          };
        }
      }

      // Delete existing receivers
      await dbSAPPS.GimmickReceiver.destroy({
        where: { gimmickreq_id: gimmickRequest.id },
        transaction,
      });

      // Create new receivers
      const receiverData = params.receiverData.map((rd) => ({
        ...rd,
        creation: Date.now(),
        modified: Date.now(),
        modified_by: params.modified_by,
        owner: params.owner,
        gimmickreq_id: gimmickRequest.id,
      }));

      await dbSAPPS.GimmickReceiver.bulkCreate(receiverData, { transaction });
    }

    // Process workflow data if exists
    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",
        };
      }

      // Find or create workflow
      let workflow = await dbSAPPS.Workflow.findOne({
        where: {
          doctype_id: params.doctype_id,
          doc_id: gimmickRequest.id,
        },
        transaction,
      });

      if (!workflow) {
        workflow = await dbSAPPS.Workflow.create(
          {
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by || 1,
            owner: params.owner || 1,
            doctype_id: params.doctype_id,
            doc_id: gimmickRequest.id,
            status: "Draft",
          },
          { transaction },
        );
      }

      // Delete existing approvers
      await dbSAPPS.WorkflowApprover.destroy({
        where: { workflow_id: workflow.id },
        transaction,
      });

      // Create new approvers
      const workflowApproversData = workflowTerritories.map((wt) => ({
        creation: Date.now(),
        modified: Date.now(),
        modified_by: params.modified_by || 1,
        owner: params.owner || 1,
        workflow_id: workflow.id,
        territory_id: wt.territory_id,
        status: null,
      }));

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

    await transaction.commit();
    return {
      success: true,
      message: "Updated successfully!",
      data: { gimmickRequest, is_balance_posted: gimmickRequest.is_balance_posted },
    };
  } catch (error) {
    await transaction.rollback();
    console.error("Update error:", error);
    return {
      success: false,
      message: error.message || "Failed to update, please try again!",
    };
  }
}

/**
 * Bulk update gimmickRequestReceiver
 * @param {object} params - the update data
 * @returns {Promise<object>} - the result of the update
 */
/**
 * Bulk update transfer records
 * @param {Array} updates - Array of objects containing update data
 * @returns {Promise<Object>} - Result of the bulk update operation
 */
async function bulkUpdateReceiver(updates) {
  // Validate inputs
  if (!Array.isArray(updates) || updates.length === 0) {
    throw new Error("Updates must be a non-empty array");
  }

  try {
    // Get current timestamp
    const modified = Date.now();

    // Prepare the updates
    const updatePromises = updates.map((update) => {
      // Validate each update object
      if (!update.id || typeof update.id !== "number") {
        throw new Error("Each update must contain a valid id");
      }

      // Create the update object with only allowed fields
      const validUpdate = {
        realization_status: update.realization_status || "On Progress",
        transfer_scheduled_date: update.transfer_scheduled_date,
        enabled: update.enabled !== undefined ? update.enabled : 1,
        modified,
      };

      // Here you would typically call your database update function
      // For example, if using a database model:
      return dbSAPPS.GimmickReceiver.update(validUpdate, { where: { id: update.id } });

      // If you're using raw SQL, you might do:
      // return database.query('UPDATE transfers SET ? WHERE id = ?', [validUpdate, update.id]);
    });

    // Execute all updates
    const results = await Promise.all(updatePromises);

    return {
      success: true,
      message: `Successfully updated ${results.length} records`,
    };
  } catch (error) {
    console.error("Bulk update failed:", error);
    throw error;
  }
}

/**
 * Enable or disable a gimmickRequest
 * @param {number} id - the id of the gimmickRequest
 * @param {object} params - the update data
 * @param {boolean} params.enabled - the enabled status of the gimmickRequest
 * @param {number} [params.modified_by] - the modified by of the gimmickRequest
 * @param {Date} [params.modified] - the modified of the gimmickRequest
 * @returns {Promise<object>} - the result of the update
 */
async function enabled(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const gimmickRequest = await dbSAPPS.GimmickRequest.findByPk(id);
    if (!gimmickRequest) return { success: false, message: "ID not found!" };

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

    // save
    await gimmickRequest.update(updateData, { transaction });

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

/**
 * Delete a gimmickRequest
 * @param {number} id - the id of the gimmickRequest
 * @returns {Promise<object>} - the result of the delete
 */
async function _delete(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const gimmickRequest = await dbSAPPS.GimmickRequest.findByPk(id, { transaction });
    if (!gimmickRequest) return { success: false, message: "ID not found!" };
    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.eq]: gimmickRequest.id,
        },
        doctype_id: {
          [Op.eq]: 176,
        },
      },
      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 gimmick request not found!" };

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

    // delete
    await gimmickRequest.destroy({ transaction });

    await transaction.commit();
    return {
      success: true,
      message: "Deleted successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to delete, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function generateDocumentNo(params) {
  try {
    const param = params.params;
    // GR/ACEH/06/2025/0001
    const lastDocumentNo = await dbSAPPS.GimmickRequest.findOne({
      where: {
        doc_no: {
          [Op.like]: `%${param.territory}/${param.month}/${param.year}%`,
        },
      },
      order: [["doc_no", "DESC"]],
    });

    let documentNo;
    if (lastDocumentNo) {
      const lastNo = parseInt(lastDocumentNo.doc_no.split("/").pop(), 10);
      documentNo = `GR/${param.territory}/${param.month}/${param.year}/${String(lastNo + 1).padStart(4, "0")}`;
    } else {
      documentNo = `GR/${param.territory}/${param.month}/${param.year}/0001`;
    }

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