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,
  reportSalesVsTarget,
  reportVisitVerify,
  reportGoodsReceipt,
  reportEmployeeAttendanceRate,
  reportSaldoBalanceBreakTahunan,
  reportSaldoBalanceUser,
  reportUcCostRealization,
  reportUcAttendance,
  reportUpCountry,
  reportGimmickTransferRealization,
  reportSurveyTreatment,
};

const MAX_GET_ALL_IDS_DEPTH = 100;

/**
 * Recursive function to get all the ids from the array of objects.
 * Safe: depth limit and visited set to avoid Maximum call stack exceeded / cycles.
 * @param {array} arr - array of objects with optional .children
 * @param {number} [_depth=0] - current depth (internal)
 * @param {Set} [_visited] - set of already seen id keys (internal)
 * @returns {array} - array of ids
 */
function getAllIds(arr, _depth, _visited) {
  const depth = _depth ?? 0;
  const visited = _visited ?? new Set();
  if (!Array.isArray(arr) || depth >= MAX_GET_ALL_IDS_DEPTH) return [];
  const ids = [];
  for (const item of arr) {
    if (!item || item.id == null) continue;
    const idKey = String(item.id);
    if (visited.has(idKey)) continue;
    visited.add(idKey);
    ids.push(item.id);
    if (item?.children?.length > 0) {
      ids.push(...getAllIds(item.children, depth + 1, visited));
    }
  }
  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_working_in_dayoff: attendance ? attendance.status_working_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"],
        },
        {
          model: dbSAPPS.LeaveApplication,
          as: "leaveapplication",
          required: false,
          attributes: ["id", "from_date", "to_date", "total_leave_days", "half_day", "half_day_date", "leavetype_name"],
          include: [
            {
              model: dbSAPPS.LeaveType,
              as: "leavetype",
              attributes: ["id", "display_name", "is_lwp"],
            },
          ],
        },
        {
          model: dbSAPPS.AttendanceRequest,
          as: "attendancerequest",
          required: false,
          attributes: ["id", "reason", "reason_type", "status"],
        },
      ],
      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", "start_time", "end_time", "late_time", "early_time"],
        },
      ],
      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 la = matchedDataAttendance?.leaveapplication;
              const ar = matchedDataAttendance?.attendancerequest;
              const leavetype = la?.leavetype;
              const leavetypeName = leavetype?.display_name || la?.leavetype_name || "";
              const isLwp = !!(leavetype?.is_lwp || la?.is_lwp);
              let leaveDaysForDate = 0;
              if (la) {
                const fromOk = moment(la.from_date).isSameOrBefore(date);
                const toOk = moment(la.to_date).isSameOrAfter(date);
                if (fromOk && toOk) {
                  leaveDaysForDate = la.half_day && la.half_day_date === date ? 0.5 : 1;
                }
              }

              const newDataExportProcessing = {
                employee_id: employee?.id,
                employee_name: employee?.employee_name,
                date: date,
                day: moment(date).format("dddd"),
                day_name: moment(date).format("dddd"),
                day_status: dayStatus,
                holiday_name: matchedDataHariLibur?.display_name,
                attendance_id: matchedDataAttendance?.id ? matchedDataAttendance?.id : null,
                schedule_in: employee?.shifttype?.start_time || employee?.shifttype?.late_time || "",
                schedule_out: employee?.shifttype?.end_time || employee?.shifttype?.early_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 != null && matchedDataAttendance?.reason !== ""
                    ? matchedDataAttendance.reason
                    : "",
                notes: matchedDataAttendance?.notes ? matchedDataAttendance?.notes : "",
                company: employee?.company?.display_name,
                company_name: employee?.company?.display_name,
                status_absenceclarification: matchedDataAttendance?.status_absenceclarification
                  ? matchedDataAttendance?.status_absenceclarification
                  : false,
                status_working_in_dayoff: matchedDataAttendance?.status_working_in_dayoff
                  ? matchedDataAttendance?.status_working_in_dayoff
                  : false,
                status_permitease: matchedDataAttendance?.status_permitease
                  ? matchedDataAttendance?.status_permitease
                  : false,
                leaveapplication_id: la?.id || null,
                leavetype_name: leavetypeName,
                is_lwp: isLwp,
                leave_days_for_date: leaveDaysForDate,
                leaveapplication_from_date: la?.from_date || null,
                leaveapplication_to_date: la?.to_date || null,
                leaveapplication_total_days: la?.total_leave_days ?? null,
                attendancerequest_id: ar?.id || null,
                attendance_request_reason: ar?.reason || ar?.reason_type || null,
                attendance_request_status: ar?.status || null,
              };

              return newDataExportProcessing;
            }),
          );

          return dataWithDate;
        }),
    );

    const mergedArray = dataExportProcessing.flat();

    const employeeIds = employees?.map((e) => e.id) || [];
    let leaveApplicationsInPeriod = [];
    let leaveBalancesByEmployee = {};

    if (params.from_date && params.to_date && employeeIds.length > 0) {
      try {
        leaveApplicationsInPeriod = await dbSAPPS.LeaveApplication.findAll({
          where: {
            employee_id: { [Op.in]: employeeIds },
            from_date: { [Op.lte]: params.to_date },
            to_date: { [Op.gte]: params.from_date },
            status: { [Op.in]: ["Approved", "approved"] },
          },
          attributes: ["id", "employee_id", "from_date", "to_date", "total_leave_days", "leavetype_name"],
          include: [
            {
              model: dbSAPPS.LeaveType,
              as: "leavetype",
              required: false,
              attributes: ["id", "display_name", "is_lwp"],
            },
          ],
        });

        const leaveAllocations = await dbSAPPS.LeaveAllocation.findAll({
          where: {
            employee_id: { [Op.in]: employeeIds },
            from_date: { [Op.lte]: params.to_date },
            to_date: { [Op.gte]: params.from_date },
          },
          attributes: [
            "id",
            "employee_id",
            "new_leaves_allocated",
            "total_leaves_allocated",
            "used_leaves",
            "unused_leaves",
            "expired_leaves",
            "leaves_pending_approval",
          ],
          include: [
            {
              model: dbSAPPS.LeaveType,
              as: "leavetype",
              attributes: ["id", "display_name"],
            },
            {
              model: dbSAPPS.LeavePeriod,
              as: "leaveperiod",
              attributes: ["id", "display_name", "from_date", "to_date"],
            },
          ],
        });

        leaveAllocations.forEach((la) => {
          const eid = la.employee_id;
          if (!leaveBalancesByEmployee[eid]) {
            leaveBalancesByEmployee[eid] = [];
          }
          leaveBalancesByEmployee[eid].push({
            leavetype_name: la.leavetype?.display_name || la.leavetype_name || "-",
            leaveperiod_name: la.leaveperiod?.display_name || "-",
            total_leaves_allocated: la.total_leaves_allocated ?? la.new_leaves_allocated ?? 0,
            used_leaves: la.used_leaves ?? 0,
            unused_leaves: la.unused_leaves ?? 0,
            expired_leaves: la.expired_leaves ?? 0,
            leaves_pending_approval: la.leaves_pending_approval ?? 0,
          });
        });
      } catch (err) {
        console.warn("reportAttendanceByEmployee: leaveApplications/leaveAllocations fetch failed:", err?.message);
      }
    }

    return {
      attendances,
      employeeAttendance: mergedArray,
      leaveApplicationsInPeriod,
      leaveBalancesByEmployee,
      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];
        }, []);
      }
      // Safe hierarchy: depth limit + cycle detection (same pattern as reportSalesTarget/reportSalesContribution)
      const MAX_HIERARCHY_DEPTH = 100;
      const sameId = (a, b) => (a == null && b == null) || (a != null && b != null && String(a) === String(b));
      const hierarchyTerritoryVersionChilds = (parent_id = null, depth = 0, ancestorIds = new Set()) => {
        if (depth >= MAX_HIERARCHY_DEPTH) return [];
        const children = territoryVersionChilds.filter((c) => sameId(c.parent_id, parent_id));
        return children
          .map((child) => {
            const childId = child?.territory_id;
            const childIdKey = childId != null ? String(childId) : null;
            if (childIdKey && ancestorIds.has(childIdKey)) return null;
            const nextAncestors = new Set(ancestorIds);
            if (childIdKey) nextAncestors.add(childIdKey);
            return {
              id: child?.territory_id,
              name: child?.name,
              children: hierarchyTerritoryVersionChilds(child?.territory_id, depth + 1, nextAncestors),
            };
          })
          .filter(Boolean);
      };

      const territoryDatas = [
        {
          id: parseInt(params?.territory_id),
          children: hierarchyTerritoryVersionChilds(parseInt(params?.territory_id), 0, new Set()),
        },
      ];
      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) {
      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", "company_id"],
            include: [
              {
                model: dbSAPPS.Territory,
                as: "territory",
                attributes: ["id", "display_name"],
                include: [
                  {
                    model: dbSAPPS.EmployeeTerritory,
                    as: "employeeterritory",
                    attributes: ["employee_id", "territory_id"],
                    include: [
                      {
                        model: dbSAPPS.Employee,
                        as: "employee",
                        attributes: ["id", "employee_name"],
                      },
                    ],
                  },
                ],
              },
            ],
          },
        ],
      });

      if (getTerritoryVersion && getTerritoryVersion.length > 0) {
        territoryVersion = getTerritoryVersion;
        const companyId = params.company_id != null ? parseInt(params.company_id, 10) : null;
        territoryVersionChilds = getTerritoryVersion.reduce((acc, curr) => {
          const childs = (curr.territoryversionchilds || []).filter(
            (c) => c.company_id == null || parseInt(c.company_id, 10) === companyId,
          );
          return [...acc, ...childs];
        }, []);
      }

      const MAX_HIERARCHY_DEPTH = 100;
      const sameId = (a, b) => (a == null && b == null) || (a != null && b != null && String(a) === String(b));
      const hierarchyTerritoryVersionChilds = (parent_id = null, depth = 0, ancestorIds = new Set()) => {
        if (depth >= MAX_HIERARCHY_DEPTH) return [];
        const children = territoryVersionChilds.filter((c) => sameId(c.parent_id, parent_id));
        return children
          .map((child) => {
            const childId = child?.territory_id;
            const childIdKey = childId != null ? String(childId) : null;
            if (childIdKey && ancestorIds.has(childIdKey)) return null;
            const nextAncestors = new Set(ancestorIds);
            if (childIdKey) nextAncestors.add(childIdKey);
            return {
              id: child?.territory_id,
              name: child?.name,
              children: hierarchyTerritoryVersionChilds(child?.territory_id, depth + 1, nextAncestors),
            };
          })
          .filter(Boolean);
      };

      const territoryDatas = [
        {
          id: parseInt(params?.territory_id),
          children: hierarchyTerritoryVersionChilds(parseInt(params?.territory_id), 0, new Set()),
        },
      ];
      territories = 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 — iterative (no recursion) + depth limit + cycle detection (ref: reportSalesTarget)
    let findVersion;
    const MAX_PARENT_HIERARCHY_DEPTH = 100;
    const parentHierarchy = (territory_id, transaction_date) => {
      const hierarchy = [];
      findVersion = territoryVersion.find((c) => transaction_date >= c.valid_from && transaction_date <= c.valid_to);
      if (!findVersion || !findVersion.territoryversionchilds) return hierarchy;

      const childs = findVersion.territoryversionchilds;
      const sameId = (a, b) => (a == null && b == null) || (a != null && b != null && String(a) === String(b));
      let currentId = territory_id;
      let depth = 0;
      const visited = new Set();

      while (currentId != null && depth < MAX_PARENT_HIERARCHY_DEPTH) {
        const idKey = String(currentId);
        if (visited.has(idKey)) break;
        visited.add(idKey);

        const node = childs.find((c) => sameId(c.territory_id, currentId));
        if (!node) break;

        if (node.parent_id != null) {
          const parentNode = childs.find((c) => sameId(c.territory_id, node.parent_id));
          if (!parentNode) break;
          if (parentNode.territory_id !== territory_id) {
            hierarchy.push({
              id: parentNode.territory_id,
              name: parentNode.name,
              employee_name: parentNode.territory?.employeeterritory?.[0]?.employee?.employee_name,
            });
          }
          currentId = node.parent_id;
        } else {
          break;
        }
        depth++;
      }

      const index = hierarchy.findIndex((h) => sameId(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", "outlettype_id"],
          include: [
            {
              model: dbSAPPS.OutletType,
              as: "outlettype",
              attributes: ["id", "display_name"],
            },
          ],
        });
        const territory = await dbSAPPS.Territory.findByPk(row.territory_id, {
          attributes: ["id", "display_name"],
          include: [
            {
              model: dbSAPPS.TerritoryItemGroup,
              as: "territoryitemgroups",
              attributes: ["territory_id", "itemgroup_id"],
              include: [
                {
                  model: dbSAPPS.ItemGroup,
                  as: "itemgroup",
                  attributes: ["id", "display_name"],
                },
              ],
            },
            {
              model: dbSAPPS.EmployeeTerritory,
              as: "employeeterritory",
              attributes: ["employee_id", "territory_id"],
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["id", "employee_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,
          // territoryItemGroup,
          value_disc,
          nett_sales,
          distributor,
          period,
          outlet,
          territory,
          employee_name: territory?.employeeterritory?.[0]?.employee?.employee_name,
          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,
    };
  }
}

/**
 * Report Sales vs Target — Dashboard.
 * Data sales dari SalesDistributor, target dari SalesTarget.
 * Filter: company_id (wajib), period_id (wajib), territory_id (opsional).
 * Jika tidak punya territory: filter by company + period saja.
 * Jika punya territory: sama seperti report sales contribution — cek TerritoryVersion & TerritoryVersionChild,
 * ambil semua bawahan (subordinate) lalu filter sales & target by territory_id IN territories.
 *
 * Penting: SalesDistributor difilter pakai transaction_date between period (sama seperti report sales contribution),
 * bukan hanya period_id, agar data konsisten dengan report lain. enabled tidak difilter agar tidak mengeluarkan data.
 */
async function reportSalesVsTarget(params) {
  try {
    // Validasi: company dan period wajib
    if (!params?.company_id || !params?.period_id) {
      return {
        success: false,
        message: "company_id dan period_id wajib diisi. Jika tidak punya territory, filter by company dan period saja.",
      };
    }

    const companyId = parseInt(params.company_id, 10);
    const periodId = parseInt(params.period_id, 10);
    const territoryIdParam = params.territory_id != null ? parseInt(params.territory_id, 10) : null;

    // Ambil period sekali untuk start_date & end_date (dipakai filter SalesDistributor & opsional territory version)
    const periodRow = await dbSAPPS.Period.findByPk(periodId, {
      attributes: ["id", "display_name", "start_date", "end_date"],
    });
    if (!periodRow) {
      return { success: false, message: "Period not found!" };
    }
    const start_date = periodRow.start_date;
    const end_date = periodRow.end_date;

    let territoryIds = []; // kosong = pakai company+period saja; terisi = filter by territory (termasuk bawahan)

    // --- Logika territory: sama seperti report sales contribution ---
    if (territoryIdParam != null && start_date && 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: companyId,
        },
        include: [
          {
            model: dbSAPPS.TerritoryVersionChild,
            as: "territoryversionchilds",
            attributes: ["id", "name", "parent_id", "territory_id", "company_id"],
          },
        ],
      });

      let territoryVersionChilds = [];
      if (getTerritoryVersion && getTerritoryVersion.length > 0) {
        territoryVersionChilds = getTerritoryVersion.reduce((acc, curr) => {
          const childs = (curr.territoryversionchilds || []).filter(
            (c) => c.company_id == null || parseInt(c.company_id, 10) === companyId,
          );
          return [...acc, ...childs];
        }, []);
      }

      const MAX_HIERARCHY_DEPTH = 100;
      const sameId = (a, b) => (a == null && b == null) || (a != null && b != null && String(a) === String(b));
      const hierarchyTerritoryVersionChilds = (parent_id = null, depth = 0, ancestorIds = new Set()) => {
        if (depth >= MAX_HIERARCHY_DEPTH) return [];
        const children = territoryVersionChilds.filter((c) => sameId(c.parent_id, parent_id));
        return children
          .map((child) => {
            const childId = child?.territory_id;
            const childIdKey = childId != null ? String(childId) : null;
            if (childIdKey && ancestorIds.has(childIdKey)) return null;
            const nextAncestors = new Set(ancestorIds);
            if (childIdKey) nextAncestors.add(childIdKey);
            return {
              id: child?.territory_id,
              name: child?.name,
              children: hierarchyTerritoryVersionChilds(child?.territory_id, depth + 1, nextAncestors),
            };
          })
          .filter(Boolean);
      };

      const territoryDatas = [
        {
          id: territoryIdParam,
          children: hierarchyTerritoryVersionChilds(territoryIdParam, 0, new Set()),
        },
      ];
      territoryIds = getAllIds(territoryDatas && territoryDatas.length > 0 ? territoryDatas : []);
      if (territoryIds.indexOf(territoryIdParam) === -1) {
        territoryIds = [territoryIdParam, ...territoryIds];
      }
    }

    // --- Filter Sales (SalesDistributor): sama pola dengan report sales contribution ---
    // Pakai transaction_date between period range + company_id; tidak filter enabled agar data tidak kosong
    const whereSales = {
      company_id: companyId,
      transaction_date: start_date && end_date ? { [Op.between]: [start_date, end_date] } : undefined,
    };
    if (territoryIds.length > 0) {
      whereSales.territory_id = { [Op.in]: territoryIds };
    }
    // Fallback: jika transaction_date tidak mengembalikan data, coba juga dengan period_id (robust)
    const whereSalesByPeriodId = {
      company_id: companyId,
      period_id: periodId,
    };
    if (territoryIds.length > 0) {
      whereSalesByPeriodId.territory_id = { [Op.in]: territoryIds };
    }

    // --- Filter Target (SalesTarget): period_id + company_id; tidak filter enabled ---
    const whereTarget = {
      company_id: companyId,
      period_id: periodId,
    };
    if (territoryIds.length > 0) {
      whereTarget.territory_id = { [Op.in]: territoryIds };
    }

    // --- Ambil data Sales: coba transaction_date dulu, lalu fallback period_id ---
    let salesRows = await dbSAPPS.SalesDistributor.findAll({
      where: whereSales,
      attributes: ["id", "territory_id", "item_id", "total", "quantity", "price", "discount"],
      include: [
        { model: dbSAPPS.Item, as: "item", attributes: ["id", "display_name", "price"] },
        { model: dbSAPPS.Territory, as: "territory", attributes: ["id", "display_name"] },
      ],
    });
    if (!salesRows || salesRows.length === 0) {
      salesRows = await dbSAPPS.SalesDistributor.findAll({
        where: whereSalesByPeriodId,
        attributes: ["id", "territory_id", "item_id", "total", "quantity", "price", "discount"],
        include: [
          { model: dbSAPPS.Item, as: "item", attributes: ["id", "display_name", "price"] },
          { model: dbSAPPS.Territory, as: "territory", attributes: ["id", "display_name"] },
        ],
      });
    }

    // --- Ambil data Target (SalesTarget + details) ---
    const targetRows = await dbSAPPS.SalesTarget.findAll({
      where: whereTarget,
      attributes: ["id", "territory_id", "period_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"] }],
        },
      ],
    });

    // --- Agregasi: total sales & total target (nilai). Akses aman untuk Sequelize model atau plain ---
    let totalSales = 0;
    const salesByItem = new Map();
    for (const row of salesRows) {
      const totalVal = Number(row.get ? row.get("total") : row.total) || 0;
      totalSales += totalVal;
      const itemId = row.get ? row.get("item_id") : row.item_id;
      const itemObj = row.item || (row.get && row.get("item"));
      const itemName = (itemObj && (itemObj.display_name || (itemObj.get && itemObj.get("display_name")))) || "";
      if (itemId != null) {
        const ex = salesByItem.get(itemId);
        if (ex) ex.sales += totalVal;
        else salesByItem.set(itemId, { item_id: itemId, item_name: itemName, sales: totalVal });
      }
    }

    let totalTarget = 0;
    const targetByItem = new Map();
    for (const st of targetRows) {
      const details = st.salestargetdetails || (st.get && st.get("salestargetdetails")) || [];
      for (const d of details) {
        const qty = Number(d.quantity != null ? d.quantity : d.get && d.get("quantity")) || 0;
        const itemObj = d.item || (d.get && d.get("item"));
        const price =
          Number(itemObj && (itemObj.price != null ? itemObj.price : itemObj.get && itemObj.get("price"))) || 0;
        const value = qty * price;
        totalTarget += value;
        const itemId = d.item_id != null ? d.item_id : d.get && d.get("item_id");
        const itemName = (itemObj && (itemObj.display_name || (itemObj.get && itemObj.get("display_name")))) || "";
        if (itemId != null) {
          const ex = targetByItem.get(itemId);
          if (ex) ex.target += value;
          else targetByItem.set(itemId, { item_id: itemId, item_name: itemName, target: value });
        }
      }
    }

    // --- Gabung byItem: sales + target per item ---
    const allItemIds = new Set([...salesByItem.keys(), ...targetByItem.keys()]);
    const byItem = [];
    for (const itemId of allItemIds) {
      const s = salesByItem.get(itemId) || { item_id: itemId, item_name: "", sales: 0 };
      const t = targetByItem.get(itemId) || { item_id: itemId, item_name: "", target: 0 };
      const itemName = s.item_name || t.item_name || "";
      const sales = s.sales || 0;
      const target = t.target || 0;
      const achievement = target > 0 ? Math.round((sales / target) * 10000) / 100 : sales > 0 ? 100 : 0;
      byItem.push({
        item_id: itemId,
        item_name: itemName,
        sales: Math.round(sales),
        target: Math.round(target),
        achievement_percent: achievement,
      });
    }
    // Urutkan by sales descending
    byItem.sort((a, b) => (b.sales || 0) - (a.sales || 0));

    const achievementPercent =
      totalTarget > 0 ? Math.round((totalSales / totalTarget) * 10000) / 100 : totalSales > 0 ? 100 : 0;

    // --- Info period & company untuk tampilan (periodRow sudah diambil di awal) ---
    const company = await dbSAPPS.Company.findByPk(companyId, {
      attributes: ["id", "display_name"],
    });

    return {
      success: true,
      message: "Report sales vs target loaded successfully.",
      data: {
        summary: {
          total_sales: Math.round(totalSales),
          total_target: Math.round(totalTarget),
          achievement_percent: achievementPercent,
        },
        byItem,
        period: periodRow ? periodRow.get({ plain: true }) : null,
        company: company ? company.get({ plain: true }) : null,
        territory_ids: territoryIds,
      },
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load report sales vs target.";
    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 = [];
    /** Id ancestor (parent ke atas) dari territory filter: dipakai stopper agar baris parent tidak masuk dataTarget. */
    let filterAncestorIds = new Set();

    if (params && params.company_id && 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 },
      });

      if (!period) {
        return {
          territoryDatas: [],
          dataTarget: [],
          itemCompany: [],
          period: null,
          success: false,
          message: "Period not found",
        };
      }

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

      // Only hierarchy for the selected company: filter by company_id so Canopus West returns only that company's tree
      const getTerritoryVersion = await dbSAPPS.TerritoryVersion.findAll({
        attributes: ["id", "display_name", "valid_from", "valid_to", "enabled", "company_id"],
        where: {
          [Op.and]: [
            { company_id: params.company_id },
            { 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", "company_id"],
          },
        ],
      });

      if (getTerritoryVersion && getTerritoryVersion.length > 0) {
        territoryVersion = getTerritoryVersion;
        const companyId = params.company_id != null ? parseInt(params.company_id, 10) : null;
        territoryVersionChilds = getTerritoryVersion.reduce((acc, curr) => {
          const childs = (curr.territoryversionchilds || []).filter(
            (c) => c.company_id == null || parseInt(c.company_id, 10) === companyId,
          );
          return [...acc, ...childs];
        }, []);
      }

      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 toPlain = (model) => (model && typeof model.get === "function" ? model.get({ plain: true }) : model);

      const MAX_HIERARCHY_DEPTH = 100;
      const sameId = (a, b) => (a == null && b == null) || (a != null && b != null && String(a) === String(b));
      /** excludedIds: territory_id yang bukan subCoordinate (ancestor + sibling) — jangan tampilkan sebagai anak. */
      const hierarchyTerritoryVersionChilds = (
        parent_id = null,
        depth = 0,
        ancestorIds = new Set(),
        excludedIds = new Set(),
      ) => {
        if (depth >= MAX_HIERARCHY_DEPTH) return [];
        const children = territoryVersionChilds.filter(
          (c) => sameId(c.parent_id, parent_id) && !excludedIds.has(String(c.territory_id)),
        );

        return children
          .map((child) => {
            const childId = child?.territory_id;
            const childIdKey = childId != null ? String(childId) : null;
            if (childIdKey && ancestorIds.has(childIdKey)) return null;
            const nextAncestors = new Set(ancestorIds);
            if (childIdKey) nextAncestors.add(childIdKey);
            const st = salesTarget.find((s) => s.territory_id === child?.territory_id);
            return {
              id: child?.territory_id,
              name: child?.name,
              children: hierarchyTerritoryVersionChilds(child?.territory_id, depth + 1, nextAncestors, excludedIds),
              salesTarget: toPlain(st),
            };
          })
          .filter(Boolean);
      };

      // Stopper: territory yang di-apply = batas atas. Hanya subCoordinate (anak di bawah); ancestor + sibling tidak boleh masuk.
      const selectedTerritoryId = parseInt(params?.territory_id, 10);
      const selectedNode = territoryVersionChilds.find((c) => sameId(c.territory_id, selectedTerritoryId));

      // Ancestor: BUM CANOPUS, INDONESIA ANANTA, dll (naik lewat parent_id).
      let node = selectedNode;
      while (node && node.parent_id != null) {
        filterAncestorIds.add(String(node.parent_id));
        node = territoryVersionChilds.find((c) => sameId(c.territory_id, node.parent_id));
      }

      // Sibling: CANOPUS EAST dll (satu level, parent_id sama dengan selected) — bukan subCoordinate dari CANOPUS WEST.
      const selectedParentId = selectedNode?.parent_id;
      const siblingIds = new Set(
        territoryVersionChilds
          .filter((c) => sameId(c.parent_id, selectedParentId))
          .map((c) => (c.territory_id != null ? String(c.territory_id) : null))
          .filter(Boolean),
      );

      const excludedFromSubTree = new Set([...filterAncestorIds, ...siblingIds]);

      const selectedTerritoryName = params?.territory_name || selectedNode?.name || "";

      const selectedSt = salesTarget.find((s) => s.territory_id === selectedTerritoryId);
      territoryDatas = [
        {
          id: selectedTerritoryId,
          name: selectedTerritoryName,
          salesTarget: toPlain(selectedSt),
          children: hierarchyTerritoryVersionChilds(selectedTerritoryId, 0, new Set(), excludedFromSubTree),
        },
      ];
    }

    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 (dengan batas kedalaman & deteksi siklus):
    const MAX_FLATTEN_DEPTH = 100;
    const flattenData = (nodes, level = 0) => {
      const result = [];

      const helper = (node, lvl, visited = new Set()) => {
        if (!node || lvl >= MAX_FLATTEN_DEPTH) {
          return { rows: [], aggregate: [] };
        }
        const nodeId = node?.id;
        const nodeIdKey = nodeId != null ? String(nodeId) : null;
        if (nodeIdKey && visited.has(nodeIdKey)) {
          return { rows: [], aggregate: [] };
        }
        const nextVisited = new Set(visited);
        if (nodeIdKey) nextVisited.add(nodeIdKey);

        const children = node.children || [];
        let subtreeRows = [];
        const aggregateMap = new Map();

        for (const child of children) {
          const { rows: childRows, aggregate: childAggregate } = helper(child, lvl + 1, nextVisited);

          subtreeRows = subtreeRows.concat(childRows);

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

    let rows = flattenData(territoryDatas);
    // Stopper: jangan tampilkan baris parent (BUM CANOPUS, INDONESIA ANANTA, dll); hanya sampai territory yang di-apply.
    if (filterAncestorIds && filterAncestorIds.size > 0) {
      rows = rows.filter((row) => row && (row.id == null || !filterAncestorIds.has(String(row.id))));
    }

    const toPlainAny = (model) => (model && typeof model.get === "function" ? model.get({ plain: true }) : model);
    const plainItemCompany = Array.isArray(itemCompany) ? itemCompany.map(toPlainAny) : itemCompany;
    const plainPeriod = toPlainAny(period);

    return {
      territoryDatas,
      dataTarget: rows,
      itemCompany: plainItemCompany,
      period: plainPeriod,
      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", "start_time", "end_time", "late_time", "early_time"],
        },
      ],
      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?.start_time || employee?.shifttype?.late_time || "",
                schedule_out: employee?.shifttype?.end_time || employee?.shifttype?.early_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_working_in_dayoff: matchedDataAttendance[0]?.status_working_in_dayoff
                  ? matchedDataAttendance[0]?.status_working_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 || [])];
        }, []);
      }

      // Iteratif BFS: hindari rekursi untuk mencegah Maximum call stack size exceeded
      const rootId = parseInt(params?.territory_id, 10);
      const sameId = (a, b) => (a == null && b == null) || (a != null && b != null && String(a) === String(b));
      const getTerritoryIdsBFS = () => {
        const ids = [rootId];
        const queue = [rootId];
        const visited = new Set([String(rootId)]);
        const MAX_ITER = 10000;
        let iter = 0;
        while (queue.length > 0 && iter < MAX_ITER) {
          iter += 1;
          const parentId = queue.shift();
          const children = territoryVersionChilds.filter((c) => sameId(c.parent_id, parentId));
          for (const child of children) {
            const cid = child?.territory_id;
            const key = cid != null ? String(cid) : null;
            if (key && !visited.has(key)) {
              visited.add(key);
              ids.push(cid);
              queue.push(cid);
            }
          }
        }
        return ids;
      };

      territories = getTerritoryIdsBFS();
    }

    // =========================
    // 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");
}

async function reportSurveyTreatment(params) {
  try {
    let territoryVersion = [];
    let territoryVersionChilds = [];
    let territories;
    let rentangPeriod;
    let firstPeriod;
    let start_date;
    let end_date;

    // If period provided, fetch period range first
    if (params && params.period_id) {
      rentangPeriod = await dbSAPPS.Period.findAll({
        where: { id: params?.period_id },
        attributes: ["id", "display_name", "start_date", "end_date", "year_id"],
      });

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

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

    // Territory/version logic requires both territory_id and period_id
    if (params && params.territory_id && params.period_id) {
      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", "company_id"],
            include: [
              {
                model: dbSAPPS.Territory,
                as: "territory",
                attributes: ["id", "display_name"],
                include: [
                  {
                    model: dbSAPPS.EmployeeTerritory,
                    as: "employeeterritory",
                    attributes: ["employee_id", "territory_id"],
                    include: [
                      {
                        model: dbSAPPS.Employee,
                        as: "employee",
                        attributes: ["id", "employee_name"],
                      },
                    ],
                  },
                ],
              },
            ],
          },
        ],
      });

      if (getTerritoryVersion && getTerritoryVersion.length > 0) {
        territoryVersion = getTerritoryVersion;
        const companyId = params.company_id != null ? parseInt(params.company_id, 10) : null;
        territoryVersionChilds = getTerritoryVersion.reduce((acc, curr) => {
          const childs = (curr.territoryversionchilds || []).filter(
            (c) => c.company_id == null || parseInt(c.company_id, 10) === companyId,
          );
          return [...acc, ...childs];
        }, []);
      }

      const MAX_HIERARCHY_DEPTH = 100;
      const sameId = (a, b) => (a == null && b == null) || (a != null && b != null && String(a) === String(b));
      const hierarchyTerritoryVersionChilds = (parent_id = null, depth = 0, ancestorIds = new Set()) => {
        if (depth >= MAX_HIERARCHY_DEPTH) return [];
        const children = territoryVersionChilds.filter((c) => sameId(c.parent_id, parent_id));
        return children
          .map((child) => {
            const childId = child?.territory_id;
            const childIdKey = childId != null ? String(childId) : null;
            if (childIdKey && ancestorIds.has(childIdKey)) return null;
            const nextAncestors = new Set(ancestorIds);
            if (childIdKey) nextAncestors.add(childIdKey);
            return {
              id: child?.territory_id,
              name: child?.name,
              children: hierarchyTerritoryVersionChilds(child?.territory_id, depth + 1, nextAncestors),
            };
          })
          .filter(Boolean);
      };

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

    if (params.territory_id) {
      if (Array.isArray(territories)) filterConditions.territory_id = { [Op.in]: territories };
      else filterConditions.territory_id = { [Op.eq]: params.territory_id };
    }

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

    // ambil survey yang sudah di approve
    const survey = await dbSAPPS.Survey.findAll({
      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 surveyIds = survey.map((p) => p.id);
    let workflows = [];

    if (surveyIds.length > 0) {
      [workflows] = await Promise.all([
        dbSAPPS.Workflow.findAll({
          where: {
            doc_id: { [Op.in]: surveyIds },
            doctype_id: 23,
          },
          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 workflowMap = {};
    workflows.forEach((w) => {
      workflowMap[w.doc_id] = w;
    });

    const resultSurvey = survey.map((item) => {
      return {
        ...item.dataValues,
        workflow: workflowMap[item.id] ? workflowMap[item.id] : null,
      };
    });

    const resultSurveyIds = resultSurvey.map((p) => p.id);

    let treatments = [];
    if (resultSurveyIds.length > 0) {
      treatments = await dbSAPPS.SurveyTreatment.findAll({
        where: {
          survey_id: { [Op.in]: resultSurveyIds },
        },
        include: [
          {
            model: dbSAPPS.Outlet,
            as: "outlet",
            attributes: ["id", "display_name"],
            include: [
              {
                model: dbSAPPS.Province,
                as: "province",
                attributes: ["id", "display_name"],
              },
              {
                model: dbSAPPS.City,
                as: "city",
                attributes: ["id", "display_name"],
              },
            ],
          },
        ],
      });
    }

    const finalData = [];

    for (const row of treatments) {
      const surveyParent = resultSurvey.find((s) => s.id === row.survey_id);
      if (!surveyParent) continue;

      let details = row.treatment_details;
      if (typeof details === "string") {
        try {
          details = JSON.parse(details);
        } catch (e) {
          // skip malformed JSON details but don't break the whole report
          continue;
        }
      }

      if (!details || typeof details !== "object") continue;

      const detailEntries = Array.isArray(details) ? details : [details];

      for (const det of detailEntries) {
        if (!det || typeof det !== "object") continue;

        const productMap = {};

        const itemKeys = Object.keys(det).filter((key) => key.startsWith("item_"));
        for (const itemKey of itemKeys) {
          const index = itemKey.split("_")[1];
          const qtyKey = `qty_${index}`;

          let product = det[itemKey];
          const qty = Number(det[qtyKey] || 0);

          // If product is not a string (likely an id), try to lookup Item.display_name
          if (product != null && typeof product !== "string") {
            try {
              const itemRec = await dbSAPPS.Item.findOne({
                where: { id: product },
                attributes: ["id", "display_name"],
              });
              if (itemRec && itemRec.display_name) product = itemRec.display_name;
              else product = String(product);
            } catch (e) {
              product = String(product);
            }
          }

          if (typeof product === "string" && product.trim() !== "" && qty > 0) {
            if (!productMap[product]) {
              productMap[product] = 0;
            }

            productMap[product] += qty;
          }
        }

        Object.entries(productMap).forEach(([product, total_qty]) => {
          finalData.push({
            province: row?.outlet?.province?.display_name || null,
            city: row?.outlet?.city?.display_name || null,
            outlet_id: row.outlet_id,
            outlet_name: row.outlet?.display_name || null,
            territory_name: surveyParent?.territory?.display_name || null,
            period: surveyParent?.period_id,
            period_name: surveyParent?.period?.display_name || null,
            patient_id: det.patient_id,
            patient_name: det.patient_name,
            sub_patient: det.sub_patient || "-",
            product,
            total_qty,
            workflow_status: surveyParent?.workflow?.status || null,
          });
        });
      }
    }

    return {
      data: finalData,
      treatments,
      success: true,
      message: "Report survey treatment have been loaded!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load report survey treatment.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
