/**
* Returns all users eligible for free task assignment.
* Includes users with full security access to the Tasks module,
* managers, and users with specific labor types (Coordinator, Sales, OA Surveyor, Revenue Clerk, Claims Adjuster).
* Ensures only active users are included.
**/
CREATE function [dbo].[udfGetAllFreeTaskUsers]()
returns table as return
(
-- These are the users who have full security access to the Tasks module.
    with ActiveTaskModule as(
        select ModulePriKey from SecModules where [Description] = 'Tasks' and Active = 1
    ), FullSecurityAccess as (
        select AccessPriKey from SecAccessTypes where [Description] = 'Full'
    ), ActiveUserStatus as (
        select PriKey from Status where Status = 'Active'
    ), AllUsers as (select distinct
        Sysuser.SysuserID,
        Sysuser.EmpNo,
        FullName = dbo.FormatLastNameFirstName( Sysuser.LastName, Sysuser.FirstName ),
        case when LaborType.LaborType = 'Coor' then 'Coordinator'
            when LaborType.LaborType = 'Disp' then 'Dispatch'
            when LaborType.LaborType = 'LD Disp' then 'LD Dispatch'
            else LaborType.LaborType
        end as LaborType,
        LaborTypePriKey = LaborType.PriKey,
        case when LaborType.LaborType = 'Coor' then dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (Coordinator)'
            when LaborType.LaborType = 'Disp' then dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (Dispatch)'
            when LaborType.LaborType = 'LD Disp' then dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (LD Dispatch)'
            else dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (' + LaborType.LaborType + ')'
        end as FullNameAndLaborType,
        -- Determine AccessLevel: 2 for 'Full' access, 3 if user is a manager (but not full), otherwise 1 (default)
        -- reason to checking access level 'Full' before the manager flag because we want to make sure that set minimum access level.
        -- i.e. if user has 'Full' access level and Manager flag is also true (1) then we set 'Full' to access level
        AccessLevel =
            case when SecProfileDetail.AccessPriKey = FullSecurityAccess.AccessPriKey then 2
            when SecProfileDetail.ManagerFlag = 1 then 3
            else 1 end
    from UserAccess UserAccess
    inner join SysUser SysUser on ( SysUser.SysUserID = UserAccess.SysUserID)
    inner join ActiveUserStatus on SysUser.Status  = ActiveUserStatus.PriKey
    inner join dbo.SysUserLaborTypeMap on SysUserLaborTypeMap.SysUserFID = SysUser.SysUserID
    inner join LaborType LaborType on LaborType.PriKey = SysUserLaborTypeMap.LaborTypeFID
    cross join FullSecurityAccess
    cross join ActiveTaskModule
    inner join SecProfileDetail on UserAccess.SecProfilePriKey = SecProfileDetail.SecProfilePriKey and ActiveTaskModule.ModulePriKey = SecProfileDetail.ModulePriKey
    where SecProfileDetail.ManagerFlag = 1 or FullSecurityAccess.AccessPriKey = SecProfileDetail.AccessPriKey

    -- We want unions here (as opposed to union alls) so that we get a distinct dataset across off three select statements.
    union

    -- These are all of the active Coordinators, Salespersons, OA Surveyors, Revenue Clerks and Claims Adjusters.
    -- In other words, the labor types in the CoordinatorType table.
    select
        Sysuser.SysuserID,
        Sysuser.EmpNo,
        FullName = dbo.FormatLastNameFirstName( Sysuser.LastName, Sysuser.FirstName ),
        case when LaborType.LaborType = 'Coor' then 'Coordinator'
            else LaborType.LaborType
        end as LaborType,
        LaborTypePriKey = LaborType.PriKey,
        case when LaborType.LaborType = 'Coor' then dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (Coordinator)'
            else dbo.GetSysuserName( Sysuser.SysuserID, 0 ) + ' (' + LaborType.LaborType + ')'
        end as FullNameAndLaborType,
    -- Final query: focus only on records that have the minimum access level.
    -- Hard-coding access level to '0', so records must meet this condition to be included in the final query.
        AccessLevel = 0
    from Sysuser
    inner join SysUserLaborTypeMap on SysUserLaborTypeMap.SysUserFID = SysUser.SysUserID
    inner join LaborType on LaborType.PriKey = SysUserLaborTypeMap.LaborTypeFID and
    LaborType.LaborType in ('Coor', 'Sales', 'OA Surveyor', 'Revenue Clerk', 'Claims Adjuster')
    inner join ActiveUserStatus on SysUser.Status  = ActiveUserStatus.PriKey)
    select
        SysuserID,
        EmpNo,
        FullName,
        LaborType,
        LaborTypePriKey,
        FullNameAndLaborType,
        AccessLevel = Min(AccessLevel)
    from AllUsers
    group by SysuserID,EmpNo,FullName,LaborType,LaborTypePriKey,FullNameAndLaborType
)
GO
GRANT SELECT ON  [dbo].[udfGetAllFreeTaskUsers] TO [MssExec]
GO