const { text } = require("body-parser");
const dbSAPPS = require("../helpers/db");
require("dotenv").config();
const { Op, where } = require("sequelize");
const moment = require("moment");
const { filter } = require("../masters/udps/udp.service");
const { array } = require("joi");

module.exports = {
  reportMasterList,
  reportAttendance,
  reportAttendanceByEmployee,
  reportCPR,
  reportSalesContribution,
  reportSalesTarget,
  reportVisitVerify,
  reportGoodsReceipt,
  reportEmployeeAttendanceRate,
  reportSaldoBalanceBreakTahunan,
  reportSaldoBalanceUser,
  reportUcCostRealization,
  reportUcAttendance,
  reportUpCountry,
  reportGimmickTransferRealization,
};

/**
 * Recursive function to get all the ids from the array of objects
 * @param {array} arr - array of objects
 * @returns {array} - array of ids
 */
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;
}

function getDuration(clock_in, clock_out) {
  // ubah string ke format Date (hari bebas, yg penting jam:menit:detik ada)
  const inTime = new Date(`1970-01-01T${clock_in}`);
  const outTime = new Date(`1970-01-01T${clock_out}`);

  // selisih dalam ms
  const diffMs = outTime - inTime;

  // convert ke jam, menit, detik
  const hours = Math.floor(diffMs / (1000 * 60 * 60));
  const minutes = Math.floor((diffMs % (1000 * 60 * 60)) / (1000 * 60));
  const seconds = Math.floor((diffMs % (1000 * 60)) / 1000);

  return `${hours} jam ${minutes} menit ${seconds} detik`;
}

// sumber data master list
async function reportMasterList(params) {
  try {
    let territoryVersion = [];
    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) {
        territoryVersion = getTerritoryVersion;
        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 = await getAllIds(territoryDatas && territoryDatas.length > 0 ? territoryDatas : []);
    }

    const condition = Array.isArray(territories) ? { [Op.in]: territories } : { [Op.eq]: territories };

    const fetchData = await dbSAPPS.MasterList.findAll({
      where: {
        territory_id: condition,
      },
      include: [
        { model: dbSAPPS.Territory, as: "territory" },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          include: [
            { model: dbSAPPS.Province, as: "province" },
            { model: dbSAPPS.City, as: "city" },
          ],
        },
        {
          model: dbSAPPS.Client,
          as: "client",
          include: [
            {
              model: dbSAPPS.ClientType,
              as: "clienttype",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Specialist,
              as: "specialist",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.SubSpecialist,
              as: "subspecialist",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.ClientSubtype,
              as: "clientsubtype",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Province,
              as: "province",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.City,
              as: "city",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.Company,
              as: "company",
              attributes: ["id", "display_name"],
            },
          ],
        },
      ],
    });

    const mappingData = fetchData.map((item) => ({
      territory_id: item.territory?.id || null,
      client_id: item.client?.id || null,
      client_province_id: item.client?.province?.id || null,
      client_city_id: item.client?.city?.id || null,
      clienttype_id: item.client?.clienttype?.id || null,
      specialist_id: item.client?.specialist?.id || null,
      subspecialist_id: item.client?.subspecialist?.id || null,
      clientsubtype_id: item.client?.clientsubtype?.id || null,
      outlet_id: item.outlet?.id || null,
      outlet_province_id: item.outlet?.province?.id || null,
      outlet_city_id: item.outlet?.city?.id || null,

      territory_name: item.territory?.display_name || null,
      client_name: item.client?.display_name || null,
      client_code: item.client?.code || null,
      client_priority: item.client?.priority || null,
      start_balance: item.client?.start_balance || null,
      credit_balance: item.client?.credit_balance || null,
      debit_balance: item.client?.debit_balance || null,
      final_balance: item.client?.final_balance || null,
      client_province_name: item.client?.province?.display_name || null,
      client_city_name: item.client?.city?.display_name || null,
      clienttype_name: item.client?.clienttype?.display_name || null,
      specialist_name: item.client?.specialist?.display_name || null,
      subspecialist_name: item.client?.subspecialist?.display_name || null,
      clientsubtype_name: item.client?.clientsubtype?.display_name || null,
      outlet_name: item.outlet?.display_name || null,
      outlet_code: item.outlet?.code || null,
      outlet_province_name: item.outlet?.province?.display_name || null,
      outlet_city_name: item.outlet?.city?.display_name || null,
    }));

    const uniqueClient = mappingData.reduce((acc, item) => {
      if (!acc.some((existingItem) => existingItem.client_id === item.client_id)) {
        acc.push({
          territory_id: item.territory_id,
          client_id: item.client_id,
          client_province_id: item.client_province_id,
          client_city_id: item.client_city_id,
          clienttype_id: item.clienttype_id,
          specialist_id: item.specialist_id,
          subspecialist_id: item.subspecialist_id,
          clientsubtype_id: item.clientsubtype_id,

          client_code: item.client_code,
          client_province_name: item.client_province_name,
          client_name: item.client_name,
          start_balance: item.start_balance,
          credit_balance: item.credit_balance,
          debit_balance: item.debit_balance,
          final_balance: item.final_balance,
          territory_name: item.territory_name,
          client_city_name: item.client_city_name,
          clienttype_name: item.clienttype_name,
          specialist_name: item.specialist_name,
          subspecialist_name: item.subspecialist_name,
          clientsubtype_name: item.clientsubtype_name,
          client_priority: item.client_priority,
        });
      }
      return acc;
    }, []);

    const doctorData = uniqueClient?.filter((item) => item.clienttype_name === "Doctor");
    const patientData = uniqueClient?.filter((item) => item.clienttype_name === "Patient");
    const procurementData = uniqueClient?.filter((item) => item.clienttype_name === "Procurement");
    const assistantData = uniqueClient?.filter((item) => item.clienttype_name === "Assistant");

    return {
      mappingData,
      uniqueClient,
      doctorData: doctorData,
      patientData: patientData,
      procurementData: procurementData,
      assistantData: assistantData,
      success: true,
      message: "Report master list have been loaded!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load report master list.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

// Simple in-memory cache for holidays
let cachedHolidays = null;
let lastHolidayFetch = 0;
const HOLIDAY_CACHE_TTL = 60 * 60 * 1000; // 1 hour

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

    if (params.employee_id) {
      filterConditions.employee_id = {
        [Op.eq]: params.employee_id,
      };
    }

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

    if (params.from_date && params.to_date) {
      filterConditions.date = {
        [Op.between]: [params.from_date, params.to_date],
      };
    } else if (params.date) {
      filterConditions.date = {
        [Op.eq]: params.date,
      };
    }

    const attendances = await dbSAPPS.Attendance.findAll({
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Employee,
          as: "employee",
          attributes: ["id", "employee_name", "employee_number"],
        },
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ShiftType,
          as: "shifttype",
          attributes: ["id", "display_name"],
        },
      ],
      order: [["modified", "DESC"]],
    });

    const filterConditionsEmployee = {
      enabled: true,
      shifttype_id: {
        [Op.not]: null,
      },
    };

    if (params.employee_id) {
      filterConditionsEmployee.id = {
        [Op.eq]: params.employee_id,
      };
    }

    if (params.company_id) {
      filterConditionsEmployee.company_id = {
        [Op.eq]: params.company_id,
      };
    }

    const employees = await dbSAPPS.Employee.findAll({
      where: filterConditionsEmployee,
      attributes: ["id", "employee_name", "employee_number"],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
      ],
      order: [["employee_name", "ASC"]],
    });

    const year = new Date().getFullYear();
    const now = Date.now();
    let holidays;

    // OPTIMIZATION: Use cached holidays if valid
    if (cachedHolidays && (now - lastHolidayFetch < HOLIDAY_CACHE_TTL)) {
      holidays = cachedHolidays;
    } else {
        holidays = await dbSAPPS.Holiday.findAll({
          where: {
            enabled: true,
            holiday_date: {
              [Op.between]: [`${year}-01-01`, `${year}-12-31`],
            },
          },
          attributes: ["id", "holiday_date", "is_national_holiday", "is_weekend_holiday", "display_name", "holidaygroup_id", "enabled"],
        });
        cachedHolidays = holidays;
        lastHolidayFetch = now;
    }

    // OPTIMIZATION: Create Maps for O(1) lookup
    const attendanceMap = new Map();
    // Iterate attendances and map by employee_id.
    // Use first found (matching original find() behavior which returns first element)
    if (attendances) {
      for (const att of attendances) {
        if (!attendanceMap.has(att.employee_id)) {
          attendanceMap.set(att.employee_id, att);
        }
      }
    }

    const holidayMap = new Map();
    if (holidays) {
      for (const h of holidays) {
        holidayMap.set(h.holiday_date, h);
      }
    }

    const employeeAttendance = employees.map((employee) => {
      // Optimized lookup
      const attendance = attendanceMap.get(employee.id);
      
      const checkDate = attendance ? attendance.date : params?.date;
      const holiday = holidayMap.get(checkDate) || null;

      return {
        employee_id: employee.id,
        employee_name: employee.employee_name,
        employee_number: employee.employee_number,
        employee: attendance
          ? { employee_name: attendance.employee.employee_name }
          : { employee_name: employee.employee_name },
        company: attendance
          ? { display_name: attendance.company.display_name }
          : { display_name: employee?.company?.display_name },
        company_id: attendance ? attendance.company_id : null,
        company_name: attendance ? attendance.company.display_name : employee?.company?.display_name,
        shifttype: attendance ? { display_name: attendance.shifttype.display_name } : null,
        shifttype_id: attendance ? attendance.shifttype_id : null,
        holiday: holiday ? { display_name: holiday.display_name } : null,
        day_status: holiday
          ? holiday.is_national_holiday
            ? "National Holiday"
            : "Holiday"
          : attendance
          ? moment(attendance.date ? attendance.date : params?.date).format("dddd") === "Saturday" ||
            moment(attendance.date ? attendance.date : params?.date).format("dddd") === "Sunday"
            ? "Day Off"
            : "Work Day"
          : null,
        date: attendance ? attendance.date : params?.date,
        in_time: attendance ? attendance.in_time : null,
        out_time: attendance ? attendance.out_time : null,
        in_status: attendance ? attendance.in_status : null,
        out_status: attendance ? attendance.out_status : null,
        in_distance: attendance ? attendance.in_distance : null,
        out_distance: attendance ? attendance.out_distance : null,
        in_address: attendance ? attendance.in_address : null,
        out_address: attendance ? attendance.out_address : null,
        total_hour: attendance ? attendance.total_hour : null,
        notes: attendance ? attendance.notes : null,
        reason: attendance ? attendance.reason : null,
        in_latitude: attendance ? attendance.in_latitude : null,
        in_longitude: attendance ? attendance.in_longitude : null,
        out_latitude: attendance ? attendance.out_latitude : null,
        out_longitude: attendance ? attendance.out_longitude : null,
        status_absenceclarification: attendance ? attendance.status_absenceclarification : null,
        status_woriking_in_dayoff: attendance ? attendance.status_woriking_in_dayoff : null,
      };
    });

    return {
      attendances,
      employeeAttendance,
      success: true,
      message: "attendance loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to retrieve attendance. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

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

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

    if (params.from_date && params.to_date) {
      filterConditions.date = {
        [Op.between]: [params.from_date, params.to_date],
      };
    } else if (params.date) {
      filterConditions.date = {
        [Op.eq]: params.date,
      };
    }

    const attendances = await dbSAPPS.Attendance.findAll({
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Employee,
          as: "employee",
          attributes: ["id", "employee_name", "employee_number"],
        },
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ShiftType,
          as: "shifttype",
          attributes: ["id", "display_name"],
        },
      ],
      order: [
        ["employee", "employee_name", "ASC"],
        ["modified", "DESC"],
      ],
    });

    if (params.employee_id) {
      filterConditionsEmployee.id = {
        [Op.in]: params?.employee_id,
      };
    }

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

    filterConditionsEmployee.enabled = true;
    filterConditionsEmployee.shifttype_id = {
      [Op.not]: null,
    };

    const employees = await dbSAPPS.Employee.findAll({
      where: filterConditionsEmployee,
      attributes: ["id", "employee_name", "employee_number"],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ShiftType,
          as: "shifttype",
          attributes: ["id", "display_name"],
        },
      ],
      order: [["employee_name", "ASC"]],
    });

    const year = new Date().getFullYear();
    const now = Date.now();
    let holidays;

    // OPTIMIZATION: Use cached holidays if valid
    if (cachedHolidays && (now - lastHolidayFetch < HOLIDAY_CACHE_TTL)) {
      holidays = cachedHolidays;
    } else {
        holidays = await dbSAPPS.Holiday.findAll({
          where: {
            enabled: true,
            holiday_date: {
              [Op.between]: [`${year}-01-01`, `${year}-12-31`],
            },
          },
          attributes: ["id", "holiday_date", "is_national_holiday", "is_weekend_holiday", "display_name", "holidaygroup_id", "enabled"],
        });
        cachedHolidays = holidays;
        lastHolidayFetch = now;
    }

    const dates = [];
    if (params.from_date && params.to_date) {
      const fromDate = moment(params.from_date);
      const toDate = moment(params.to_date);
      while (fromDate.isSameOrBefore(toDate)) {
        dates.push(fromDate.format("YYYY-MM-DD"));
        fromDate.add(1, "days");
      }
    }

    // OPTIMIZATION: Create Maps for O(1) lookup
    const attendanceMap = new Map();
    if (attendances) {
      for (const att of attendances) {
        // Create a unique key using employee_id and date
        const key = `${att.employee_id}_${att.date}`;
        // Preserve first-found behavior (by order of fetch which is modified DESC)
        // Original code used filter() then took [0], which matches first found.
        if (!attendanceMap.has(key)) {
          attendanceMap.set(key, att);
        }
      }
    }

    const holidayMap = new Map();
    if (holidays) {
      for (const h of holidays) {
        holidayMap.set(h.holiday_date, h);
      }
    }

    const dataExportProcessing = await Promise.all(
      employees &&
        employees.map(async (employee) => {
          const dataWithDate = await Promise.all(
            dates.map(async (date) => {
              // Optimized lookup
              const attKey = `${employee.id}_${date}`;
              const matchedDataAttendance = attendanceMap.get(attKey);
              
              const matchedDataHariLibur = holidayMap.get(date);

              const dayStatus = matchedDataHariLibur?.is_national_holiday
                ? "National Holiday"
                : moment(date).format("dddd") === "Saturday" || moment(date).format("dddd") === "Sunday"
                ? "Day Off"
                : "Work Day";

              const newDataExportProcessing = {
                employee_id: employee?.id,
                employee_name: employee?.employee_name,
                date: date,
                day: moment(date).format("dddd"),
                day_status: dayStatus,
                holiday_name: matchedDataHariLibur?.display_name,
                attendance_id: matchedDataAttendance?.id ? matchedDataAttendance?.id : null,
                schedule_in: employee?.shifttype?.late_time ? employee?.shifttype?.late_time : "",
                schedule_out: employee?.shifttype?.ealy_time ? employee?.shifttype?.ealy_time : "",
                in_time: matchedDataAttendance?.in_time ? matchedDataAttendance?.in_time : "",
                out_time: matchedDataAttendance?.out_time ? matchedDataAttendance?.out_time : "",
                in_status: matchedDataAttendance?.in_status ? matchedDataAttendance?.in_status : "",
                out_status: matchedDataAttendance?.out_status ? matchedDataAttendance?.out_status : "",
                in_distance: matchedDataAttendance?.in_distance ? matchedDataAttendance?.in_distance : "",
                out_distance: matchedDataAttendance?.out_distance ? matchedDataAttendance?.out_distance : "",
                in_address: matchedDataAttendance?.in_address ? matchedDataAttendance?.in_address : "",
                out_address: matchedDataAttendance?.out_address ? matchedDataAttendance?.out_address : "",
                total_hour: matchedDataAttendance?.total_hour ? matchedDataAttendance?.total_hour : "",
                reason: matchedDataAttendance?.reason ? matchedDataAttendance?.reason : "",
                notes: matchedDataAttendance?.notes ? matchedDataAttendance?.notes : "",
                company: employee?.company?.display_name,
                status_absenceclarification: matchedDataAttendance?.status_absenceclarification
                  ? matchedDataAttendance?.status_absenceclarification
                  : false,
                status_woriking_in_dayoff: matchedDataAttendance?.status_woriking_in_dayoff
                  ? matchedDataAttendance?.status_woriking_in_dayoff
                  : false,
                status_permitease: matchedDataAttendance?.status_permitease
                  ? matchedDataAttendance?.status_permitease
                  : false,
              };

              return newDataExportProcessing;
            })
          );

          return dataWithDate;
        })
    );

    const mergedArray = dataExportProcessing.flat();
    return {
      attendances,
      employeeAttendance: mergedArray,
      success: true,
      message: "attendance loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to retrieve attendance. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function reportCPR(params) {
  try {
    let filterConditions = {};

    const year = new Date().getFullYear();
    const now = Date.now();
    let holidays;
    // OPTIMIZATION: Use cached holidays
    if (cachedHolidays && (now - lastHolidayFetch < HOLIDAY_CACHE_TTL)) {
       holidays = cachedHolidays;
    } else {
       holidays = await dbSAPPS.Holiday.findAll({
        where: {
            enabled: true,
            holiday_date: {
            [Op.between]: [`${year}-01-01`, `${year}-12-31`],
            },
        },
        attributes: ["id", "holiday_date", "is_national_holiday", "is_weekend_holiday", "display_name", "holidaygroup_id", "enabled"],
       });
       cachedHolidays = holidays;
       lastHolidayFetch = now;
    }

    let territoryVersion = [];
    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) {
        territoryVersion = getTerritoryVersion;
        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.year_id) {
      filterConditions.year_id = {
        [Op.like]: `%${params.year_id}%`,
      };
    }

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

    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 callPlans = await dbSAPPS.CallPlan.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",
          attributes: ["id", "display_name"],
        },
      ],
    });

    const employeeTerritory = await dbSAPPS.EmployeeTerritory.findAll({
      attributes: ["employee_id", "territory_id"],
      include: [
        {
          model: dbSAPPS.Employee,
          as: "employee",
          attributes: ["id", "employee_name", "employee_number"],
        },
      ],
    });

    const callPlansWithClients = await Promise.all(
      callPlans.map(async (callPlan) => {
        const callplandestinations = await dbSAPPS.CallPlanDestination.findAll({
          where: { callplan_id: callPlan.id },
          include: [
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name", "priority", "clienttype_id"],
              include: [
                {
                  model: dbSAPPS.ClientType,
                  as: "clienttype",
                  attributes: ["display_name"],
                },
                {
                  model: dbSAPPS.Specialist,
                  as: "specialist",
                  attributes: ["id", "display_name"],
                },
                {
                  model: dbSAPPS.SubSpecialist,
                  as: "subspecialist",
                  attributes: ["id", "display_name"],
                },
                {
                  model: dbSAPPS.ClientSubtype,
                  as: "clientsubtype",
                  attributes: ["id", "display_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              attributes: ["id", "display_name"],
              include: [
                {
                  model: dbSAPPS.City,
                  as: "city",
                  attributes: ["id", "display_name"],
                },
              ],
            },
          ],
        });
        const callplanvisits = await dbSAPPS.CallPlanVisit.findAll({
          where: { callplan_id: callPlan.id },
          include: [
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name", "priority", "clienttype_id"],
              include: [
                {
                  model: dbSAPPS.ClientType,
                  as: "clienttype",
                  attributes: ["display_name"],
                },
                {
                  model: dbSAPPS.Specialist,
                  as: "specialist",
                  attributes: ["id", "display_name"],
                },
                {
                  model: dbSAPPS.SubSpecialist,
                  as: "subspecialist",
                  attributes: ["id", "display_name"],
                },
                {
                  model: dbSAPPS.ClientSubtype,
                  as: "clientsubtype",
                  attributes: ["id", "display_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              attributes: ["id", "display_name"],
              include: [
                {
                  model: dbSAPPS.City,
                  as: "city",
                  attributes: ["id", "display_name"],
                },
              ],
            },
          ],
        });
        const callplanrealizations = await dbSAPPS.CallPlanRealization.findAll({
          where: { callplan_id: callPlan.id },
          include: [
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name", "priority"],
            },
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              attributes: ["id", "display_name"],
            },
          ],
        });

        const employees = employeeTerritory.filter((item) => item.territory_id === callPlan.territory_id);
        const endDate = moment(callPlan.to_date).format("DD");
        // OPTIMIZATION: Filter cached holidays in memory
        const holidayList = holidays.filter(h => {
             const hDate = new Date(h.holiday_date);
             const from = new Date(callPlan?.from_date);
             const to = new Date(callPlan?.to_date);
             return hDate >= from && hDate <= to;
        });

        const hariKerja = endDate - holidayList.length || 0;

        const clientsByPriority = {
          A: [],
          B: [],
          C: [],
          D: [],
        };

        const visitByPriority = {
          A: [],
          B: [],
          C: [],
          D: [],
        };

        const realizationByPriority = {
          A: [],
          B: [],
          C: [],
          D: [],
        };

        const realizationNonScheduleByPriority = {
          A: [],
          B: [],
          C: [],
          D: [],
        };

        const actualByPriority = {
          A: [],
          B: [],
          C: [],
          D: [],
        };

        callplandestinations.forEach((item) => {
          const client = item.client;
          if (client && client.priority) {
            clientsByPriority[client.priority].push(client);
          }
        });

        callplanvisits.forEach((item) => {
          const client = item.client;
          if (client && client.priority) {
            visitByPriority[client.priority].push(client);
          }
        });

        callplanrealizations.forEach((item) => {
          const client = item.client;
          const realization_status = item.realization_status;
          if (client && client.priority && item.data_source === "from_plan" && item.doc_status === 2) {
            realizationByPriority[client.priority].push(client);
          }

          if (client && client.priority && item.doc_status === 2 && realization_status !== "P") {
            actualByPriority[client.priority].push(client);
          }

          if (
            client &&
            client.priority &&
            item.doc_status === 2 &&
            item.data_source === "not_from_plan" &&
            realization_status !== "P"
          ) {
            realizationNonScheduleByPriority[client.priority].push(client);
          }
        });

        const permit = callplanrealizations?.filter((item) => item?.realization_status === "P");
        const permissionData = permit?.map((item) => ({
          ...item.dataValues,
          workflow: workflows.find((wf) => wf.doc_id === item.id && wf.doctype_id === 117)?.dataValues || null,
        }));
        const permission =
          permissionData?.length > 0 ? permissionData?.filter((item) => item?.workflow?.status === "Approved") : null;

        const visit = callplanrealizations.filter(
          (item) => item.data_source === "from_plan" && item.realization_status === "V" && item.doc_status === 2
        );
        const jv = callplanrealizations.filter(
          (item) => item.data_source === "from_plan" && item.realization_status === "JV" && item.doc_status === 2
        );
        const rv = callplanrealizations.filter(
          (item) => item.data_source === "from_plan" && item.realization_status === "RV" && item.doc_status === 2
        );
        const rjv = callplanrealizations.filter(
          (item) => item.data_source === "from_plan" && item.realization_status === "RJV" && item.doc_status === 2
        );
        const nsv = callplanrealizations.filter(
          (item) => item.data_source === "not_from_plan" && item.realization_status === "NSV" && item.doc_status === 2
        );
        const nsjv = callplanrealizations.filter(
          (item) => item.data_source === "not_from_plan" && item.realization_status === "NSJV" && item.doc_status === 2
        );
        const nonPlan = callplanrealizations.filter(
          (item) => item.data_source === "not_from_plan" && item.doc_status === 2
        );
        const klarifikasi = callplanrealizations.filter(
          (item) => item?.realization_status === "K" && item.doc_status === 2
        );
        const actual =
          visit?.length + jv?.length + rv?.length + rjv?.length + nsv?.length + nsjv?.length + klarifikasi?.length;
        const persentaseCPR = ((actual / callplanvisits.length) * 100).toFixed(0) + " %";
        const persentaseNonPlan = ((nonPlan.length / callplanvisits.length) * 100).toFixed(0) + " %";
        const totalHariKerja = permission?.length > 0 ? hariKerja - permission?.length : hariKerja;

        const totalPhotoIdeal = callplanrealizations.filter(
          (item) => item.realization_status !== "K" && item.doc_status === 2 && item?.filename !== null
        );

        return {
          ...callPlan.toJSON(), // Mengonversi callPlan ke objek biasa
          clientsByPriority, // Menambahkan klien berdasarkan prioritas
          totalPhotoIdeal,
          visitByPriority,
          realizationByPriority,
          actualByPriority,
          realizationNonScheduleByPriority,
          visit,
          jv,
          rv,
          rjv,
          nsv,
          nsjv,
          nonPlan,
          klarifikasi,
          permission,
          actual,
          persentaseCPR,
          persentaseNonPlan,
          callplandestinations,
          callplanvisits,
          callplanrealizations,
          hariKerja,
          totalHariKerja,
          employee: employees[0],
        };
      })
    );

    return {
      callPlans: callPlansWithClients,
      success: true,
      message: `report cpr loaded successfully!`,
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load report cpr.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function reportSalesContribution(params) {
  try {
    let territoryVersion = [];
    let territoryVersionChilds = [];
    let territories, rentangPeriod;
    if (params && params.territory_id && params.period_from && params.period_to && params.company_id) {
      rentangPeriod = await dbSAPPS.Period.findAll({
        where: {
          id: {
            [Op.between]: [params.period_from, params.period_to],
          },
        },
      });

      if (!rentangPeriod || rentangPeriod.length === 0) {
        throw new Error("Period not found!");
      }

      const firstPeriod = rentangPeriod[0];
      const start_date = firstPeriod.start_date;
      const end_date = rentangPeriod[rentangPeriod.length - 1].end_date;

      const getTerritoryVersion = await dbSAPPS.TerritoryVersion.findAll({
        attributes: ["id", "display_name", "valid_from", "valid_to", "enabled", "company_id"],
        where: {
          [Op.and]: [
            { valid_from: { [Op.lte]: end_date } },
            {
              [Op.or]: [{ valid_to: { [Op.gte]: start_date } }, { valid_to: null }],
            },
          ],
          company_id: params.company_id,
        },
        include: [
          {
            model: dbSAPPS.TerritoryVersionChild,
            as: "territoryversionchilds",
            attributes: ["id", "name", "parent_id", "territory_id"],
          },
        ],
      });

      if (getTerritoryVersion && getTerritoryVersion.length > 0) {
        territoryVersion = getTerritoryVersion;
        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 = await getAllIds(territoryDatas && territoryDatas.length > 0 ? territoryDatas : []);
    }

    const firstPeriod = rentangPeriod[0];
    const start_date = firstPeriod.start_date;
    const end_date = rentangPeriod[rentangPeriod.length - 1].end_date;
    const filterConditions = {};

    if (params.territory_id)
      filterConditions.territory_id = params.territory_id
        ? Array.isArray(territories)
          ? { [Op.in]: territories }
          : { [Op.eq]: params.territory_id }
        : undefined;
    // if(params.period_id)  filterConditions.period_id = {[Op.eq]: parseInt(params.period_id)};
    if (start_date && end_date) {
      filterConditions.transaction_date = {
        [Op.between]: [start_date, end_date],
      };
    }
    if (params.distributor_id)
      filterConditions.distributor_id = {
        [Op.eq]: parseInt(params.distributor_id),
      };
    if (params.enabled)
      filterConditions.enabled = {
        [Op.eq]: params.enabled === "1" ? true : false,
      };

    // ambil data sales
    const dataSalesTerritory = await dbSAPPS.SalesDistributor.findAll({
      where: filterConditions,
    });

    // ambil data atasan
    let findVersion;
    const parentHierarchy = (territory_id, transaction_date) => {
      const hierarchy = [];
      findVersion = territoryVersion.find((c) => transaction_date >= c.valid_from && transaction_date <= c.valid_to);

      const findParent = (id) => {
        // find the territory version
        const parent = findVersion?.territoryversionchilds.find((c) => c.territory_id === id);
        if (parent && parent.parent_id) {
          if (parent.territory_id !== territory_id) {
            hierarchy.push({
              id: parent?.territory_id,
              name: parent?.name,
            });
          }
          findParent(parent?.parent_id);
        } else {
          hierarchy.push({
            id,
            name: parent?.name,
          });
        }
      };

      findParent(territory_id);
      const index = hierarchy.findIndex((h) => h.id === territory_id);
      if (index > -1) {
        hierarchy.splice(index, 1);
      }
      return hierarchy;
    };

    //  formatted data
    const formatData = await Promise.all(
      dataSalesTerritory?.map(async (row) => {
        const distributor = await dbSAPPS.Distributor.findByPk(row.distributor_id, {
          attributes: ["id", "display_name"],
        });
        const period = await dbSAPPS.Period.findByPk(row.period_id, {
          attributes: ["id", "display_name"],
        });
        const outlet = await dbSAPPS.Outlet.findByPk(row.outlet_id, {
          attributes: ["id", "display_name"],
        });
        const territory = await dbSAPPS.Territory.findByPk(row.territory_id, {
          attributes: ["id", "display_name"],
        });
        const item = await dbSAPPS.Item.findByPk(row.item_id, {
          attributes: ["id", "display_name"],
        });
        const territoryParents = parentHierarchy(row.territory_id, row.transaction_date);
        const value_disc = row.price * (row.discount / 100) * row.quantity;
        const nett_sales = row.total - value_disc.toFixed(2);
        return {
          ...row.dataValues,
          value_disc,
          nett_sales,
          distributor,
          period,
          outlet,
          territory,
          territoryParents,
          item,
        };
      })
    );

    return {
      dataSales: formatData,
      dataSalesTerritory,
      dataTerritory: territories,
      territoryVersion,
      territories,
      start_date,
      end_date,
      rentangPeriod,
      findVersion,
      success: true,
      message: "Report sales contribution have been loaded!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load report sales contribution.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function reportVisitVerify(params) {
  try {
    const callPlans = await dbSAPPS.CallPlan.findAll({
      order: [["modified", "DESC"]],
      where: {
        territory_id: params.territory_id,
        year_id: params.year_id,
        period_id: params.period_id,
      },
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Period,
          as: "period",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.CallPlanRealization,
          as: "callplanrealizations",
          include: [
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name", "priority"],
            },
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              attributes: ["id", "display_name"],
            },
          ],
        },
      ],
    });

    const callPlanRealizations = callPlans.reduce((result, callPlan) => {
      callPlan.callplanrealizations.forEach((callPlanRealization) => {
        result.push({
          ...callPlanRealization.dataValues,
          callPlan: callPlan.dataValues,
        });
      });
      return result;
    }, []);

    return {
      callPlans,
      callPlanRealizations,
      success: true,
      message: `report cpr loaded successfully!`,
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load report cpr.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function reportSalesTarget(params) {
  try {
    let territoryVersion = [];
    let territoryVersionChilds = [];
    let territories;
    let territoryDatas = [];

    if (params && params.territory_id && params.year_id && params.period_id) {
      const period = await dbSAPPS.Period.findOne({
        where: { id: params.period_id, year_id: params.year_id },
      });

      const start_date = period.start_date;
      const end_date = period.end_date;

      const getTerritoryVersion = await dbSAPPS.TerritoryVersion.findAll({
        attributes: ["id", "display_name", "valid_from", "valid_to", "enabled"],
        where: {
          [Op.and]: [
            { valid_from: { [Op.lte]: end_date } },
            {
              [Op.or]: [{ valid_to: { [Op.gte]: start_date } }, { valid_to: null }],
            },
          ],
        },
        include: [
          {
            model: dbSAPPS.TerritoryVersionChild,
            as: "territoryversionchilds",
            attributes: ["id", "name", "parent_id", "territory_id"],
          },
        ],
      });

      if (getTerritoryVersion && getTerritoryVersion.length > 0) {
        territoryVersion = getTerritoryVersion;
        territoryVersionChilds = getTerritoryVersion.reduce(
          (acc, curr) => [...acc, ...curr.territoryversionchilds],
          []
        );
      }

      const salesTarget = await dbSAPPS.SalesTarget.findAll({
        where: {
          period_id: params.period_id,
          company_id: params.company_id,
          enabled: true,
        },
        attributes: ["id", "territory_id", "period_id", "company_id"],
        include: [
          {
            model: dbSAPPS.SalesTargetDetail,
            as: "salestargetdetails",
            attributes: ["id", "item_id", "quantity", "salestarget_id"],
            include: [
              {
                model: dbSAPPS.Item,
                as: "item",
                attributes: ["id", "display_name", "price"],
              },
            ],
          },
        ],
      });

      const hierarchyTerritoryVersionChilds = (parent_id = null) => {
        const children = territoryVersionChilds.filter((c) => c.parent_id === parent_id);

        return children.map((child) => ({
          id: child?.territory_id,
          name: child?.name,
          children: hierarchyTerritoryVersionChilds(child?.territory_id),
          salesTarget: salesTarget.find((s) => s.territory_id === child?.territory_id),
        }));
      };

      territoryDatas = [
        {
          id: parseInt(params?.territory_id),
          name: params?.territory_name,
          salesTarget: salesTarget.find((s) => s.territory_id === parseInt(params?.territory_id)),
          children: hierarchyTerritoryVersionChilds(parseInt(params?.territory_id)),
        },
      ];
    }

    const itemCompany = await dbSAPPS.Item.findAll({
      where: { company_id: params.company_id },
      attributes: ["id", "display_name"],
    });

    const period = await dbSAPPS.Period.findOne({
      where: { id: params.period_id },
      attributes: ["id", "display_name"],
    });

    // Ganti fungsi flattenData lama dengan yang ini:
    const flattenData = (nodes, level = 0) => {
      const result = [];

      // helper mengembalikan { rows: [...], aggregate: [...] }
      const helper = (node, lvl) => {
        const children = node.children || [];

        // rows untuk subtree ini
        let subtreeRows = [];

        // aggregateMap untuk menampung penjumlahan yang akan dikirim ke parent
        const aggregateMap = new Map();

        // 1) proses semua children dulu (post-order)
        for (const child of children) {
          const { rows: childRows, aggregate: childAggregate } = helper(child, lvl + 1);

          // tambahkan semua baris hasil flatten anak ke subtreeRows
          subtreeRows = subtreeRows.concat(childRows);

          // parent harus meng-agregasi hanya dari childAggregate (child final totals)
          (childAggregate || []).forEach((d) => {
            const k = String(d.item_id);
            const ex = aggregateMap.get(k);
            if (ex) ex.quantity += Number(d.quantity || 0);
            else aggregateMap.set(k, { item_id: d.item_id, quantity: Number(d.quantity || 0), item: d.item || null });
          });
        }

        // 2) tambahkan ownDetails (target node sendiri) ke aggregateMap
        const ownDetails = node.salesTarget?.salestargetdetails
          ? node.salesTarget.salestargetdetails.map((d) => ({
              item_id: d.item_id,
              quantity: Number(d.quantity || 0),
              item: d.item || null,
            }))
          : [];

        ownDetails.forEach((d) => {
          const k = String(d.item_id);
          const ex = aggregateMap.get(k);
          if (ex) ex.quantity += Number(d.quantity || 0);
          else aggregateMap.set(k, { item_id: d.item_id, quantity: Number(d.quantity || 0), item: d.item || null });
        });

        // 3) final aggregated array untuk node ini (dipakai oleh parent)
        const aggregatedDetails = Array.from(aggregateMap.values());

        // 4) hitung totalPriceItem (opsional)
        const totalPriceItem = Math.floor(
          aggregatedDetails.reduce((acc, d) => {
            const price = Number(d.item?.price || 0);
            return acc + price * Number(d.quantity || 0);
          }, 0)
        );

        // 5) Push child's rows dulu (agar bottom-up order) lalu node ini
        //    subtreeRows sudah berisi semua rows dari children (post-order)
        subtreeRows.push({
          id: node.id,
          name: node.name,
          level: lvl,
          targetDetails: aggregatedDetails,
          totalPriceItem,
        });

        // 6) helper mengembalikan rows untuk subtree ini, dan aggregate yang akan dipakai parent
        return { rows: subtreeRows, aggregate: aggregatedDetails };
      };

      // proses tiap root node di nodes
      for (const n of nodes) {
        const { rows: r } = helper(n, level);
        result.push(...r);
      }

      return result;
    };

    const rows = flattenData(territoryDatas);

    return {
      territoryDatas,
      dataTarget: rows,
      itemCompany,
      period,
      success: true,
      message: "Report sales target loaded",
    };
  } catch (error) {
    return {
      success: false,
      message: error.message || "Failed to load report sales target",
    };
  }
}

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

    if (
      params.access_read_all_data === true ||
      params.access_read_all_data === "true" ||
      params.administrator_of_pirlo === true ||
      params.administrator_of_pirlo === "true"
    ) {
      filterConditions.shipper = {
        [Op.ne]: null,
      };
    } else {
      if (params.employeesId && params.employeesId.length > 0) {
        filterConditions.shipper = {
          [Op.in]: params.employeesId.map((item) => parseInt(item)),
        };
      } else {
        if (params.employee_id) {
          filterConditions.shipper = {
            [Op.eq]: parseInt(params.employee_id),
          };
        }
      }
    }

    if (params.from_date && params.to_date) {
      filterConditions.shipper_date = {
        [Op.between]: [params.from_date, params.to_date],
      };

      filterConditions.received_date = {
        [Op.between]: [params.from_date, params.to_date],
      };
    } else if (params.date) {
      filterConditions.shipper_date = {
        [Op.eq]: params.date,
      };

      filterConditions.received_date = {
        [Op.eq]: params.date,
      };
    }

    const goodsReceipts = await dbSAPPS.GoodsReceipt.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
    });

    const goodsReceiptsPromises = goodsReceipts.map(async (goodsReceipt) => {
      const employeePromises = [];

      if (goodsReceipt.shipper) {
        employeePromises.push(
          dbSAPPS.Employee.findOne({
            where: { id: goodsReceipt.shipper },
            attributes: ["id", "employee_name", "company_id"],
            include: [
              {
                model: dbSAPPS.Company,
                as: "company",
                attributes: ["id", "display_name"],
              },
            ],
          })
        );
      }

      if (goodsReceipt.cosignee) {
        employeePromises.push(
          dbSAPPS.Employee.findOne({
            where: { id: goodsReceipt.cosignee },
            attributes: ["id", "employee_name", "company_id"],
            include: [
              {
                model: dbSAPPS.Company,
                as: "company",
                attributes: ["id", "display_name"],
              },
            ],
          })
        );
      }

      const [shipper, cosignee] = await Promise.all(employeePromises);

      return {
        ...goodsReceipt.dataValues,
        shipper_name: shipper ? shipper.employee_name : null,
        cosignee_name: cosignee ? cosignee.employee_name : null,
        shipper_company_name: shipper ? shipper.company.display_name : null,
        cosignee_company_name: cosignee ? cosignee.company.display_name : null,
      };
    });

    const goodsReceiptsWithEmployeeNames = await Promise.all(goodsReceiptsPromises);

    return {
      goodsReceipts: goodsReceiptsWithEmployeeNames,
      success: true,
      message: "goods receipts loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load goods receipts!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Retrieves attendance records of employees by given filter conditions
 *
 * @param {Object} params - An object containing filter conditions
 * @param {Array<number>} [params.employee_id] - An array of employee IDs
 * @param {Array<number>} [params.company_id] - An array of company IDs
 * @param {Date|string} [params.from_date] - Start date of the attendance period
 * @param {Date|string} [params.to_date] - End date of the attendance period
 * @param {Date|string} [params.date] - Specific date of the attendance period
 * @returns {Promise<Object>} - An object containing the attendance records of employees
 */
async function reportEmployeeAttendanceRate(params) {
  try {
    const filterConditions = {};
    const filterConditionsEmployee = {};

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

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

    if (params.from_date && params.to_date) {
      filterConditions.date = {
        [Op.between]: [params.from_date, params.to_date],
      };
    } else if (params.date) {
      filterConditions.date = {
        [Op.eq]: params.date,
      };
    }

    const attendances = await dbSAPPS.Attendance.findAll({
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Employee,
          as: "employee",
          attributes: ["id", "employee_name", "employee_number"],
        },
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ShiftType,
          as: "shifttype",
          attributes: ["id", "display_name"],
        },
      ],
      order: [
        ["employee", "employee_name", "ASC"],
        ["modified", "DESC"],
      ],
    });

    if (params.employee_id) {
      filterConditionsEmployee.id = {
        [Op.in]: params?.employee_id,
      };
    }

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

    filterConditionsEmployee.enabled = true;
    filterConditionsEmployee.shifttype_id = {
      [Op.not]: null,
    };

    const employees = await dbSAPPS.Employee.findAll({
      where: filterConditionsEmployee,
      attributes: ["id", "employee_name", "employee_number"],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ShiftType,
          as: "shifttype",
          attributes: ["id", "display_name"],
        },
      ],
      order: [["employee_name", "ASC"]],
    });

    const year = new Date().getFullYear();
    const holidays = await dbSAPPS.Holiday.findAll({
      where: {
        enabled: true,
        holiday_date: {
          [Op.between]: [`${year}-01-01`, `${year}-12-31`],
        },
      },
      attributes: ["holiday_date", "is_national_holiday", "is_weekend_holiday", "display_name"],
    });

    const dates = [];
    if (params.from_date && params.to_date) {
      const fromDate = moment(params.from_date);
      const toDate = moment(params.to_date);
      while (fromDate.isSameOrBefore(toDate)) {
        dates.push(fromDate.format("YYYY-MM-DD"));
        fromDate.add(1, "days");
      }
    }

    const dataExportProcessing = await Promise.all(
      employees &&
        employees.map(async (employee) => {
          const dataWithDate = await Promise.all(
            dates.map(async (date) => {
              const matchedDataAttendance =
                attendances &&
                attendances.filter((attendance) => attendance.employee_id === employee.id && attendance.date === date);
              const matchedDataHariLibur = holidays && holidays.filter((holiday) => holiday.holiday_date === date);
              const dayStatus = matchedDataHariLibur[0]?.is_national_holiday
                ? "National Holiday"
                : moment(date).format("dddd") === "Saturday" || moment(date).format("dddd") === "Sunday"
                ? "Day Off"
                : "Work Day";

              let valueAbsence = 0;
              if (dayStatus === "Work Day" && matchedDataAttendance[0]?.in_time && matchedDataAttendance[0]?.out_time) {
                const regex = /cuti|sakit/gi;
                const checkReason = matchedDataAttendance[0]?.reason
                  ? regex.test(matchedDataAttendance[0]?.reason)
                  : false;
                if (!checkReason) {
                  valueAbsence = 1;
                }
              }

              const newDataExportProcessing = {
                employee_id: employee?.id,
                employee_name: employee?.employee_name,
                date: date,
                day: moment(date).format("dddd"),
                day_status: dayStatus,
                holiday_name: matchedDataHariLibur[0]?.display_name,
                attendance_id: matchedDataAttendance[0]?.id ? matchedDataAttendance[0]?.id : null,
                schedule_in: employee?.shifttype?.late_time ? employee?.shifttype?.late_time : "",
                schedule_out: employee?.shifttype?.ealy_time ? employee?.shifttype?.ealy_time : "",
                in_time: matchedDataAttendance[0]?.in_time ? matchedDataAttendance[0]?.in_time : "",
                out_time: matchedDataAttendance[0]?.out_time ? matchedDataAttendance[0]?.out_time : "",
                in_status: matchedDataAttendance[0]?.in_status ? matchedDataAttendance[0]?.in_status : "",
                out_status: matchedDataAttendance[0]?.out_status ? matchedDataAttendance[0]?.out_status : "",
                in_distance: matchedDataAttendance[0]?.in_distance ? matchedDataAttendance[0]?.in_distance : "",
                out_distance: matchedDataAttendance[0]?.out_distance ? matchedDataAttendance[0]?.out_distance : "",
                in_address: matchedDataAttendance[0]?.in_address ? matchedDataAttendance[0]?.in_address : "",
                out_address: matchedDataAttendance[0]?.out_address ? matchedDataAttendance[0]?.out_address : "",
                total_hour: matchedDataAttendance[0]?.total_hour ? matchedDataAttendance[0]?.total_hour : "",
                reason: matchedDataAttendance[0]?.reason ? matchedDataAttendance[0]?.reason : "",
                notes: matchedDataAttendance[0]?.notes ? matchedDataAttendance[0]?.notes : "",
                company: employee?.company?.display_name,
                status_absenceclarification: matchedDataAttendance[0]?.status_absenceclarification
                  ? matchedDataAttendance[0]?.status_absenceclarification
                  : false,
                status_woriking_in_dayoff: matchedDataAttendance[0]?.status_woriking_in_dayoff
                  ? matchedDataAttendance[0]?.status_woriking_in_dayoff
                  : false,
                status_permitease: matchedDataAttendance[0]?.status_permitease
                  ? matchedDataAttendance[0]?.status_permitease
                  : false,
                value_absence: valueAbsence,
                value_workday: dayStatus === "Work Day" ? 1 : 0,
              };

              return newDataExportProcessing;
            })
          );

          const totalValueAbsence = dataWithDate.reduce((acc, curr) => acc + curr.value_absence, 0);
          const totalValueWorkday = dataWithDate.reduce((acc, curr) => acc + curr.value_workday, 0);
          const attendanceRate = totalValueWorkday > 0 ? (totalValueAbsence / totalValueWorkday) * 100 : 0;

          return {
            ...employee.dataValues,
            attendance_rate: attendanceRate,
            value_absence: totalValueAbsence,
            value_workday: totalValueWorkday,
          };
        })
    );

    const totalValueAbsence = dataExportProcessing.reduce((acc, curr) => acc + curr.value_absence, 0);
    const totalValueWorkday = dataExportProcessing.reduce((acc, curr) => acc + curr.value_workday, 0);

    // Calculate average attendance rate without including data with attendance rate 0
    const filteredDataExportProcessing = dataExportProcessing.filter((item) => item.attendance_rate !== 0);
    const totalAttendanceRate = filteredDataExportProcessing.reduce((acc, curr) => acc + curr.attendance_rate, 0);
    const averageAttendanceRate =
      filteredDataExportProcessing.length > 0 ? totalAttendanceRate / filteredDataExportProcessing.length : 0;

    const mergedArray = dataExportProcessing;

    return {
      attendances,
      employeeAttendance: mergedArray,
      averageAttendanceRate,
      totalValueAbsence,
      totalValueWorkday,
      totalAttendanceRate,
      success: true,
      message: "attendance loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to retrieve attendance. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}





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

    // =========================
    // 1. LOAD PERIOD
    // =========================
    const rentangPeriod = await dbSAPPS.Period.findAll({
      where: {
        id: { [Op.between]: [params.period_from, params.period_to] },
      },
      attributes: ["id", "display_name", "start_date", "end_date"],
      order: [["id", "ASC"]],
    });

    const periodIds = rentangPeriod.map((p) => p.id);

    let territoryVersion = [];
    let territoryVersionChilds = [];
    let territories, periode;
    if (params && params.territory_id && params.period_from && params.period_to && params.company_id) {
      periode = await dbSAPPS.Period.findAll({
        where: {
          id: {
            [Op.between]: [params.period_from, params.period_to],
          },
        },
      });

      if (!periode || periode.length === 0) {
        throw new Error("Period not found!");
      }

      const firstPeriod = periode[0];
      const start_date = firstPeriod.start_date;
      const end_date = periode[periode.length - 1].end_date;

      const getTerritoryVersion = await dbSAPPS.TerritoryVersion.findAll({
        attributes: ["id", "display_name", "valid_from", "valid_to", "enabled", "company_id"],
        where: {
          [Op.and]: [
            { valid_from: { [Op.lte]: end_date } },
            {
              [Op.or]: [{ valid_to: { [Op.gte]: start_date } }, { valid_to: null }],
            },
          ],
          company_id: params.company_id,
        },
        include: [
          {
            model: dbSAPPS.TerritoryVersionChild,
            as: "territoryversionchilds",
            attributes: ["id", "name", "parent_id", "territory_id"],
          },
        ],
      });

      if (getTerritoryVersion && getTerritoryVersion.length > 0) {
        territoryVersion = getTerritoryVersion;
        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 = await getAllIds(territoryDatas && territoryDatas.length > 0 ? territoryDatas : []);
    }


    // =========================
    // 3. LOAD ACCOUNT ENTRIES
    // =========================
    const accountEntries = await dbSAPPS.AccountEntries.findAll({
      where: {
        period_id: { [Op.in]: periodIds },
        territory_id: { [Op.in]: territories },
        status: 1,
      },
      include: [
        { model: dbSAPPS.Territory, as: "territory", attributes: ["id", "display_name"] },
        { model: dbSAPPS.Client, as: "client" },
        { model: dbSAPPS.Item, as: "item" },
        {
          model: dbSAPPS.GimmickRequest,
          as: "gimmickreq",
          include: [
            {
              model: dbSAPPS.GimmickAllocation,
              as: "gimmickallocations",
              include: [
                {
                  model: dbSAPPS.Item,
                  as: "item",
                  attributes: ["id", "display_name", "price"],
                },
              ],
            },
          ],
        },
      ],
    });

    // =========================
    // 4. LOAD SALDO AWAL (ONCE)
    // =========================
    const saldoAwals = await dbSAPPS.SaldoAwalClient.findAll({
      where: { enabled: true },
    });

    const saldoMap = {};
    for (const s of saldoAwals) {
      const key = `${s.client_id}_${s.item_id}`;
      saldoMap[key] ??= { debit: 0, credit: 0 };

      const balance = toNumber(s.balance);

      if (s.type === "debit") saldoMap[key].debit += balance;
      if (s.type === "credit") saldoMap[key].credit += balance;
    }

    // =========================
    // 5. BUILD DATA
    // =========================
    const territoryMap = {};

    for (const entry of accountEntries) {
      const territoryName = entry.territory?.display_name || "UNKNOWN";
      const clientId = entry.client_id;
      const itemId = entry.item_id;
      const periodId = entry.period_id;

      const saldoKey = `${clientId}_${itemId}`;
      const saldoAwalDebit = saldoMap[saldoKey]?.debit || 0;
      const saldoAwalCredit = saldoMap[saldoKey]?.credit || 0;

      territoryMap[territoryName] ??= { clients: {} };
      const territory = territoryMap[territoryName];

      territory.clients[clientId] ??= {
        client_id: clientId,
        client_name: entry.client?.display_name,
        items: {},
      };

      const client = territory.clients[clientId];

      client.items[itemId] ??= {
        item_id: itemId,
        item_name: entry.item?.display_name,
        debit: 0,
        credit: 0,
        saldoAwalDebit,
        saldoAwalCredit,
        balance: 0,
        periods: {},
      };

      const item = client.items[itemId];

      item.periods[periodId] ??= { debit: 0, credit: 0 };

      // ===== DEBIT =====
      if (entry.type === "debit") {
        const val = toNumber(entry.item?.price) * toNumber(entry.quantity) * (toNumber(entry.kerja_sama) / 100);

        item.periods[periodId].debit += val;
        item.debit += val;
      }

      // ===== CREDIT =====
      //  RUMUS item
      // RUMUS CREDIT SEBELUM ADA SALDO AWAL
      //  HNA * QTY * KS / 100
      // (entry.item?.price || 0) * entry.quantity * (entry.kerja_sama / 100) * entry?.gimmickreq?.periode;
      if (entry.type === "credit") {
        const val =
          toNumber(entry.item?.price) *
          toNumber(entry.quantity) *
          (toNumber(entry.kerja_sama) / 100) *
          entry?.gimmickreq?.periode;

        item.periods[periodId].credit += val;
        item.credit += val;
        // entry.gimmickreq?.gimmickallocations?.forEach((g) => {
        //   const val =
        //     toNumber(g.item?.price) * toNumber(entry.gimmickreq?.periode || (1 * toNumber(g.kerja_sama)) / 100);

        //   item.periods[periodId].credit += val;
        //   item.credit += val;
        // });
      }
    }

    // =========================
    // 6. HITUNG BALANCE
    // =========================
    Object.values(territoryMap).forEach((t) => {
      Object.values(t.clients).forEach((c) => {
        Object.values(c.items).forEach((i) => {
          i.balance =
            toNumber(i.saldoAwalDebit) + toNumber(i.debit) - (toNumber(i.saldoAwalCredit) + toNumber(i.credit));
        });
      });
    });

    // =========================
    // 7. SUMMARY (FINAL)
    // =========================
    const summaryByTerritory = {};
    const grandTotals = {
      init_debit: 0,
      init_credit: 0,
      debit: 0,
      credit: 0,
      balance: 0,
    };

    Object.entries(territoryMap).forEach(([territoryName, territory]) => {
      const summary = {
        territory_name: territoryName,
        init_debit: 0,
        init_credit: 0,
        debit: 0,
        credit: 0,
        balance: 0,
        period_totals: {},
      };

      Object.values(territory.clients).forEach((client) => {
        Object.values(client.items).forEach((item) => {
          // INIT
          summary.init_debit += toNumber(item.saldoAwalDebit);
          summary.init_credit += toNumber(item.saldoAwalCredit);

          // MUTASI
          summary.debit += toNumber(item.debit);
          summary.credit += toNumber(item.credit);

          // PERIOD
          Object.entries(item.periods).forEach(([pid, val]) => {
            summary.period_totals[pid] ??= { debit: 0, credit: 0 };
            summary.period_totals[pid].debit += toNumber(val.debit);
            summary.period_totals[pid].credit += toNumber(val.credit);
          });
        });
      });

      // BALANCE (AKUNTANSI BENAR)
      summary.balance = summary.init_debit + summary.debit - (summary.init_credit + summary.credit);

      // GRAND TOTAL
      grandTotals.init_debit += summary.init_debit;
      grandTotals.init_credit += summary.init_credit;
      grandTotals.debit += summary.debit;
      grandTotals.credit += summary.credit;
      grandTotals.balance += summary.balance;

      summaryByTerritory[territoryName] = summary;
    });

    // =========================
    // 8. RETURN (FINAL)
    // =========================
    return {
      success: true,
      message: "Report saldo balance have been loaded!",
      data: {
        territories: Object.entries(territoryMap).map(([name, t]) => ({
          territory_name: name,
          clients: Object.values(t.clients).map((c) => ({
            ...c,
            items: Object.values(c.items),
          })),
        })),
        summary_by_territory: Object.values(summaryByTerritory),
        grand_totals: grandTotals,
        rentangPeriod,
      },
    };
  } catch (err) {
    return {
      success: false,
      message: err.message,
    };
  }
}

async function reportSaldoBalanceUser(params) {
  try {
    // =========================
    // HELPER
    // =========================
    const toNumber = (val) => {
      const num = Number(val);
      return isNaN(num) ? 0 : num;
    };
   
    // =========================
    //  LOAD ACCOUNT ENTRIES
    // =========================
    const accountEntries = await dbSAPPS.AccountEntries.findAll({
      where: {
        client_id: { [Op.eq]: params.client_id },
        status: 1,
      },
      include: [
        { model: dbSAPPS.Client, as: "client" },
        { model: dbSAPPS.Item, as: "item" },
        {
          model: dbSAPPS.GimmickRequest,
          as: "gimmickreq",
          include: [
            {
              model: dbSAPPS.GimmickAllocation,
              as: "gimmickallocations",
              include: [
                {
                  model: dbSAPPS.Item,
                  as: "item",
                  attributes: ["id", "display_name", "price"],
                },
              ],
            },
          ],
        },
      ],
    });

    // =========================
    //  LOAD SALDO AWAL (ONCE)
    // =========================
    const saldoAwals = await dbSAPPS.SaldoAwalClient.findAll({
      where: { client_id: { [Op.eq]: params.client_id }, enabled: true },
      include: [
        { model: dbSAPPS.Client, as: "client", attributes: ["id", "display_name"] },
        { model: dbSAPPS.Item, as: "item", attributes: ["id", "display_name"] },
      ],
    });

    // Map saldo awal per client_id dan item_id
    const saldoMap = {};
    for (const s of saldoAwals) {
      const key = `${s.client_id}_${s.item_id}`;
      saldoMap[key] ??= { debit: 0, credit: 0 };

      const balance = toNumber(s.balance);

      if (s.type === "debit") saldoMap[key].debit += balance;
      if (s.type === "credit") saldoMap[key].credit += balance;
    }

    // =========================
    //  BUILD DATA
    // =========================
    const clientMap = {};
    const itemMap = {}; // Temporary map untuk tracking items per client

    for (const entry of accountEntries) {
      const clientId = entry.client_id;
      const itemId = entry.item_id;

      // Initialize client if not exists
      if (!clientMap[clientId]) {
        clientMap[clientId] = {
          client_id: clientId,
          client_name: entry.client?.display_name,
          initial_debit: 0,
          initial_credit: 0,
          debit: 0,
          credit: 0,
          balance: 0,
          items: [],
        };
        itemMap[clientId] = {};
      }

      const client = clientMap[clientId];

      // Initialize item if not exists
      if (!itemMap[clientId][itemId]) {
        const saldoKey = `${clientId}_${itemId}`;
        const saldoAwalDebit = saldoMap[saldoKey]?.debit || 0;
        const saldoAwalCredit = saldoMap[saldoKey]?.credit || 0;

        itemMap[clientId][itemId] = {
          item_id: itemId,
          item_name: entry.item?.display_name,
          initial_debit: saldoAwalDebit,
          initial_credit: saldoAwalCredit,
          debit: 0,
          credit: 0,
          balance: 0,
        };

        // Add to client's initial totals
        client.initial_debit += saldoAwalDebit;
        client.initial_credit += saldoAwalCredit;
      }

      const item = itemMap[clientId][itemId];

      // ===== DEBIT =====
      if (entry.type === "debit") {
        const val = toNumber(entry.item?.price) * toNumber(entry.quantity) * (toNumber(entry.kerja_sama) / 100);
        item.debit += val;
        client.debit += val;
      }

      // ===== CREDIT =====
      // RUMUS: HNA * QTY * KS / 100 * periode
      if (entry.type === "credit") {
        const val =
          toNumber(entry.item?.price) *
          toNumber(entry.quantity) *
          (toNumber(entry.kerja_sama) / 100) *
          toNumber(entry?.gimmickreq?.periode || 1);
        item.credit += val;
        client.credit += val;
      }
    }

    // Add items that only exist in saldo awal but not in accountEntries
    for (const s of saldoAwals) {
      const clientId = s.client_id;
      const itemId = s.item_id;
      const saldoKey = `${clientId}_${itemId}`;

      // Initialize client if not exists (for items with only saldo awal)
      if (!clientMap[clientId]) {
        // Get client name from saldo awal if available
        const clientName = s.client?.display_name || "Unknown Client";
        clientMap[clientId] = {
          client_id: clientId,
          client_name: clientName,
          initial_debit: 0,
          initial_credit: 0,
          debit: 0,
          credit: 0,
          balance: 0,
          items: [],
        };
        itemMap[clientId] = {};
      }

      // Add item if not already added
      if (!itemMap[clientId][itemId]) {
        const saldoAwalDebit = saldoMap[saldoKey]?.debit || 0;
        const saldoAwalCredit = saldoMap[saldoKey]?.credit || 0;

        itemMap[clientId][itemId] = {
          item_id: itemId,
          item_name: s.item?.display_name || "Unknown Item",
          initial_debit: saldoAwalDebit,
          initial_credit: saldoAwalCredit,
          debit: 0,
          credit: 0,
          balance: 0,
        };

        // Add to client's initial totals
        clientMap[clientId].initial_debit += saldoAwalDebit;
        clientMap[clientId].initial_credit += saldoAwalCredit;
      }
    }

    // Convert itemMap to items array and calculate item balance
    Object.entries(clientMap).forEach(([clientId, client]) => {
      if (itemMap[clientId]) {
        Object.values(itemMap[clientId]).forEach((item) => {
          // Calculate balance for each item
          item.balance =
            toNumber(item.initial_debit) +
            toNumber(item.debit) -
            (toNumber(item.initial_credit) + toNumber(item.credit));
          client.items.push(item);
        });
      }
    });

    // =========================
    //  HITUNG BALANCE (CLIENT TOTAL)
    // =========================
    Object.values(clientMap).forEach((client) => {
      client.balance =
        toNumber(client.initial_debit) +
        toNumber(client.debit) -
        (toNumber(client.initial_credit) + toNumber(client.credit));
    });

    // =========================
    //  SUMMARY (FINAL)
    // =========================
    const summaryByClient = {};

    Object.entries(clientMap).forEach(([clientId, client]) => {
      const summary = {
        client_name: client.client_name,
        initial_debit: toNumber(client.initial_debit),
        initial_credit: toNumber(client.initial_credit),
        debit: toNumber(client.debit),
        credit: toNumber(client.credit),
        balance: toNumber(client.balance),
      };

      summaryByClient[clientId] = summary;
    });

    // =========================
    //  RETURN (FINAL)
    // =========================
    return {
      success: true,
      message: "Report saldo balance have been loaded!",
      data: {
        clients: Object.values(clientMap),
        summary_by_client: Object.values(summaryByClient),
      },
    };
  } catch (err) {
    return {
      success: false,
      message: err.message,
    };
  }
}

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

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

    if (params.from_date && params.to_date) {
      filterConditions.upload_date = {
        [Op.between]: [params.from_date, params.to_date],
      };
    }

    // ambil data berdasrakan rentang
    const ucCostRealizations = await dbSAPPS.UcCostRealization.findAll({
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "name", "display_name"],
        },
        {
          model: dbSAPPS.UpCountry,
          as: "upcountry",
          attributes: ["id", "doc_no", "creation"],
        },
        {
          model: dbSAPPS.Designation,
          as: "designation",
          attributes: ["id", "name", "display_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "employee",
          attributes: ["id", "employee_name"],
        },
      ],
    });

    return {
      data: ucCostRealizations,
      success: true,
      message: "Data report has been loaded!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load report data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

    if (params.employee_id) {
      filterConditions.employee_id = params.employee_id;
    }
    if (params.uc_request_id) {
      filterConditions.id = params.uc_request_id;
    }

    // ambil data berdasrakan rentang
    upCountry = await dbSAPPS.UpCountry.findOne({
      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.Designation,
          as: "designation",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "employee",
          attributes: ["id", "employee_name"],
        },
      ],
    });

    const callplan = await dbSAPPS.CallPlan.findOne({
      where: {
        id: upCountry.callplan_id,
      },
      include: [
        {
          model: dbSAPPS.CallPlanRealization,
          as: "callplanrealizations",
          where: {
            realization_status: "UC",
            doc_status: 2,
          },
          attributes: [
            "id",
            "callplan_id",
            "start_date",
            "end_date",
            "doc_status",
            "realization_status",
            "modified",
            "uc_destination",
            "clock_in_latitude",
            "clock_in_longitude",
            "clock_out_latitude",
            "clock_out_longitude",
            "clock_in",
            "clock_out",
          ],
        },
      ],
    });

    const callrealizations = callplan?.callplanrealizations
      ? callplan.callplanrealizations.map((realization) => realization.dataValues)
      : null;

    const ucAttendance = callrealizations.map((cr) => {
      // ambil data clock in dan clock out, lalu hitung durasi nya
      const duration = getDuration(cr.clock_in, cr.clock_out);

      return {
        ...cr,
        ...upCountry.dataValues,
        callplan: callplan.doc_no,
        duration: duration,
        territory_name: upCountry.territory.display_name,
        employee_name: upCountry.employee.employee_name,
      };
    });

    return {
      data: ucAttendance,
      success: true,
      message: "Data report has been loaded!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load report data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

// START HELPER UNTUK REPORT UC //
const groupByDate = (data) => {
  return data.reduce((acc, item) => {
    const date = item.start_date; // 2025-11-26

    if (!acc[date]) {
      acc[date] = [];
    }

    acc[date].push({
      id: item.id,
      client_name: item.client?.display_name || null,
      outlet_name: item.outlet?.display_name || null,
      action: item.action,
      note: item.note,
      priority: item.client?.priority || null,
      latitude: item.latitude || null,
      longitude: item.longitude || null,
      address: item.address || null,
      filename: item.filename || null,
    });

    return acc;
  }, {});
};

const mergeCheckIn = (groupedData, employeeCheckIn) => {
  const result = { ...groupedData };

  employeeCheckIn.forEach((item) => {
    const date = item.date;

    // kalau tanggal belum ada, buat array kosong
    if (!result[date]) result[date] = [];

    // kasih flag biar mudah di-detect
    const data = {
      ...item,
      isCheckIn: true,
    };

    // taruh di awal kalau IN, di akhir kalau OUT
    item.log_type === "IN"
      ? result[date].unshift(data) // awal
      : result[date].push(data); // akhir
  });

  return result;
};
// END HELPER UNTUK UC

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

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

    // if (params.employee_id) {
    //   filterConditions.employee_id = {
    //     [Op.in]: params?.employee_id,
    //   };
    // }

    if (params.employee_id) {
      filterConditions.employee_id = {
        [Op.eq]: params?.employee_id,
      };
    }

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

    if (params.from_date && params.to_date) {
      filterConditions[Op.and] = [
        { from_date: { [Op.lte]: params.to_date } },
        { to_date: { [Op.gte]: params.from_date } },
      ];
    }

    // AMBIL UC
    let upCountries = await dbSAPPS.UpCountry.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.Designation, as: "designation", attributes: ["id", "display_name"] },
        { model: dbSAPPS.Employee, as: "employee", attributes: ["id", "employee_name"] },
      ],
    });

    upCountries = await Promise.all(
      upCountries.map(async (upCountry) => {
        // workflow
        const workflow = await dbSAPPS.Workflow.findOne({
          where: { doc_id: upCountry.id, doctype_id: 194 },
          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"],
                        },
                      ],
                    },
                  ],
                },
              ],
            },
          ],
        });

        // visit lists
        const visitlistData = await dbSAPPS.UpCountryVisitList.findAll({
          where: { upcountry_id: upCountry.id },
          order: [["id", "ASC"]],
          include: [
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name", "code", "priority", "specialist_id"],
              include: [{ model: dbSAPPS.Specialist, as: "specialist", attributes: ["id", "display_name"] }],
            },
            { model: dbSAPPS.Outlet, as: "outlet", attributes: ["id", "display_name", "code"] },
          ],
        });

        // itinerary / agenda
        const upcountryitinerary = await dbSAPPS.UpCountryItinerary.findAll({
          where: { upcountry_id: upCountry.id },
        });

        // distances
        const estimation_distances = await dbSAPPS.UpCountryDistance.findAll({
          where: { upcountry_id: upCountry.id },
        });

        // accommodation_requests
        const accommodation_requests = await dbSAPPS.UpCountryAccommodation.findAll({
          where: { upcountry_id: upCountry.id },
        });

        const visitlists = visitlistData?.map((visitlist) => {
          return {
            ...visitlist.dataValues,
            items: JSON.parse(visitlist.items),
          };
        });

        const uccostrealizations = await dbSAPPS.UcCostRealization.findAll({
          where: { upcountry_id: upCountry.id },
          attributes: [
            "id",
            "category",
            "upload_date",
            "amount",
            "filename",
            "territory_id",
            "employee_id",
            "designation_id",
          ],
        });

        const uccostrealizationsWithWorkflow = await Promise.all(
          uccostrealizations.map(async (uccostrealization) => {
            const workflow = await dbSAPPS.Workflow.findOne({
              where: { doc_id: uccostrealization.id, doctype_id: 224 },
            });

            return {
              ...uccostrealization.dataValues,
              workflow: workflow ? workflow.dataValues : null, // Explicitly set to null if not found
            };
          })
        );

        const callplan = await dbSAPPS.CallPlan.findOne({
          where: { id: upCountry?.callplan_id },
          attributes: ["id", "from_date", "to_date"],
        });

        const callplanrealizations = await dbSAPPS.CallPlanRealization.findAll({
          where: { callplan_id: callplan?.id, realization_status: "UC" },
          include: [
            {
              model: dbSAPPS.Client,
              as: "client",
              attributes: ["id", "display_name", "priority"],
            },
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              attributes: ["id", "display_name"],
            },
          ],
        });

        const groupedData = groupByDate(callplanrealizations);
        const dates = Object.keys(groupedData);

        // cari employee_checkin IN & OUT
        const employee_checkin = await dbSAPPS.EmployeeCheckin.findAll({
          where: {
            employee_id: upCountry?.employee_id,
            date: {
              [Op.in]: dates, // ini bagian terpentingnya
            },
            log_type: {
              [Op.in]: ["IN", "OUT"],
            },
          },
          attributes: ["id", "address", "log_type", "employee_id", "latitude", "longitude", "date"],
        });

        const attendance = employee_checkin.map((checkin) => checkin.dataValues);
        const merged = mergeCheckIn(groupedData, attendance);

        return {
          ...upCountry.dataValues,
          callplan: callplan ? callplan.dataValues : null,
          callplanrealizations: callplanrealizations?.map((realization) => realization.dataValues) || [],
          dates: dates,
          groupedData: groupedData || [],
          laporan_kunjungan: merged || [],
          uccostrealizations: uccostrealizationsWithWorkflow || [],
          company_name: upCountry?.company?.display_name,
          territory_name: upCountry?.territory?.display_name,
          employee_name: upCountry?.employee?.employee_name,
          designation_name: upCountry?.designation?.display_name,
          workflow: workflow ? workflow.dataValues : null,
          visitlists: visitlists,
          estimation_distances: estimation_distances.map((distance) => distance.dataValues),
          itineraries: upcountryitinerary.map((itinerary) => itinerary.dataValues),
          accommodation_requests: accommodation_requests.map((request) => request.dataValues),
        };
      })
    );

    return {
      upCountries,
      success: true,
      message: "Report loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to retrieve report. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function reportGimmickTransferRealization(params) {
  try {
    const filterConditions = {};
    if (params.employee_id)
      filterConditions.employee_id = {
        [Op.eq]: parseInt(params.employee_id),
      };

    if (params.territory_id)
      filterConditions.territory_id = {
        [Op.eq]: parseInt(params.territory_id),
      };

    if (params.doc_no)
      filterConditions.doc_no = {
        [Op.eq]: parseInt(params.doc_no),
      };

    if (params.transfer_status)
      filterConditions.transfer_status = {
        [Op.eq]: params.transfer_status,
      };

    if (params.transfer_scheduled_date) {
      const date = new Date(params.transfer_scheduled_date);

      const start = new Date(date);
      start.setHours(0, 0, 0, 0);

      const end = new Date(date);
      end.setHours(23, 59, 59, 999);

      filterConditions.transfer_scheduled_date = {
        [Op.between]: [start, end],
      };
    }

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

    // ambil data sales
    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"],
            },
          ],
        });

        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 nextPeriod = await _getDateByMonthOffset(
          gimmickRequest.transfer_realization_date,
          gimmickRequest?.periode
        );

        const transfer_realization_date = moment(gimmickRequest.transfer_realization_date).format("YYYY-MM-DD");
        const periodFrom = await dbSAPPS.Period.findOne({
          where: {
            start_date: { [Op.lte]: transfer_realization_date },
            end_date: { [Op.gte]: transfer_realization_date },
          },
          attributes: ["id", "display_name", "start_date", "end_date"],
        });

        const periodTo = await dbSAPPS.Period.findOne({
          where: {
            start_date: { [Op.lte]: nextPeriod },
            end_date: { [Op.gte]: nextPeriod },
          },
          attributes: ["id", "display_name", "start_date", "end_date"],
        });

        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,
          period_from_name: periodFrom ? periodFrom?.display_name : null,
          period_to_name: periodTo ? periodTo?.display_name : null,
          territoryitemgroup: territoryItemgroup ? territoryItemgroup.dataValues : null,
        };
      })
    );

    return {
      data: gimmickRequests,
      success: true,
      message: "Report gimmick transfer realization have been loaded!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load report gimmick transfer realization.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

// FUNGSI HELPER UNTUK REPORT GIMMCIK TRANSFER REALIZATION
async function _getDateByMonthOffset(dateStr, offsetMonth) {
  const date = new Date(dateStr);
  date.setMonth(date.getMonth() + Number(offsetMonth));

  return moment(date).format("YYYY-MM-DD");
}
