import {
    ENV_OPTIONAL_NUMBER_KEYS_MAPPING,
    ISO_DATE,
    OptionalLimitOffsetPagination,
    SEQUELIZE_INSTANCE_TOKEN,
    WORKDAYS,
    allPromises,
    moment,
    toWorkday,
} from '@humand-packages/common';
import { Includeable, Op, Order, Sequelize, WhereOperators, WhereOptions, col, where as seqWhere } from 'sequelize';
import { Sequelize as SequelizeInstance } from 'sequelize-typescript';
import { Inject, Service } from 'typedi';
import { BaseRepositoryAdapter } from '../../../../../dbAdapters/baseRepositoryAdapter';
import { Instance } from '../../../../instances/business/models/instance';
import { Holiday } from '../../../../regions/business/models/holiday';
import { TimeOffRequest } from '../../../../timeOff/business/models/requests/timeOffRequest';
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 { UserDAO } from '../../../../users/infrastructure/dataAccessObjects/userDAO';
import { GridFilterRequest, GridCell } from '../../../business/interfaces/shiftManagement/gridFiltersInterface';
import { TimeTrackingDaySummaryWithInstanceFilters } from '../../../business/interfaces/timeTrackingDaySummaryFilters';
import { TimeTrackingDaySummary } from '../../../business/models/daySummary';
import { TIME_FORMAT } from '../../../business/models/timeTracking/scheduleData';
import { TimeTrackingEntry, TimeTrackingEntryPrototype } from '../../../business/models/timeTrackingEntry';
import { TimeTrackingEntryType } from '../../../business/types/entry';
import { TimeTrackingEntrySource } from '../../../business/types/source';
import { TimeTrackingEmployeeStatus } from '../../../business/types/status';
import { Incidence } from '../../constants/incidences';
import { State } from '../../constants/state';
import { TimeTrackingDaySummaryCreationAttributes, TimeTrackingDaySummaryDAO } from '../../dataAccessObjects/daySummary';
import { TimeTrackingDaySummaryHolidayCreationAttributes, TimeTrackingDaySummaryHolidayDAO } from '../../dataAccessObjects/daySummaryHoliday';
import { TimeTrackingDaySummaryTimeOffCreationAttributes, TimeTrackingDaySummaryTimeOffDAO } from '../../dataAccessObjects/daySummaryTimeOff';
import { TimeTrackingEntryCreationAttributes, TimeTrackingEntryDAO } from '../../dataAccessObjects/entry';
import { TimeTrackingKioskDAO } from '../../dataAccessObjects/kiosk';
import { TimeTrackingPoliciesHistoryCreationAttributes, TimeTrackingPoliciesHistoryDAO } from '../../dataAccessObjects/policyHistory';
import { ScheduledDaysDAO } from '../../dataAccessObjects/scheduledDays';
import { TimeSlotDAO } from '../../dataAccessObjects/timeSlots';
import { WorkScheduleDAO } from '../../dataAccessObjects/workSchedule';
import { mapSummaryDaoToModel } from '../../mappers/daySummariesMapper';
import { mapDaoToModel, mapModelToDaoCreation } from '../../mappers/timeTrackingToTimeTrackingDao';
import { TimeTrackingRepositoryPort } from '../../ports/TimeTrackingRepositoryPort';

export const WORKING_TIME_LIMIT = 20;
const USER_ID_KEY = 'userId';
const updateOnDuplicate = [
    'autoCloseTime',
    'estimatedHours',
    'incidences',
    'scheduledHours',
    'state',
    'timeSlots',
    'weekday',
    'workScheduleId',
    'workedHours',
] as (keyof TimeTrackingDaySummaryDAO)[];
@Service()
export class TimeTrackingRepositoryAdapter extends BaseRepositoryAdapter implements TimeTrackingRepositoryPort {
    constructor(
        @Inject(ENV_OPTIONAL_NUMBER_KEYS_MAPPING.BULK_CREATE_CHUNK_SIZE) bulkCreateChunkSize: number,
        @Inject(SEQUELIZE_INSTANCE_TOKEN) sequelize: SequelizeInstance,
    ) {
        super(bulkCreateChunkSize, sequelize);
    }

    public createEntry = async (entry: TimeTrackingEntryPrototype): Promise<TimeTrackingEntry> => {
        const creationAttributes = mapModelToDaoCreation(entry);

        if (creationAttributes.desertEagleEntryId) {
            const [dao] = await TimeTrackingEntryDAO.findCreateFind({
                defaults: creationAttributes,
                where: {
                    [Op.or]: [
                        { desertEagleEntryId: creationAttributes.desertEagleEntryId },
                        { userId: creationAttributes.userId, time: creationAttributes.time, type: creationAttributes.type },
                    ],
                    deletedAt: null,
                },
            });
            return mapDaoToModel(dao);
        }

        if (creationAttributes.eventTime) {
            const dao = await this.upsertEntry(creationAttributes);
            return mapDaoToModel(dao);
        }

        const dao = await this.findOrCreateEntry(creationAttributes);
        return mapDaoToModel(dao);
    };

    private findOrCreateEntry = async (creationAttributes: TimeTrackingEntryCreationAttributes) => {
        const [dao] = await TimeTrackingEntryDAO.findCreateFind({
            defaults: creationAttributes,
            where: {
                ...(creationAttributes.eventTime ? { eventTime: creationAttributes.eventTime } : { time: creationAttributes.time }),
                type: creationAttributes.type,
                userId: creationAttributes.userId,
            },
        });
        return dao;
    };

    private upsertEntry = async (creationAttributes: TimeTrackingEntryCreationAttributes) => {
        const conflictFields: (keyof TimeTrackingEntryDAO)[] = [];
        if (creationAttributes.eventTime) {
            conflictFields.push('eventTime', 'type', 'userId');
        }
        const [dao] = await TimeTrackingEntryDAO.upsert(creationAttributes, {
            conflictFields,
            conflictWhere: { deletedAt: null },
            hooks: true,
            returning: true,
        });
        return dao;
    };

    public updateEntry = async (entry: Partial<TimeTrackingEntry>): Promise<TimeTrackingEntry | undefined> => {
        await TimeTrackingEntryDAO.update(
            {
                comment: entry.comment,
                createdBy: entry.createdBy,
                desertEagleEntryId: entry.desertEagleEntryId,
                facialRecognitionSkipped: entry.facialRecognitionSkipped,
                isRemoteWork: entry.isRemoteWork,
                latitude: entry.location?.latitude,
                longitude: entry.location?.longitude,
                siteId: entry.siteId,
                source: entry.source,
                time: entry.time,
                type: entry.type,
                updatedBy: entry.updatedBy,
            },
            {
                individualHooks: true,
                where: { id: entry.id },
            },
        );
        const updatedEntry = await TimeTrackingEntryDAO.findOne({
            include: [
                {
                    model: TimeTrackingKioskDAO,
                    paranoid: false,
                },
            ],
            where: { id: entry.id },
        });
        return updatedEntry ? mapDaoToModel(updatedEntry) : undefined;
    };

    public listEntries = async (
        userId: number,
        fromDate: Date,
        toDate: Date,
        pagination: OptionalLimitOffsetPagination,
        order?: Order,
    ): Promise<{ count: number; rows: TimeTrackingEntry[] }> => {
        const { count, rows } = await TimeTrackingEntryDAO.findAndCountAll({
            include: [{ model: TimeTrackingKioskDAO, paranoid: false }],
            limit: pagination.limit,
            offset: pagination.offset,
            order: order ?? [['time', 'DESC']],
            where: {
                time: { [Op.gte]: fromDate, [Op.lte]: toDate },
                userId,
            },
        });
        return { count, rows: rows.map(mapDaoToModel) };
    };

    public createBulkEntries = async (entries: TimeTrackingEntryPrototype[]): Promise<TimeTrackingEntry[]> => {
        const daos = entries.map(mapModelToDaoCreation);
        const createdDaos = await TimeTrackingEntryDAO.bulkCreate(daos, { ignoreDuplicates: true });
        return createdDaos.map(mapDaoToModel);
    };

    public getDaySummariesForDayChange = async (date: Date, fromDate: Date, limit: number): Promise<TimeTrackingDaySummary[]> => {
        const daySummaries = await TimeTrackingDaySummaryDAO.findAll({
            include: [{ model: TimeTrackingEntryDAO, paranoid: true, required: true }],
            limit,
            paranoid: false,
            where: {
                dayChange: { [Op.lte]: date, [Op.gte]: fromDate },
                nightShift: false,
                state: State.OPEN,
            },
        });
        return daySummaries.map(mapSummaryDaoToModel);
    };

    public getDaySummariesForAutoClose = async (date: Date, fromDate: Date, limit: number): Promise<TimeTrackingDaySummary[]> => {
        const daySummaries = await TimeTrackingDaySummaryDAO.findAll({
            include: [{ model: TimeTrackingEntryDAO, paranoid: true, required: true }],
            limit,
            paranoid: false,
            where: {
                autoCloseTime: { [Op.lte]: date, [Op.gte]: fromDate },
                state: State.OPEN,
            },
        });
        return daySummaries.map(mapSummaryDaoToModel);
    };

    public updateDaySummaries = async (
        daySummaries: { autoCloseTime?: Date; id: number; incidences?: Incidence[]; state: State; workedHours: number }[],
    ): Promise<void> => {
        await allPromises(daySummaries.map((summary) => () => TimeTrackingDaySummaryDAO.update(summary, { where: { id: summary.id } })));
    };

    // After use one should update day summary
    public deleteEntry = async (entry: TimeTrackingEntry): Promise<void> => {
        await TimeTrackingEntryDAO.update(
            { deletedAt: new Date(), deletedBy: entry.deletedBy },
            { individualHooks: true, where: { time: entry.time, type: entry.type, userId: entry.userId } },
        );
    };

    public getDaySummary = async (userId: number, dateString: string): Promise<TimeTrackingDaySummary | undefined> => {
        const daySummary = await TimeTrackingDaySummaryDAO.findOne({
            include: [
                { include: [{ model: TimeTrackingKioskDAO, paranoid: false }], model: TimeTrackingEntryDAO },
                { model: TimeTrackingDaySummaryHolidayDAO },
                { model: TimeTrackingDaySummaryTimeOffDAO },
                { model: TimeTrackingPoliciesHistoryDAO },
            ],
            where: { dateString, userId },
        });
        return daySummary ? mapSummaryDaoToModel(daySummary) : undefined;
    };

    public getDaySummaryById = async (instanceId: number, id: number): Promise<TimeTrackingDaySummary | undefined> => {
        const daySummary = await TimeTrackingDaySummaryDAO.findOne({
            include: [
                { include: [{ model: TimeTrackingKioskDAO, paranoid: false }], model: TimeTrackingEntryDAO },
                { model: TimeTrackingDaySummaryHolidayDAO },
                { model: TimeTrackingDaySummaryTimeOffDAO },
                { model: TimeTrackingPoliciesHistoryDAO },
            ],
            where: { id, instanceId },
        });
        return daySummary ? mapSummaryDaoToModel(daySummary) : undefined;
    };

    /**
     * @deprecated this method should not be used.
     */
    public createDaySummary = async (
        daySummary: TimeTrackingDaySummaryCreationAttributes,
        holidays: Holiday[],
        timeOffRequests: TimeOffRequest[],
    ): Promise<TimeTrackingDaySummary> => {
        const [daySummaryDao, created] = await TimeTrackingDaySummaryDAO.upsert(daySummary, {
            conflictFields: ['dateString', 'userId'],
            conflictWhere: { deletedAt: null },
            returning: true,
        });
        if (holidays.length && created) {
            daySummaryDao.holidays = await this.createHolidayAssociations(holidays, daySummaryDao);
        }
        if (timeOffRequests.length && created) {
            daySummaryDao.timeOffRequests = await this.createTimeOffAssociations(timeOffRequests, daySummaryDao);
        }
        return mapSummaryDaoToModel(daySummaryDao);
    };

    public updateDaySummary = async (id: number, daySummary: Partial<TimeTrackingDaySummaryCreationAttributes>): Promise<void> => {
        await TimeTrackingDaySummaryDAO.update({ ...daySummary }, { where: { id } });
    };

    public addTimeOffRequestToDaySummary = async (
        activityType: TIME_OFF_POLICY_TYPE_ACTIVITY_TYPES | null,
        daySummaryId: number,
        requestId: number,
        policyTypeName: string,
        policyTypeIcon: string,
        hours: number,
        consumptionType: TIME_OFF_REQUEST_CONSUMPTION_TYPES,
        fromTime?: string | null,
        toTime?: string | null,
        fromDate?: string | null,
        toDate?: string | null,
    ): Promise<void> => {
        await TimeTrackingDaySummaryTimeOffDAO.create({
            activityType,
            consumptionType,
            daySummaryId,
            fromDate: fromDate ?? null,
            fromTime: fromTime ?? null,
            hours,
            timeOffPolicyTypeIcon: policyTypeIcon,
            timeOffPolicyTypeName: policyTypeName,
            timeOffRequestId: requestId,
            toDate: toDate ?? null,
            toTime: toTime ?? null,
        });
    };

    public removeTimeOffRequestFromDaySummary = async (daySummaryId: number, timeOffRequestId: number): Promise<void> => {
        await TimeTrackingDaySummaryTimeOffDAO.destroy({ where: { daySummaryId, timeOffRequestId } });
    };

    public updateDaySummaryAfterEntryChanges = async (
        id: number,
        workedHours: number,
        incidences: Incidence[],
        state: State,
        autoCloseTime?: Date,
        autoCloseEntryTime?: Date,
    ): Promise<void> => {
        await TimeTrackingDaySummaryDAO.update({ autoCloseEntryTime, autoCloseTime, incidences, state, workedHours }, { where: { id } });
    };

    public searchUserIdsWithMissingSummaries = async (userIds: number[], fromDate: string, toDate: string): Promise<number[]> => {
        const days = moment(toDate).diff(fromDate, 'days') + 1;
        const result = await TimeTrackingDaySummaryDAO.count({
            attributes: [USER_ID_KEY],
            col: 'dateString',
            distinct: true,
            group: USER_ID_KEY,
            where: { dateString: { [Op.gte]: fromDate, [Op.lte]: toDate }, userId: userIds },
        });
        const usersWithAllSummaries = new Set<number>(result.filter((summary) => summary.count === days).map((summary) => summary.userId as number));
        return userIds.filter((userId) => !usersWithAllSummaries.has(userId));
    };

    public getDaySummariesByUser = async (
        userId: number,
        fromDate: string,
        toDate: string,
        includeEntries = true,
        includePolicyHistory = true,
    ): Promise<TimeTrackingDaySummary[]> => {
        const include: Includeable[] = [{ model: TimeTrackingDaySummaryHolidayDAO }, { model: TimeTrackingDaySummaryTimeOffDAO }];
        if (includeEntries) {
            include.push({ include: [{ model: TimeTrackingKioskDAO, paranoid: false }], as: 'timeTrackingEntries', model: TimeTrackingEntryDAO });
        }
        if (includePolicyHistory) {
            include.push({
                model: TimeTrackingPoliciesHistoryDAO,
            });
        }
        const daySummaries = await TimeTrackingDaySummaryDAO.findAll({
            include,
            order: [['date', 'ASC']],
            where: {
                dateString: {
                    [Op.gte]: fromDate,
                    [Op.lte]: toDate,
                },
                userId,
            },
        });
        return daySummaries.map(mapSummaryDaoToModel);
    };

    public createDaySummaries = async (daySummaries: Partial<TimeTrackingDaySummary>[], instance: Instance): Promise<TimeTrackingDaySummary[]> => {
        const createdSummaries = await TimeTrackingDaySummaryDAO.bulkCreate(
            daySummaries.map((summary) => {
                const entries = summary.timeTrackingEntries;
                const state = entries?.length && entries[0].type === TimeTrackingEntryType.START ? State.OPEN : State.CLOSED;
                return {
                    autoCloseEntryTime: summary.autoCloseEntryTime,
                    autoCloseMode: summary.autoCloseMode!,
                    autoCloseTime: summary.autoCloseTime,
                    date: summary.date!,
                    dateString: moment.tz(summary.date!, instance.getTimezone()).format(ISO_DATE),
                    dayChange: moment.tz(summary.date!, instance.getTimezone()).endOf('day').toDate(),
                    estimatedHours: summary.estimatedHours ?? 0,
                    incidences: summary.incidences ?? [],
                    instanceId: summary.instanceId!,
                    isWorkday: summary.isWorkday ?? false,
                    nightShift: summary.nightShift ?? false,
                    scheduleSource: summary.scheduleSource,
                    scheduledHours: summary.scheduledHours ?? summary.estimatedHours ?? 0,
                    shiftAssignedDayId: summary.shiftAssignedDayId,
                    state,
                    timeSlots: summary.timeSlots ?? null,
                    userId: summary.userId!,
                    weekday: summary.weekday ?? toWorkday(summary.date!),
                    workScheduleId: summary.workScheduleId,
                    workedHours: summary.workedHours ?? 0,
                };
            }),
            { conflictAttributes: ['dateString', 'userId'], conflictWhere: { deletedAt: null }, updateOnDuplicate },
        );
        const allSummaries: TimeTrackingDaySummary[] = [];
        const newSummaries: TimeTrackingDaySummary[] = [];
        createdSummaries.forEach((summary) => {
            const createdSummary = mapSummaryDaoToModel(summary);
            const matchingDaySummary = daySummaries.find(
                (s) => moment.tz(s.date, instance.getTimezone()).format(ISO_DATE) === summary.dateString && s.userId === summary.userId,
            );
            if (matchingDaySummary) {
                createdSummary.latenessTolerance = matchingDaySummary.latenessTolerance;
                createdSummary.createPolicyHistory = matchingDaySummary.createPolicyHistory;
            }
            allSummaries.push(createdSummary);

            if (summary.createdAt.getTime() === summary.updatedAt.getTime()) {
                newSummaries.push(createdSummary);
            }
        });

        const policiesHistoryToCreate: TimeTrackingPoliciesHistoryCreationAttributes[] = [];
        allSummaries.forEach((summary) => {
            if (summary.createPolicyHistory) {
                policiesHistoryToCreate.push({
                    dateString: summary.dateString!,
                    daySummaryId: summary.id!,
                    latenessTolerance: summary.latenessTolerance ?? null,
                    userId: summary.userId!,
                });
            }
        });

        if (policiesHistoryToCreate.length > 0) {
            await TimeTrackingPoliciesHistoryDAO.bulkCreate(policiesHistoryToCreate, {
                conflictAttributes: ['dateString', 'userId'],
                conflictWhere: { deletedAt: null },
                updateOnDuplicate: ['daySummaryId'],
            });
        }

        const updateQueries: string[] = [];
        const holidayAssociationsToCreate: TimeTrackingDaySummaryHolidayCreationAttributes[] = [];
        const timeOffAssociationsToCreate: TimeTrackingDaySummaryTimeOffCreationAttributes[] = [];
        daySummaries.forEach((summary) => {
            const createdSummary = newSummaries.find((s) => moment(s.date).isSame(summary.date, 'day'));
            if (!createdSummary) return; // continue
            if (summary.holidays && summary.holidays.length !== 0) {
                createdSummary.holidays = summary.holidays;
                summary.holidays.forEach((temp) =>
                    holidayAssociationsToCreate.push({ daySummaryId: createdSummary.id, holidayId: temp.holidayId, holidayName: temp.holidayName }),
                );
            }
            if (summary.timeOffRequests && summary.timeOffRequests.length !== 0) {
                createdSummary.timeOffRequests = summary.timeOffRequests;
                summary.timeOffRequests.forEach((temp) =>
                    timeOffAssociationsToCreate.push({
                        activityType: temp.activityType,
                        consumptionType: temp.consumptionType,
                        daySummaryId: createdSummary.id,
                        fromDate: temp.fromDate,
                        fromTime: temp.fromTime,
                        hours: temp.hours,
                        timeOffPolicyTypeIcon: temp.timeOffPolicyTypeIcon,
                        timeOffPolicyTypeName: temp.timeOffPolicyTypeName,
                        timeOffRequestId: temp.timeOffRequestId,
                        toDate: temp.toDate,
                        toTime: temp.toTime,
                    }),
                );
            }
            if (summary.timeTrackingEntries && summary.timeTrackingEntries.length !== 0) {
                createdSummary.timeTrackingEntries = summary.timeTrackingEntries;
                const ids = summary.timeTrackingEntries.map((entry) => entry.id).join(', ');
                updateQueries.push(
                    `UPDATE "TimeTrackingEntries" SET "summaryId" = ${createdSummary.id}, "updatedAt" = NOW() WHERE "id" IN (${ids});`,
                );
            }
        });
        if (holidayAssociationsToCreate.length) {
            await TimeTrackingDaySummaryHolidayDAO.bulkCreate(holidayAssociationsToCreate, { ignoreDuplicates: true });
        }
        if (timeOffAssociationsToCreate.length) {
            await TimeTrackingDaySummaryTimeOffDAO.bulkCreate(timeOffAssociationsToCreate, { ignoreDuplicates: true });
        }
        if (updateQueries.length) {
            await this.sequelize.query(updateQueries.join('\n'));
        }
        return allSummaries;
    };

    public getDaySummariesByInstanceAndDate = async (instanceId: number, date: string): Promise<TimeTrackingDaySummary[]> => {
        const daySummaries = await TimeTrackingDaySummaryDAO.findAll({
            include: [{ model: TimeTrackingEntryDAO }, { model: TimeTrackingDaySummaryHolidayDAO }, { model: TimeTrackingDaySummaryTimeOffDAO }],
            where: { dateString: date, instanceId },
        });
        return daySummaries.map(mapSummaryDaoToModel);
    };

    public getDaySummariesForUsersOnDates = async (userIds: number[], dateStrings: string[]): Promise<TimeTrackingDaySummary[]> => {
        if (userIds.length === 0 || dateStrings.length === 0) return [];
        const daySummaries = await TimeTrackingDaySummaryDAO.findAll({
            include: [{ model: TimeTrackingEntryDAO }, { model: TimeTrackingDaySummaryHolidayDAO }, { model: TimeTrackingDaySummaryTimeOffDAO }],
            where: {
                dateString: { [Op.in]: dateStrings },
                userId: { [Op.in]: userIds },
            },
        });
        return daySummaries.map(mapSummaryDaoToModel);
    };

    public addHolidayToDaySummary = async (daySummaryId: number, holidayId: number, holidayName: string) => {
        await TimeTrackingDaySummaryHolidayDAO.create({
            daySummaryId,
            holidayId: holidayId,
            holidayName: holidayName,
        });
    };

    public removeHolidayFromDaySummary = async (daySummaryId: number, holidayId: number) => {
        await TimeTrackingDaySummaryHolidayDAO.destroy({ where: { daySummaryId, holidayId } });
    };

    public deleteDaySummariesForUsers = async (userIds: number[], fromDate: string, endDate?: string): Promise<void> => {
        const dateCondition = endDate ? { [Op.and]: [{ [Op.lte]: endDate }, { [Op.gte]: fromDate }] } : { [Op.gte]: fromDate };
        await TimeTrackingDaySummaryDAO.destroy({ where: { dateString: dateCondition, userId: userIds } });
    };

    public searchUserIdsByDaySummaries = async (filters: TimeTrackingDaySummaryWithInstanceFilters): Promise<number[]> => {
        const where: WhereOptions<TimeTrackingDaySummaryDAO> = {
            dateString: { [Op.gte]: filters.fromDate, [Op.lte]: filters.toDate },
            instanceId: filters.instanceId,
        };
        const today = moment.tz(filters.timezone);
        const todayString = today.format(ISO_DATE);
        const currentTime = today.format(TIME_FORMAT);
        const weekday = today.format('dddd').toUpperCase() as WORKDAYS;

        let userIds = filters.userIds;

        //Incidence filters
        if (userIds.length && (filters.hasTimeOff || filters.hasHolidays || filters.incidences?.length)) {
            const incidenceConditions: WhereOptions = [];
            const includeable: Includeable[] = [];
            if (filters.hasTimeOff) {
                includeable.push({ as: 'timeOffRequests', attributes: [], model: TimeTrackingDaySummaryTimeOffDAO, required: false });
                incidenceConditions.push({ '$timeOffRequests.id$': { [Op.not]: null } });
            }
            if (filters.hasHolidays) {
                includeable.push({ as: 'holidays', attributes: [], model: TimeTrackingDaySummaryHolidayDAO, required: false });
                incidenceConditions.push({ '$holidays.id$': { [Op.not]: null } });
            }
            if (filters.incidences?.length && filters.incidences.includes(Incidence.AUTO_CLOSE)) {
                includeable.push({ as: 'timeTrackingEntries', attributes: [], model: TimeTrackingEntryDAO, required: false });
                incidenceConditions.push({ '$timeTrackingEntries.source$': { [Op.eq]: TimeTrackingEntrySource.AUTO_CLOSE } });
                filters.incidences = filters.incidences.filter((incidence) => incidence !== Incidence.AUTO_CLOSE);
            }
            if (filters.incidences?.length && filters.incidences.includes(Incidence.EXTRA_HOURS)) {
                incidenceConditions.push({ estimatedHours: { [Op.gt]: 0 }, workedHours: { [Op.gt]: Sequelize.col('estimatedHours') } });
                filters.incidences = filters.incidences.filter((incidence) => incidence !== Incidence.EXTRA_HOURS);
            }
            if (filters.incidences?.length) {
                incidenceConditions.push(
                    ...filters.incidences.map((incidence) => ({
                        ...(incidence === Incidence.ABSENT ? { dateString: { [Op.lt]: todayString } } : {}),
                        incidences: { [Op.contains]: [incidence] },
                    })),
                );
            }

            const daySummaries = await TimeTrackingDaySummaryDAO.findAll({
                attributes: [[col('TimeTrackingDaySummaryDAO.userId'), 'userId']],
                group: [col('TimeTrackingDaySummaryDAO.userId')],
                include: includeable,
                raw: true,
                where: {
                    ...where,
                    [Op.and]: [seqWhere(col('TimeTrackingDaySummaryDAO.userId'), { [Op.in]: userIds })],
                    [Op.or]: incidenceConditions,
                },
            });
            userIds = daySummaries.map((summary) => summary.userId);
        }

        //Status filters
        if (userIds.length && filters.status?.length) {
            const includeable: Includeable[] = [];
            const currentTimeSlotsIncludeable: Includeable = this.currentTimeSlotsIncludeable(currentTime, weekday);
            const askedUnscheduled = filters.status.includes(TimeTrackingEmployeeStatus.UNSCHEDULED);
            if (askedUnscheduled && !filters.status.includes(TimeTrackingEmployeeStatus.INACTIVE)) {
                filters.status.push(TimeTrackingEmployeeStatus.INACTIVE);
            }
            const inactive = filters.status.includes(TimeTrackingEmployeeStatus.INACTIVE);
            const notClockedIn = filters.status.includes(TimeTrackingEmployeeStatus.NOT_CLOCKED_IN);

            if (notClockedIn || inactive) {
                includeable.push(currentTimeSlotsIncludeable);
                includeable.push({ as: 'timeTrackingEntries', attributes: [], model: TimeTrackingEntryDAO, required: false });
            }

            const stateConditions = [
                ...(filters.status.includes(TimeTrackingEmployeeStatus.ACTIVE) ? [{ state: State.OPEN }] : []),
                ...(inactive
                    ? [
                          { '$timeTrackingEntries.id$': { [Op.not]: null }, isWorkday: true, state: State.CLOSED },
                          { isWorkday: false, state: State.CLOSED },
                          {
                              '$timeTrackingEntries.id$': { [Op.is]: null },
                              '$workSchedule.scheduledDays.timeSlots.id$': { [Op.is]: null },
                              isWorkday: true,
                              state: State.CLOSED,
                              timeSlots: { [Op.not]: null },
                          },
                      ]
                    : []),
                ...(notClockedIn
                    ? [
                          {
                              '$timeTrackingEntries.id$': { [Op.is]: null },
                              isWorkday: true,
                              state: State.CLOSED,
                              [Op.or]: [
                                  { timeSlots: { [Op.is]: null } },
                                  { '$workSchedule.scheduledDays.timeSlots.id$': { [Op.not]: null }, timeSlots: { [Op.not]: null } },
                              ],
                          },
                      ]
                    : []),
            ];
            const daySummaries = await TimeTrackingDaySummaryDAO.findAll({
                attributes: [[col('TimeTrackingDaySummaryDAO.userId'), 'userId']],
                group: [col('TimeTrackingDaySummaryDAO.userId')],
                include: includeable,
                raw: true,
                where: { ...where, [Op.and]: [seqWhere(col('TimeTrackingDaySummaryDAO.userId'), { [Op.in]: userIds })], [Op.or]: stateConditions },
            });
            userIds = daySummaries.map((summary) => summary.userId);
        }
        return userIds;
    };

    public getEntriesOfInstance = async (
        instanceId: number,
        fromDate: Date,
        limit: number,
    ): Promise<{ count: number; rows: TimeTrackingEntry[] }> => {
        const { count, rows } = await TimeTrackingEntryDAO.findAndCountAll({
            include: [{ model: UserDAO, where: { instanceId } }],
            limit,
            order: [['time', 'ASC']],
            where: { time: { [Op.gte]: fromDate } },
        });
        return { count, rows: rows.map(mapDaoToModel) };
    };

    public deleteDaySummariesForInstance = async (instanceId: number, fromDate: string): Promise<void> => {
        await TimeTrackingDaySummaryDAO.destroy({ where: { dateString: { [Op.gte]: fromDate }, instanceId } });
    };

    private currentTimeSlotsIncludeable(currentTime: string, weekday: WORKDAYS): Includeable {
        return {
            as: 'workSchedule',
            attributes: [],
            include: [
                {
                    as: 'scheduledDays',
                    attributes: [],
                    include: [
                        {
                            as: 'timeSlots',
                            attributes: [],
                            model: TimeSlotDAO,
                            required: false,
                            where: { endTime: { [Op.gte]: currentTime }, startTime: { [Op.lte]: currentTime } },
                        },
                    ],
                    model: ScheduledDaysDAO,
                    required: false,
                    where: { dayOfWeek: weekday },
                },
            ],
            model: WorkScheduleDAO,
            required: false,
        };
    }

    private async createHolidayAssociations(holidays: Holiday[], daySummary: TimeTrackingDaySummaryDAO) {
        return await TimeTrackingDaySummaryHolidayDAO.bulkCreate(
            holidays
                .filter((holiday) => holiday.getId())
                .map((holiday) => ({
                    daySummaryId: daySummary.id,
                    holidayId: holiday.getId()!,
                    holidayName: holiday.getName(),
                })),
            { ignoreDuplicates: true },
        );
    }

    /**
     * @deprecated this method should not be used.
     */
    private async createTimeOffAssociations(requests: TimeOffRequest[], daySummary: TimeTrackingDaySummaryDAO) {
        return await TimeTrackingDaySummaryTimeOffDAO.bulkCreate(
            requests.map((request) => ({
                daySummaryId: daySummary.id,
                timeOffPolicyTypeName: request.policyType!.name,
                timeOffRequestId: request.id,
            })),
            { ignoreDuplicates: true },
        );
    }

    public updateEntriesSummaryId = async (summaryId: number, entryIds: number[]): Promise<TimeTrackingEntry[]> => {
        const [_, updatedRows] = await TimeTrackingEntryDAO.update(
            { summaryId },
            {
                returning: true,
                where: {
                    id: entryIds,
                },
            },
        );
        return updatedRows.map(mapDaoToModel);
    };

    public filterUsersBySites = async (
        instanceId: number,
        userIds: number[],
        fromDate: string,
        toDate: string,
        siteIds?: number[],
        excludeSiteIds?: number[],
    ): Promise<number[]> => {
        if (!(siteIds?.length || excludeSiteIds?.length)) {
            return userIds;
        }
        const daySummaryWhere: WhereOptions<TimeTrackingDaySummaryDAO> = {
            dateString: { [Op.gte]: fromDate, [Op.lte]: toDate },
            instanceId: instanceId,
        };

        const siteIdsWhere: WhereOperators<number> = {};
        if (siteIds?.length) {
            siteIdsWhere[Op.in] = siteIds;
        }
        if (excludeSiteIds?.length) {
            siteIdsWhere[Op.notIn] = excludeSiteIds;
        }

        const entries = await TimeTrackingEntryDAO.findAll({
            attributes: ['userId'],
            group: ['TimeTrackingEntryDAO.userId'],
            include: { attributes: [], model: TimeTrackingDaySummaryDAO, required: true, where: daySummaryWhere },
            where: { siteId: siteIdsWhere, userId: userIds },
        });
        return entries.map((entry) => entry.userId);
    };

    public getTimeTrackingPolicyHistoryLatenessTolerance = async (
        userId: number,
        fromDate: string,
        toDate: string,
    ): Promise<Array<{ dateString: string; latenessTolerance: number | null }>> => {
        const rows = await TimeTrackingPoliciesHistoryDAO.findAll({
            attributes: ['dateString', 'latenessTolerance'],
            order: [['dateString', 'DESC']],
            where: {
                dateString: {
                    [Op.gte]: fromDate,
                    [Op.lte]: toDate,
                },
                userId,
            },
        });
        return rows.map((row) => ({
            dateString: row.dateString,
            latenessTolerance: row.latenessTolerance,
        }));
    };

    public createTimeTrackingPolicyHistory = async (userId: number, date: string, daySummaryId: number, latenessTolerance: number) => {
        await TimeTrackingPoliciesHistoryDAO.create({
            dateString: date,
            daySummaryId: daySummaryId,
            latenessTolerance: latenessTolerance,
            userId: userId,
        });
    };

    public getDaySummariesByUsers = async (userIds: number[], fromDate: string, toDate: string): Promise<TimeTrackingDaySummary[]> => {
        const include: Includeable[] = [{ model: TimeTrackingDaySummaryHolidayDAO }, { model: TimeTrackingDaySummaryTimeOffDAO }];
        const daySummaries = await TimeTrackingDaySummaryDAO.findAll({
            include,
            order: [['date', 'ASC']],
            where: {
                dateString: {
                    [Op.gte]: fromDate,
                    [Op.lte]: toDate,
                },
                userId: userIds,
            },
        });
        return daySummaries.map(mapSummaryDaoToModel);
    };

    public deleteDaySummariesByShiftAssignedDayId = async (instanceId: number, shiftAssignedDayId: number): Promise<void> => {
        await TimeTrackingDaySummaryDAO.destroy({
            where: {
                instanceId,
                shiftAssignedDayId,
            },
        });
    };

    public deleteDaySummariesByGridFilters = async (instanceId: number, filters: GridFilterRequest): Promise<void> => {
        const { userIds, dates, startDate, endDate, cells, excludeCells } = filters;

        const hasBulkFilters = userIds?.length && (dates?.length || (startDate && endDate));
        const hasCellFilters = cells?.length;
        const hasExcludeCellFilters = excludeCells?.length;
        const hasSpecificDatesFilter = !!dates?.length;
        const hasRangeDateFilter = !!startDate && !!endDate;
        if (!(hasBulkFilters || hasCellFilters)) {
            return;
        }

        const includeClauses: WhereOptions[] = [];
        if (hasBulkFilters) {
            if (hasSpecificDatesFilter) {
                includeClauses.push(this.getGridBulkSpecificDatesWhere(userIds, dates));
            } else if (hasRangeDateFilter) {
                includeClauses.push(this.getGridBulkRangeDatesWhere(userIds, startDate, endDate));
            }
        }
        if (hasCellFilters) {
            includeClauses.push(...this.getGridCellWhere(cells));
        }

        const where: WhereOptions = {
            instanceId,
            [Op.or]: includeClauses,
            ...(hasExcludeCellFilters ? { [Op.not]: this.getGridExcludeCellWhere(excludeCells) } : {}),
        };
        await TimeTrackingDaySummaryDAO.destroy({ where });
    };

    public getDaySummariesToCorrectByGridFilters = async (filters: GridFilterRequest, limit: number): Promise<TimeTrackingDaySummary[]> => {
        const { userIds, dates, startDate, endDate, cells, excludeCells } = filters;

        const hasBulkFilters = userIds?.length && (dates?.length || (startDate && endDate));
        const hasCellFilters = cells?.length;
        const hasExcludeCellFilters = excludeCells?.length;
        const hasSpecificDatesFilter = !!dates?.length;
        const hasRangeDateFilter = !!startDate && !!endDate;
        if (!(hasBulkFilters || hasCellFilters)) {
            return [];
        }

        const includeClauses: WhereOptions[] = [];
        if (hasBulkFilters) {
            if (hasSpecificDatesFilter) {
                includeClauses.push(this.getGridBulkSpecificDatesWhere(userIds, dates));
            } else if (hasRangeDateFilter) {
                includeClauses.push(this.getGridBulkRangeDatesWhere(userIds, startDate, endDate));
            }
        }
        if (hasCellFilters) {
            includeClauses.push(...this.getGridCellWhere(cells));
        }

        if (!includeClauses.length) return [];

        const where: WhereOptions = {
            [Op.or]: includeClauses,
            ...(hasExcludeCellFilters ? { [Op.not]: this.getGridExcludeCellWhere(excludeCells) } : {}),
        };
        const daySummaries = await TimeTrackingDaySummaryDAO.findAll({
            include: [{ model: TimeTrackingEntryDAO, paranoid: true, required: true }],
            limit,
            paranoid: false,
            where,
        });
        return daySummaries.map(mapSummaryDaoToModel);
    };

    public bulkDeleteEntries = async (instanceId: number, entryIds: number[]): Promise<void> => {
        await TimeTrackingEntryDAO.destroy({ where: { id: entryIds, instanceId } });
    };

    private getGridBulkSpecificDatesWhere = (userIds: number[], dates: string[]): WhereOptions => ({
        dateString: dates,
        userId: userIds,
    });

    private getGridBulkRangeDatesWhere = (userIds: number[], startDate: string, endDate: string): WhereOptions => ({
        dateString: {
            [Op.between]: [startDate, endDate],
        },
        userId: userIds,
    });

    private getGridCellWhere = (cells: GridCell[]): WhereOptions[] =>
        cells.map((cell) => ({
            dateString: cell.date,
            userId: cell.userId,
        }));

    private getGridExcludeCellWhere = (excludeCells: GridCell[]): WhereOptions[] =>
        excludeCells.map((cell) => ({
            [Op.and]: [{ userId: cell.userId }, { dateString: cell.date }],
        }));
}
