USP_UPDATE_AND_LOCK_CLIENTUSER

CREATE procedure dbo.USP_UPDATE_AND_LOCK_CLIENTUSER
    (        @ClientUserID int,
            @INFORMATION nvarchar(1000) =null
    )
        as
        begin
            set nocount on;
            declare @MULTIPLELOCKOUT as int =0;
            declare @BRUTEFORCEATTACK as int =0;
            declare @BRUTEFORCEINFO as nvarchar(max);
            declare @DATETIME as datetime = getutcdate();            
            declare @CHANGEAGENTID uniqueidentifier
            declare @TIMEWINDOWINMINUTES as int  = (select Value from dbo.setting where ID = 168)
            declare @BRUTEFORCELIMIT as int  = (select Value from dbo.setting where ID = 169)
            declare @BRUTEFORCEMULTIPLEACCOUNTLOCKOUTDURATION as int = (select Value from dbo.setting where ID = 170)
            declare @LASTEVENTRAISEDTIME as datetime     =(select UPDATEDATE from dbo.SERVERSTATUS where ID = 5)

            --Constants

            declare @TIMEWINDOWINMINUTES_MAXVALUE as int  = 30;
            declare @BRUTEFORCELIMIT_MAXVALUE as int  = 10;
            declare @BRUTEFORCEMULTIPLEACCOUNTLOCKOUTDURATION_MAXVALUE as int = 30;

            if(isnull(@TIMEWINDOWINMINUTES,0) <=0 or @TIMEWINDOWINMINUTES >@TIMEWINDOWINMINUTES_MAXVALUE)
            begin
                set @TIMEWINDOWINMINUTES = @TIMEWINDOWINMINUTES_MAXVALUE;
            end

            if(isnull(@BRUTEFORCELIMIT,0)<=0 or @BRUTEFORCELIMIT >@BRUTEFORCELIMIT_MAXVALUE)
            begin
                set @BRUTEFORCELIMIT = @BRUTEFORCELIMIT_MAXVALUE;
            end

            if(isnull(@BRUTEFORCEMULTIPLEACCOUNTLOCKOUTDURATION,0) <=0 or @BRUTEFORCEMULTIPLEACCOUNTLOCKOUTDURATION >@BRUTEFORCEMULTIPLEACCOUNTLOCKOUTDURATION_MAXVALUE)
            begin
                set @BRUTEFORCEMULTIPLEACCOUNTLOCKOUTDURATION = @BRUTEFORCEMULTIPLEACCOUNTLOCKOUTDURATION_MAXVALUE;
            end


            --Get Change agent

            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            --If last event was raised before @TIMEWINDOWINMINUTES mins the reset to current time - @TIMEWINDOWINMINUTES mins WI#1584998

            if((select datediff(minute,@LASTEVENTRAISEDTIME, getutcdate())) >@TIMEWINDOWINMINUTES)
            begin
                set @LASTEVENTRAISEDTIME = dateadd(minute,-1*@TIMEWINDOWINMINUTES,@DATETIME);
            end    

            --If any account locked twice without successful login (lockout time > datelastlogin)

            -- If account locked twice in @BRUTEFORCEMULTIPLEACCOUNTLOCKOUTDURATION amount of time            


            if exists (select 1 from dbo.CLIENTUSERS where ID = @CLIENTUSERID and LASTLOCKEDOUTTIME is not null and LASTLOCKEDOUTTIME > isnull(DATELASTLOGIN,@DATETIME) and datediff(minute,LASTLOCKEDOUTTIME, @DATETIME) <= @BRUTEFORCEMULTIPLEACCOUNTLOCKOUTDURATION) 
            begin
                set @MULTIPLELOCKOUT = 1;
            end

            --Lock this User's account

            update dbo.CLIENTUSERS set LOCKEDOUTTIME = @DATETIME,LASTLOCKEDOUTTIME = @DATETIME, NumFailedAttempts=0 where ID = @ClientUserID;

            --Add Account lockout information for Brute force

            insert into dbo.CLIENTUSERSFAILEDLOGININFO(CLIENTUSERSID,INFORMATION,DATEADDED,DATECHANGED,ADDEDBYID,CHANGEDBYID)
            values(@ClientUserID,isnull(@INFORMATION,''),@DATETIME,@DATETIME,@CHANGEAGENTID,@CHANGEAGENTID)

            --If any 10 account locked in last @TIMEWINDOWINMINUTES mins

            select @BRUTEFORCEINFO = coalesce(@BRUTEFORCEINFO + INFORMATION, INFORMATION) from dbo.CLIENTUSERSFAILEDLOGININFO
            where DATECHANGED > @LASTEVENTRAISEDTIME;


            --num of rows selected in Bruteforceinfo query 

            select @BRUTEFORCEATTACK = case when @@ROWCOUNT >=@BRUTEFORCELIMIT then 1 else 0 end;            

            select @MULTIPLELOCKOUT as MULTIPLELOCKOUT,  @BRUTEFORCEATTACK as BRUTEFORCEATTACKDETECTED, @DATETIME as LASTEVENTTIME, @BRUTEFORCEINFO as BRUTEFORCEINFO;        
end