CREATE PROCEDURE [dbo].[EmailApiMarkFailures]
    @inOutgoingEmailQueueIds IntList readonly,
    @inText nvarchar(max),
    @inRequestTraceId varchar(64),
    @inAttemptedAt datetimeoffset,
    @inSentAt datetimeoffset = null,
    @inCompletedAt datetimeoffset = null
AS
begin
    set nocount on

    declare @batchFailureId int

    insert into OutgoingEmailBatchFailure(
        BodyOrMessage,
        AttemptedAt,
        SentAt,
        CompletedAt,
        RequestTraceId
    )
    select
        BodyOrMessage = @inText,
        @inAttemptedAt,
        @inSentAt,
        @inCompletedAt,
        @inRequestTraceId
        
    set @batchFailureId = SCOPE_IDENTITY()

    insert into OutgoingEmailFailure(
        OutgoingEmailFID,
        OutgoingEmailBatchFailureFID,
        EnqueuedAt
    )
    select
        OutgoingEmail.OutgoingEmailID,
        @batchFailureId,
        OutgoingEmailQueue.EnqueuedAt
    from @inOutgoingEmailQueueIds EmailQueueIds
    inner join OutgoingEmailQueue on EmailQueueIds.Item = OutgoingEmailQueue.OutgoingEmailQueueID
    inner join OutgoingEmail on OutgoingEmailQueue.OutgoingEmailFID = OutgoingEmail.OutgoingEmailID
    order by EmailQueueIds.Item asc

    exec SyncOutgoingEmailStatus
        @inOutgoingEmailQueueIds = @inOutgoingEmailQueueIds,
        @inActivityTimeStamp = @inAttemptedAt,
        @inSucceeded = 0

    delete OutgoingEmailQueue
    from @inOutgoingEmailQueueIds EmailQueueIds
    inner join OutgoingEmailQueue on EmailQueueIds.Item = OutgoingEmailQueue.OutgoingEmailQueueID
end
GO
GRANT EXECUTE ON  [dbo].[EmailApiMarkFailures] TO [MssExec]
GO