CREATE PROCEDURE [dbo].[EmailApiPurgeOldEmails]
AS
begin
    set nocount on
    declare @DELETION_BATCH_SIZE int = 50
    declare @emailLifetimeInDays int = isnull(
        (select convert(int,[Value]) from GlobalSystemOption where [Name] = 'MoversConnectEmailArchiveLifetime' ),
        90 )

    -- declare @oldestEmailToDelete = dbo.SysDateTimeOffset
    declare @now datetimeoffset = sysdatetimeoffset()
    declare @deletionThreshold datetimeoffset = dateadd( day, @emailLifetimeInDays* -1, @now )

    declare @numberOfEmailsDeleted int;
    ;with DeletionQuery AS
    (
        select top (@DELETION_BATCH_SIZE)
        OutgoingEmailFID
        from OutgoingEmailStatus where LastActivity < @deletionThreshold and
        OutgoingEmailStatus.Succeeded is not null -- i.e. not enqueued
        order by LastActivity asc -- delete oldest first

    )
    delete OutgoingEmail
    from DeletionQuery
    where OutgoingEmail.OutgoingEmailID = DeletionQuery.OutgoingEmailFID

    set @numberOfEmailsDeleted = @@ROWCOUNT
    select @numberOfEmailsDeleted
end
GO
GRANT EXECUTE ON  [dbo].[EmailApiPurgeOldEmails] TO [MssExec]
GO