import {
    ENV_OPTIONAL_NUMBER_KEYS_MAPPING,
    ErrorCodes,
    ISO_DATE,
    lodash,
    NotFoundError,
    OrderItem,
    SEQUELIZE_INSTANCE_TOKEN,
    WORKDAYS,
} from '@humand-packages/common';
import moment from 'moment';
import { Includeable, Op, QueryTypes, WhereOperators, WhereOptions } from 'sequelize';
import { Sequelize } from 'sequelize-typescript';
import { Inject, Service } from 'typedi';
import { BaseRepositoryAdapter } from '../../../../../dbAdapters/baseRepositoryAdapter';
import { ScheduleCodeFilter, ScheduleFilter } from '../../../business/interfaces/timeTrackingDaySummaryFilters';
import { WorkScheduleUserAssignmentAttributes } from '../../../business/interfaces/WorkScheduleUserAssignmentAttributes';
import {
    WorkScheduleCreationAttributes,
    WorkSchedule,
    ScheduledDays,
    AssignedWorkSchedule,
    WorkScheduleSimpleUpdateAttributes,
    WorkScheduleUserAssignment,
    WorkScheduleUser,
} from '../../../business/models/workSchedule';
import { WorkSchedulesSearchFilters } from '../../../business/models/workScheduleSearchFilters';
import { WORK_SCHEDULE_ASSIGNMENT_TYPE } from '../../constants/workScheduleUserAssignment';
import { ScheduledDaysDAO } from '../../dataAccessObjects/scheduledDays';
import { TimeSlotDAO } from '../../dataAccessObjects/timeSlots';
import { WorkScheduleDAO } from '../../dataAccessObjects/workSchedule';
import { WorkScheduleUsersDAO, WorkScheduleUsersDAOCreationAttributes } from '../../dataAccessObjects/workScheduleUsers';
import {
    convertDAOToAssignedWorkSchedule,
    convertDAOToWorkSchedule,
    mapDAOsToWorkSchedule,
    mapUserAssigmentWorkSchedule,
    mapWorkScheduleUserDAOToUserAssigmentWorkSchedule,
    mapWorkScheduleUserDAOToWorkScheduleUser,
} from '../../mappers/workScheduleMapper';
import { WorkScheduleRepositoryPort } from '../../ports/WorkScheduleRepositoryPort';

const infinity = '9999-12-31';
const USER_ID_KEY = 'userId';

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

    public createSchedule = async (params: WorkScheduleCreationAttributes): Promise<WorkSchedule> => {
        return await this.getTransaction(async () => {
            const createdSchedule = await this.createWorkSchedule(params);
            const createdDays = await this.createScheduledDays(params.scheduledDays, createdSchedule.id);
            const createdSlots = await this.createTimeSlots(params.scheduledDays, createdDays);
            return mapDAOsToWorkSchedule(createdSchedule, createdDays, createdSlots);
        });
    };

    public listWorkSchedules = async (
        params: WorkSchedulesSearchFilters,
        pagination: { limit: number; offset: number },
        order?: OrderItem[],
    ): Promise<{ count: number; rows: WorkSchedule[] }> => {
        const whereClause: WhereOptions<WorkScheduleDAO> = {};
        let paranoid = !params.includeDeleted;
        if (params.ids?.length) {
            whereClause.id = params.ids;
            paranoid = false;
        }
        if (params.codes?.length) {
            whereClause.code = params.codes;
            paranoid = false;
        } else if (paranoid) {
            whereClause.lastVersion = true;
        }
        if (params.autoCreated != undefined) whereClause.autoCreated = params.autoCreated;
        if (params.name) whereClause.name = { [Op.iLike]: `%${params.name}%` };
        if (params.description) whereClause.description = { [Op.iLike]: `%${params.description}%` };
        const includes = this.includeScheduledDaysAndTimeSlots();
        const { count, rows } = await WorkScheduleDAO.scope({ method: ['ofInstance', params.instanceId] }).findAndCountAll({
            distinct: true,
            include: includes,
            limit: pagination.limit,
            offset: pagination.offset,
            order: order || [['createdAt', 'DESC']],
            paranoid,
            where: whereClause,
        });
        return { count, rows: rows.map(convertDAOToWorkSchedule) };
    };

    public getWorkSchedule = async (
        id: number,
        instanceId: number,
        timezone: string,
        includeCollaborators = false,
        excludeDeleted = true,
    ): Promise<WorkSchedule> => {
        const now = moment.tz(timezone).format(ISO_DATE);
        const includes = this.includeScheduledDaysAndTimeSlots();
        if (includeCollaborators) {
            includes.push({
                model: WorkScheduleUsersDAO,
                required: false,
                where: { endDateString: { [Op.gte]: now }, startDateString: { [Op.lte]: now } },
            });
        }
        const schedule = await WorkScheduleDAO.scope({ method: ['ofInstance', instanceId] }).findByPk(id, {
            include: includes,
            paranoid: excludeDeleted,
        });
        if (!schedule) {
            throw new NotFoundError('Schedule not found', ErrorCodes.NOT_FOUND);
        }
        return convertDAOToWorkSchedule(schedule);
    };

    public getWorkScheduleByUser = async (userId: number, instanceId: number, timezone: string): Promise<WorkSchedule | undefined> => {
        const now = moment.tz(timezone).format(ISO_DATE);
        const schedule = await WorkScheduleDAO.scope({ method: ['ofInstance', instanceId] }).findOne({
            include: [
                {
                    attributes: [],
                    model: WorkScheduleUsersDAO,
                    required: true,
                    where: { endDateString: { [Op.gte]: now }, startDateString: { [Op.lte]: now }, userId },
                },
                ...this.includeScheduledDaysAndTimeSlots(),
            ],
        });
        return schedule ? convertDAOToWorkSchedule(schedule) : undefined;
    };

    public getWorkSchedulesByUsers = async (userIds: number[], instanceId: number, timezone: string): Promise<AssignedWorkSchedule[]> => {
        const now = moment.tz(timezone).format(ISO_DATE);
        const workScheduleUsers = await WorkScheduleUsersDAO.findAll({
            include: [
                {
                    include: this.includeScheduledDaysAndTimeSlots(),
                    model: WorkScheduleDAO,
                    required: true,
                    where: { instanceId },
                },
            ],
            where: { endDateString: { [Op.gte]: now }, startDateString: { [Op.lte]: now }, userId: userIds },
        });
        return workScheduleUsers.map((workScheduleUser) => convertDAOToAssignedWorkSchedule(workScheduleUser.workSchedule, workScheduleUser));
    };

    public getAssignedWorkSchedulesByUser = async (
        userId: number,
        instanceId: number,
        fromDate: string,
        toDate: string,
    ): Promise<AssignedWorkSchedule[]> => {
        const schedules = await WorkScheduleDAO.scope({ method: ['ofInstance', instanceId] }).findAll({
            include: [
                {
                    model: WorkScheduleUsersDAO,
                    required: true,
                    where: {
                        endDateString: { [Op.gte]: fromDate },
                        startDateString: { [Op.lte]: toDate },
                        userId,
                    },
                },
                ...this.includeScheduledDaysAndTimeSlots(),
            ],
            paranoid: false,
        });
        return schedules.flatMap((schedule) => {
            const assignments = schedule.workScheduleUsers?.filter((association) => association.userId === userId);
            if (!assignments?.length) {
                throw new Error('User is not assigned to this schedule');
            }
            return assignments.map((assignment) => convertDAOToAssignedWorkSchedule(schedule, assignment));
        });
    };

    public getAssignedWorkSchedulesBySchedule = async (
        id: number,
        instanceId: number,
        pagination: { limit: number; offset: number },
    ): Promise<{ count: number; rows: AssignedWorkSchedule[] }> => {
        const { count, rows: assignments } = await WorkScheduleUsersDAO.findAndCountAll({
            include: [{ model: WorkScheduleDAO, paranoid: false, required: true, where: { id, instanceId } }],
            limit: pagination.limit,
            offset: pagination.offset,
        });
        return {
            count,
            rows: assignments.map((assignment) => {
                return convertDAOToAssignedWorkSchedule(assignment.workSchedule, assignment);
            }),
        };
    };

    public eliminateWorkSchedule = async (id: number, newScheduleId?: number): Promise<number[]> => {
        return await this.getTransaction(async () => {
            const today = moment.tz('UTC').startOf('day');
            const todayString = today.format(ISO_DATE);
            const deleteValues: Partial<WorkScheduleDAO> = { deletedAt: today.toDate() };
            if (newScheduleId) {
                deleteValues.lastVersion = false;
            }
            const affectedUsers = new Set<number>();
            const [destroyedCount] = await WorkScheduleDAO.update(deleteValues, { where: { id } });
            if (destroyedCount) {
                if (newScheduleId) {
                    const ongoingAssignments = await WorkScheduleUsersDAO.findAll({
                        where: { endDateString: { [Op.gte]: todayString }, startDateString: { [Op.lte]: todayString }, workScheduleId: id },
                    });
                    ongoingAssignments.forEach((mod) => affectedUsers.add(mod.userId));
                    const dayBefore = today.clone().subtract(1, 'day');
                    for (const assignment of ongoingAssignments) {
                        // create new one
                        await WorkScheduleUsersDAO.create({
                            current: true,
                            endDate: assignment.endDate,
                            endDateString: assignment.endDateString,
                            startDate: today.toDate(),
                            startDateString: todayString,
                            userId: assignment.userId,
                            workScheduleId: newScheduleId,
                        });
                        // end the old one
                        assignment.endDate = dayBefore.toDate();
                        assignment.endDateString = dayBefore.format(ISO_DATE);
                        assignment.current = false;
                        await assignment.save();
                        affectedUsers.add(assignment.userId);
                    }
                    const futureAssignments = await WorkScheduleUsersDAO.findAll({
                        where: { endDateString: { [Op.gt]: todayString }, startDateString: { [Op.gt]: todayString }, workScheduleId: id },
                    });
                    futureAssignments.forEach((mod) => affectedUsers.add(mod.userId));
                    for (const assignment of futureAssignments) {
                        assignment.workScheduleId = newScheduleId;
                        await assignment.save();
                        affectedUsers.add(assignment.userId);
                    }
                } else {
                    const [_, modified] = await WorkScheduleUsersDAO.update(
                        { current: false, endDate: today.toDate(), endDateString: todayString },
                        { returning: true, where: { endDateString: { [Op.gte]: todayString }, workScheduleId: id } },
                    );
                    modified.forEach((mod) => affectedUsers.add(mod.userId));
                }
                await WorkScheduleUsersDAO.update(
                    { deletedAt: today.toDate() },
                    { where: { endDateString: { [Op.lt]: Sequelize.col('startDateString') }, workScheduleId: id } },
                );
            }
            return Array.from(affectedUsers);
        });
    };

    public updateWorkSchedule = async (id: number, newParameters: WorkScheduleSimpleUpdateAttributes): Promise<WorkSchedule> => {
        const [_, updatedRows] = await WorkScheduleDAO.update(
            {
                description: newParameters.description,
                name: newParameters.name,
            },
            { returning: true, where: { id: id } },
        );
        return convertDAOToWorkSchedule(updatedRows[0]);
    };

    public assignUsers = async (params: WorkScheduleUserAssignmentAttributes, timezone: string) => {
        if (params.collaborators.length === 0) {
            return;
        }
        return this.getTransaction(async () => {
            // Dates are deprecated, but we need to keep them for the moment, so just use utc
            const startDate = moment.tz(params.startDate, timezone).toDate();
            const endDate = params.endDate ? moment.tz(params.endDate, timezone).toDate() : null;
            const usersToAssign = params.collaborators;
            const endDateForFiltering = params.endDate || infinity;
            const dateBeforeStart = moment.tz(params.startDate, timezone).subtract(1, 'day');
            const dateAfterEnd = params.endDate ? moment.tz(params.endDate, timezone).add(1, 'day') : moment.tz(infinity, 'UTC');

            // Get already existing associations within the date range, ONLY those that have an end date
            const existingAssignments = await WorkScheduleUsersDAO.findAll({
                where: {
                    userId: usersToAssign,
                    workScheduleId: params.id,
                    [Op.or]: [
                        { startDateString: { [Op.between]: [params.startDate, endDateForFiltering] } },
                        { endDateString: { [Op.between]: [params.startDate, endDateForFiltering] } },
                        {
                            endDateString: { [Op.gte]: endDateForFiltering },
                            startDateString: { [Op.lte]: params.startDate },
                        },
                    ],
                },
            });
            // Update start and end dates of existing associations
            const alreadyAssignedUsers = new Map<number, WorkScheduleUsersDAO>();
            for (const assignment of existingAssignments) {
                let currentAssignment = assignment;
                if (alreadyAssignedUsers.has(assignment.userId)) {
                    // Remove duplicates that would be created by editing more than one assignment at a time
                    currentAssignment = alreadyAssignedUsers.get(assignment.userId)!;
                    await assignment.destroy();
                }
                if (currentAssignment.startDateString > params.startDate) {
                    currentAssignment.startDate = startDate;
                    currentAssignment.startDateString = params.startDate;
                }
                if (currentAssignment.endDateString && params.endDate && currentAssignment.endDateString < params.endDate) {
                    currentAssignment.endDate = endDate;
                    currentAssignment.endDateString = params.endDate;
                } else if (assignment.endDateString && !params.endDate) {
                    currentAssignment.endDate = null;
                    currentAssignment.endDateString = infinity;
                }
                currentAssignment.current = moment
                    .tz(timezone)
                    .isBetween(currentAssignment.startDateString, currentAssignment.endDateString, 'day', '[]');
                await currentAssignment.save();
                alreadyAssignedUsers.set(assignment.userId, currentAssignment);
            }
            const current = moment.tz(timezone).isBetween(params.startDate, endDateForFiltering, 'day', '[]');
            const associationsToCreate = usersToAssign
                .filter((userId) => !alreadyAssignedUsers.has(userId))
                .map((userId) => ({
                    current,
                    endDate,
                    endDateString: endDateForFiltering,
                    startDate,
                    startDateString: params.startDate,
                    userId,
                    workScheduleId: params.id,
                }));

            // Update any schedules of users that would end during the new associations. This also cleans identical other schedule associations
            await WorkScheduleUsersDAO.update(
                { endDate: dateBeforeStart.toDate(), endDateString: dateBeforeStart.format(ISO_DATE) },
                {
                    where: {
                        endDateString: { [Op.lte]: endDateForFiltering, [Op.gte]: params.startDate },
                        startDateString: { [Op.lte]: endDateForFiltering },
                        userId: usersToAssign,
                        workScheduleId: { [Op.not]: params.id },
                    },
                },
            );

            if (!!params.endDate) {
                // Update any schedules that would start during the new associations
                await WorkScheduleUsersDAO.update(
                    { startDate: dateAfterEnd.toDate(), startDateString: dateAfterEnd.format(ISO_DATE) },
                    {
                        where: {
                            endDateString: { [Op.gte]: params.endDate },
                            startDateString: { [Op.lte]: params.endDate, [Op.gte]: params.startDate },
                            userId: usersToAssign,
                            workScheduleId: { [Op.not]: params.id },
                        },
                    },
                );

                // Split any schedules that would completely cover the new associations
                const overlappingSchedules = await WorkScheduleUsersDAO.findAll({
                    where: {
                        endDateString: { [Op.gt]: params.endDate },
                        startDateString: { [Op.lt]: params.startDate },
                        userId: usersToAssign,
                        workScheduleId: { [Op.not]: params.id },
                    },
                });
                overlappingSchedules.forEach((schedule) =>
                    associationsToCreate.push({
                        current: false,
                        endDate: schedule.endDate,
                        endDateString: schedule.endDateString,
                        startDate: dateAfterEnd.toDate(),
                        startDateString: dateAfterEnd.format(ISO_DATE),
                        userId: schedule.userId,
                        workScheduleId: schedule.workScheduleId,
                    }),
                );
                await WorkScheduleUsersDAO.update(
                    { endDate: dateBeforeStart.toDate(), endDateString: dateBeforeStart.format(ISO_DATE) },
                    {
                        where: {
                            endDateString: { [Op.gt]: endDateForFiltering },
                            startDateString: { [Op.lt]: params.startDate },
                            userId: usersToAssign,
                            workScheduleId: { [Op.not]: params.id },
                        },
                    },
                );
            }

            // Create new associations
            if (associationsToCreate.length) await WorkScheduleUsersDAO.bulkCreate(associationsToCreate);

            // Delete now invalid associations
            await WorkScheduleUsersDAO.update(
                { deletedAt: new Date() },
                { where: { endDateString: { [Op.lt]: Sequelize.col('startDateString') }, userId: { [Op.in]: usersToAssign } } },
            );
        });
    };

    public listSimilarAutoCreatedWorkSchedules = async (instanceId: number, hashes: string[]): Promise<WorkSchedule[]> => {
        const schedules = await WorkScheduleDAO.scope({ method: ['ofInstance', instanceId] }).findAll({
            include: [...this.includeScheduledDaysAndTimeSlots(), { model: WorkScheduleUsersDAO, required: false, where: { current: true } }],
            where: { autoCreated: true, hash: { [Op.in]: hashes } },
        });
        return schedules.map(convertDAOToWorkSchedule);
    };

    public unassignUsers = async (userIds: number[]): Promise<void> => {
        const endDate = new Date();
        const endDateString = moment(endDate).format(ISO_DATE);
        await WorkScheduleUsersDAO.update({ current: false, endDate, endDateString }, { where: { userId: userIds } });
    };

    public getUsersWithoutWorkSchedulesWithWorkdays = async (limit: number) => {
        const query = `SELECT 
            "Instances"."id" AS "instanceId",
            "Instances"."name" AS "instanceName",
            "Users".id AS "userId",
            "Users"."workdays" AS "workdays"
        FROM 
            "Instances"
        INNER JOIN 
            "Users" ON "Instances"."id" = "Users"."instanceId"
        LEFT JOIN
            "WorkScheduleUsers" ON "WorkScheduleUsers"."userId" = "Users"."id" 
                AND "WorkScheduleUsers"."current" = true
        WHERE 
            "Users"."workdays" IS NOT NULL 
            AND "Users"."workdays" <> '{}'
            AND "WorkScheduleUsers".id IS NULL
        ORDER BY 
            "Instances"."id" ASC, 
            "Users"."id" ASC
        LIMIT :limit;`;
        const queryResults = await this.sequelize.query<{ instanceId: number; instanceName: string; userId: number; workdays: string[] }>(query, {
            replacements: { limit },
            type: QueryTypes.SELECT,
        });
        return Object.entries(lodash.groupBy(queryResults, 'instanceId')).map(([_, rows]) => ({
            instance: { id: rows[0].instanceId, name: rows[0].instanceName },
            users: rows.map((row) => ({ id: row.userId, workdays: row.workdays.map((day) => day as WORKDAYS) })),
        }));
    };

    public bulkCreateAssignments = async (assignments: WorkScheduleUsersDAOCreationAttributes[]): Promise<void> => {
        await WorkScheduleUsersDAO.bulkCreate(assignments);
    };

    public listWorkScheduleIdsByInstance = async (instanceId: number, pagination: { limit: number; offset: number }) => {
        const { count, rows } = await WorkScheduleDAO.findAndCountAll({
            attributes: ['id'],
            include: [],
            limit: pagination.limit,
            offset: pagination.offset,
            where: { instanceId },
        });
        return { count, rows: rows.map((row) => row.id) };
    };

    private includeScheduledDaysAndTimeSlots(): Includeable[] {
        return [
            {
                as: 'scheduledDays',
                include: [{ as: 'timeSlots', model: TimeSlotDAO }],
                model: ScheduledDaysDAO,
            },
        ];
    }

    private async createWorkSchedule(params: WorkScheduleCreationAttributes) {
        return WorkScheduleDAO.create(
            {
                autoCreated: params.autoCreated,
                code: params.code,
                createdBy: params.createdBy,
                description: params.description,
                generalTimeSlots: params.generalTimeSlots?.length ? true : false,
                hash: params.hash,
                instanceId: params.instanceId,
                lastVersion: true,
                name: params.name,
                version: params.version,
            },
            { returning: true },
        );
    }

    private async createScheduledDays(scheduledDays: ScheduledDays[], scheduleId: number) {
        const daysToCreate = scheduledDays.map((day) => ({
            dayOfWeek: day.dayOfWeek,
            workScheduleId: scheduleId,
        }));
        return ScheduledDaysDAO.bulkCreate(daysToCreate, { returning: true });
    }

    private async createTimeSlots(scheduledDays: ScheduledDays[], createdDays: ScheduledDaysDAO[]) {
        const slotsToCreate = scheduledDays.flatMap((day) => {
            if (!day.timeSlots || day.timeSlots.length === 0) {
                return [];
            }
            const createdDay = createdDays.find((createdDay) => createdDay.dayOfWeek === day.dayOfWeek)!;
            if (!createdDay) {
                throw new Error('one of the days was not created correctly');
            }
            return day.timeSlots.map((slot) => ({
                endTime: slot.endTime,
                scheduledDayId: createdDay.id,
                startTime: slot.startTime,
            }));
        });
        return slotsToCreate?.length > 0 ? TimeSlotDAO.bulkCreate(slotsToCreate, { returning: true }) : [];
    }

    public getAllUsersAssignedToWorkSchedule = async (
        id: number,
        instanceId: number,
        userIds: number[],
        pagination: { limit: number; offset: number },
        timezone: string,
    ): Promise<{ count: number; rows: WorkScheduleUserAssignment[] }> => {
        const today = moment.tz(timezone).format(ISO_DATE);
        const schedule = await WorkScheduleDAO.findOne({
            where: { id, instanceId },
        });
        if (!schedule) {
            throw new NotFoundError('Work schedule not found', ErrorCodes.NOT_FOUND);
        }
        // We use a custom query here because we need to fetch exactly one "WorkScheduleUser" record per WorkSchedule.
        // That single record is selected based on its assignment type: ACTIVE (1), UPCOMING (2), or PAST (3).

        const selectQuery = this.getAllUsersAssignedToWorkScheduleSelectQuery(userIds);

        const countQuery = this.getAllUsersAssignedToWorkScheduleCountQuery(userIds);

        const [countResult] = await this.sequelize.query<{ total: number }>(countQuery, {
            replacements: {
                instanceId,
                scheduleCode: schedule.code,
                today,
                userIds,
            },
            type: QueryTypes.SELECT,
        });

        const count = Number(countResult?.total ?? 0);

        const rowsResult = await this.sequelize.query<WorkScheduleUserAssignment>(selectQuery, {
            replacements: {
                instanceId,
                limit: pagination.limit,
                offset: pagination.offset,
                scheduleCode: schedule.code,
                today,
                userIds,
            },
            type: QueryTypes.SELECT,
        });
        return { count, rows: rowsResult.map(mapUserAssigmentWorkSchedule) };
    };

    public getAllUserAssignmentsInWorkSchedule = async (
        id: number,
        instanceId: number,
        userId: number,
        pagination: { limit: number; offset: number },
        timezone: string,
    ): Promise<{ count: number; rows: WorkScheduleUserAssignment[] }> => {
        const today = moment.tz(timezone);
        const schedule = await WorkScheduleDAO.findOne({
            where: { id, instanceId },
        });
        if (!schedule) {
            throw new NotFoundError('Work schedule not found', ErrorCodes.NOT_FOUND);
        }

        const { count, rows: workScheduleUsers } = await WorkScheduleUsersDAO.findAndCountAll({
            include: [{ model: WorkScheduleDAO, paranoid: false, required: true, where: { code: schedule.code, instanceId } }],
            limit: pagination.limit,
            offset: pagination.offset,
            order: [['startDate', 'DESC']],
            where: {
                userId,
            },
        });

        return {
            count,
            rows: workScheduleUsers.map((workScheduleUser) =>
                mapWorkScheduleUserDAOToUserAssigmentWorkSchedule(
                    workScheduleUser.workSchedule,
                    workScheduleUser,
                    this.computeAssignmentTypeNumber(workScheduleUser, today),
                ),
            ),
        };
    };

    private computeAssignmentTypeNumber = (workScheduleUser: WorkScheduleUsersDAO, today: moment.Moment): number => {
        const effectiveEndDate = workScheduleUser.endDate ?? new Date(infinity);
        if (workScheduleUser.startDate <= today.toDate() && effectiveEndDate >= today.toDate()) {
            return WORK_SCHEDULE_ASSIGNMENT_TYPE.ACTIVE;
        } else if (workScheduleUser.startDate > today.toDate()) {
            return WORK_SCHEDULE_ASSIGNMENT_TYPE.UPCOMING;
        }
        return WORK_SCHEDULE_ASSIGNMENT_TYPE.PAST;
    };

    public deleteWorkScheduleUser = async (id: number, workScheduleId: number): Promise<void> => {
        await WorkScheduleUsersDAO.destroy({ where: { id, workScheduleId } });
    };

    public getWorkScheduleUser = async (id: number, workScheduleId: number): Promise<WorkScheduleUser | null> => {
        const workScheduleUser = await WorkScheduleUsersDAO.findOne({ where: { id, workScheduleId } });
        return workScheduleUser ? mapWorkScheduleUserDAOToWorkScheduleUser(workScheduleUser) : null;
    };

    private getAllUsersAssignedToWorkScheduleSelectQuery = (userIds: number[]) => {
        return `
            WITH sub AS (
                ${this.getAllUsersAssignedToWorkScheduleWithAssignmentTypeSubQuery(userIds)}
            ),

            agg AS (
                ${this.getMinAssignmentTypeNumberSubQuery()}
            ),

            tie AS (
                ${this.getMinStartDateSubQuery()}
            ),

            final AS (
                ${this.getWorkScheduleUserWithAssignmentTypeAndMinStartDateSubQuery()}
            )

            SELECT
                wsu."id",
                wsu."workScheduleId",
                wsu."userId",
                wsu."startDate",
                wsu."startDateString",
                wsu."endDate",
                wsu."endDateString",
                wsu."current",
                ws.version as "workScheduleVersion",
                CASE
                    WHEN wsu."startDate" <= :today AND wsu."endDate" >= :today THEN 1
                    WHEN wsu."startDate" > :today THEN 2
                    ELSE 3
                END AS "assignmentTypeNumber"
            FROM "WorkScheduleUsers" wsu
            JOIN "WorkSchedules" ws ON ws."id" = wsu."workScheduleId"
            JOIN final f ON f."userId" = wsu."userId"
                AND f."minStartDate" = wsu."startDateString"
                AND f."assignmentTypeNumber" = (
                    CASE
                        WHEN wsu."startDateString" <= :today AND wsu."endDateString" >= :today THEN 1
                        WHEN wsu."startDateString" > :today THEN 2
                        ELSE 3
                    END
                )
            WHERE wsu."deletedAt" IS NULL
            ORDER BY
                wsu."startDateString" DESC,
                "assignmentTypeNumber" ASC
            LIMIT :limit
            OFFSET :offset
        `;
    };

    private getAllUsersAssignedToWorkScheduleCountQuery = (userIds: number[]) => {
        return `
            WITH sub AS (
                ${this.getAllUsersAssignedToWorkScheduleWithAssignmentTypeSubQuery(userIds)}
            ),

            agg AS (
                ${this.getMinAssignmentTypeNumberSubQuery()}
            ),

            tie AS (
                ${this.getMinStartDateSubQuery()}
            ),

            final AS (
                ${this.getWorkScheduleUserWithAssignmentTypeAndMinStartDateSubQuery()}
            )
            SELECT COUNT(*) as "total" FROM final
        `;
    };

    private getAllUsersAssignedToWorkScheduleWithAssignmentTypeSubQuery = (userIds: number[]) => {
        return `SELECT
                    wsu."workScheduleId",
                    wsu."userId",
                    wsu."startDateString",
                    wsu."endDateString",
                    CASE
                        WHEN wsu."startDateString" <= :today AND wsu."endDateString" >= :today THEN 1
                        WHEN wsu."startDateString" > :today THEN 2
                        ELSE 3
                    END AS "assignmentTypeNumber"
                FROM "WorkScheduleUsers" wsu
                    JOIN "WorkSchedules" ws ON ws."id" = wsu."workScheduleId"
                WHERE
                    ws."code" = :scheduleCode
                    AND ws."instanceId" = :instanceId
                    AND wsu."deletedAt" IS NULL
                    ${userIds.length ? 'AND wsu."userId" IN (:userIds)' : 'AND wsu."userId" IN (NULL)'}`;
    };

    private getMinAssignmentTypeNumberSubQuery = () => {
        return `SELECT
                    "userId",
                    MIN("assignmentTypeNumber") AS "minAssignmentTypeNumber"
                FROM sub
                GROUP BY "userId"`;
    };

    private getMinStartDateSubQuery = () => {
        return `SELECT
                    "userId",
                    "assignmentTypeNumber",
                MAX("startDateString") AS "minStartDate"
                FROM sub
                GROUP BY "userId", "assignmentTypeNumber"`;
    };

    private getWorkScheduleUserWithAssignmentTypeAndMinStartDateSubQuery = () => {
        return `SELECT
                    t."userId",
                    t."assignmentTypeNumber",
                    t."minStartDate"
                FROM agg
                JOIN tie t ON t."userId" = agg."userId"
                    AND t."assignmentTypeNumber" = agg."minAssignmentTypeNumber"`;
    };

    public getUserIdsByScheduleCriteria = async (
        instanceId: number,
        userIds: number[],
        fromDate: string,
        toDate: string,
        filters: ScheduleFilter,
    ): Promise<number[]> => {
        if (userIds.length === 0) {
            return [];
        }
        let userIdsFiltered = userIds;

        if (filters.onlyNoSchedule) {
            //We first get the users that are assigned to a work schedule
            const allAssignedUserIds = await this.filterUserIdsBySchedule(instanceId, userIds, fromDate, toDate);
            const allAssignedUserIdsSet = new Set(allAssignedUserIds);
            //Filter users that are assigned to a work schedule
            userIdsFiltered = userIds.filter((userId) => !allAssignedUserIdsSet.has(userId));
        }

        if (!filters.onlyNoSchedule && filters.hasSchedule) {
            userIdsFiltered = await this.filterUserIdsBySchedule(instanceId, userIds, fromDate, toDate, filters);
        }

        if (!filters.onlyNoSchedule && !filters.hasSchedule) {
            //We first get the users that are assigned to a work schedule
            const allAssignedUserIds = await this.filterUserIdsBySchedule(instanceId, userIds, fromDate, toDate);
            const allAssignedUserIdsSet = new Set(allAssignedUserIds);
            //Filter users that are assigned to a work schedule
            const unassignedUserIds = userIds.filter((userId) => !allAssignedUserIdsSet.has(userId));

            const userIdsFromFilters = await this.filterUserIdsBySchedule(instanceId, userIds, fromDate, toDate, filters);
            //Join lists
            userIdsFiltered = [...unassignedUserIds, ...userIdsFromFilters];
        }

        return userIdsFiltered;
    };

    private filterUserIdsBySchedule = async (
        instanceId: number,
        userIds: number[],
        fromDate: string,
        toDate: string,
        filters?: ScheduleCodeFilter,
    ) => {
        const workScheduleUsersWhereClause = this.buildWorkScheduleUsersWhereClause(userIds, fromDate, toDate);
        const workScheduleWhereClause = this.buildWorkScheduleWhereClause(instanceId, filters);
        const workScheduleUsersQuery = await WorkScheduleUsersDAO.findAll({
            attributes: [USER_ID_KEY],
            group: [USER_ID_KEY],
            include: [{ attributes: [], model: WorkScheduleDAO, required: true, where: { ...workScheduleWhereClause } }],
            raw: true,
            where: workScheduleUsersWhereClause,
        });
        return workScheduleUsersQuery.map((user) => user[USER_ID_KEY]);
    };

    private buildWorkScheduleWhereClause = (instanceId: number, filters?: ScheduleCodeFilter): WhereOptions<WorkScheduleDAO> => {
        const scheduleCodesWhere: WhereOperators<WorkScheduleDAO> = {};
        if (filters?.scheduleCodes?.length) scheduleCodesWhere[Op.in] = filters.scheduleCodes;
        if (filters?.excludeScheduleCodes?.length) scheduleCodesWhere[Op.notIn] = filters.excludeScheduleCodes;
        const hasCodeFilters = filters?.scheduleCodes?.length || filters?.excludeScheduleCodes?.length;
        const whereClause: WhereOptions<WorkScheduleDAO> = {
            instanceId,
            ...(hasCodeFilters ? { [Op.and]: [{ code: scheduleCodesWhere }] } : {}),
        };
        return whereClause;
    };

    private buildWorkScheduleUsersWhereClause = (userIds: number[], fromDate: string, toDate: string): WhereOptions<WorkScheduleUsersDAO> => {
        return {
            endDateString: { [Op.or]: [{ [Op.eq]: infinity }, { [Op.gte]: fromDate }] },
            startDateString: { [Op.lte]: toDate },
            userId: userIds,
        };
    };
}
