import { allPromises, BadRequestError, BaseAdapter, ErrorCodes, LoggerPort, NotFoundError } from '@humand-packages/common';
import { DateTime } from 'luxon';
import { Service } from 'typedi';
import { PortsDI } from '../../../../../config/portsDI';
import { Instance } from '../../../../instances/business/models/instance';
import { InstancesService } from '../../../../instances/business/services/instancesService';
import { TimeTrackingCategorizedHourStatus } from '../../../infrastructure/constants/timeTracking/categorizedHourStatus';
import { TimeTrackingCategorizedHourRepositoryPort } from '../../../infrastructure/ports/timeTracking/timeTrackingCategorizedHourRepositoryPort';
import { TimeTrackingHourCategorizationRepositoryPort } from '../../../infrastructure/ports/timeTracking/timeTrackingHourCategorizationRepositoryPort';
import { PolicyHourCategoriesChangePayload } from '../../constants/categorizedHoursActionTypes';
import { HourCategory } from '../../models/hourCategorization';
import { RecalculatedCategorizedHour } from '../../models/timeTracking/recalculateCategorizedHoursResult';
import {
    CategorizedHourDataUpdate,
    TimeTrackingCategorizedHourForCreation,
    TimeTrackingCategorizedHourWithCategoryData,
} from '../../models/timeTracking/timeTrackingCategorizedHour';
import { TimeTrackingCategorizedHourApprovalStep } from '../../models/timeTracking/timeTrackingCategorizedHourApprovalStep';
import { TimeTrackingHourCategoryWithAssociationData } from '../../models/TimeTrackingPolicyHourCategory';
import TimeTrackingNotificationsPort from '../../ports/timeTrackingNotificationsPort';
import { TimeTrackingPoliciesPort } from '../../ports/timeTrackingPoliciesPort';
import { TimeTrackingHourCategorizationService } from '../hourCategorizationService';
import TimeTrackingService from '../timeTracking';
import { TimeTrackingCategorizedHourApprovalService } from './timeTrackingCategorizedHourApprovalService';

@Service()
export class TimeTrackingCategorizedHourService extends BaseAdapter {
    constructor(
        @PortsDI(TimeTrackingHourCategorizationRepositoryPort)
        private readonly timeTrackingHourCategorizationRepository: TimeTrackingHourCategorizationRepositoryPort,
        @PortsDI(TimeTrackingCategorizedHourRepositoryPort)
        private readonly timeTrackingCategorizedHourRepository: TimeTrackingCategorizedHourRepositoryPort,
        private readonly instanceService: InstancesService,
        @PortsDI(TimeTrackingService)
        private readonly timeTrackingService: TimeTrackingService,
        private readonly timeTrackingHourCategorizationService: TimeTrackingHourCategorizationService,
        @PortsDI(TimeTrackingPoliciesPort)
        private readonly timeTrackingPoliciesService: TimeTrackingPoliciesPort,
        private readonly categorizedHourApprovalService: TimeTrackingCategorizedHourApprovalService,
        @PortsDI(TimeTrackingNotificationsPort)
        private readonly timeTrackingNotificationsPort: TimeTrackingNotificationsPort,
        private readonly logger: LoggerPort,
    ) {
        super();
    }

    /* Handles the policy assignment changes for a given instanceId, policyId, assignedUserIds, unassignedUserIds, and date.
     * It will delete the categorized hours without approval from the previous policies for the users that are now assigned to the policy.
     * It will delete the categorized hours without approval for the users that are no longer assigned to the policy.
     * It will create the categorized hours without approval for the users that are assigned to the policy.
     */
    public async handlePolicyAsignationChanges({
        instanceId,
        policyId,
        assignedUserIds,
        unassignedUserIds,
        date,
    }: {
        instanceId: number;
        policyId: number;
        assignedUserIds: number[];
        unassignedUserIds: number[];
        date: string;
    }): Promise<void> {
        if (assignedUserIds.length === 0 && unassignedUserIds.length === 0) return;
        const policyHourCategories = await this.timeTrackingHourCategorizationRepository.getPolicyHourCategoriesForPolicyIds([policyId], instanceId);
        if (policyHourCategories.length === 0) return;
        const instance = await this.instanceService.findByPk(instanceId);
        const currentDate = DateTime.now().setZone(instance.getTimezone()).startOf('day').toISODate()!;
        let stepsToNotify: TimeTrackingCategorizedHourApprovalStep[] = [];
        await this.timeTrackingCategorizedHourRepository.getTransaction(async () => {
            await this.deleteCategorizedHoursWithoutApprovalForUsersInOldPolicies(instance, unassignedUserIds, date);
            await this.deleteCategorizedHoursWithoutApprovalForUsers(instance, unassignedUserIds, policyHourCategories, { dateTo: date });
            await this.createCategorizedHoursWithoutApprovalForUsers(instance, assignedUserIds, policyHourCategories, { dateTo: date });
            stepsToNotify = await this.createCategorizedHoursWithApprovalForAssignedUsers(
                instance,
                assignedUserIds,
                policyHourCategories,
                currentDate,
            );
        });
        await this.dispatchStepPendingNotifications(instanceId, stepsToNotify);
    }

    /* Handles the policy hour categories changes for a given instanceId, policyId, userIds, removedHourCategoryIds, and date.
     * It will delete the categorized hours without approval for the users that are associated to the removed hour categories.
     * It will create the categorized hours without approval for the users that are associated to the new hour categories.
     */
    public async handlePolicyHourCategoriesChange({
        instanceId,
        policyId,
        userIds,
        removedHourCategoryIds,
        date,
    }: PolicyHourCategoriesChangePayload): Promise<void> {
        if (userIds.length === 0) return;
        const instance = await this.instanceService.findByPk(instanceId);
        const effectiveTo = DateTime.now().setZone(instance.getTimezone()).startOf('day').toISODate()!;
        const policyHourCategories = await this.timeTrackingHourCategorizationRepository.getPolicyHourCategoriesForPolicyIds([policyId], instanceId);

        if (removedHourCategoryIds.length === 0 && policyHourCategories.length === 0) return;

        await this.timeTrackingCategorizedHourRepository.getTransaction(async () => {
            if (removedHourCategoryIds.length > 0) {
                for (const hourCategoryId of removedHourCategoryIds) {
                    const category = await this.timeTrackingHourCategorizationRepository.getHourCategory(instanceId, hourCategoryId);
                    await this.deleteCategorizedHours(instanceId, userIds, hourCategoryId, category.effectiveFrom, effectiveTo);
                }
            }
            if (policyHourCategories.length === 0) return;
            await this.deleteCategorizedHoursWithoutApprovalForUsers(instance, userIds, policyHourCategories, { dateTo: date });
            await this.createCategorizedHoursWithoutApprovalForUsers(instance, userIds, policyHourCategories, { dateTo: date });
        });
    }

    private async deleteCategorizedHoursWithoutApprovalForUsersInOldPolicies(instance: Instance, userIds: number[], date: string): Promise<void> {
        const oldPolicyAssociations = await this.timeTrackingPoliciesService.listPolicyAssociationsEndedAt(instance.getId(), date, userIds);

        if (oldPolicyAssociations.length === 0) return;

        const oldPolicyForUsers: Map<number, number[]> = new Map();
        oldPolicyAssociations.forEach((policyAssociation) => {
            if (policyAssociation.policy?.id) {
                const userIds = oldPolicyForUsers.get(policyAssociation.policy.id) ?? [];
                userIds.push(policyAssociation.userId);
                oldPolicyForUsers.set(policyAssociation.policy.id, userIds);
            }
        });

        const oldPolicyIds = Array.from(oldPolicyForUsers.keys());
        const oldUnassignedPolicyHourCategories = await this.timeTrackingHourCategorizationRepository.getPolicyHourCategoriesForPolicyIds(
            oldPolicyIds,
            instance.getId(),
        );
        if (oldUnassignedPolicyHourCategories.length === 0) return;
        const oldUnassignedPolicyHourCategoriesByPolicyId: Map<number, TimeTrackingHourCategoryWithAssociationData[]> = new Map();
        for (const policyHourCategory of oldUnassignedPolicyHourCategories) {
            const policyId = policyHourCategory.policyId;
            const policyHourCategories = oldUnassignedPolicyHourCategoriesByPolicyId.get(policyId) ?? [];
            policyHourCategories.push(policyHourCategory);
            oldUnassignedPolicyHourCategoriesByPolicyId.set(policyId, policyHourCategories);
        }
        for (const [innerPolicyId, innerOldUnassignedPolicyHourCategories] of oldUnassignedPolicyHourCategoriesByPolicyId) {
            const userIds = oldPolicyForUsers.get(innerPolicyId) ?? [];
            await this.deleteCategorizedHoursWithoutApprovalForUsers(instance, userIds, innerOldUnassignedPolicyHourCategories, { dateTo: date });
        }
    }

    private async deleteCategorizedHoursWithoutApprovalForUsers(
        instance: Instance,
        userIds: number[],
        policyHourCategories: TimeTrackingHourCategoryWithAssociationData[],
        dateRange: { dateFrom?: string; dateTo: string },
    ) {
        if (userIds.length === 0) return;
        const effectiveWithoutApprovalPolicyHourCategories = policyHourCategories.filter(
            (policyHourCategory) =>
                this.isHourCategoryEffective(policyHourCategory.category, dateRange.dateTo, dateRange.dateFrom) &&
                !policyHourCategory.requiresApproval,
        );
        if (effectiveWithoutApprovalPolicyHourCategories.length === 0) return;
        const deletedCategorizedHourPromises = effectiveWithoutApprovalPolicyHourCategories.map(
            (policyHourCategory) => async () =>
                await this.deleteCategorizedHours(
                    instance.getId(),
                    userIds,
                    policyHourCategory.category.id,
                    dateRange.dateFrom ?? policyHourCategory.category.effectiveFrom,
                    dateRange.dateTo,
                ),
        );
        await allPromises(deletedCategorizedHourPromises);
    }

    private async deleteCategorizedHours(
        instanceId: number,
        userIds: number[],
        hourCategoryId: number,
        dateFrom: string,
        dateTo: string,
    ): Promise<void> {
        await this.timeTrackingCategorizedHourRepository.bulkDeleteCategorizedHours(
            instanceId,
            userIds,
            hourCategoryId,
            dateFrom,
            dateTo,
            TimeTrackingCategorizedHourStatus.AUTO_APPROVED,
        );
    }

    private isHourCategoryEffective(hourCategory: HourCategory, dateTo: string, dateFrom?: string): boolean {
        const dateFromDt = dateFrom ? DateTime.fromISO(dateFrom).startOf('day') : null;
        const dateToDt = DateTime.fromISO(dateTo).startOf('day');
        const categoryEffectiveFrom = DateTime.fromISO(hourCategory.effectiveFrom).startOf('day');
        const categoryEffectiveTo = hourCategory.effectiveTo ? DateTime.fromISO(hourCategory.effectiveTo).startOf('day') : null;
        return categoryEffectiveFrom <= dateToDt && (!dateFromDt || !categoryEffectiveTo || categoryEffectiveTo >= dateFromDt);
    }

    /** Returns true if the given date falls within both the requested window and the category's own effective period. */
    private isDayWithinCategoryEffectiveWindow(dateString: string, category: HourCategory, dateFrom: string, dateTo: string): boolean {
        const rangeStart =
            DateTime.fromISO(dateFrom).startOf('day') > DateTime.fromISO(category.effectiveFrom).startOf('day') ? dateFrom : category.effectiveFrom;
        const rangeEnd =
            category.effectiveTo && DateTime.fromISO(category.effectiveTo).startOf('day') < DateTime.fromISO(dateTo).startOf('day')
                ? category.effectiveTo
                : dateTo;
        return this.isDateInRange(dateString, rangeStart, rangeEnd);
    }

    private isDateInRange(dateString: string, rangeStart: string, rangeEnd: string): boolean {
        const date = DateTime.fromISO(dateString).startOf('day');
        const start = DateTime.fromISO(rangeStart).startOf('day');
        const end = DateTime.fromISO(rangeEnd).startOf('day');
        return date >= start && date <= end;
    }

    private async createCategorizedHoursWithoutApprovalForUsers(
        instance: Instance,
        userIds: number[],
        policyHourCategories: TimeTrackingHourCategoryWithAssociationData[],
        dateRange: { dateFrom?: string; dateTo: string },
    ): Promise<void> {
        if (userIds.length === 0) {
            return;
        }
        const effectiveWithOutApprovalPolicyHourCategories = policyHourCategories.filter(
            (policyHourCategory) =>
                this.isHourCategoryEffective(policyHourCategory.category, dateRange.dateTo, dateRange.dateFrom) &&
                !policyHourCategory.requiresApproval,
        );
        if (effectiveWithOutApprovalPolicyHourCategories.length === 0) {
            return;
        }
        const minEffectiveFromDate = this.getMinEffectiveFromDate(effectiveWithOutApprovalPolicyHourCategories, instance);
        if (!minEffectiveFromDate) {
            return;
        }

        const userDaySummaries = await allPromises(
            userIds.map((userId) => async () => {
                return await this.timeTrackingService.getOrCreateDaySummariesByUser(userId, instance, minEffectiveFromDate, dateRange.dateTo);
            }),
        );

        const daySummaries = userDaySummaries.flat();
        if (daySummaries.length === 0) {
            return;
        }

        const categorizedHoursToCreate: TimeTrackingCategorizedHourForCreation[] = [];

        for (const policyHourCategory of effectiveWithOutApprovalPolicyHourCategories) {
            for (const daySummary of daySummaries) {
                if (
                    !this.isDayWithinCategoryEffectiveWindow(
                        daySummary.dateString,
                        policyHourCategory.category,
                        dateRange.dateFrom ?? policyHourCategory.category.effectiveFrom,
                        dateRange.dateTo,
                    )
                )
                    continue;
                const hours = this.timeTrackingHourCategorizationService.calculateCategoryHours(
                    policyHourCategory.category,
                    daySummary,
                    instance.getTimezone(),
                );
                if (hours <= 0) continue;
                const entryIds = (daySummary.timeTrackingEntries ?? []).map((e) => e.id);
                categorizedHoursToCreate.push(
                    new TimeTrackingCategorizedHourForCreation({
                        dateString: daySummary.dateString,
                        entryIds: entryIds.length > 0 ? entryIds : null,
                        hourCategoryId: policyHourCategory.category.id,
                        hours,
                        instanceId: instance.getId(),
                        status: TimeTrackingCategorizedHourStatus.AUTO_APPROVED,
                        userId: daySummary.userId,
                    }),
                );
            }
        }
        if (categorizedHoursToCreate.length > 0) {
            await this.timeTrackingCategorizedHourRepository.bulkCreateCategorizedHours(categorizedHoursToCreate);
        }
    }

    private getMinEffectiveFromDate(policyHourCategories: TimeTrackingHourCategoryWithAssociationData[], instance: Instance): string {
        const ANCHOR_DATE = '9999-12-31';
        return policyHourCategories
            .map((policyHourCategory) => policyHourCategory.category.effectiveFrom)
            .reduce((min, current) => {
                return DateTime.fromISO(current, {
                    zone: instance.getTimezone(),
                }).startOf('day') <
                    DateTime.fromISO(min, {
                        zone: instance.getTimezone(),
                    }).startOf('day')
                    ? current
                    : min;
            }, ANCHOR_DATE);
    }

    /**
     * Creates PENDING categorized hours (with approval snapshots) for newly assigned users.
     * Called when users are assigned to a policy that has approval-required hour categories.
     *
     * 1. Filter to approval-required categories effective on the current date.
     * 2. Validate single-policy invariant (snapshots are per-policy).
     * 3. Fetch/create day summaries for each user on the current date.
     * 4. Calculate hours per (user, category) and build PENDING creation records.
     * 5. Bulk-insert the categorized hours and snapshot the approval steps from the policy config.
     *
     * @param instance - The instance to create the categorized hours for.
     * @param userIds - The user ids to create the categorized hours for.
     * @param policyHourCategories - The policy hour categories to create the categorized hours for.
     * @param currentDate - The current date to create the categorized hours for.
     */
    private async createCategorizedHoursWithApprovalForAssignedUsers(
        instance: Instance,
        userIds: number[],
        policyHourCategories: TimeTrackingHourCategoryWithAssociationData[],
        currentDate: string,
    ): Promise<TimeTrackingCategorizedHourApprovalStep[]> {
        if (userIds.length === 0) return [];

        // Stage 1: keep only categories that require approval and are effective today
        const approvalCategories = policyHourCategories.filter(
            (phc) => phc.requiresApproval && this.isHourCategoryEffective(phc.category, currentDate, currentDate),
        );
        if (approvalCategories.length === 0) return [];

        const instanceId = instance.getId();
        const timezone = instance.getTimezone();

        // Stage 2: approval step snapshots reference a single policyId — enforce that invariant
        const distinctPolicyIds = [...new Set(approvalCategories.map((c) => c.policyId))];
        if (distinctPolicyIds.length !== 1) {
            throw new Error(`Expected single policyId for approval categories, got: ${distinctPolicyIds.join(', ')}`);
        }
        const policyId = distinctPolicyIds[0];

        // Stage 3: fetch or create day summaries (entries + worked time) for each user
        const userDaySummaries = await allPromises(
            userIds.map((userId) => async () => this.timeTrackingService.getOrCreateDaySummariesByUser(userId, instance, currentDate, currentDate)),
        );
        const daySummaries = userDaySummaries.flat();
        if (daySummaries.length === 0) return [];

        // Stage 4: calculate categorized hours per (user × category) pair
        const categorizedHoursToCreate: TimeTrackingCategorizedHourForCreation[] = [];

        for (const ds of daySummaries) {
            for (const phc of approvalCategories) {
                if (!this.isDayWithinCategoryEffectiveWindow(ds.dateString, phc.category, currentDate, currentDate)) continue;
                const hours = this.timeTrackingHourCategorizationService.calculateCategoryHours(phc.category, ds, timezone);
                if (hours <= 0) continue;
                const entryIds = (ds.timeTrackingEntries ?? []).map((e) => e.id);
                categorizedHoursToCreate.push(
                    new TimeTrackingCategorizedHourForCreation({
                        dateString: ds.dateString,
                        entryIds: entryIds.length > 0 ? entryIds : null,
                        hourCategoryId: phc.category.id,
                        hours,
                        instanceId,
                        status: TimeTrackingCategorizedHourStatus.PENDING,
                        userId: ds.userId,
                    }),
                );
            }
        }

        // Stage 5: persist PENDING hours and snapshot the current approval step configuration
        if (categorizedHoursToCreate.length === 0) return [];
        const created = await this.timeTrackingCategorizedHourRepository.bulkCreateCategorizedHours(categorizedHoursToCreate);
        return await this.categorizedHourApprovalService.bulkCreateApprovalStepsSnapshots(created, instanceId, policyId);
    }

    /* Handles the hour category changes for a given instanceId, hourCategoryId.
     * It will delete the categorized hours without approval for the users that are associated to the hour category.
     * It will create the categorized hours without approval for the users that are associated to the hour category.
     */
    public async handleHourCategoryChanges(instanceId: number, hourCategoryId: number): Promise<void> {
        const instance = await this.instanceService.findByPk(instanceId);
        const hourCategory = await this.timeTrackingHourCategorizationRepository.getHourCategory(instanceId, hourCategoryId);
        if (!hourCategory) return;
        const effectiveTo = DateTime.now().setZone(instance.getTimezone()).startOf('day').toISODate()!;
        const policyHourCategories = await this.timeTrackingHourCategorizationRepository.getPolicyHourCategoriesByHourCategoryIds(
            instanceId,
            [hourCategoryId],
            effectiveTo,
        );
        if (policyHourCategories.length === 0) return;

        const policies = await this.timeTrackingPoliciesService.listPoliciesByIds(
            policyHourCategories.map((policyHourCategory) => policyHourCategory.policyId),
        );
        if (policies.length === 0) return;
        const affectedUserIds = policies.flatMap((policy) => policy.userIds);
        if (affectedUserIds.length === 0) return;

        await this.timeTrackingCategorizedHourRepository.getTransaction(async () => {
            await this.deleteCategorizedHoursWithoutApprovalForUsers(instance, affectedUserIds, policyHourCategories, { dateTo: effectiveTo });
            await this.createCategorizedHoursWithoutApprovalForUsers(instance, affectedUserIds, policyHourCategories, { dateTo: effectiveTo });
        });
    }

    public async handleHourCategoryDeletion(instanceId: number, hourCategoryId: number): Promise<void> {
        const instance = await this.instanceService.findByPk(instanceId);
        const hourCategory = await this.timeTrackingHourCategorizationRepository.getHourCategory(instanceId, hourCategoryId, {
            includeDeleted: true,
        });
        if (!hourCategory) return;
        const effectiveTo = DateTime.now().setZone(instance.getTimezone()).startOf('day').toISODate()!;
        const { rows: policies } = await this.timeTrackingPoliciesService.listPolicies({ instanceId }, {});
        const allUserIds = policies.flatMap((p) => p.userIds);
        if (allUserIds.length === 0) return;

        await this.timeTrackingCategorizedHourRepository.getTransaction(async () => {
            await this.timeTrackingCategorizedHourRepository.bulkDeleteCategorizedHours(
                instanceId,
                allUserIds,
                hourCategoryId,
                hourCategory.effectiveFrom,
                effectiveTo,
                TimeTrackingCategorizedHourStatus.AUTO_APPROVED,
            );

            await this.timeTrackingCategorizedHourRepository.bulkUpdateCategorizedHoursStatusForUsersInRange(
                instanceId,
                allUserIds,
                [hourCategoryId],
                hourCategory.effectiveFrom,
                effectiveTo,
                [TimeTrackingCategorizedHourStatus.PENDING],
                TimeTrackingCategorizedHourStatus.CANCELLED_BY_MODIFICATION,
            );
        });
    }

    public async getApprovedCategorizedHoursByUserAndDates(
        instanceId: number,
        userIds: number[],
        dateStrings: string[],
    ): Promise<TimeTrackingCategorizedHourWithCategoryData[]> {
        if (userIds.length === 0 || dateStrings.length === 0) {
            return [];
        }
        return this.timeTrackingHourCategorizationRepository.getApprovedCategorizedHours(instanceId, userIds, dateStrings);
    }

    /* Handles the entries changes for a given instanceId, userId, and dateString.
     * It will delete the categorized hours without approval for the user on that date.
     * It will create the categorized hours without approval for the user on that date.
     * It will cancel the categorized hours with approval for the user on that date.
     * It will create the categorized hours with approval for the user on that date.
     */
    public async handleEntriesChanges(instanceId: number, userId: number, dateString: string): Promise<void> {
        const instance = await this.instanceService.findByPk(instanceId);
        const { rows: policies } = await this.timeTrackingPoliciesService.listPolicies({ instanceId, userIds: [userId] }, { limit: 1, offset: 0 });
        if (policies.length === 0) return;
        const userPolicy = policies[0];

        const policyHourCategories = await this.timeTrackingHourCategorizationRepository.getPolicyHourCategoriesForPolicyIds(
            [userPolicy.id],
            instanceId,
            dateString,
        );
        if (policyHourCategories.length === 0) return;
        let stepsToNotify: TimeTrackingCategorizedHourApprovalStep[] = [];
        await this.timeTrackingCategorizedHourRepository.getTransaction(async () => {
            await this.deleteCategorizedHoursWithoutApprovalForUsers(instance, [userId], policyHourCategories, {
                dateFrom: dateString,
                dateTo: dateString,
            });
            await this.createCategorizedHoursWithoutApprovalForUsers(instance, [userId], policyHourCategories, {
                dateFrom: dateString,
                dateTo: dateString,
            });
            stepsToNotify = await this.handleApprovalCategorizedHoursOnChange(instance, [userId], policyHourCategories, dateString, dateString);
        });
        await this.dispatchStepPendingNotifications(instanceId, stepsToNotify);
    }

    /**
     * Diffs existing approval-required categorized hours against freshly calculated values
     * and reconciles the differences. Called when entries or schedules change.
     *
     * 1. Filter to approval-required categories and validate single-policy invariant.
     * 2. Fetch existing active hours (PENDING/APPROVED/REJECTED, excluding admin-overridden).
     * 3. Recalculate expected hours from current day summaries → build newCalcMap.
     * 4. Diff existing vs recalculated:
     *    - PENDING + hours changed → update in-place
     *    - PENDING + hours dropped to 0 → delete (with approval data)
     *    - APPROVED/REJECTED + hours changed → cancel old, create new PENDING
     *    - APPROVED/REJECTED + hours unchanged → no-op
     *    - New (userId, date, category) key with hours > 0 → create PENDING
     * 5. Persist all mutations and snapshot approval steps for newly created hours.
     */
    private async handleApprovalCategorizedHoursOnChange(
        instance: Instance,
        userIds: number[],
        policyHourCategories: TimeTrackingHourCategoryWithAssociationData[],
        startDate: string,
        endDate: string,
    ): Promise<TimeTrackingCategorizedHourApprovalStep[]> {
        if (userIds.length === 0) return [];

        // Stage 1: filter to approval categories, validate single-policy
        const approvalCategories = policyHourCategories.filter(
            (phc) => phc.requiresApproval && this.isHourCategoryEffective(phc.category, endDate, startDate),
        );
        if (approvalCategories.length === 0) return [];

        const instanceId = instance.getId();
        const timezone = instance.getTimezone();
        const hourCategoryIds = approvalCategories.map((c) => c.category.id);

        const distinctPolicyIds = [...new Set(approvalCategories.map((c) => c.policyId))];
        if (distinctPolicyIds.length !== 1) {
            throw new Error(`Expected single policyId for approval categories, got: ${distinctPolicyIds.join(', ')}`);
        }
        const policyId = distinctPolicyIds[0];

        // Stage 2: fetch existing active hours (excludes CANCELLED and admin-overridden)
        const existingHours = await this.timeTrackingCategorizedHourRepository.findActiveCategorizedHoursWithApproval(
            instanceId,
            userIds,
            hourCategoryIds,
            startDate,
            endDate,
        );

        // Stage 3: recalculate expected hours from current entries/schedule
        const userDaySummaries = await allPromises(
            userIds.map((userId) => async () => this.timeTrackingService.getOrCreateDaySummariesByUser(userId, instance, startDate, endDate)),
        );
        const daySummaries = userDaySummaries.flat();

        const newCalcMap = new Map<string, { hours: number; entryIds: number[] | null }>();
        for (const ds of daySummaries) {
            for (const phc of approvalCategories) {
                if (!this.isDayWithinCategoryEffectiveWindow(ds.dateString, phc.category, startDate, endDate)) continue;
                const hours = this.timeTrackingHourCategorizationService.calculateCategoryHours(phc.category, ds, timezone);
                const entryIds = (ds.timeTrackingEntries ?? []).map((e) => e.id);
                const key = `${ds.userId}:${ds.dateString}:${phc.category.id}`;
                newCalcMap.set(key, { hours, entryIds: entryIds.length > 0 ? entryIds : null });
            }
        }

        // Stage 4: diff existing hours against recalculated values
        const pendingToUpdate: CategorizedHourDataUpdate[] = [];
        const pendingToDelete: string[] = [];
        const toCancel: string[] = [];
        const newPendingToCreate: TimeTrackingCategorizedHourForCreation[] = [];
        // Tracks (userId:dateString:hourCategoryId) keys already handled from existingHours,
        // so the second loop over newCalcMap only creates entries for truly new combinations.
        const processedKeys = new Set<string>();

        // 4a: reconcile each existing hour against its recalculated value
        for (const existing of existingHours) {
            const key = `${existing.userId}:${existing.dateString}:${existing.hourCategoryId}`;
            processedKeys.add(key);
            const calc = newCalcMap.get(key);
            const newHours = calc ? calc.hours : 0;

            if (existing.status === TimeTrackingCategorizedHourStatus.PENDING) {
                if (newHours <= 0) {
                    pendingToDelete.push(existing.id);
                } else {
                    pendingToUpdate.push({ id: existing.id, hours: newHours, entryIds: calc!.entryIds });
                }
            } else if (
                existing.status === TimeTrackingCategorizedHourStatus.APPROVED ||
                existing.status === TimeTrackingCategorizedHourStatus.REJECTED
            ) {
                if (newHours !== existing.hours) {
                    toCancel.push(existing.id);
                    if (newHours > 0) {
                        newPendingToCreate.push(
                            new TimeTrackingCategorizedHourForCreation({
                                dateString: existing.dateString,
                                entryIds: calc!.entryIds,
                                hourCategoryId: existing.hourCategoryId,
                                hours: newHours,
                                instanceId,
                                status: TimeTrackingCategorizedHourStatus.PENDING,
                                userId: existing.userId,
                            }),
                        );
                    }
                }
            }
        }

        // 4b: create PENDING hours for new (user, date, category) combinations not in existing
        for (const [key, calc] of newCalcMap) {
            if (processedKeys.has(key) || calc.hours <= 0) continue;
            const [userIdStr, dateString, hourCategoryIdStr] = key.split(':');
            newPendingToCreate.push(
                new TimeTrackingCategorizedHourForCreation({
                    dateString,
                    entryIds: calc.entryIds,
                    hourCategoryId: parseInt(hourCategoryIdStr, 10),
                    hours: calc.hours,
                    instanceId,
                    status: TimeTrackingCategorizedHourStatus.PENDING,
                    userId: parseInt(userIdStr, 10),
                }),
            );
        }

        // Stage 5: persist all mutations
        const stepsToNotify: TimeTrackingCategorizedHourApprovalStep[] = [];
        if (pendingToUpdate.length > 0) {
            await this.timeTrackingCategorizedHourRepository.bulkUpdateCategorizedHoursData(pendingToUpdate, instanceId);
            const userByHourId = new Map(existingHours.map((h) => [h.id, h.userId]));
            const firstStepsFromUpdates = (
                await allPromises(
                    pendingToUpdate.map((u) => async () => {
                        const userId = userByHourId.get(u.id)!;
                        return this.categorizedHourApprovalService.createApprovalStepsSnapshotIfNeeded(u.id, userId, instanceId, policyId);
                    }),
                )
            ).filter((s) => s != null);
            stepsToNotify.push(...firstStepsFromUpdates);
        }
        if (pendingToDelete.length > 0) {
            await this.timeTrackingCategorizedHourRepository.deleteCategorizedHoursByIds(pendingToDelete);
            await this.categorizedHourApprovalService.deleteApprovalDataByCategorizedHourIds(pendingToDelete);
        }
        if (toCancel.length > 0) {
            await Promise.all(
                toCancel.map((id) =>
                    this.timeTrackingCategorizedHourRepository.updateCategorizedHourStatus(
                        id,
                        instanceId,
                        TimeTrackingCategorizedHourStatus.CANCELLED_BY_MODIFICATION,
                    ),
                ),
            );
        }
        if (newPendingToCreate.length > 0) {
            const created = await this.timeTrackingCategorizedHourRepository.bulkCreateCategorizedHours(newPendingToCreate);
            const newFirstSteps = await this.categorizedHourApprovalService.bulkCreateApprovalStepsSnapshots(created, instanceId, policyId);
            stepsToNotify.push(...newFirstSteps);
        }
        return stepsToNotify;
    }

    private async dispatchStepPendingNotifications(instanceId: number, steps: TimeTrackingCategorizedHourApprovalStep[]): Promise<void> {
        if (steps.length === 0) return;
        await allPromises(
            steps.map((step) => async () => {
                await this.timeTrackingNotificationsPort
                    .notifyCategorizedHourStepPending(instanceId, step.categorizedHourId, step.id)
                    .catch((err) => this.logger.error('Failed to notify step pending', { err, categorizedHourId: step.categorizedHourId }));
            }),
        );
    }

    /* Handles the schedule changes for a given instanceId, userIds, startDate, and endDate.
     * For each policy in the date range, it will delete the categorized hours without approval for the associated users.
     * It will create the categorized hours without approval for the associated users.
     * It will cancel the categorized hours with approval for the associated users.
     * It will create the categorized hours with approval for the associated users.
     */
    public async handleScheduleChanges(instanceId: number, userIds: number[], startDate: string, endDate: string): Promise<void> {
        const instance = await this.instanceService.findByPk(instanceId);
        const maxEndDate = DateTime.now().setZone(instance.getTimezone()).plus({ days: 1 }).startOf('day').toISODate()!;
        const cappedEndDate = endDate > maxEndDate ? maxEndDate : endDate;
        if (cappedEndDate !== endDate) {
            this.logger.warn(
                { cappedEndDate, instanceId, originalEndDate: endDate, startDate, userIds },
                'handleScheduleChanges endDate capped to prevent unbounded recalculation',
            );
        }
        if (startDate > cappedEndDate) {
            this.logger.warn({ cappedEndDate, instanceId, startDate, userIds }, 'handleScheduleChanges skipped: startDate is after capped endDate');
            return;
        }
        const { rows: policyAssociations } = await this.timeTrackingPoliciesService.listPolicyAssociations(
            {
                fromDate: startDate,
                instanceId: instance.getId(),
                toDate: cappedEndDate,
                userIds: userIds,
            },
            {},
        );

        if (policyAssociations.length === 0) {
            return;
        }

        const policyForUsers: Map<number, number[]> = new Map();
        policyAssociations.forEach((policyAssociation) => {
            if (policyAssociation.policy?.id) {
                const userIds = policyForUsers.get(policyAssociation.policy.id) ?? [];
                userIds.push(policyAssociation.userId);
                policyForUsers.set(policyAssociation.policy.id, userIds);
            }
        });

        const policyIds = Array.from(policyForUsers.keys());
        const policyHourCategories = await this.timeTrackingHourCategorizationRepository.getPolicyHourCategoriesForPolicyIds(
            policyIds,
            instanceId,
            startDate,
        );
        if (policyHourCategories.length === 0) return;
        const policyHourCategoriesByPolicyId: Map<number, TimeTrackingHourCategoryWithAssociationData[]> = new Map();
        for (const policyHourCategory of policyHourCategories) {
            const policyId = policyHourCategory.policyId;
            const policyHourCategories = policyHourCategoriesByPolicyId.get(policyId) ?? [];
            policyHourCategories.push(policyHourCategory);
            policyHourCategoriesByPolicyId.set(policyId, policyHourCategories);
        }
        for (const [policyId, policyHourCategories] of policyHourCategoriesByPolicyId) {
            const userIds = policyForUsers.get(policyId) ?? [];
            let stepsToNotify: TimeTrackingCategorizedHourApprovalStep[] = [];
            await this.timeTrackingCategorizedHourRepository.getTransaction(async () => {
                await this.deleteCategorizedHoursWithoutApprovalForUsers(instance, userIds, policyHourCategories, {
                    dateFrom: startDate,
                    dateTo: cappedEndDate,
                });
                await this.createCategorizedHoursWithoutApprovalForUsers(instance, userIds, policyHourCategories, {
                    dateFrom: startDate,
                    dateTo: cappedEndDate,
                });
                stepsToNotify = await this.handleApprovalCategorizedHoursOnChange(instance, userIds, policyHourCategories, startDate, cappedEndDate);
            });
            await this.dispatchStepPendingNotifications(instanceId, stepsToNotify);
        }
    }

    private static readonly DEVOPS_LOG_TAG = '[categorized-hours-recalc-devops]';

    /**
     * Devops escape hatch: recompute only the `hours` value of a single existing TimeTrackingCategorizedHour row.
     *
     * - Input: a single categorizedHourId scoped to instanceId.
     * - Writes only the `hours` column. Status, entryIds, overrides, note — never touched.
     * - 404 if the row is missing.
     * - 400 (TIME_TRACKING_CATEGORIZED_HOUR_VALUE_UNCHANGED) if recompute equals the stored value.
     * - Emits start, row-updated, and end logs (tagged for Datadog).
     */
    public recalculateCategorizedHourDevops = async (params: {
        instanceId: number;
        categorizedHourId: string;
        triggeredByUserId: number;
        dryRun?: boolean;
    }): Promise<RecalculatedCategorizedHour> => {
        const { instanceId, categorizedHourId, triggeredByUserId, dryRun } = params;
        const tag = TimeTrackingCategorizedHourService.DEVOPS_LOG_TAG;

        this.logger.info({ tag, instanceId, triggeredByUserId, categorizedHourId }, `${tag} start`);

        const instance = await this.instanceService.findByPk(instanceId);
        const timezone = instance.getTimezone();

        // 1. Load the row (404 if missing under this instance).
        const categorizedHour = await this.timeTrackingHourCategorizationRepository.getCategorizedHour(instanceId, categorizedHourId);
        if (!categorizedHour) {
            throw new NotFoundError('Categorized hour not found', ErrorCodes.TIME_TRACKING_CATEGORIZED_HOUR_NOT_FOUND);
        }

        // 2. Load the full HourCategory (the calculator needs the rules, not just the id+name).
        const category = await this.timeTrackingHourCategorizationRepository.getHourCategory(instanceId, categorizedHour.hourCategoryId);

        // 3. Load the day summary for (user, date).
        const [daySummary] = await this.timeTrackingService.getOrCreateDaySummariesByUser(
            categorizedHour.userId,
            instance,
            categorizedHour.dateString,
            categorizedHour.dateString,
        );

        // 4. Recompute hours.
        const newHours = daySummary ? this.timeTrackingHourCategorizationService.calculateCategoryHours(category, daySummary, timezone) : 0;
        const previousHours = categorizedHour.hours;

        const result = new RecalculatedCategorizedHour({
            categorizedHourId,
            userId: categorizedHour.userId,
            dateString: categorizedHour.dateString,
            status: categorizedHour.status,
            hourCategory: { id: category.id, name: category.name },
            previousHours,
            newHours,
        });

        if (dryRun) {
            this.logger.info({ tag, instanceId, triggeredByUserId, categorizedHourId, previousHours, newHours }, `${tag} dry-run complete`);
            return result;
        }

        // 5. No-op → 400 with the dedicated error code.
        if (newHours === previousHours) {
            throw new BadRequestError(
                `Recalculated hours for category '${category.name}' (id ${category.id}) are ${newHours} — same as the stored value. No update performed.`,
                ErrorCodes.TIME_TRACKING_CATEGORIZED_HOUR_VALUE_UNCHANGED,
            );
        }

        // 6. Persist only the `hours` column.
        await this.timeTrackingCategorizedHourRepository.updateCategorizedHourValue(categorizedHourId, instanceId, newHours);

        const wasNonAutoApproved = categorizedHour.status !== TimeTrackingCategorizedHourStatus.AUTO_APPROVED;
        this.logger.info(
            {
                tag,
                instanceId,
                triggeredByUserId,
                categorizedHourId,
                userId: categorizedHour.userId,
                dateString: categorizedHour.dateString,
                hourCategoryId: categorizedHour.hourCategoryId,
                hourCategoryName: category.name,
                status: categorizedHour.status,
                wasNonAutoApproved,
                oldHours: previousHours,
                newHours,
            },
            `${tag} row updated`,
        );

        this.logger.info({ tag, instanceId, triggeredByUserId, categorizedHourId }, `${tag} end`);

        return result;
    };
}
