import { ENV_OPTIONAL_NUMBER_KEYS_MAPPING, ErrorCodes, NotFoundError, SEQUELIZE_INSTANCE_TOKEN } from '@humand-packages/common';
import { LimitOffsetPagination } from 'humand-packages/monolith/src/api/types/listOptions';
import { Op, Order, WhereOptions, IncludeOptions, col, fn } from 'sequelize';
import { Sequelize } from 'sequelize-typescript';
import { Inject, Service } from 'typedi';
import { v7 as uuidv7 } from 'uuid';
import { BaseRepositoryAdapter } from '../../../../../../dbAdapters/baseRepositoryAdapter';
import { BULK_REVIEW_HARD_CAP } from '../../../../business/constants/bulkReview';
import { HourCategory, HourCategoryForCreation, HourCategoryForUpdate } from '../../../../business/models/hourCategorization';
import { CategorizedHourCategoryCount } from '../../../../business/models/timeTracking/categorizedHourCategoryCount';
import {
    SavedPendingCategorizedHour,
    TimeTrackingCategorizedHour,
    TimeTrackingCategorizedHourForCreation,
    TimeTrackingCategorizedHourWithCategoryData,
} from '../../../../business/models/timeTracking/timeTrackingCategorizedHour';
import {
    TimeTrackingHourCategoryWithAssociationData,
    TimeTrackingPolicyHourCategory,
} from '../../../../business/models/TimeTrackingPolicyHourCategory';
import { CategorizedHoursFilters } from '../../../../business/types/hourCategorization/categorizedHoursFilters';
import { GetCategorizedHoursVC } from '../../../../presentation/validationClasses/hourCategorization/getCategorizedHoursVC';
import { UpdateCategorizedHourVC } from '../../../../presentation/validationClasses/hourCategorization/updateCategorizedHourVC';
import { SortOrder, SORT_FIELD_MAP } from '../../../constants/timeTracking/categorizedHoursSort';
import { TimeTrackingCategorizedHourStatus } from '../../../constants/timeTracking/categorizedHourStatus';
import { TimeTrackingCategorizedHourDAO } from '../../../dataAccessObjects/hourCategory/categorizedHour';
import { TimeTrackingHourCategorizationRuleDAO } from '../../../dataAccessObjects/hourCategory/hourCategorizationRule';
import { TimeTrackingHourCategoryDAO } from '../../../dataAccessObjects/hourCategory/hourCategory';
import { TimeTrackingPolicyHourCategoryDAO } from '../../../dataAccessObjects/hourCategory/policyHourCategory';
import {
    mapCategorizedHourDAOToModelWithCategoryData,
    mapHourCategoryDAOToModel,
    mapHourCategoryForUpdateToDAO,
    mapHourCategorizationRuleModelToDAO,
    mapHourCategoryDAOToPolicyHourCategory,
} from '../../../mappers/timeTracking/timeTrackingHourCategorizationMapper';
import { TimeTrackingHourCategorizationRepositoryPort } from '../../../ports/timeTracking/timeTrackingHourCategorizationRepositoryPort';

@Service()
export default class TimeTrackingHourCategorizationAdapter extends BaseRepositoryAdapter implements TimeTrackingHourCategorizationRepositoryPort {
    constructor(
        @Inject(ENV_OPTIONAL_NUMBER_KEYS_MAPPING.BULK_CREATE_CHUNK_SIZE) bulkCreateChunkSize: number,
        @Inject(SEQUELIZE_INSTANCE_TOKEN) sequelize: Sequelize,
    ) {
        super(bulkCreateChunkSize, sequelize);
    }

    /**
     * List all hour categories
     * @param instanceId - The instance id
     * @param pagination - The pagination options
     * @param targetDateString - Optional date to filter categories where effectiveFrom <= targetDateString
     * @returns The hour categories
     */
    public listHourCategories = async (
        instanceId: number,
        pagination: LimitOffsetPagination,
        targetDateString?: string,
        search?: string,
    ): Promise<{ count: number; rows: HourCategory[] }> => {
        const whereClause: WhereOptions<TimeTrackingHourCategoryDAO> = {
            instanceId,
        };

        if (targetDateString) {
            whereClause.effectiveFrom = {
                [Op.lte]: targetDateString,
            };
        }
        if (search) {
            whereClause.name = {
                [Op.iLike]: `%${search}%`,
            };
        }
        const { rows: categories, count } = await TimeTrackingHourCategoryDAO.findAndCountAll({
            include: [
                {
                    as: 'rules',
                    model: TimeTrackingHourCategorizationRuleDAO,
                    required: false,
                },
            ],
            limit: pagination.limit,
            offset: pagination.offset,
            order: [['name', 'ASC']],
            where: whereClause,
        });

        return { count, rows: categories.map(mapHourCategoryDAOToModel) };
    };

    /**
     * Get a hour category by id with its rules
     * @param instanceId - The instance id
     * @param id - The hour category id
     * @returns The hour category
     */
    public getHourCategory = async (instanceId: number, id: number, options?: { includeDeleted?: boolean }): Promise<HourCategory> => {
        const category = await TimeTrackingHourCategoryDAO.findOne({
            include: [
                {
                    as: 'rules',
                    model: TimeTrackingHourCategorizationRuleDAO,
                    required: false,
                },
            ],
            paranoid: !options?.includeDeleted,
            where: {
                id,
                instanceId,
            },
        });
        if (!category) {
            throw new NotFoundError('Hour category not found', ErrorCodes.TIME_TRACKING_HOUR_CATEGORY_NOT_FOUND);
        }
        return mapHourCategoryDAOToModel(category);
    };

    /**
     * Create a new hour category
     * @param instanceId - The instance id
     * @param hourCategory - The hour category for creation
     * @returns The created hour category
     */
    public createHourCategory = async (instanceId: number, hourCategory: HourCategoryForCreation): Promise<HourCategory> => {
        return await this.getTransaction(async () => {
            const category = await TimeTrackingHourCategoryDAO.create(hourCategory);

            if (hourCategory.rules?.length) {
                const createdRules = await TimeTrackingHourCategorizationRuleDAO.bulkCreate(
                    hourCategory.rules.map((rule) => mapHourCategorizationRuleModelToDAO(rule, category.id)),
                );
                category.rules = createdRules;
            } else {
                category.rules = [];
            }
            return mapHourCategoryDAOToModel(category);
        });
    };

    /**
     * Update a hour category. If the rules are provided, the existing rules will be replaced.
     * @param instanceId - The instance id
     * @param id - The hour category id
     * @param hourCategoryForUpdate - The hour category for update
     * @returns The updated hour category
     */
    public updateHourCategory = async (instanceId: number, id: number, hourCategoryForUpdate: HourCategoryForUpdate): Promise<HourCategory> => {
        return await this.getTransaction(async () => {
            const [_, [updatedCategory]] = await TimeTrackingHourCategoryDAO.update(
                mapHourCategoryForUpdateToDAO({ ...hourCategoryForUpdate, instanceId }),
                {
                    returning: true,
                    where: { id, instanceId },
                },
            );

            if (hourCategoryForUpdate.rules) {
                await TimeTrackingHourCategorizationRuleDAO.destroy({ where: { hourCategoryId: id } });

                if (hourCategoryForUpdate.rules.length > 0) {
                    const createdRules = await TimeTrackingHourCategorizationRuleDAO.bulkCreate(
                        hourCategoryForUpdate.rules.map((rule) => mapHourCategorizationRuleModelToDAO(rule, id)),
                    );
                    updatedCategory.rules = createdRules;
                } else {
                    updatedCategory.rules = [];
                }
            }
            return mapHourCategoryDAOToModel(updatedCategory);
        });
    };

    public deleteHourCategory = async (instanceId: number, id: number): Promise<void> => {
        return await this.getTransaction(async () => {
            await TimeTrackingHourCategoryDAO.destroy({
                where: { id, instanceId },
            });
            await TimeTrackingHourCategorizationRuleDAO.destroy({
                where: { hourCategoryId: id },
            });
        });
    };

    /**
     * Lists all hour categories for an instance, ordered with categories assigned to the given
     * policy first, followed by unassigned categories, and then alphabetically by name.
     *
     * The result includes both assigned and unassigned categories, where:
     * - `assigned` indicates whether the category is currently associated with the policy
     * - `requiresApproval` is only meaningful when `assigned === true`
     *
     * Example result for a given policy:
     * [
     *  { id: 1, name: 'Category 1', description: 'Description 1', assigned: true,  requiresApproval: true },
     *  { id: 2, name: 'Category 2', description: 'Description 2', assigned: true,  requiresApproval: false },
     *  { id: 3, name: 'Category 3', description: 'Description 3', assigned: false, requiresApproval: null },
     * ]
     * @param instanceId - The instance id
     * @param policyId - The policy id
     * @param pagination - The pagination options
     * @param search - The search query
     * @returns The hour categories
     */
    public listHourCategoriesForPolicy = async (
        instanceId: number,
        policyId: number,
        pagination: LimitOffsetPagination,
        search?: string,
    ): Promise<{ count: number; rows: Array<TimeTrackingPolicyHourCategory> }> => {
        const whereClause: WhereOptions<TimeTrackingHourCategoryDAO> = {
            instanceId,
        };

        if (search) {
            whereClause.name = {
                [Op.iLike]: `%${search}%`,
            };
        }

        // Sort first by the ones that have policyHourCategories, then by name
        const { rows: categories, count } = await TimeTrackingHourCategoryDAO.findAndCountAll({
            include: [
                {
                    as: 'policyHourCategories',
                    attributes: ['requiresApproval'],
                    model: TimeTrackingPolicyHourCategoryDAO,
                    required: false,
                    where: {
                        instanceId,
                        policyId,
                    },
                },
            ],
            limit: pagination.limit,
            offset: pagination.offset,
            // This is to sort the categories first by the ones that have policyHourCategories, then by name
            order: [
                [Sequelize.literal(`CASE WHEN "policyHourCategories"."policyId" IS NULL THEN 1 ELSE 0 END`), 'ASC'],
                ['name', 'ASC'],
            ],
            subQuery: false,
            where: whereClause,
        });

        const policyHourCategories = categories.map(mapHourCategoryDAOToPolicyHourCategory);

        return { count, rows: policyHourCategories };
    };

    /**
     * Save the categorized hours for a day summary.
     * Returns metadata for PENDING hours so callers can create approval step snapshots.
     */
    public saveCategorizedHours = async (
        instanceId: number,
        categorizedHoursMap: Map<string, TimeTrackingCategorizedHourForCreation[]>,
    ): Promise<SavedPendingCategorizedHour[]> => {
        const categorizedHoursToCreate: TimeTrackingCategorizedHour[] = [];

        for (const [key, categorizedHours] of categorizedHoursMap) {
            const [userId, ...dateStringParts] = key.split('-');
            const dateString = dateStringParts.join('-');
            const userIdNum = parseInt(userId, 10);

            for (const categorizedHour of categorizedHours) {
                categorizedHoursToCreate.push(
                    new TimeTrackingCategorizedHour({
                        dateString,
                        entryIds: categorizedHour.entryIds ?? null,
                        hourCategoryId: categorizedHour.hourCategoryId,
                        hours: categorizedHour.hours,
                        id: uuidv7(),
                        instanceId,
                        status: categorizedHour.status,
                        userId: userIdNum,
                    }),
                );
            }
        }

        const created = await TimeTrackingCategorizedHourDAO.bulkCreate(categorizedHoursToCreate, {
            conflictAttributes: ['instanceId', 'userId', 'dateString', 'hourCategoryId'],
            conflictWhere: { deletedAt: null, status: { [Op.ne]: TimeTrackingCategorizedHourStatus.CANCELLED_BY_MODIFICATION } },
            updateOnDuplicate: ['hours', 'entryIds'],
        });

        return created.filter((dao) => dao.status === TimeTrackingCategorizedHourStatus.PENDING).map((dao) => ({ id: dao.id, userId: dao.userId }));
    };

    /**
     * Get the approved categorized hours map for day summaries
     * @param instanceId - The instance id
     * @param userIds - The user IDs
     * @param dateStrings - The date strings
     * @returns The map of userId-dateString to approved categorized hours
     */
    public getApprovedCategorizedHoursMap = async (
        instanceId: number,
        userIds: number[],
        dateStrings: string[],
    ): Promise<Map<string, TimeTrackingCategorizedHourWithCategoryData[]>> => {
        if (userIds.length === 0 || dateStrings.length === 0) {
            return new Map<string, TimeTrackingCategorizedHourWithCategoryData[]>();
        }

        const categorizedHours = await TimeTrackingCategorizedHourDAO.findAll({
            include: [
                {
                    as: 'hourCategory',
                    attributes: ['id', 'name', 'description'],
                    model: TimeTrackingHourCategoryDAO,
                    paranoid: false,
                    required: false,
                },
            ],
            where: {
                dateString: { [Op.in]: dateStrings },
                instanceId,
                status: { [Op.in]: ['APPROVED', 'AUTO_APPROVED'] },
                userId: { [Op.in]: userIds },
            },
        });

        // Group by userId-dateString
        const categorizedHoursMap = new Map<string, TimeTrackingCategorizedHourWithCategoryData[]>();
        for (const catHour of categorizedHours) {
            const key = `${catHour.userId}-${catHour.dateString}`;
            if (!categorizedHoursMap.has(key)) {
                categorizedHoursMap.set(key, []);
            }
            categorizedHoursMap.get(key)!.push(mapCategorizedHourDAOToModelWithCategoryData(catHour));
        }

        return categorizedHoursMap;
    };

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

        const categorizedHours = await TimeTrackingCategorizedHourDAO.findAll({
            include: [
                {
                    as: 'hourCategory',
                    attributes: ['id', 'name', 'description'],
                    model: TimeTrackingHourCategoryDAO,
                    paranoid: false,
                    required: false,
                },
            ],
            where: {
                dateString: { [Op.in]: dateStrings },
                instanceId,
                status: { [Op.in]: [TimeTrackingCategorizedHourStatus.APPROVED, TimeTrackingCategorizedHourStatus.AUTO_APPROVED] },
                userId: { [Op.in]: userIds },
            },
        });

        return categorizedHours.map(mapCategorizedHourDAOToModelWithCategoryData);
    };

    public getCategorizedHoursForUserDate = async (
        instanceId: number,
        userId: number,
        dateString: string,
    ): Promise<TimeTrackingCategorizedHourWithCategoryData[]> => {
        const categorizedHours = await TimeTrackingCategorizedHourDAO.findAll({
            include: [
                {
                    as: 'hourCategory',
                    attributes: ['id', 'name', 'description'],
                    model: TimeTrackingHourCategoryDAO,
                    paranoid: false,
                    required: false,
                },
            ],
            where: { dateString, instanceId, userId },
        });

        return categorizedHours.map(mapCategorizedHourDAOToModelWithCategoryData);
    };

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

        const rows = await TimeTrackingCategorizedHourDAO.findAll({
            attributes: ['hourCategoryId'],
            group: ['hourCategoryId'],
            raw: true,
            where: {
                dateString: { [Op.between]: [fromDate, toDate] },
                instanceId,
                status: { [Op.in]: ['APPROVED', 'AUTO_APPROVED'] },
                userId: { [Op.in]: userIds },
            },
        });

        return new Set(rows.map((r) => r.hourCategoryId));
    };

    /**
     * Get policy hour categories for a specific policy with their requiresApproval flag
     * @param policyId - The policy id
     * @param instanceId - The instance id
     * @param targetDate - The target date to filter categories by effectiveFrom
     * @returns Array of categories with their requiresApproval flag
     */
    public getPolicyHourCategoriesForPolicyIds = async (
        policyIds: number[],
        instanceId: number,
        targetDate?: string,
    ): Promise<Array<TimeTrackingHourCategoryWithAssociationData>> => {
        const whereClause: WhereOptions<TimeTrackingHourCategoryDAO> = {
            ...(targetDate ? { effectiveFrom: { [Op.lte]: targetDate } } : {}),
            instanceId,
        };

        const policyHourCategories = await TimeTrackingPolicyHourCategoryDAO.findAll({
            include: [
                {
                    as: 'hourCategory',
                    include: [
                        {
                            as: 'rules',
                            model: TimeTrackingHourCategorizationRuleDAO,
                            required: true,
                        },
                    ],
                    model: TimeTrackingHourCategoryDAO,
                    required: true,
                    where: whereClause,
                },
            ],
            where: {
                instanceId,
                policyId: policyIds,
            },
        });

        return policyHourCategories.map((policyHourCategory) => {
            this.assertHourCategoryExists(policyHourCategory.hourCategory);
            return {
                category: mapHourCategoryDAOToModel(policyHourCategory.hourCategory),
                policyId: policyHourCategory.policyId,
                requiresApproval: policyHourCategory.requiresApproval ?? false,
            };
        });
    };

    private assertHourCategoryExists(hourCategory: TimeTrackingHourCategoryDAO | undefined): asserts hourCategory is TimeTrackingHourCategoryDAO {
        if (!hourCategory) throw new NotFoundError('Hour category not found', ErrorCodes.TIME_TRACKING_HOUR_CATEGORY_NOT_FOUND);
    }

    public getPolicyHourCategoriesByHourCategoryIds = async (
        instanceId: number,
        hourCategoryIds: number[],
        targetDate?: string,
    ): Promise<Array<TimeTrackingHourCategoryWithAssociationData>> => {
        const whereClause: WhereOptions<TimeTrackingHourCategoryDAO> = {
            ...(targetDate ? { effectiveFrom: { [Op.lte]: targetDate } } : {}),
            id: hourCategoryIds,
            instanceId,
        };

        const policyHourCategories = await TimeTrackingPolicyHourCategoryDAO.findAll({
            include: [
                {
                    as: 'hourCategory',
                    include: [
                        {
                            as: 'rules',
                            model: TimeTrackingHourCategorizationRuleDAO,
                            required: true,
                        },
                    ],
                    model: TimeTrackingHourCategoryDAO,
                    required: true,
                    where: whereClause,
                },
            ],
            where: {
                hourCategoryId: hourCategoryIds,
                instanceId,
            },
        });

        return policyHourCategories.map((policyHourCategory) => {
            this.assertHourCategoryExists(policyHourCategory.hourCategory);
            return {
                category: mapHourCategoryDAOToModel(policyHourCategory.hourCategory),
                policyId: policyHourCategory.policyId,
                requiresApproval: policyHourCategory.requiresApproval ?? false,
            };
        });
    };

    public getPendingCategorizedHoursAmount = async (instanceId: number, loggedUserId: number): Promise<number> => {
        const { count } = await TimeTrackingCategorizedHourDAO.findAndCountAll({
            attributes: ['id'],
            limit: 100,
            raw: true,
            where: {
                [Op.and]: [
                    {
                        instanceId,
                        status: TimeTrackingCategorizedHourStatus.PENDING,
                    },
                    Sequelize.literal(TimeTrackingHourCategorizationAdapter.userIsApproverInApprovalStepWithState(loggedUserId, ['PENDING'])),
                ],
            },
        });

        return count;
    };

    /**
     * Get a categorized hour by id
     * @param instanceId - The instance id
     * @param id - The categorized hour id
     * @returns The categorized hour with category data
     */
    public getCategorizedHour = async (
        instanceId: number,
        categorizedHourId: string,
    ): Promise<TimeTrackingCategorizedHourWithCategoryData | null> => {
        const categorizedHour = await TimeTrackingCategorizedHourDAO.findOne({
            include: [
                {
                    as: 'hourCategory',
                    attributes: ['id', 'name'],
                    model: TimeTrackingHourCategoryDAO,
                    paranoid: false,
                    required: false,
                },
            ],
            where: {
                id: categorizedHourId,
                instanceId,
            },
        });
        if (!categorizedHour) {
            return null;
        }
        return mapCategorizedHourDAOToModelWithCategoryData(categorizedHour);
    };

    public updateCategorizedHour = async (
        instanceId: number,
        categorizedHourId: string,
        params: UpdateCategorizedHourVC,
    ): Promise<TimeTrackingCategorizedHourWithCategoryData> => {
        const [, updatedRows] = await TimeTrackingCategorizedHourDAO.update(params, {
            returning: true,
            where: { id: categorizedHourId, instanceId },
        });
        if (!updatedRows || updatedRows.length === 0) {
            throw new NotFoundError('Categorized hour not found', ErrorCodes.TIME_TRACKING_CATEGORIZED_HOUR_NOT_FOUND);
        }
        return mapCategorizedHourDAOToModelWithCategoryData(updatedRows[0]);
    };

    /**
     * Lists categorized hours for an instance applying pagination, filtering, search and sorting.
     * User scope (userIds, itemIds, siteIds) is resolved by the service; filters are AND-combined.
     */
    public listCategorizedHours = async (
        instanceId: number,
        pagination: LimitOffsetPagination,
        filters: GetCategorizedHoursVC,
        loggedUserId: number,
        hasManageCapability: boolean,
        searchedUserIds?: number[],
        approverCategorizedHourIds?: string[],
    ): Promise<{ count: number; rows: TimeTrackingCategorizedHourWithCategoryData[] }> => {
        const { where, include } = this.buildCategorizedHoursQuery({
            instanceId,
            filters,
            loggedUserId,
            hasManageCapability,
            searchedUserIds,
            approverCategorizedHourIds,
        });
        const order = this.buildListCategorizedHoursOrder(filters);

        const result = await TimeTrackingCategorizedHourDAO.findAndCountAll({
            distinct: true,
            include: [include],
            limit: pagination.limit,
            offset: pagination.offset,
            order,
            subQuery: false,
            where,
        });

        return { count: result.count, rows: result.rows.map(mapCategorizedHourDAOToModelWithCategoryData) };
    };

    public listCategorizedHourIdsByFilters = async (input: {
        instanceId: number;
        filters: CategorizedHoursFilters;
        loggedUserId: number;
        hasManageCapability: boolean;
        searchedUserIds?: number[];
    }): Promise<string[]> => {
        const { instanceId, filters, loggedUserId, hasManageCapability, searchedUserIds } = input;
        const { where, include } = this.buildCategorizedHoursQuery({ instanceId, filters, loggedUserId, hasManageCapability, searchedUserIds });
        const rows = await TimeTrackingCategorizedHourDAO.findAll({
            attributes: ['id'],
            where,
            include: [include],
            limit: BULK_REVIEW_HARD_CAP + 1,
            order: [['id', 'ASC']],
        });
        return rows.map((r) => r.id);
    };

    public countCategorizedHoursByCategory = async (input: {
        instanceId: number;
        filters: CategorizedHoursFilters;
        loggedUserId: number;
        hasManageCapability: boolean;
        searchedUserIds?: number[];
        approverCategorizedHourIds?: string[];
    }): Promise<CategorizedHourCategoryCount[]> => {
        const { instanceId, filters, loggedUserId, hasManageCapability, searchedUserIds, approverCategorizedHourIds } = input;
        const { where, include } = this.buildCategorizedHoursQuery({
            instanceId,
            filters,
            loggedUserId,
            hasManageCapability,
            searchedUserIds,
            approverCategorizedHourIds,
        });
        const categorizedHoursByCategory = (await TimeTrackingCategorizedHourDAO.findAll({
            attributes: [
                'hourCategoryId',
                [col('hourCategory.name'), 'hourCategoryName'],
                [fn('COUNT', col('TimeTrackingCategorizedHourDAO.id')), 'count'],
            ],
            include: [{ ...include, required: true }],
            where,
            group: ['TimeTrackingCategorizedHourDAO.hourCategoryId', 'hourCategory.id', 'hourCategory.name'],
            raw: true,
            subQuery: false,
        })) as Array<TimeTrackingCategorizedHourDAO & { hourCategoryName: string; count: string | number }>;

        return categorizedHoursByCategory.map(
            (r) =>
                new CategorizedHourCategoryCount({
                    hourCategoryId: r.hourCategoryId,
                    hourCategoryName: r.hourCategoryName,
                    count: typeof r.count === 'number' ? r.count : parseInt(r.count, 10),
                }),
        );
    };

    /**
     * Assembles the {where, include} used by both the paginated list and the ID-only bulk-review query.
     * Keeps the exact WHERE assembly (visibility, status branching, site subquery, search OR) shared.
     */
    private buildCategorizedHoursQuery(input: {
        instanceId: number;
        filters: CategorizedHoursFilters;
        loggedUserId: number;
        hasManageCapability: boolean;
        searchedUserIds?: number[];
        approverCategorizedHourIds?: string[];
    }): { where: WhereOptions<TimeTrackingCategorizedHourDAO>; include: IncludeOptions } {
        const { instanceId, filters, loggedUserId, hasManageCapability, searchedUserIds, approverCategorizedHourIds } = input;
        const where = this.buildListCategorizedHoursWhere(instanceId, filters, approverCategorizedHourIds, loggedUserId, hasManageCapability);
        const searchWhere = this.buildListCategorizedHoursSearchOr(filters.search, searchedUserIds);
        const finalWhere = searchWhere ? { [Op.and]: [where, { [Op.or]: searchWhere }] } : where;
        const include = this.buildListCategorizedHoursInclude(filters.search, searchedUserIds);
        return { where: finalWhere, include };
    }

    private buildListCategorizedHoursWhere(
        instanceId: number,
        filters: CategorizedHoursFilters,
        approverCategorizedHourIds: string[] | undefined,
        loggedUserId: number,
        hasManageCapability: boolean,
    ): WhereOptions<TimeTrackingCategorizedHourDAO> {
        const baseWhere: WhereOptions<TimeTrackingCategorizedHourDAO> = { instanceId };
        const andConditions: unknown[] = [baseWhere];

        if (approverCategorizedHourIds?.length) {
            const scopeConditions: WhereOptions<TimeTrackingCategorizedHourDAO>[] = [];
            if (filters.userIds?.length) {
                scopeConditions.push({ userId: { [Op.in]: filters.userIds } });
            }
            scopeConditions.push({ id: { [Op.in]: approverCategorizedHourIds } });
            andConditions.push({ [Op.or]: scopeConditions });
        } else if (filters.userIds?.length) {
            baseWhere.userId = { [Op.in]: filters.userIds };
        }

        andConditions.push(this.buildStatusCondition(filters.status, loggedUserId, hasManageCapability));

        if (filters.hourCategoryIds?.length) {
            baseWhere.hourCategoryId = { [Op.in]: filters.hourCategoryIds };
        }
        if (filters.startDate || filters.endDate) {
            baseWhere.dateString = {
                ...(filters.startDate && { [Op.gte]: filters.startDate }),
                ...(filters.endDate && { [Op.lte]: filters.endDate }),
            };
        }
        if (filters.siteIds?.length) {
            const siteIdsList = filters.siteIds.join(',');
            andConditions.push(
                Sequelize.literal(
                    `EXISTS (SELECT 1 FROM unnest("TimeTrackingCategorizedHourDAO"."entryIds") AS "entryId" ` +
                        `JOIN "TimeTrackingEntries" e ON e.id = "entryId" ` +
                        `AND e."siteId" IN (${siteIdsList}) ` +
                        `AND e."deletedAt" IS NULL)`,
                ),
            );
        }

        return { [Op.and]: andConditions } as WhereOptions<TimeTrackingCategorizedHourDAO>;
    }

    /**
     * Builds the status WHERE condition. Each PENDING hour matches exactly ONE of the PENDING / IN_PROGRESS
     * filters so the UI tabs ("Pendientes" / "Procesadas") never overlap for an admin.
     *
     * Predicates over a PENDING hour:
     *   R = at least one approval step is APPROVED/REJECTED (the hour has progress)
     *   A = the logged user is a potential approver of the current PENDING step
     *   S = the current PENDING step is stuck (state='PENDING' with no potential approvers)
     *
     * - PENDING (admin)         → status='PENDING' AND (¬R OR A OR S)        — needs attention: untouched, mine, or stuck
     * - PENDING (non-admin)     → status='PENDING' AND A                     — my turn on the active step
     * - IN_PROGRESS (admin)     → status='PENDING' AND R AND ¬A AND ¬S       — moving forward, not mine right now
     * - IN_PROGRESS (non-admin) → status='PENDING' AND EXISTS(user was approver in a reviewed step)
     * - IN_PROGRESS also includes status='CANCELLED_BY_MODIFICATION' for both admin and non-admin.
     * - AUTO_APPROVED is always excluded from this list.
     * Real DB statuses (APPROVED, REJECTED, etc.) are passed through as-is.
     */
    private buildStatusCondition(
        statusFilter: TimeTrackingCategorizedHourStatus[] | undefined,
        loggedUserId: number,
        hasManageCapability: boolean,
    ): WhereOptions<TimeTrackingCategorizedHourDAO> {
        const filtered = statusFilter?.filter((s) => s !== TimeTrackingCategorizedHourStatus.AUTO_APPROVED);

        if (!filtered?.length) {
            return { status: { [Op.ne]: TimeTrackingCategorizedHourStatus.AUTO_APPROVED } };
        }

        const hasPending = filtered.includes(TimeTrackingCategorizedHourStatus.PENDING);
        const hasInProgress = filtered.includes(TimeTrackingCategorizedHourStatus.IN_PROGRESS);
        const otherStatuses = filtered.filter(
            (s) => s !== TimeTrackingCategorizedHourStatus.PENDING && s !== TimeTrackingCategorizedHourStatus.IN_PROGRESS,
        );

        if (!hasPending && !hasInProgress) {
            return { status: { [Op.in]: otherStatuses } };
        }

        const orConditions: unknown[] = [];

        if (hasPending) {
            const userIsCurrentApprover = TimeTrackingHourCategorizationAdapter.userIsApproverInApprovalStepWithState(loggedUserId, ['PENDING']);
            // Single literal so Sequelize can't split the status check from the EXISTS subqueries.
            const pendingLiteral = hasManageCapability
                ? Sequelize.literal(
                      `("TimeTrackingCategorizedHourDAO"."status" = 'PENDING' ` +
                          `AND (NOT ${TimeTrackingHourCategorizationAdapter.existsReviewedStep()} ` +
                          `OR ${userIsCurrentApprover} ` +
                          `OR ${TimeTrackingHourCategorizationAdapter.existsStuckPendingStep()}))`,
                  )
                : Sequelize.literal(`("TimeTrackingCategorizedHourDAO"."status" = 'PENDING' AND ${userIsCurrentApprover})`);
            orConditions.push(pendingLiteral);
        }

        if (hasInProgress) {
            const inProgressLiteral = hasManageCapability
                ? Sequelize.literal(
                      `("TimeTrackingCategorizedHourDAO"."status" = 'PENDING' ` +
                          `AND ${TimeTrackingHourCategorizationAdapter.existsReviewedStep()} ` +
                          `AND NOT ${TimeTrackingHourCategorizationAdapter.userIsApproverInApprovalStepWithState(loggedUserId, ['PENDING'])} ` +
                          `AND NOT ${TimeTrackingHourCategorizationAdapter.existsStuckPendingStep()})`,
                  )
                : Sequelize.literal(
                      `("TimeTrackingCategorizedHourDAO"."status" = 'PENDING' ` +
                          `AND ${TimeTrackingHourCategorizationAdapter.userIsApproverInApprovalStepWithState(loggedUserId, ['APPROVED', 'REJECTED'])})`,
                  );
            orConditions.push(inProgressLiteral);
            orConditions.push({ status: TimeTrackingCategorizedHourStatus.CANCELLED_BY_MODIFICATION });
        }

        if (otherStatuses.length > 0) {
            orConditions.push({ status: { [Op.in]: otherStatuses } });
        }

        return { [Op.or]: orConditions } as WhereOptions<TimeTrackingCategorizedHourDAO>;
    }

    /**
     * Search with user IDs requires OR in the where: match by user OR by category name.
     */
    private buildListCategorizedHoursSearchOr(
        search: string | undefined,
        searchedUserIds: number[] | undefined,
    ): WhereOptions<TimeTrackingCategorizedHourDAO>[] | undefined {
        const searchWithUserIds = search && searchedUserIds !== undefined && searchedUserIds.length > 0;
        if (!searchWithUserIds) return undefined;

        const categoryNameMatch = Sequelize.where(Sequelize.col('hourCategory.name'), { [Op.iLike]: `%${search}%` });
        return [{ userId: { [Op.in]: searchedUserIds } }, categoryNameMatch] as WhereOptions<TimeTrackingCategorizedHourDAO>[];
    }

    private buildListCategorizedHoursInclude(search: string | undefined, searchedUserIds: number[] | undefined): IncludeOptions {
        const searchOnlyCategory = search && (searchedUserIds === undefined || searchedUserIds.length === 0);
        return {
            as: 'hourCategory',
            attributes: ['id', 'name'],
            model: TimeTrackingHourCategoryDAO,
            paranoid: false,
            required: Boolean(search),
            ...(searchOnlyCategory && { where: { name: { [Op.iLike]: `%${search}%` } } }),
        };
    }

    private buildListCategorizedHoursOrder(filters: GetCategorizedHoursVC): Order {
        const sortField = filters.sortBy ? SORT_FIELD_MAP[filters.sortBy] : 'dateString';
        const sortOrder = (filters.sortOrder ?? SortOrder.ASC) === SortOrder.DESC ? 'DESC' : 'ASC';
        return [
            [sortField, sortOrder],
            ['id', 'DESC'],
        ];
    }

    /**
     * Returns a raw SQL EXISTS subquery that is true when the given user is a potential approver
     * in an approval step (for the current categorized hour row) whose state matches one of the supplied values.
     */
    private static userIsApproverInApprovalStepWithState(userId: number, stepStates: string[]): string {
        const stateCondition =
            stepStates.length === 1 ? `s.state = '${stepStates[0]}'` : `s.state IN (${stepStates.map((s) => `'${s}'`).join(', ')})`;

        return (
            `EXISTS (SELECT 1 FROM "TimeTrackingCategorizedHourApprovalSteps" s ` +
            `JOIN "TimeTrackingCategorizedHourApprovalStepPotentialApprovers" pa ON pa."approvalStepId" = s.id ` +
            `WHERE s."categorizedHourId" = "TimeTrackingCategorizedHourDAO"."id" ` +
            `AND ${stateCondition} ` +
            `AND pa."userId" = ${userId} ` +
            `AND pa."deletedAt" IS NULL ` +
            `AND s."deletedAt" IS NULL)`
        );
    }

    /**
     * Returns a raw SQL EXISTS subquery that is true when the current categorized hour row has at least one
     * approval step in APPROVED or REJECTED state — i.e. the approval flow has made progress.
     */
    private static existsReviewedStep(): string {
        return (
            `EXISTS (SELECT 1 FROM "TimeTrackingCategorizedHourApprovalSteps" s ` +
            `WHERE s."categorizedHourId" = "TimeTrackingCategorizedHourDAO"."id" ` +
            `AND s.state IN ('APPROVED', 'REJECTED') ` +
            `AND s."deletedAt" IS NULL)`
        );
    }

    /**
     * Returns a raw SQL EXISTS subquery that is true when the current categorized hour row has a PENDING
     * approval step with no potential approvers — i.e. the flow is stuck and needs admin attention.
     */
    private static existsStuckPendingStep(): string {
        return (
            `EXISTS (SELECT 1 FROM "TimeTrackingCategorizedHourApprovalSteps" s ` +
            `WHERE s."categorizedHourId" = "TimeTrackingCategorizedHourDAO"."id" ` +
            `AND s.state = 'PENDING' ` +
            `AND s."deletedAt" IS NULL ` +
            `AND NOT EXISTS (SELECT 1 FROM "TimeTrackingCategorizedHourApprovalStepPotentialApprovers" pa ` +
            `WHERE pa."approvalStepId" = s.id AND pa."deletedAt" IS NULL))`
        );
    }
}
