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

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

    saveSellingIn,
    sellingBySurveyId,

    saveExpense,
    submitExpense,
    expenseBySurveyId,

    saveTreatment,
    treatmentBySurveyId,

    bulkUpdateSubmit,
};



const stringNameMessage = "Survey";

// SURVEY
async function getAll(params) {
  try {
    // console.log('params survey : ', params)
    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),
                        };
                    }
                }
            }
        }


    const surveys = await dbSAPPS.Survey.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.Period,
          as: "period",
        },
        {
          model: dbSAPPS.SurveySellingIn,
          as: "surveysellingins",
        },
        {
          model: dbSAPPS.SurveyExpense,
          as: "surveyexpenses",
          include: [
            {
                model: dbSAPPS.AccountEntries,
                as: "accountentries",
                attributes: ['id', 'kerja_sama', 'outlet_id', 'period_id', 'sales_id', 'status', 'gimmickreq_id','type','surveyexpense_id', 'territory_id', 'year_id', 'quantity', 'createdAt', 'updatedAt']
            }
          ]
        },
        {
          model: dbSAPPS.SurveyTreatment,
          as: "surveytreatments",
        },
      ]
    });

    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 territoryItemGroup = await dbSAPPS.TerritoryItemGroup.findAll({
      attributes: ['territory_id', 'itemgroup_id'],
      include: [{
        model: dbSAPPS.ItemGroup,
        as: 'itemgroup',
        attributes: ['id', 'display_name']
      }]
    });

    const data = await Promise.all(surveys.map(async (item) => {
      const workflow = workflows.find(wf => wf.doc_id === item.id && wf.doctype_id === 23);
      const itemGroup = territoryItemGroup.filter(ig => ig.territory_id === item.territory_id)

      return {
        ...item.toJSON(),
        workflow: workflow ? workflow.dataValues : null,
        itemGroups: itemGroup ? itemGroup.map(ig => ig.itemgroup) : []
      };
    }));

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

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

        if (params.id) {
            filterConditions.id = params.id;
        }
        if (params.doc_no) {
            filterConditions.doc_no = params.doc_no;
        }
        if (params.territory_id) {
            filterConditions.territory_id = params.territory_id;
        }
        if (params.survey_id) {
            filterConditions.survey_id = params.survey_id;
        }
        if (params.year_id) {
            filterConditions.year_id = params.year_id;
        }
        if (params.period_id) {
            filterConditions.period_id = params.period_id;
        }


        const surveys = await dbSAPPS.Survey.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.Period,
                as: "period",
              },
              {
                model: dbSAPPS.SurveySellingIn,
                as: "surveysellingins",
              },
              {
                model: dbSAPPS.SurveyExpense,
                as: "surveyexpenses",
              },
              {
                model: dbSAPPS.SurveyTreatment,
                as: "surveytreatments",
              },
            ]
          });
      
          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 territoryItemGroup = await dbSAPPS.TerritoryItemGroup.findAll({
            attributes: ['territory_id', 'itemgroup_id'],
            include: [{
              model: dbSAPPS.ItemGroup,
              as: 'itemgroup',
              attributes: ['id', 'display_name']
            }]
          });
      
          const data = await Promise.all(surveys.map(async (item) => {
            const workflow = workflows.find(wf => wf.doc_id === item.id && wf.doctype_id === 23);
            const itemGroup = territoryItemGroup.filter(ig => ig.territory_id === item.territory_id).map(ig => ig.dataValues);
            return {
              ...item.toJSON(),
              workflow: workflow ? workflow.dataValues : null,
              itemGroup: itemGroup ? itemGroup.dataValues : null
            };
          }));
      

        return {
            surveys: data,
            success: true,
            message: `${stringNameMessage} loaded successfully!`,
        };

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

async function option() {
    try {
        const callPlans = await dbSAPPS.Survey.findAll({
            attributes: ["id", "display_name", "code", "company_id"],
            where: { enabled: true },
            order: [["display_name", "ASC"]],
            include: [
                {
                    model: dbSAPPS.Company,
                    as: "company",
                    attributes: ["id", "display_name"],
                },
                {
                    model: dbSAPPS.Specialist,
                    as: "specialists",
                    attributes: ["id", "display_name", "code"],
                    include: [
                        {
                            model: dbSAPPS.SubSpecialist,
                            as: 'subspecialists',
                            attributes: ["id", "display_name", "code"],
                        }
                    ]
                },
                {
                    model: dbSAPPS.ClientSubtype,
                    as: "clientsubtypes",
                    attributes: ["id", "display_name", "code"],
                },
            ],
        });

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

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

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

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

    try {
        // periksa apakah data dengan doc_no, territory_id, year_id, period_id sudah ada di dalam database
        const existingData = await dbSAPPS.Survey.findOne({ where: { territory_id: params.territory_id, year_id: params.year_id, period_id: params.period_id, company_id: params.company_id } });
        if (existingData) {
            return { success: false, message: `Survey already exists.` };
        }

        // Jika tidak ada nilai untuk beberapa field, set default value
        if (!params.creation) params.creation = Date.now();
        if (!params.modified) params.modified = Date.now();
        if (!params.modified_by) params.modified_by = 1;  // Default jika tidak ada nilai
        if (!params.owner) params.owner = 1;  // Default jika tidak ada nilai

        const territory = await dbSAPPS.Territory.findByPk(params.territory_id);
        if (!territory) {
            return {
                success: false,
                message: "Territory not found!"
            };
        }
        const period = await dbSAPPS.Period.findByPk(params.period_id);
        if (!period) {
            return {
                success: false,
                message: "Period not found!"
            };
        }
          
        const docNo = `SMS-SURVEY-${territory.display_name.toUpperCase()}-${period.display_name.toUpperCase()}`;
        params.doc_no = docNo;
    
        // Set default value untuk enabled
        if (!params.enabled) params.enabled = true;

        const survey = await dbSAPPS.Survey.create(params, { transaction });
     
        // buat workflow approval
        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: survey?.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.'
                        };
                    }
                }
            }
        }

        // Commit transaksi jika semua berhasil
        await transaction.commit();
        return {
            success: true,
            message: `${stringNameMessage} has been created!`
        };
    } catch (error) {
        // Rollback transaksi jika ada error
        await transaction.rollback();
        const errorMessage = error.message || `An error occurred during creating ${stringNameMessage}.`;
        return {
            success: false,
            message: errorMessage
        };
    }
}

async function update(id, params) {
    const transaction = await dbSAPPS.sequelize.transaction();
    try {
        const callPlan = await dbSAPPS.Survey.findByPk(id);
        if (!callPlan) return {
            success: false,
            message: `${stringNameMessage} not found!`
        };

        // Update Call Plan
        await callPlan.update(params, { transaction });

        // Pastikan `callplandestinations` adalah array yang valid
        if (params.callplandestinations) {
            // Jika formatnya string JSON, ubah menjadi array
            if (typeof params.callplandestinations === 'string') {
                try {
                    params.callplandestinations = JSON.parse(params.callplandestinations);
                } catch (err) {
                    return {
                        success: false,
                        message: 'Invalid JSON format for callplandestinations'
                    };
                }
            }

            // Jika `callplandestinations` adalah array dan memiliki data
            if (Array.isArray(params.callplandestinations) && params.callplandestinations.length > 0) {
                // console.log(' params.callplandestinations: ', params.callplandestinations);
                const uniqueDestinations = new Set();
                const duplicateIds = [];

                for (const destination of params.callplandestinations) {
                    const identifier = `${destination.client_id}-${destination.outlet_id}`;
                    if (uniqueDestinations.has(identifier)) {
                        duplicateIds.push(identifier);
                    } else {
                        uniqueDestinations.add(identifier);
                    }
                }

                if (duplicateIds.length > 0) {
                    await transaction.rollback();
                    return {
                        success: false,
                        message: `Duplicate client_id and outlet_id pairs: ${duplicateIds.join(', ')}`
                    };
                }

                // Hapus data lama terlebih dahulu
                await dbSAPPS.SurveyDestination.destroy({ where: { callplan_id: id }, transaction });

                // Persiapkan data baru untuk disimpan
                const callplandestinationsData = params.callplandestinations.map(destination => ({
                    ...destination,
                    creation: Date.now(),
                    modified: Date.now(),
                    modified_by: params.modified_by || 1,
                    owner: params.owner || 1,
                    callplan_id: parseInt(id)
                }));

                // Masukkan data anak yang baru
                try {
                    await dbSAPPS.SurveyDestination.bulkCreate(callplandestinationsData, { transaction });
                } catch (err) {
                    console.log(' err: ', err);
                    await transaction.rollback();
                    return {
                        success: false,
                        message: `Error saving callplandestinations: ${err.message || 'Unknown error'}`
                    };
                }
            } else {
                // Jika tidak ada data, hapus semua child terkait
                await dbSAPPS.SurveyDestination.destroy({ where: { callplan_id: id }, transaction });
            }

            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: callPlan?.id,
                        status: 'Draft',
                    }

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

                    // saya mau update dulu
                    // Cari dulu workflow sebelum update apakah sudah ada atau belum
                    const existingWorkflow = await dbSAPPS.Workflow.findOne({
                        where: {
                            doctype_id: workflowData.doctype_id,
                            doc_id: workflowData.doc_id
                        },
                        transaction,
                    });

                    if (existingWorkflow) {
                        // Jika workflow sudah ada, update status menjadi 'Draft'
                        await existingWorkflow.update({
                            modified: Date.now(),
                            modified_by: params.modified_by || 1,
                            owner: params.owner || 1,
                            status: 'Draft'
                        }, { transaction, lock: transaction.LOCK.UPDATE });

                        await dbSAPPS.WorkflowApprover.destroy({
                            where: {
                                workflow_id: existingWorkflow.id
                            }
                        }, { transaction, lock: transaction.LOCK.UPDATE });

                        const workflowApproversData = workflowTerritories?.map(workflowTerritory => ({
                            creation: Date.now(),
                            modified: Date.now(),
                            modified_by: params.modified_by || 1,
                            owner: params.owner || 1,
                            workflow_id: existingWorkflow.id,
                            territory_id: workflowTerritory?.territory_id,
                            status: null
                        }));

                        const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData.map(data => ({
                            ...data,
                            workflow_id: existingWorkflow.id
                        })), { transaction, lock: transaction.LOCK.UPDATE });

                        if (approvers?.length !== workflowTerritories.length || !approvers.every(approver => approver.workflow_id === existingWorkflow.id && approver.territory_id !== null)) {
                            await transaction.rollback();
                            return {
                                success: false,
                                message: 'Failed to create workflow approvers. Please try again later.'
                            };
                        }

                    } else {
                        // Jika workflow belum ada, create baru
                        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.'
                                };
                            }
                        } else {
                            await transaction.rollback();
                            return {
                                success: false,
                                message: `Failed to create workflow. Please try again later.`
                            };
                        }

                    }


                }
            }
        }

        // Commit transaksi jika semua sukses
        await transaction.commit();
        return {
            success: true,
            message: `${stringNameMessage} has been updated!`
        };
    } catch (error) {
        // Rollback jika terjadi error
        await transaction.rollback();
        const errorMessage = error.message || `An error occurred during updating ${stringNameMessage}.`;
        return {
            success: false,
            message: errorMessage
        };
    }
}

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

        await transaction.commit();

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

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

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

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

    try {
        // periksa apakah data dengan doc_no, territory_id, year_id, period_id sudah ada di dalam database
        // const existingData = await dbSAPPS.SurveySellingIn.findOne({ where: { survey_id: params.survey_id, outlet_id: params.outlet_id, company_id: params.company_id } });

        const existingData = await dbSAPPS.SurveySellingIn.findOne({ where: { survey_id: params.survey_id, outlet_id: params.outlet_id, company_id: params.company_id }, transaction });
        if (existingData) {
            await existingData.update(params, { transaction });
        } else {
            await dbSAPPS.SurveySellingIn.create(params, { transaction });
        }

        // Commit transaksi jika semua berhasil
        await transaction.commit();
        return {
            success: true,
            message: `Selling In has been saved!`
        };
    } catch (error) {
        // Rollback transaksi jika ada error
        await transaction.rollback();
        const errorMessage = error.message || `An error occurred during save Selling In.`;
        return {
            success: false,
            message: errorMessage
        };
    }
}

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

        if (params.survey_id) {
            filterConditions.survey_id = params.survey_id;
        }

        if (params.outlet_id) {
            filterConditions.outlet_id = params.outlet_id;
        }

        if (params.company_id) {
            filterConditions.company_id = params.company_id;
        }

        const sellingIns = await dbSAPPS.SurveySellingIn.findAll({
            order: [["modified", "DESC"]],
            where: filterConditions,
            include: [
                {
                    model: dbSAPPS.Company,
                    as: "company",
                    attributes: ["id", "display_name"],
                },
                {
                    model: dbSAPPS.Outlet,
                    as: "outlet",
                    attributes: ["id", "display_name"],
                },
            ],
        });

      
        return {
            sellingIns,
            success: true,
            message: `${stringNameMessage} loaded successfully!`,
        };

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

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

  try {
    let surveyExpenseRecord = await dbSAPPS.SurveyExpense.findOne({
      where: { survey_id: params.survey_id },
      transaction
    });

    if (!params.period_id || !params.year_id || !params.territory_id) {
        return {
            success: false,
            message: "Period, Year, and Territory are required!",
        };
    }

    if (surveyExpenseRecord) {
      await surveyExpenseRecord.update(params, { transaction });
    } else {
      surveyExpenseRecord = await dbSAPPS.SurveyExpense.create({...params, status: 'DRAFT'}, { transaction });
    }

    await transaction.commit();
    return {
      success: true,
      message: `Survey Expense has been saved!`
    };

  } catch (error) {
    await transaction.rollback();
    return {
      success: false,
      message: error.message || `An error occurred during saving Survey Expense.`
    };
  }
}


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

  try {
    let surveyExpenseRecord = await dbSAPPS.SurveyExpense.findOne({
      where: { survey_id: params.survey_id },
      transaction
    });

    if (!params.period_id || !params.year_id || !params.territory_id) {
        return {
            success: false,
            message: "Period, Year, and Territory are required!",
        };
    }

    if (surveyExpenseRecord) {
      await surveyExpenseRecord.update(params, { transaction });
    } else {
      surveyExpenseRecord = await dbSAPPS.SurveyExpense.create({...params, status: 2}, { transaction });
    }

    const balances = [];

    if(params.expense_details) {
        const surveyExpenseId = surveyExpenseRecord.id;
        const expenseDetails = params.expense_details;

        for (const outlet of expenseDetails) {
        const outletId = outlet.outlet_id;
            for (const sale of outlet.sales_detail) {
                const salesId = sale.id;
                const periodId = params.period_id;
                const yearId = params.year_id;
                const territoryId = params.territory_id;
                const itemId = sale.item_id;

                const clients = [
                ...(sale.doctor_data || []),
                ...(sale.patient_data || []),
                ...(sale.assistant_data || []),
                ...(sale.procurement_data || [])
                ];

                for (const client of clients) {
                const clientId = client.doctor_id || client.patient_id || client.assistant_id || client.procurement_id || null;
                const kerjaSama = client.kerja_sama || null;
                const quantity = client.qty_doctor || client.qty_patient || client.qty_assistant || client.qty_procurement || null;

                if (!clientId) continue;

                balances.push({
                    type: "debit",
                    surveyexpense_id: surveyExpenseId,
                    sales_id: salesId,
                    outlet_id: outletId,
                    client_id: clientId,
                    territory_id: territoryId,
                    item_id: itemId,
                    period_id: periodId,
                    year_id: yearId,
                    kerja_sama: kerjaSama,
                    quantity: quantity,
                    status: 0,
                    enabled: params.enabled,
                    modified_by: params.modified_by,
                    owner:params.owner,
                    creation: new Date(),
                    modified: new Date()
                });
                }
            }
        }
            if (balances.length > 0) {
            await dbSAPPS.AccountEntries.bulkCreate(balances, { transaction });
        }
    }

    await transaction.commit();
    return {
      success: true,
      message: `Survey Expense has been submitted!`,
    };

  } catch (error) {
    await transaction.rollback();
    return {
      success: false,
      message: error.message || `An error occurred during saving Survey Expense.`
    };
  }
}
   

async function expenseBySurveyId(id) {
    try {
        const expense = await dbSAPPS.SurveyExpense.findOne({
            where: { survey_id: id },
            include: [
                {
                    model: dbSAPPS.AccountEntries,
                    as: "accountentries",
                    attributes: ['id', 'kerja_sama', 'outlet_id', 'period_id', 'sales_id', 'status', 'surveyexpense_id', 'territory_id', 'year_id', 'quantity', 'createdAt', 'updatedAt']
                }
            ]
        });
        return {
            expense,
            success: true,
            message: `Survey Expense loaded successfully!`,
        };

    } catch (error) {
        console.error("Error retrieving Survey Expense: ", error);
        const errorMessage =
            error.message ||
            "Failed to retrieve Survey Expense. Please try again later.";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

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

    try {
        const existingData = await dbSAPPS.SurveyTreatment.findOne({ where: { survey_id: params.survey_id, outlet_id: params.outlet_id }, transaction });
        if (existingData) {
            await existingData.update(params, { transaction });
        } else {
            await dbSAPPS.SurveyTreatment.create(params, { transaction });
        }

        // Commit transaksi jika semua berhasil
        await transaction.commit();
        return {
            success: true,
            message: `Survey Treatment has been saved!`
        };
    } catch (error) {
        // Rollback transaksi jika ada error
        await transaction.rollback();
        const errorMessage = error.message || `An error occurred during creating Survey Treatment.`;
        return {
            success: false,
            message: errorMessage
        };
    }
}

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

        if (params.survey_id) {
            filterConditions.survey_id = params.survey_id;
        }

        if (params.outlet_id) {
            filterConditions.outlet_id = params.outlet_id;
        }

        const treatment = await dbSAPPS.SurveyTreatment.findAll({
            order: [["modified", "DESC"]],
            where: filterConditions,
            include: [
                {
                    model: dbSAPPS.Outlet,
                    as: "outlet",
                    attributes: ["id", "display_name"],
                },
            ],
        });

        return {
            treatment,
            success: true,
            message: `Treatment loaded successfully!`,
        };

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

async function bulkUpdateSubmit(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    //  update doc_status sellingIn, expense, treatment
    const updateConditions = params.map(param => ({
      model: param.sellingInExist ? dbSAPPS.SurveySellingIn : param.expenseExist ? dbSAPPS.SurveyExpense : dbSAPPS.SurveyTreatment,
      where: { survey_id: param.survey_id },
      update: { status: param.doc_status },
    }));

    await Promise.all(updateConditions.map(condition => condition.model.update(condition.update, condition.where, { transaction })));

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


