import {
    allPromises,
    BadRequestError,
    BaseAdapter,
    BasePort,
    CapabilityName,
    ErrorCodes,
    getMapByGetter,
    I18n,
    ISO_DATE,
    LanguagesValues,
    Modules,
    moment,
    OrderItem,
    TranslationsPort,
    WORKDAYS,
} from '@humand-packages/common';
import { Workbook, Worksheet } from 'exceljs';
import _ from 'lodash';
import { Service } from 'typedi';
import { PortsDI } from '../../../../config/portsDI';
import FaceRecognitionServicePort from '../../../facialRecognition/business/ports/faceRecognitionPort';
import { Instance } from '../../../instances/business/models/instance';
import { OrganizationChartsService } from '../../../organizationCharts/business/services/organizationChartsService';
import { PermissionsService } from '../../../permissions/business/services/permissionsService';
import { StaticUserFields } from '../../../profileFields/business/types/profileFields';
import { Site } from '../../../regions/business/models/site';
import { SiteService } from '../../../regions/business/services/sitesService';
import { SegmentationGroup } from '../../../segmentations/business/models/segmentationGroup';
import { SegmentationItem } from '../../../segmentations/business/models/segmentationItem';
import { SegmentationsService } from '../../../segmentations/business/services/segmentationsService';
import { TIME_OFF_POLICY_TYPE_ACTIVITY_TYPES } from '../../../timeOff/business/types/policyTypes/activityTypes';
import { TIME_OFF_REQUEST_CONSUMPTION_TYPES } from '../../../timeOff/business/types/requests/timeOffRequestConsumptionTypes';
import { User } from '../../../users/business/models/user';
import { UserInterface } from '../../../users/business/models/userInterface';
import { UserListType } from '../../../users/infrastructure/dataAccessObjects/userDAO';
import { Incidence } from '../../infrastructure/constants/incidences';
import { TimeTrackingReportKeys } from '../constants/translationKeys';
import { getSiteCellValue, hasLocationIncidence } from '../helpers/siteHelper';
import { TimeTrackingDaySummaryReportFilters } from '../interfaces/timeTrackingDaySummaryFilters';
import { TimeTrackingDaySummary } from '../models/daySummary';
import { PairedEntry } from '../models/PairedEntry';
import { TIME_FORMAT } from '../models/timeTracking/scheduleData';
import { TimeTrackingCategorizedHourWithCategoryData } from '../models/timeTracking/timeTrackingCategorizedHour';
import { TimeTrackingPoliciesPort } from '../ports/timeTrackingPoliciesPort';
import { UsersServicePort } from '../ports/userServicePort';
import { TimeTrackingEntrySource } from '../types/source';
import { TimeTrackingHourCategorizationService } from './hourCategorizationService';
import TimeTrackingService from './timeTracking';

const PADDING = null;
const WHITE = 'FFFFFF';
const LIGHT_BLUE = 'ebeefd';
const HUMAND_BLUE = '3873cb';
const CELL_TIME_FORMAT = '[h]:mm';

const DETAILED_COLUMNS_ORDER = [
    TimeTrackingReportKeys.DATE,
    TimeTrackingReportKeys.WEEKDAY,
    TimeTrackingReportKeys.TIME_SLOTS_START,
    TimeTrackingReportKeys.TIME_SLOTS_END,
    TimeTrackingReportKeys.TIME_SLOT_HOURS,
    TimeTrackingReportKeys.WORKED_HOURS,
    TimeTrackingReportKeys.SCHEDULED_HOURS,
    TimeTrackingReportKeys.DIFF_HOURS,
    TimeTrackingReportKeys.EXTRA_HOURS,
    TimeTrackingReportKeys.TIME_OFF,
    TimeTrackingReportKeys.HOLIDAYS,
    TimeTrackingReportKeys.ABSCENCES,
    TimeTrackingReportKeys.INCUMPLIMIENTO,
    TimeTrackingReportKeys.TARDINESS_HS,
    TimeTrackingReportKeys.COMMENTS,
    TimeTrackingReportKeys.LOCATION_INCIDENCE,
    TimeTrackingReportKeys.SITE_START,
    TimeTrackingReportKeys.SITE_END,
    TimeTrackingReportKeys.GEOLOCATION_START,
    TimeTrackingReportKeys.GEOLOCATION_END,
];
const CONSOLIDATED_COLUMNS_ORDER = [
    TimeTrackingReportKeys.WORKED_HOURS,
    TimeTrackingReportKeys.SCHEDULED_HOURS,
    TimeTrackingReportKeys.DIFF_HOURS,
    TimeTrackingReportKeys.EXTRA_HOURS,
    TimeTrackingReportKeys.REST_HOURS,
    TimeTrackingReportKeys.HOLIDAY_HOURS,
    TimeTrackingReportKeys.SUNDAY_HOURS,
    TimeTrackingReportKeys.ABSCENCE_HOURS,
    TimeTrackingReportKeys.ABSCENCES,
    TimeTrackingReportKeys.INCUMPLIMIENTO,
    TimeTrackingReportKeys.TARDINESS,
];
const TIME_COLUMNS_DETAILED = [
    TimeTrackingReportKeys.TIME_SLOT_HOURS,
    TimeTrackingReportKeys.WORKED_HOURS,
    TimeTrackingReportKeys.SCHEDULED_HOURS,
    TimeTrackingReportKeys.DIFF_HOURS,
    TimeTrackingReportKeys.EXTRA_HOURS,
    TimeTrackingReportKeys.TARDINESS_HS,
];
const TIME_COLUMNS_CONSOLIDATED = [
    ...TIME_COLUMNS_DETAILED,
    TimeTrackingReportKeys.ABSCENCE_HOURS,
    TimeTrackingReportKeys.REST_HOURS,
    TimeTrackingReportKeys.HOLIDAY_HOURS,
    TimeTrackingReportKeys.SUNDAY_HOURS,
];
const LEFT_ALIGNED = [TimeTrackingReportKeys.DATE, TimeTrackingReportKeys.WEEKDAY];
const COLUMN_WIDTH_DEFAULT = 15;
const ROW_HEIGHT_DEFAULT = 25;
const INCIDENCE_PRESENT = 1;
const NO_INCIDENCE = 0;
const HOURS_IN_DAY = 24;
const GEO_URL = 'https://www.google.com/maps/';
const MINUTES_IN_HOUR = 60;

const SYSTEM_SOURCES = [
    TimeTrackingEntrySource.DAY_CHANGE,
    TimeTrackingEntrySource.AUTO_CLOSE,
    TimeTrackingEntrySource.INTEGRATION,
    TimeTrackingEntrySource.MANUAL,
];

export abstract class ReportServicePort extends BasePort {
    abstract detailedReport: (user: UserInterface, instance: Instance, reportFilters: TimeTrackingDaySummaryReportFilters) => Promise<Workbook>;
    abstract consolidatedReport: (user: UserInterface, instance: Instance, reportFilters: TimeTrackingDaySummaryReportFilters) => Promise<Workbook>;
}

type DayTotals = {
    absence: boolean;
    differenceHours: number;
    estimatedHours: number;
    extraHours: number;
    faceRecognitionSkipped: number;
    lateness: number;
    locationIncidence: number;
    locations: string[];
    underworked: boolean;
    wasLate: boolean;
    workedHours: number;
    workedHoursLog: string[];
};

const MAX_CATEGORIES_LIMIT = 100;

@Service()
export class ReportService extends BaseAdapter implements ReportServicePort {
    constructor(
        @PortsDI(TimeTrackingService) private readonly timeTrackingService: TimeTrackingService,
        @PortsDI(UsersServicePort) private readonly usersService: UsersServicePort,
        private readonly segmentationsService: SegmentationsService,
        private readonly orgChart: OrganizationChartsService,
        @PortsDI(FaceRecognitionServicePort) private readonly faceRecognitionService: FaceRecognitionServicePort,
        private readonly sitesService: SiteService,
        @PortsDI(TimeTrackingPoliciesPort) private readonly timeTrackingPoliciesService: TimeTrackingPoliciesPort,
        private readonly permissionService: PermissionsService,
        private readonly translationsPort: TranslationsPort,
        private readonly hourCategorizationService: TimeTrackingHourCategorizationService,
    ) {
        super();
    }

    public detailedReport = async (
        user: UserInterface,
        instance: Instance,
        reportFilters: TimeTrackingDaySummaryReportFilters,
    ): Promise<Workbook> => {
        const segmentationGroups = reportFilters.groupIds?.length
            ? await this.segmentationsService.getSegmentationGroups(instance.getId(), { ids: reportFilters.groupIds })
            : [];
        const segmentationGroupsKeys = segmentationGroups.map((group) => group.getName());
        if ((reportFilters.groupIds?.length ?? 0) !== segmentationGroups.length)
            throw new BadRequestError('Some segmentation groups do not exist', ErrorCodes.SEGMENTATION_GROUP_NOT_FOUND);
        const facialRecognitionEnabled = await this.faceRecognitionService.getInstanceRecognitionEnabled(instance.getId());

        const employees = await this.getUsers(user, instance, reportFilters);
        const enabledCategoryIds = reportFilters.hourCategoryIds?.length
            ? await this.getRelevantCategoryIdsForReport(
                  instance.getId(),
                  employees.map((e) => e.getId()),
                  reportFilters.fromDate,
                  reportFilters.toDate,
              )
            : new Set<number>();

        const hourCategories = await this.hourCategorizationService.listHourCategories(
            instance.getId(),
            { limit: MAX_CATEGORIES_LIMIT, offset: 0 },
            reportFilters.fromDate,
        );

        const categoryColumns = reportFilters.hourCategoryIds?.length
            ? hourCategories?.rows
                  .filter((category) => reportFilters.hourCategoryIds!.includes(category.id) && enabledCategoryIds.has(category.id))
                  .map((category) => category.name)
            : [];

        const columns = this.getDetailedReportColumns(reportFilters, segmentationGroupsKeys, facialRecognitionEnabled, instance, categoryColumns);
        const timeTrackingTranslations = this.translationsPort.translateModule(user.getLanguage(), Modules.TIME_TRACKING, [
            { key: TimeTrackingReportKeys.WORKSHEET_DETAIL_TITLE },
            { key: TimeTrackingReportKeys.TIME_PERIOD },
            { key: TimeTrackingReportKeys.TITLE },
            { key: TimeTrackingReportKeys.GEOLOCATION },
            { key: TimeTrackingReportKeys.TOTAL },
            { key: TimeTrackingReportKeys.AUTO_CLOSE_INCIDENCE_TEXT },
            { key: TimeTrackingReportKeys.CLOCK_IN_METHOD },
            { key: TimeTrackingReportKeys.CLOCK_OUT_METHOD },
            { key: TimeTrackingReportKeys.KIOSK_START },
            { key: TimeTrackingReportKeys.KIOSK_END },
            { key: TimeTrackingReportKeys.SCHEDULED_TIME_START },
            { key: TimeTrackingReportKeys.SCHEDULED_TIME_END },
            { key: TimeTrackingReportKeys.SITE_REMOTE_WORK },
            ...this.getTranslatableColumns(columns, segmentationGroupsKeys, instance),
            ...Object.values(WORKDAYS).map((day) => ({ key: `general.dayOfWeek.${day.toLowerCase()}` })),
        ]);
        const generalTranslations = this.translationsPort.translateModule(user.getLanguage(), Modules.TRANSLATION, [
            ...Object.values(WORKDAYS).map((day) => ({ key: `general.dayOfWeek.${day.toLowerCase()}` })),
        ]);
        const translations = {
            ...timeTrackingTranslations,
            ...generalTranslations,
        };
        const workbook = new Workbook();
        const worksheet = workbook.addWorksheet(translations[TimeTrackingReportKeys.WORKSHEET_DETAIL_TITLE]);
        this.addGeneralFormats(worksheet, columns, categoryColumns);
        this.addWorksheetTitleAndDates(
            worksheet,
            reportFilters.fromDate,
            reportFilters.toDate,
            translations,
            TimeTrackingReportKeys.WORKSHEET_DETAIL_TITLE,
            user.getLanguage(),
        );
        worksheet.views = [{ state: 'frozen', ySplit: 2 }];
        const titlesRow = worksheet.addRow(columns.map((col) => (translations[col] ? translations[col] : col)));
        titlesRow.font = { bold: true };
        titlesRow.fill = { fgColor: { argb: LIGHT_BLUE }, pattern: 'solid', type: 'pattern' };
        titlesRow.eachCell((cell) => {
            cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
        });
        titlesRow.eachCell((cell, colNumber) => {
            const columnKey = columns[colNumber - 1];
            if (categoryColumns?.includes(columnKey)) {
                cell.alignment = { ...cell.alignment, wrapText: false };
            }
        });

        const userSegmentationItems = segmentationGroups.length
            ? await this.segmentationsService.getSegmentationItemsByUsers(employees.map((user) => user.getId()))
            : {};
        for (const employee of employees) {
            const employeeTotals = {
                absences: 0,
                differenceHours: 0,
                estimatedHours: 0,
                extraHours: 0,
                facialRecognitionSkipped: 0,
                locationIncidence: 0,
                tardiness: 0,
                underworked: 0,
                workedHours: 0,
            };
            const groupsData = this.getGroupsData(segmentationGroups, userSegmentationItems[employee.getId()]);
            const daySummaries = await this.timeTrackingService.getOrCreateDaySummariesByUser(
                employee.id,
                instance,
                reportFilters.fromDate,
                reportFilters.toDate,
            );
            const siteIds = new Set<number>();
            daySummaries
                .flatMap((summary) => summary.timeTrackingEntries ?? [])
                .forEach((entry) => {
                    if (entry.siteId) siteIds.add(entry.siteId);
                });
            const sites = siteIds.size ? await this.sitesService.getByIds(Array.from(siteIds), instance.getId(), true) : [];
            const sitesById = getMapByGetter(sites, (site) => site.getId());
            const fieldProperties = instance.getFieldProperties();
            const profileData: Record<string, any> = {};
            reportFilters.userCustomFields?.forEach((field) => (profileData[fieldProperties[field].name] = employee.profileData[field]));

            const categorizedHoursMap = await this.hourCategorizationService.getApprovedCategorizedHoursForDaySummaries(
                instance.getId(),
                daySummaries,
            );
            const categoryTotals = new Map<string, number>();
            categoryColumns?.forEach((categoryName) => categoryTotals.set(categoryName, 0));

            daySummaries.forEach((summary) => {
                const hasAutoCloseEntry = summary.timeTrackingEntries?.some((e) => e.source === TimeTrackingEntrySource.AUTO_CLOSE) ?? false;
                if (hasAutoCloseEntry && !summary.incidences.includes(Incidence.AUTO_CLOSE)) {
                    summary.incidences.push(Incidence.AUTO_CLOSE);
                }
                const categorizedHours = categorizedHoursMap?.get(summary.id) ?? [];
                const dayTotals = this.addDayRows(
                    summary,
                    instance.getTimezone(),
                    worksheet,
                    translations,
                    employee,
                    groupsData,
                    sitesById,
                    profileData,
                    user.getLanguage(),
                    categorizedHours,
                    reportFilters.includeScheduledTime,
                    reportFilters.includeClockInOut,
                    reportFilters.includeAutoCloseIncidence,
                    reportFilters.includeKiosk,
                    categoryColumns,
                );
                categorizedHours.forEach((catHour) => {
                    const currentTotal = categoryTotals.get(catHour.hourCategoryName) ?? 0;
                    categoryTotals.set(catHour.hourCategoryName, currentTotal + catHour.hours);
                });
                employeeTotals.workedHours += dayTotals.workedHours;
                employeeTotals.estimatedHours += dayTotals.estimatedHours;
                employeeTotals.differenceHours += dayTotals.differenceHours;
                employeeTotals.extraHours += dayTotals.extraHours;
                employeeTotals.locationIncidence += dayTotals.locationIncidence;
                employeeTotals.absences += dayTotals.absence ? 1 : 0;
                employeeTotals.underworked += dayTotals.underworked ? 1 : 0;
                employeeTotals.facialRecognitionSkipped += dayTotals.faceRecognitionSkipped;
                employeeTotals.tardiness += dayTotals.lateness;
            });

            const categoryTotalsData: Record<string, number> = {};
            categoryColumns?.forEach((categoryName) => {
                categoryTotalsData[categoryName] = (categoryTotals.get(categoryName) ?? 0) / HOURS_IN_DAY;
            });

            const totalRow = worksheet.addRow({
                [TimeTrackingReportKeys.ABSCENCES]: employeeTotals.absences,
                [TimeTrackingReportKeys.DATE]: translations[TimeTrackingReportKeys.TOTAL],
                [TimeTrackingReportKeys.DIFF_HOURS]: employeeTotals.differenceHours,
                [TimeTrackingReportKeys.EMPLOYEE_INTERNAL_ID]: employee.employeeInternalId,
                [TimeTrackingReportKeys.EXTRA_HOURS]: employeeTotals.extraHours,
                [TimeTrackingReportKeys.FACIAL_RECOGNITION_SKIPPED]: employeeTotals.facialRecognitionSkipped,
                [TimeTrackingReportKeys.INCUMPLIMIENTO]: employeeTotals.underworked,
                [TimeTrackingReportKeys.LOCATION_INCIDENCE]: employeeTotals.locationIncidence,
                [TimeTrackingReportKeys.SCHEDULED_HOURS]: employeeTotals.estimatedHours,
                [TimeTrackingReportKeys.TARDINESS_HS]: employeeTotals.tardiness,
                [TimeTrackingReportKeys.WORKED_HOURS]: employeeTotals.workedHours,
                ...categoryTotalsData,
            });
            categoryColumns?.forEach((categoryName) => {
                const cell = totalRow.getCell(this.getColumnNumber(worksheet, categoryName));
                cell.numFmt = CELL_TIME_FORMAT;
            });
            if (employeeTotals.differenceHours < 0) {
                totalRow.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.DIFF_HOURS)).value = -1 * employeeTotals.differenceHours;
                totalRow.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.DIFF_HOURS)).numFmt = `- ${CELL_TIME_FORMAT}`;
            }
            totalRow.height = ROW_HEIGHT_DEFAULT;
            totalRow.font = { bold: true, color: { argb: WHITE } };
            totalRow.fill = { fgColor: { argb: HUMAND_BLUE }, pattern: 'solid', type: 'pattern' };
        }
        worksheet.eachRow((row, rowNumber) => {
            if (rowNumber !== 2) {
                row.height = ROW_HEIGHT_DEFAULT;
            }
        });
        return workbook;
    };

    public consolidatedReport = async (
        user: UserInterface,
        instance: Instance,
        reportFilters: TimeTrackingDaySummaryReportFilters,
    ): Promise<Workbook> => {
        const segmentationGroups = reportFilters.groupIds?.length
            ? await this.segmentationsService.getSegmentationGroups(instance.getId(), { ids: reportFilters.groupIds })
            : [];
        if ((reportFilters.groupIds?.length ?? 0) !== segmentationGroups.length)
            throw new BadRequestError('Some segmentation groups do not exist', ErrorCodes.SEGMENTATION_GROUP_NOT_FOUND);
        const segmentationGroupsKeys = segmentationGroups.map((group) => group.getName());

        const employees = await this.getUsers(user, instance, reportFilters);
        const enabledCategoryIds = reportFilters.hourCategoryIds?.length
            ? await this.getRelevantCategoryIdsForReport(
                  instance.getId(),
                  employees.map((e) => e.getId()),
                  reportFilters.fromDate,
                  reportFilters.toDate,
              )
            : new Set<number>();

        const hourCategories = await this.hourCategorizationService.listHourCategories(
            instance.getId(),
            {
                limit: MAX_CATEGORIES_LIMIT,
                offset: 0,
            },
            reportFilters.fromDate,
        );
        const categoryColumns = reportFilters.hourCategoryIds?.length
            ? hourCategories?.rows
                  .filter((category) => reportFilters.hourCategoryIds!.includes(category.id) && enabledCategoryIds.has(category.id))
                  .map((category) => category.name)
            : [];

        const columnsKeys = this.getConsolidatedReportColumns(reportFilters, segmentationGroupsKeys, instance);
        columnsKeys.push(...(categoryColumns ?? []));
        const translations = this.translationsPort.translateModule(user.getLanguage(), Modules.TIME_TRACKING, [
            { key: TimeTrackingReportKeys.WORKSHEET_CONSOLIDATED_TITLE },
            { key: TimeTrackingReportKeys.TIME_PERIOD },
            { key: TimeTrackingReportKeys.TITLE },
            ...this.getTranslatableColumns(columnsKeys, segmentationGroupsKeys, instance),
        ]);
        const workbook = new Workbook();
        const worksheet = workbook.addWorksheet(translations[TimeTrackingReportKeys.WORKSHEET_CONSOLIDATED_TITLE]);
        this.addGeneralFormats(worksheet, columnsKeys, categoryColumns);
        this.addWorksheetTitleAndDates(
            worksheet,
            reportFilters.fromDate,
            reportFilters.toDate,
            translations,
            TimeTrackingReportKeys.WORKSHEET_CONSOLIDATED_TITLE,
            user.getLanguage(),
        );
        worksheet.views = [{ state: 'frozen', ySplit: 2 }];
        const titlesRow = worksheet.addRow(columnsKeys.map((col) => (translations[col] ? translations[col] : col)));
        titlesRow.font = { bold: true };
        titlesRow.fill = { fgColor: { argb: LIGHT_BLUE }, pattern: 'solid', type: 'pattern' };
        titlesRow.eachCell((cell) => {
            cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
        });

        const userSegmentationItems = segmentationGroupsKeys.length
            ? await this.segmentationsService.getSegmentationItemsByUsers(employees.map((user) => user.getId()))
            : {};
        for (const employee of employees) {
            const employeeTotals = {
                absenceHours: 0,
                absences: 0,
                differenceHours: 0,
                estimatedHours: 0,
                extraHours: 0,
                facialRecognitionSkipped: 0,
                holidayHours: 0,
                restHours: 0,
                sundayHours: 0,
                tardiness: 0,
                underworked: 0,
                workedHours: 0,
            };
            const daySummaries = await this.timeTrackingService.getOrCreateDaySummariesByUser(
                employee.id,
                instance,
                reportFilters.fromDate,
                reportFilters.toDate,
                true,
            );

            const categorizedHoursMap = await this.hourCategorizationService.getApprovedCategorizedHoursForDaySummaries(
                instance.getId(),
                daySummaries,
            );

            const categoryTotals = new Map<string, number>();
            categoryColumns?.forEach((categoryName) => categoryTotals.set(categoryName, 0));

            daySummaries.forEach((summary) => {
                const dayTotals = this.calculateDayTotals(summary, instance.getTimezone());
                employeeTotals.workedHours += dayTotals.workedHours;
                employeeTotals.estimatedHours += dayTotals.estimatedHours;
                employeeTotals.differenceHours += dayTotals.differenceHours;
                employeeTotals.extraHours += dayTotals.extraHours;
                employeeTotals.facialRecognitionSkipped += dayTotals.faceRecognitionSkipped;
                employeeTotals.tardiness += dayTotals.wasLate ? INCIDENCE_PRESENT : NO_INCIDENCE;
                employeeTotals.absences += dayTotals.absence ? INCIDENCE_PRESENT : NO_INCIDENCE;
                employeeTotals.underworked += dayTotals.underworked ? INCIDENCE_PRESENT : NO_INCIDENCE;
                employeeTotals.holidayHours += summary.holidays?.length ? dayTotals.workedHours : 0;
                employeeTotals.sundayHours += summary.weekday === WORKDAYS.SUNDAY ? dayTotals.workedHours : 0;
                employeeTotals.absenceHours += summary.incidences.includes(Incidence.ABSENT) ? dayTotals.estimatedHours : 0;
                employeeTotals.restHours += summary.isWorkday ? 0 : dayTotals.workedHours;

                const categorizedHours = categorizedHoursMap?.get(summary.id) ?? [];
                categorizedHours.forEach((catHour) => {
                    const currentTotal = categoryTotals.get(catHour.hourCategoryName) ?? 0;
                    categoryTotals.set(catHour.hourCategoryName, currentTotal + catHour.hours);
                });
            });
            const categoryTotalsData: Record<string, number> = {};
            categoryColumns?.forEach((categoryName) => {
                categoryTotalsData[categoryName] = (categoryTotals.get(categoryName) ?? 0) / HOURS_IN_DAY;
            });
            const groupsData = this.getGroupsData(segmentationGroups, userSegmentationItems[employee.getId()]);
            const fieldProperties = instance.getFieldProperties();
            const profileData: Record<string, any> = {};
            reportFilters.userCustomFields?.forEach((field) => (profileData[fieldProperties[field].name] = employee.profileData[field]));
            const employeeRow = worksheet.addRow({
                [TimeTrackingReportKeys.ABSCENCES]: employeeTotals.absences,
                [TimeTrackingReportKeys.ABSCENCE_HOURS]: employeeTotals.absenceHours,
                [TimeTrackingReportKeys.BIRTHDATE]: employee.birthday,
                [TimeTrackingReportKeys.BOSS]: employee.boss?.getFullName(),
                [TimeTrackingReportKeys.DIFF_HOURS]: employeeTotals.differenceHours,
                [TimeTrackingReportKeys.EMAIL]: employee.email,
                [TimeTrackingReportKeys.EMPLOYEE_INTERNAL_ID]: employee.employeeInternalId,
                [TimeTrackingReportKeys.EXTRA_HOURS]: employeeTotals.extraHours,
                [TimeTrackingReportKeys.FACIAL_RECOGNITION_SKIPPED]: employeeTotals.facialRecognitionSkipped,
                [TimeTrackingReportKeys.HIRING_DATE]: employee.hiringDate,
                [TimeTrackingReportKeys.HOLIDAY_HOURS]: employeeTotals.holidayHours,
                [TimeTrackingReportKeys.INCUMPLIMIENTO]: employeeTotals.underworked,
                [TimeTrackingReportKeys.NAME]: `${employee.lastName}, ${employee.firstName}`,
                [TimeTrackingReportKeys.REST_HOURS]: employeeTotals.restHours,
                [TimeTrackingReportKeys.SCHEDULED_HOURS]: employeeTotals.estimatedHours,
                [TimeTrackingReportKeys.SUNDAY_HOURS]: employeeTotals.sundayHours,
                [TimeTrackingReportKeys.TARDINESS]: employeeTotals.tardiness,
                [TimeTrackingReportKeys.WORKED_HOURS]: employeeTotals.workedHours,
                ...groupsData,
                ...profileData,
                ...categoryTotalsData,
            });
            categoryColumns?.forEach((categoryName) => {
                const cell = employeeRow.getCell(this.getColumnNumber(worksheet, categoryName));
                cell.numFmt = CELL_TIME_FORMAT;
            });
            if (employeeTotals.differenceHours < 0) {
                employeeRow.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.DIFF_HOURS)).value = -1 * employeeTotals.differenceHours;
                employeeRow.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.DIFF_HOURS)).numFmt = `- ${CELL_TIME_FORMAT}`;
            }
        }
        worksheet.eachRow((row, rowNumber) => {
            if (rowNumber !== 2) {
                row.height = ROW_HEIGHT_DEFAULT;
            }
        });
        return workbook;
    };

    private addDayRows = (
        summary: TimeTrackingDaySummary,
        timezone: string,
        worksheet: Worksheet,
        translations: Record<string, string>,
        employee: User,
        groupData: Record<string, any>,
        sites: Map<number, Site>,
        profileData: Record<string, any>,
        lenguage: LanguagesValues,
        categorizedHours: TimeTrackingCategorizedHourWithCategoryData[],
        includeScheduledTime: boolean,
        includeClockInOut: boolean,
        includeAutoCloseIncidence: boolean,
        includeKiosk: boolean,
        categoryColumns?: string[],
    ) => {
        const timeOff: string[] = [];
        summary.timeOffRequests?.forEach((timeOffRequest) => timeOff.push(timeOffRequest.timeOffPolicyTypeName));
        const holidays: string[] = [];
        summary.holidays?.forEach((holiday) => holidays.push(holiday.holidayName));
        const dayTotals = this.calculateDayTotals(summary, timezone);
        const pairedEntries = PairedEntry.getPairedResults(summary.timeTrackingEntries).reverse();
        if (pairedEntries.length === 0) pairedEntries.push({});
        pairedEntries.forEach((pairedEntry, i) => {
            const comments = [pairedEntry.start?.comment, pairedEntry.end?.comment].filter(Boolean);
            // If at least a time off request that is not in hours is non_work, we should not compute incidences
            const isNonWorkTimeOffRequest =
                summary.timeOffRequests?.some(
                    (request) =>
                        request.activityType !== TIME_OFF_POLICY_TYPE_ACTIVITY_TYPES.NON_WORK &&
                        request.consumptionType === TIME_OFF_REQUEST_CONSUMPTION_TYPES.HOURS,
                ) ?? false;
            const locationIncidences = isNonWorkTimeOffRequest ? 0 : this.calculateLocationIncidences(pairedEntry, sites);
            let facialRecognitionSkipped = 0;
            if (pairedEntry.start?.facialRecognitionSkipped) facialRecognitionSkipped++;
            if (pairedEntry.end?.facialRecognitionSkipped) facialRecognitionSkipped++;
            const row = worksheet.addRow({
                [TimeTrackingReportKeys.ABSCENCES]: dayTotals.absence ? INCIDENCE_PRESENT : NO_INCIDENCE,
                [TimeTrackingReportKeys.BIRTHDATE]: moment(employee.birthday).format(I18n.getDateFormat(lenguage)),
                [TimeTrackingReportKeys.BOSS]: employee.boss?.getFullName(),
                [TimeTrackingReportKeys.COMMENTS]: comments.join('\n'),
                [TimeTrackingReportKeys.DATE]: moment(summary.dateString).format(I18n.getDateFormat(lenguage)),
                [TimeTrackingReportKeys.DIFF_HOURS]: dayTotals.differenceHours,
                [TimeTrackingReportKeys.EMAIL]: employee.email,
                [TimeTrackingReportKeys.EMPLOYEE_INTERNAL_ID]: employee.employeeInternalId,
                [TimeTrackingReportKeys.EXTRA_HOURS]: dayTotals.extraHours,
                ...this.getCategoryHoursForRow(categorizedHours, categoryColumns),
                [TimeTrackingReportKeys.FACIAL_RECOGNITION_SKIPPED]: facialRecognitionSkipped,
                [TimeTrackingReportKeys.GEOLOCATION_END]: pairedEntry.end?.location ? this.getGeolocationLink(pairedEntry.end.location) : PADDING,
                [TimeTrackingReportKeys.GEOLOCATION_START]: pairedEntry.start?.location
                    ? this.getGeolocationLink(pairedEntry.start.location)
                    : PADDING,
                [TimeTrackingReportKeys.HIRING_DATE]: moment(employee.hiringDate).format(I18n.getDateFormat(lenguage)),
                [TimeTrackingReportKeys.HOLIDAYS]: holidays.length ? holidays.join(', ') : PADDING,
                [TimeTrackingReportKeys.INCUMPLIMIENTO]: dayTotals.underworked ? INCIDENCE_PRESENT : NO_INCIDENCE,
                [TimeTrackingReportKeys.LOCATION_INCIDENCE]: locationIncidences,
                [TimeTrackingReportKeys.NAME]: `${employee.lastName}, ${employee.firstName}`,
                [TimeTrackingReportKeys.SCHEDULED_HOURS]: dayTotals.estimatedHours,
                [TimeTrackingReportKeys.SITE_END]: getSiteCellValue(pairedEntry.end, sites, translations[TimeTrackingReportKeys.SITE_REMOTE_WORK]),
                [TimeTrackingReportKeys.SITE_START]: getSiteCellValue(
                    pairedEntry.start,
                    sites,
                    translations[TimeTrackingReportKeys.SITE_REMOTE_WORK],
                ),
                [TimeTrackingReportKeys.TARDINESS_HS]: !i && dayTotals.lateness ? dayTotals.lateness : PADDING,
                [TimeTrackingReportKeys.TIME_OFF]: timeOff.length ? timeOff.join(', ') : PADDING,
                [TimeTrackingReportKeys.TIME_SLOTS_END]: pairedEntry.end ? moment(pairedEntry.end.time).tz(timezone).format(TIME_FORMAT) : PADDING,
                [TimeTrackingReportKeys.TIME_SLOTS_START]: pairedEntry.start
                    ? moment(pairedEntry.start.time).tz(timezone).format(TIME_FORMAT)
                    : PADDING,
                [TimeTrackingReportKeys.TIME_SLOT_HOURS]:
                    pairedEntry.start && pairedEntry.end
                        ? moment(pairedEntry.end.time).diff(moment(pairedEntry.start.time), 'hours', true) / HOURS_IN_DAY
                        : PADDING,
                [TimeTrackingReportKeys.WEEKDAY]: translations[`general.dayOfWeek.${summary.weekday.toLowerCase()}`],
                [TimeTrackingReportKeys.WORKED_HOURS]: dayTotals.workedHours,
                ...groupData,
                ...profileData,
            });
            if (includeAutoCloseIncidence && summary.incidences.includes(Incidence.AUTO_CLOSE)) {
                row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.AUTO_CLOSE_INCIDENCE)).value =
                    translations[TimeTrackingReportKeys.AUTO_CLOSE_INCIDENCE_TEXT];
            }
            if (includeClockInOut) {
                row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.CLOCK_IN_METHOD)).value = this.getClockMethod(
                    pairedEntry.start?.source,
                );

                row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.CLOCK_OUT_METHOD)).value = this.getClockMethod(
                    pairedEntry.end?.source,
                );
            }
            if (includeKiosk) {
                const kioskStart = summary.timeTrackingEntries.find((entry) => entry.kioskId === pairedEntry.start?.kioskId)?.kiosk;
                const kioskEnd = summary.timeTrackingEntries.find((entry) => entry.kioskId === pairedEntry.end?.kioskId)?.kiosk;
                row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.KIOSK_START)).value = kioskStart?.name;
                row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.KIOSK_END)).value = kioskEnd?.name;
            }
            if (includeScheduledTime) {
                const timeSlots = summary.timeSlots ?? [];
                // If there are more paired entries than time slots, repeat the last time slot in the row
                const timeSlot = timeSlots[i] ?? timeSlots[timeSlots.length - 1];
                row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.SCHEDULED_TIME_START)).value = timeSlot?.startTime;
                row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.SCHEDULED_TIME_END)).value = timeSlot?.endTime;
            }
            if (pairedEntry.start?.location) {
                const linkCell = row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.GEOLOCATION_START));
                linkCell.font = { color: { argb: '0000FF' }, underline: true };
                linkCell.value = {
                    hyperlink: this.getGeolocationLink(pairedEntry.start.location),
                    text: translations[TimeTrackingReportKeys.GEOLOCATION],
                };
            }
            if (pairedEntry.end?.location) {
                const linkCell = row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.GEOLOCATION_END));
                linkCell.font = { color: { argb: '0000FF' }, underline: true };
                linkCell.value = {
                    hyperlink: this.getGeolocationLink(pairedEntry.end.location),
                    text: translations[TimeTrackingReportKeys.GEOLOCATION],
                };
            }
            TIME_COLUMNS_DETAILED.forEach((col) => (row.getCell(this.getColumnNumber(worksheet, col)).numFmt = CELL_TIME_FORMAT));
            if (dayTotals.differenceHours < 0) {
                row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.DIFF_HOURS)).value = -1 * dayTotals.differenceHours;
                row.getCell(this.getColumnNumber(worksheet, TimeTrackingReportKeys.DIFF_HOURS)).numFmt = `- ${CELL_TIME_FORMAT}`;
            }
            categoryColumns?.forEach((categoryName) => {
                const cell = row.getCell(this.getColumnNumber(worksheet, categoryName));
                cell.numFmt = CELL_TIME_FORMAT;
            });
            row.height = ROW_HEIGHT_DEFAULT;
        });
        return dayTotals;
    };

    private getCategoryHoursForRow(
        categorizedHours: TimeTrackingCategorizedHourWithCategoryData[],
        categoryColumns?: string[],
    ): Record<string, number> {
        const categoryMap = new Map<string, number>();
        categorizedHours.forEach((catHour) => {
            categoryMap.set(catHour.hourCategoryName, catHour.hours);
        });
        const result: Record<string, number> = {};
        categoryColumns?.forEach((categoryName) => {
            result[categoryName] = (categoryMap.get(categoryName) ?? 0) / HOURS_IN_DAY;
        });

        return result;
    }

    private getTranslatableColumns(columnsKeys: string[], segmentationGroupsKeys: string[], instance: Instance) {
        const customFieldNames = new Set();
        Object.values(instance.getFieldProperties()).forEach((field) => customFieldNames.add(field.name));
        return columnsKeys.filter((col) => !segmentationGroupsKeys.includes(col) && !customFieldNames.has(col)).map((col) => ({ key: col }));
    }

    private calculateLocationIncidences(pairedEntry: PairedEntry, sites: Map<number, Site>) {
        let locationIncidences = 0;
        if (pairedEntry.start && hasLocationIncidence(pairedEntry.start, sites)) locationIncidences++;
        if (pairedEntry.end && hasLocationIncidence(pairedEntry.end, sites)) locationIncidences++;
        return locationIncidences;
    }

    private getGroupsData(segmentationGroups: SegmentationGroup[], segmentationItems: SegmentationItem[]) {
        const returnData: Record<string, any> = {};
        const segmentationItemsByGroupId = _.groupBy(segmentationItems, (item) => item.getSegmentationGroupId());
        segmentationGroups.forEach((group) => {
            if (group.getId()) {
                const segmentationItems = segmentationItemsByGroupId[group.getId()] ?? [];
                returnData[group.getName()] = _.uniq(segmentationItems.map((item) => item.getName())).join(', ');
            }
        });
        return returnData;
    }

    private getDetailedReportColumns(
        reportFilters: TimeTrackingDaySummaryReportFilters,
        segmentationGroupKeys: string[],
        facialRecognitionEnabled: boolean,
        instance: Instance,
        categoryColumns: string[],
    ) {
        const columnsKeys: string[] = this.getUserColumns(reportFilters, segmentationGroupKeys, instance);

        const extraHoursIndex = DETAILED_COLUMNS_ORDER.indexOf(TimeTrackingReportKeys.EXTRA_HOURS);

        const columnsBeforeCategoryColumns = DETAILED_COLUMNS_ORDER.slice(0, extraHoursIndex + 1);

        const columnsAfterCategoryColumns = DETAILED_COLUMNS_ORDER.slice(extraHoursIndex + 1);

        columnsKeys.push(...columnsBeforeCategoryColumns, ...(categoryColumns ?? []), ...columnsAfterCategoryColumns);

        // Scheduled time is after WEEKDAY column
        if (reportFilters.includeScheduledTime) {
            this.insertAfter(columnsKeys, TimeTrackingReportKeys.WEEKDAY, [
                TimeTrackingReportKeys.SCHEDULED_TIME_START,
                TimeTrackingReportKeys.SCHEDULED_TIME_END,
            ]);
        }

        // Clock in/out is after DIFF_HOURS column
        if (reportFilters.includeClockInOut) {
            this.insertAfter(columnsKeys, TimeTrackingReportKeys.DIFF_HOURS, [
                TimeTrackingReportKeys.CLOCK_IN_METHOD,
                TimeTrackingReportKeys.CLOCK_OUT_METHOD,
            ]);
        }

        // Auto close incidence is after COMMENTS column
        if (reportFilters.includeAutoCloseIncidence) {
            this.insertAfter(columnsKeys, TimeTrackingReportKeys.COMMENTS, [TimeTrackingReportKeys.AUTO_CLOSE_INCIDENCE]);
        }

        // Kiosk is after LOCATION_INCIDENCE column
        if (reportFilters.includeKiosk) {
            this.insertAfter(columnsKeys, TimeTrackingReportKeys.LOCATION_INCIDENCE, [
                TimeTrackingReportKeys.KIOSK_START,
                TimeTrackingReportKeys.KIOSK_END,
            ]);
        }

        if (facialRecognitionEnabled) {
            columnsKeys.push(TimeTrackingReportKeys.FACIAL_RECOGNITION_SKIPPED);
        }

        return columnsKeys;
    }

    private getConsolidatedReportColumns(reportFilters: TimeTrackingDaySummaryReportFilters, segmentationGroupKeys: string[], instance: Instance) {
        const columnsKeys: string[] = this.getUserColumns(reportFilters, segmentationGroupKeys, instance);
        columnsKeys.push(...CONSOLIDATED_COLUMNS_ORDER);
        return columnsKeys;
    }

    private getUserColumns(reportFilters: TimeTrackingDaySummaryReportFilters, segmentationGroupKeys: string[], instance: Instance) {
        const columnsKeys: string[] = [];
        if (reportFilters.userFields.includes(StaticUserFields.EMPLOYEE_INTERNAL_ID)) {
            columnsKeys.push(TimeTrackingReportKeys.EMPLOYEE_INTERNAL_ID);
        }
        if (reportFilters.userFields.includes(StaticUserFields.COMPLETE_NAME)) {
            columnsKeys.push(TimeTrackingReportKeys.NAME);
        }
        if (reportFilters.userFields.includes(StaticUserFields.EMAIL)) {
            columnsKeys.push(TimeTrackingReportKeys.EMAIL);
        }
        if (reportFilters.userFields.includes(StaticUserFields.DIRECT_BOSS)) {
            columnsKeys.push(TimeTrackingReportKeys.BOSS);
        }
        if (reportFilters.userFields.includes(StaticUserFields.BIRTHDATE)) {
            columnsKeys.push(TimeTrackingReportKeys.BIRTHDATE);
        }
        if (reportFilters.userFields.includes(StaticUserFields.HIRING_DATE)) {
            columnsKeys.push(TimeTrackingReportKeys.HIRING_DATE);
        }
        if (reportFilters.userCustomFields?.length) {
            const profileFields = instance.getFieldProperties();
            columnsKeys.push(...reportFilters.userCustomFields.map((fieldKey) => profileFields[fieldKey].name));
        }
        columnsKeys.push(...segmentationGroupKeys);
        return columnsKeys;
    }

    private calculateDayTotals(summary: TimeTrackingDaySummary, timezone: string) {
        const now = moment().tz(timezone);
        const dayTotals: DayTotals = {
            absence: summary.incidences.includes(Incidence.ABSENT) && now.isAfter(summary.dayChange),
            differenceHours: 0,
            estimatedHours: summary.estimatedHours / HOURS_IN_DAY,
            extraHours: 0,
            faceRecognitionSkipped: summary.incidences.filter((incidence) => incidence === Incidence.FACIAL_RECOGNITION_SKIPPED).length,
            lateness: summary.getLatenessInMinutes(timezone) / MINUTES_IN_HOUR / HOURS_IN_DAY,
            locationIncidence: 0,
            locations: [],
            underworked: summary.incidences.includes(Incidence.UNDERWORKED) && now.isAfter(summary.dayChange),
            wasLate: summary.incidences.includes(Incidence.LATE),
            workedHours: summary.timeTrackingEntries.length ? 0 : summary.workedHours,
            workedHoursLog: [],
        };
        PairedEntry.getPairedResults(summary.timeTrackingEntries).forEach((pairedEntry) => {
            if (!pairedEntry.start && !pairedEntry.end) {
                return;
            }
            if (pairedEntry.end && pairedEntry.start) {
                dayTotals.workedHours += moment(pairedEntry.end.time).diff(moment(pairedEntry.start.time), 'hours', true);
            }
            const startTime = pairedEntry.start ? moment(pairedEntry.start.time).tz(timezone).format(TIME_FORMAT) : '';
            const endTime = pairedEntry.end ? moment(pairedEntry.end.time).tz(timezone).format(TIME_FORMAT) : '';
            //unshift because paired results are sorted in descending order
            dayTotals.workedHoursLog.unshift(`${startTime} - ${endTime}`);
            if (this.locationsAreSame(pairedEntry) && pairedEntry.start?.location) {
                dayTotals.locations.unshift(this.getGeolocationLink(pairedEntry.start?.location));
            } else {
                if (pairedEntry.end?.location) dayTotals.locations.unshift(this.getGeolocationLink(pairedEntry.end.location));
                if (pairedEntry.start?.location) dayTotals.locations.unshift(this.getGeolocationLink(pairedEntry.start.location));
            }
        });
        dayTotals.workedHours /= HOURS_IN_DAY;
        dayTotals.differenceHours = dayTotals.workedHours - dayTotals.estimatedHours;
        dayTotals.extraHours = summary.estimatedHours && dayTotals.differenceHours > 0 ? dayTotals.differenceHours : 0;
        dayTotals.locationIncidence = summary.incidences.filter((incidence) => incidence === Incidence.LOCATION_INCIDENCE).length;
        return dayTotals;
    }

    private locationsAreSame(pairedEntry: PairedEntry) {
        return (
            pairedEntry.start?.location &&
            pairedEntry.end?.location &&
            pairedEntry.start.location.latitude === pairedEntry.end.location.latitude &&
            pairedEntry.start.location.longitude === pairedEntry.end.location.longitude
        );
    }

    private getGeolocationLink(location: { latitude: number; longitude: number }): string {
        return `${GEO_URL}?q=${location.latitude},${location.longitude}`;
    }

    private addGeneralFormats(worksheet: Worksheet, columns: string[], categoryColumns: string[]) {
        worksheet.properties.defaultColWidth = COLUMN_WIDTH_DEFAULT;
        worksheet.properties.defaultRowHeight = ROW_HEIGHT_DEFAULT;
        worksheet.properties.showGridLines = false;
        for (let i = 1; i <= columns.length; i++) {
            const column = worksheet.getColumn(i);
            column.width = COLUMN_WIDTH_DEFAULT;
            column.key = columns[i - 1];
            column.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
            if (LEFT_ALIGNED.includes(column.key as TimeTrackingReportKeys)) {
                column.alignment = { ...column.alignment, horizontal: 'left' };
            }
            if (TIME_COLUMNS_CONSOLIDATED.includes(column.key as TimeTrackingReportKeys)) {
                column.numFmt = CELL_TIME_FORMAT;
            }
            if (categoryColumns?.includes(column.key as string)) {
                column.alignment = { ...column.alignment, wrapText: false };
                column.width = 15;
            }
        }
    }

    private getColumnNumber(worksheet: Worksheet, colName: string) {
        return worksheet.getColumn(colName).number;
    }

    private addWorksheetTitleAndDates(
        worksheet: Worksheet,
        fromDate: string,
        toDate: string,
        translations: Record<string, string>,
        type: string,
        language: LanguagesValues,
    ) {
        const title = `${translations[type as TimeTrackingReportKeys]}`;
        const timePeriodLabel = translations[TimeTrackingReportKeys.TIME_PERIOD];
        const formattedPeriod = `${moment(fromDate, ISO_DATE).format(I18n.getDateFormat(language))} - ${moment(toDate, ISO_DATE).format(
            I18n.getDateFormat(language),
        )}`;

        const infoRow = worksheet.addRow([]);

        worksheet.mergeCells(`A${infoRow.number}:C${infoRow.number}`);
        infoRow.getCell(1).value = title;
        infoRow.getCell(1).font = { bold: true, size: 15 };
        infoRow.getCell(1).alignment = { horizontal: 'left', wrapText: false };

        infoRow.getCell(4).value = timePeriodLabel;
        infoRow.getCell(4).font = { bold: true, size: 15 };
        infoRow.getCell(4).alignment = { horizontal: 'center', wrapText: false };

        infoRow.getCell(5).value = formattedPeriod;
        infoRow.getCell(5).font = { size: 15 };
        infoRow.getCell(5).alignment = { horizontal: 'left', vertical: 'middle', wrapText: false };
    }

    private async getUsers(user: UserInterface, instance: Instance, reportFilters: TimeTrackingDaySummaryReportFilters): Promise<User[]> {
        const hasManageCapability = this.permissionService.hasAnyCapability(user, [CapabilityName.MANAGE_TIME_TRACKING]);
        let userIds = await this.usersService.getUserIds(
            user,
            {
                departmentIds: reportFilters.departmentIds,
                excludeDepartmentIds: reportFilters.excludeDepartmentIds,
                ids: reportFilters.userIds,
                search: reportFilters.search,
                segmentationItemIds: reportFilters.itemIds?.join(','),
            },
            hasManageCapability,
        );
        if (!userIds.length) return [];

        if (reportFilters.siteIds?.length || reportFilters.excludeSiteIds?.length) {
            userIds = await this.timeTrackingService.filterUsersBySites(
                user.getInstanceId(),
                userIds,
                reportFilters.fromDate,
                reportFilters.toDate,
                reportFilters.siteIds,
                reportFilters.excludeSiteIds,
            );
        }
        if (!userIds.length) return [];

        // From the filtered users, we retrieve only those who have an active policy.
        if (userIds.length) {
            userIds = await this.timeTrackingPoliciesService.getUserIdsWithPolicies(instance.getId(), userIds);
        }

        userIds = await this.timeTrackingService.searchUserIdsByDaySummaries({
            fromDate: reportFilters.fromDate,
            hasHolidays: reportFilters.hasHolidays,
            hasTimeOff: reportFilters.hasTimeOff,
            incidences: reportFilters.incidences,
            instanceId: user.getInstanceId(),
            itemIds: reportFilters.itemIds,
            status: reportFilters.status,
            timezone: instance.getTimezone(),
            toDate: reportFilters.toDate,
            userIds,
        });
        if (!userIds.length) return [];

        const sortingOptions = [['lastName', 'asc'] as OrderItem, ['firstName', 'asc'] as OrderItem];
        const { rows: employees } = await this.usersService.getUsers(
            user,
            { ids: userIds },
            { sortingOptions },
            { excludeLoggedUser: false, type: UserListType.FULL },
        );
        const bossMap = await this.orgChart.getBossesOfUsers(userIds, user.getInstanceId());
        employees.forEach((employee) => {
            employee.setInstanceId(user.getInstanceId());
            if (bossMap[employee.getId()]?.length) {
                employee.boss = bossMap[employee.getId()][0];
            }
        });
        return employees;
    }

    private getClockMethod(source?: TimeTrackingEntrySource): TimeTrackingEntrySource | 'SYSTEM' | undefined {
        if (!source) return undefined;
        return SYSTEM_SOURCES.includes(source) ? 'SYSTEM' : source;
    }

    private insertAfter(columns: string[], afterKey: string, newColumns: string[]) {
        const index = columns.indexOf(afterKey);
        if (index === -1) {
            columns.push(...newColumns);
            return;
        }
        columns.splice(index + 1, 0, ...newColumns);
    }

    private async getRelevantCategoryIdsForReport(instanceId: number, userIds: number[], fromDate: string, toDate: string): Promise<Set<number>> {
        if (userIds.length === 0) return new Set();

        const [policyCategoryIds, dataCategoryIds] = await allPromises([
            () => this.getPolicyCategoryIds(instanceId, userIds),
            () => this.hourCategorizationService.getDistinctApprovedCategoryIds(instanceId, userIds, fromDate, toDate),
        ]);

        return new Set([...policyCategoryIds, ...dataCategoryIds]);
    }

    private async getPolicyCategoryIds(instanceId: number, userIds: number[]): Promise<Set<number>> {
        const policyAssociations = await this.timeTrackingPoliciesService.listActivePolicyAssociations(instanceId, userIds);
        const policyIds = [...new Set(policyAssociations.filter((a) => a.policy).map((a) => a.policy!.id))];
        return await this.hourCategorizationService.getEnabledCategoryIdsForPolicies(policyIds, instanceId);
    }
}
