const { Op, where } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const moment = require('moment');
const { BULKUPDATE } = require("sequelize/lib/query-types");

module.exports = {
  // client ks / clientcollab
  getAll,
  filter,
  option,
  count,
  create,
  update,
  enabled,
  delete: _delete,

  getByTerritoryPeriod,

  createDetails,
  bulkUpdateSubmit,
  getByCollabId,
  resetDetails,

  deleteDetails,
};

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

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

            // untuk mendapatkan data ks yang masih dalam period
            if(params.input_date) {
                filterConditions.end_date = {
                    [Op.gte]: new Date(params.input_date)
                }
            }
        }
      }


    const clientCollabs = await dbSAPPS.ClientCollab.findAll({
      order: [["modified", "DESC"]],
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ClientCollabDetail,
          as: "clientcollabdetails",
          include: [
            {
              model: dbSAPPS.Item,
              as: "item",
              attributes: ["id", "display_name", "item_cor"],
            },
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              // attributes: ["id", "display_name"],
            },
          ]
        }
      ]
    });

    //  const workflows = await dbSAPPS.Workflow.findAll({
    //             attributes: ['id', 'doc_id', 'doctype_id', 'status'],
    //             include: [{
    //                 model: dbSAPPS.WorkflowApprover,
    //                 as: 'workflowapprovers',
    //                 attributes: ['id', 'workflow_id', 'territory_id', 'status'],
    //                 include: [{
    //                     model: dbSAPPS.Territory,
    //                     as: 'territory',
    //                     attributes: ['id', 'name', 'display_name']
    //                 }]
    //             }]
    //         });

     const data = await Promise.all(
        clientCollabs.map(async (item) => {
          const workflow = await dbSAPPS.Workflow.findOne({
            where: {
              doc_id: item.id,
              doctype_id: 25
            },
            attributes: ['id', 'doc_id', 'doctype_id', 'status'],
            include: [
              {
                model: dbSAPPS.WorkflowApprover,
                as: 'workflowapprovers',
                attributes: ['id', 'workflow_id', 'territory_id', 'status'],
                include: [
                  {
                    model: dbSAPPS.Territory,
                    as: 'territory',
                    attributes: ['id', 'name', 'display_name']
                  }
                ]
              }
            ]
          });

          return {
            ...item.toJSON(),
            workflow
          };
        })
      );
   

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

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

  let territoryVersionChilds = [];
  let territories;
  if (params && params.territory_id) {
      const today = new Date();
      const getTerritoryVersion = await dbSAPPS.TerritoryVersion.findAll({
          attributes: ['id', 'display_name', 'valid_from', 'valid_to', 'enabled'],
          where: {
              [Op.and]: [
                  { valid_from: { [Op.lte]: today } },
                  { [Op.or]: [{ valid_to: { [Op.gte]: today } }, { valid_to: null }] },
              ],
          },
          include: [{ model: dbSAPPS.TerritoryVersionChild, as: 'territoryversionchilds', attributes: ['id', 'name', 'parent_id', 'territory_id'], }]
      });

      if (getTerritoryVersion && getTerritoryVersion.length > 0) {
          territoryVersionChilds = getTerritoryVersion.reduce((acc, curr) => {
              return [...acc, ...curr.territoryversionchilds];
          }, []);
      }
      const hierarchyTerritoryVersionChilds = (parent_id = null) => {
          const children = territoryVersionChilds.filter((c) => c.parent_id === parent_id);
          const hierarchy = children.map((child) => ({
              id: child?.territory_id,
              name: child?.name,
              children: hierarchyTerritoryVersionChilds(child?.territory_id),
          }));
          return hierarchy;
      };

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

  }

  if (params.company_id) {
    filterConditions.company_id = {
      [Op.like]: `%${params.company_id}%`,
    };
  }

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

  try {
    const clientCollabs = await dbSAPPS.ClientCollab.findAll({
      order: [["modified", "DESC"]],
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ClientCollabDetail,
          as: "clientcollabdetails",
          include: [
            {
              model: dbSAPPS.Item,
              as: "item",
              attributes: ["id", "display_name", "item_cor"],
            },
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              // attributes: ["id", "display_name"],
            },
          ]
        }
      ]
    });

     const workflows = await dbSAPPS.Workflow.findAll({
                attributes: ['id', 'doc_id', 'doctype_id', 'status'],
                include: [{
                    model: dbSAPPS.WorkflowApprover,
                    as: 'workflowapprovers',
                    attributes: ['id', 'workflow_id', 'territory_id', 'status'],
                    include: [{
                        model: dbSAPPS.Territory,
                        as: 'territory',
                        attributes: ['id', 'name', 'display_name']
                    }]
                }]
            });

     const data = clientCollabs.map(item => {
      const workflow = workflows.find(wf => wf.doc_id === item.id && wf.doctype_id === 25);
        return {
          ...item.toJSON(),
          workflow: workflow ? workflow.dataValues : null,
        }
     })       

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

async function option() {
  try {
    const clientCollabs = await dbSAPPS.ClientCollab.findAll({
      attributes: ["id", "doc_no", 'territory_id', 'compay_id' ],
      where: { enabled: true },
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
      ]
    });

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

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

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

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // cek unique adata
    const uniqueData = await dbSAPPS.ClientCollab.findOne({
      where: {
        territory_id: params.territory_id,
        company_id: params.company_id,
        start_date: params.start_date,
        end_date: params.end_date,
      },
    });

    if(uniqueData) {
      const startDate = moment(params.start_date).format('YYYY-MM-DD');
      const endDate = moment(params.end_date).format('YYYY-MM-DD');
      return {
        success: false,
        message: `Data Client KS dengan Territory id : ${params.territory_id} dan masa berlaku ${startDate} - ${endDate} sudah ada !`
      }
    }


    const territory = await dbSAPPS.Territory.findByPk(params.territory_id);
    if (!territory) {
        return {
            success: false,
            message: "Territory not found!"
        };
    }

    const lastClientCollab = await dbSAPPS.ClientCollab.findOne({
        where: {
            territory_id: params.territory_id
        },
        order: [["id", "DESC"]],
        attributes: ["doc_no"],
    });

    const currentNumber = lastClientCollab ? parseInt(lastClientCollab.doc_no.split('-')[3], 10) : 0;
    const newNumber = currentNumber + 1;
    const docNo = `SMS-KS-${territory.display_name.toUpperCase()}-${newNumber.toString().padStart(4, '0')}`;

    params.doc_no = docNo;

    // Set default value untuk enabled
    if (!params.enabled) params.enabled = true;

    const clientCollabs = await dbSAPPS.ClientCollab.create(params, transaction);


    // create workflow
      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: clientCollabs?.id,
              status: 'Draft',
          }

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

          if (workflow) {
              const workflowApproversData = workflowTerritories?.map(workflowTerritory => ({
                  creation: Date.now(),
                  modified: Date.now(),
                  modified_by: params.modified_by || 1,
                  owner: params.owner || 1,
                  workflow_id: workflow.id,
                  territory_id: workflowTerritory?.territory_id,
                  status: 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: "Client KS data has been successfully added!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage =
      error.message || "An error occurred during create Client KS processing.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function update(id, params) {
  try {
    const clientCollabs = await dbSAPPS.ClientCollab.findByPk(id);
    if (!clientCollabs)
      return {
        success: false,
        message: "Client KS  not found!",
      };

    if (params.display_name && params.display_name !== clientCollabs.display_name) {
      const existingclientCollabsName = await dbSAPPS.ClientCollab.findOne({
        where: { display_name: params.display_name },
      });
      if (existingclientCollabsName) {
        return {
          success: false,
          message: `Client KS name "${params.display_name}" is already taken. Try again!`,
        };
      }
    }

    await clientCollabs.update(params);

    return {
      success: true,
      message: "Data Client KS  has been saved!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to update Client KS  data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

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

async function _delete(id) {
    const transaction = await dbSAPPS.sequelize.transaction();
    try {
        const clientCollab = await dbSAPPS.ClientCollab.findByPk(id, { transaction });
        if (!clientCollab) return { success: false, message: 'Client KS not found!' };
        const workflows = await dbSAPPS.Workflow.findAll({
            where: {
                doc_id: {
                    [Op.eq]: clientCollab.id
                },
                doctype_id: {
                    [Op.eq]: 25
                }
            },
            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 client collab 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 clientCollab.destroy({ transaction });

        await transaction.commit();

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

async function getByCollabId(params) {
  try {
    const toNumber = (val) => {
      const num = Number(val);
      return isNaN(num) ? 0 : num;
    };

    const makeKey = (clientId, itemId) => `${clientId}_${itemId}`;

    const clientCollabDetails = await dbSAPPS.ClientCollabDetail.findAll({
      where: { clientcollab_id: params?.id },
      include: [{
        model: dbSAPPS.Item,
        as: 'item',
        attributes: ['id',  'display_name', 'item_cor']
      }]
  } );

    if (!Array.isArray(clientCollabDetails) || clientCollabDetails.length === 0) {
      return {
        clientCollabDetails: [],
        success: true,
        message: "Client KS Detail data has been successfully loaded !",
      };
    }

    const uniquePairs = Array.from(
      new Set(
        clientCollabDetails
          .filter((row) => row?.client_id != null && row?.item_id != null)
          .map((row) => makeKey(row.client_id, row.item_id))
      )
    );

    const clientIds = Array.from(
      new Set(
        uniquePairs
          .map((pair) => parseInt(String(pair).split('_')[0], 10))
          .filter((id) => !isNaN(id))
      )
    );

    const itemIds = Array.from(
      new Set(
        uniquePairs
          .map((pair) => parseInt(String(pair).split('_')[1], 10))
          .filter((id) => !isNaN(id))
      )
    );

    const saldoMap = {};

    if (clientIds.length > 0 && itemIds.length > 0) {
      const saldoAwals = await dbSAPPS.SaldoAwalClient.findAll({
        where: {
          enabled: true,
          client_id: { [Op.in]: clientIds },
          item_id: { [Op.in]: itemIds },
        },
        attributes: ['client_id', 'item_id', 'type', 'balance'],
      });

      for (const s of saldoAwals) {
        const key = makeKey(s.client_id, s.item_id);
        saldoMap[key] ??= { initial_debit: 0, initial_credit: 0, debit: 0, credit: 0, balance: 0 };
        const balance = toNumber(s.balance);
        if (s.type === 'debit') saldoMap[key].initial_debit += balance;
        if (s.type === 'credit') saldoMap[key].initial_credit += balance;
      }

      const accountEntries = await dbSAPPS.AccountEntries.findAll({
        where: {
          status: 1,
          client_id: { [Op.in]: clientIds },
          item_id: { [Op.in]: itemIds },
        },
        attributes: ['id', 'type', 'client_id', 'item_id', 'quantity', 'kerja_sama'],
        include: [
          {
            model: dbSAPPS.Item,
            as: 'item',
            attributes: ['id', 'price'],
          },
          {
            model: dbSAPPS.GimmickRequest,
            as: 'gimmickreq',
            attributes: ['id', 'periode'],
          },
        ],
      });

      for (const entry of accountEntries) {
        const key = makeKey(entry.client_id, entry.item_id);
        saldoMap[key] ??= { initial_debit: 0, initial_credit: 0, debit: 0, credit: 0, balance: 0 };

        const price = toNumber(entry?.item?.price);
        const qty = toNumber(entry?.quantity);
        const ks = toNumber(entry?.kerja_sama) / 100;

        if (entry?.type === 'debit') {
          const val = price * qty * ks;
          saldoMap[key].debit += val;
        }

        if (entry?.type === 'credit') {
          const periode = toNumber(entry?.gimmickreq?.periode || 1);
          const val = price * qty * ks * periode;
          saldoMap[key].credit += val;
        }
      }

      Object.keys(saldoMap).forEach((key) => {
        const row = saldoMap[key];
        row.balance =
          toNumber(row.initial_debit) +
          toNumber(row.debit) -
          (toNumber(row.initial_credit) + toNumber(row.credit));
      });
    }

    const clientCollabDetailsWithSaldo = clientCollabDetails.map((row) => {
      const key = makeKey(row.client_id, row.item_id);
      const balance = toNumber(saldoMap[key]?.balance || 0);
      return {
        ...(row?.toJSON ? row.toJSON() : row),
        saldo: balance,
      };
    });
    
    // Commit the transaction if all operations succeed
    return {
      clientCollabDetails: clientCollabDetailsWithSaldo,
      success: true,
      message: "Client KS Detail data has been successfully loaded !",
    };
  } catch (error) {
    // Rollback the transaction in case of error
    const errorMessage =
      error.message || "An error occurred during create Client KS Detail loaded.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

// async function createDetails(params) {
//   const transaction = await dbSAPPS.sequelize.transaction();
//   try {
//     // Hapus data dengan params clientcollab_id, client_id, outlet_id
//     await dbSAPPS.ClientCollabDetail.destroy({
//       where: {
//         clientcollab_id: params[0].clientcollab_id,
//         client_id: params[0].client_id,
//         outlet_id: params[0].outlet_id,
//       },
//       transaction,
//     });

//     try {
//       params.forEach(param => delete param.id);

//       await dbSAPPS.ClientCollabDetail.bulkCreate(params, { transaction, validate: true });
//     } catch (error) {
//       const validationErrors = error.errors.map(err => err.message);
//       const errorMessage = `Validation error: ${validationErrors.join(', ')}`;
//       throw new Error(errorMessage);
//     }
 
//     // Commit the transaction if all operations succeed
//     await transaction.commit();
//     return {
//       success: true,
//       message: "Client KS Detail data has been successfully added/updated!",
//     };
//   } catch (error) {
//     // Rollback the transaction in case of error
//     await transaction.rollback();
//     const errorMessage =
//       error.message || "An error occurred during create Client KS processing.";
//     return {
//       success: false,
//       message: errorMessage,
//     };
//   }
// }


// tolol
async function createDetails(params) {
    const transaction = await dbSAPPS.sequelize.transaction();
    try {    
      try {
        const dataToCreate = params.filter(param => param.is_submitted !== 1).map(param => ({
          ...param,
          is_saved: 1,
        }));
        await dbSAPPS.ClientCollabDetail.bulkCreate(dataToCreate, { transaction, validate: true });
      } catch (error) {
        const validationErrors = error.errors.map(err => err.message);
        const errorMessage = `Validation error: ${validationErrors.join(', ')}`;
        throw new Error(errorMessage);
      }
   
      // Commit the transaction if all operations succeed
      await transaction.commit();
      return {
        success: true,
        message: "Client KS Detail data has been successfully added/updated!",
      };
    } catch (error) {
      // Rollback the transaction in case of error
      await transaction.rollback();
      const errorMessage =
        error.message || "An error occurred during create Client KS processing.";
      return {
        success: false,
        message: errorMessage,
      };
    }
  }
  


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

    for (const param of params) {
      await dbSAPPS.ClientCollabDetail.update(
        { is_submitted: param.is_submitted },
        {
          where: {
            client_id: param.client_id,
            outlet_id: param.outlet_id,
            clientcollab_id: param.clientcollab_id,
          },
          transaction,
        }
      );
    }

    // Commit the transaction if all operations succeed
    await transaction.commit();
    return {
      success: true,
      message: "Client KS Detail data has been successfully bulk-updated!",
    };
  } catch (error) {
    // Rollback the transaction in case of error
    await transaction.rollback();
    const errorMessage =
      error.message || "An error occurred during bulk update Client KS processing.";
    return {
      success: false,
      message: errorMessage,
    };
  }
} 

async function resetDetails(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Hapus data dengan params clientcollab_id, client_id, outlet_id
    if (Array.isArray(params) && params.length > 0) {
      const deletionConditions = params.map(param => ({
        clientcollab_id: param.clientcollab_id,
        client_id: param.client_id,
        outlet_id: param.outlet_id
      }));

      await dbSAPPS.ClientCollabDetail.destroy({
        where: {
          [Op.or]: deletionConditions
        },
        transaction,
      });
    }

    // Commit the transaction if all operations succeed
    await transaction.commit();
    return {
      success: true,
      message: "Client KS Detail data has been successfully reset!",
    };
  } catch (error) {
    // Rollback the transaction in case of error
    await transaction.rollback();
    const errorMessage =
      error.message || "An error occurred during reset Client KS processing.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function getByTerritoryPeriod(params) {
  try {
    const period = await dbSAPPS.Period.findByPk(params.period_id);
    if (!period) {
      return {
        success: false,
        message: "Period not found",
      };
    }

    if (!params.territory_id) {
      return {
        success: false,
        message: "Territory not found",
      };
    }

    const clientCollabs = await dbSAPPS.ClientCollab.findAll({
      where: {
        territory_id: params.territory_id,
        end_date: {
          [Op.gt]: period.end_date,
        },
      },
      include: [
        {
          model: dbSAPPS.ClientCollabDetail,
          as: 'clientcollabdetails',
          include: [
            { model: dbSAPPS.Item, as: 'item', attributes: ['id', 'display_name', 'item_cor'] },
            { model: dbSAPPS.Client, as: 'client', attributes: ['id', 'display_name'] },
            { model: dbSAPPS.Outlet, as: 'outlet', attributes: ['id', 'display_name'] },
          ],
        },
      ],
    });

    // cari workflow nya
    const workflows = await dbSAPPS.Workflow.findAll({
      attributes: ['id', 'doc_id', 'doctype_id', 'status'],
      include: [{
              model: dbSAPPS.WorkflowApprover,
              as: 'workflowapprovers',
              attributes: ['id', 'workflow_id', 'territory_id', 'status'],
              include: [{
                  model: dbSAPPS.Territory,
                  as: 'territory',
                  attributes: ['id', 'name', 'display_name']
              }]
          }]
      });

      const data = clientCollabs.map(item => {
      const workflow = workflows.find(wf => wf.doc_id === item.id && wf.doctype_id === 25);
      return {
      ...item.toJSON(),
      workflow: workflow ? workflow.dataValues : null,
      }
      })       

    // Commit the transaction if all operations succeed
    return {
      clientCollabs: data,
      success: true,
      message: "Client KS data has been successfully loaded !",
    };
  } catch (error) {
    // Rollback the transaction in case of error
    const errorMessage =
      error.message || "An error occurred during create Client KS Detail loaded.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function deleteDetails(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const ClientCollabDetail = await dbSAPPS.ClientCollabDetail.findByPk(id, { transaction });
    if (!ClientCollabDetail) return { success: false, message: 'Client KS Detail not found!' };
    
    await ClientCollabDetail.destroy({ transaction });
    await transaction.commit();

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





