CREATE TRIGGER [dbo].[StatusLog_Email]
  ON  [dbo].[StatusLog]
  AFTER UPDATE
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @html nvarchar(MAX);

    SELECT * INTO #inserted FROM inserted
    --SELECT * FROM #inserted

    DECLARE @deviceID int =  (Select TOP 1 DeviceID from #inserted)
    DECLARE @enabled bit = (SELECT [Enabled] FROM Device WHERE ID = @deviceID)

    EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM #inserted';
    DECLARE @recipients nvarchar(max) = (SELECT Value FROM Setting WHERE Name = 'EmailRecipients')
    DECLARE @downloadThreshholdWindow int = (SELECT Value FROM Setting WHERE Name = 'MaxDownloadThresholdTimeWindow')
    DECLARE @downloadThreshhold int = (SELECT Value FROM Setting WHERE Name = 'MaxDownloadThreshold')
    DECLARE @date DATETIME = GETUTCDATE()
    DECLARE @downloadCount int = (SELECT COUNT(*) FROM (SELECT DISTINCT FilePath FROM DownloadedFile WHERE Timestamp BETWEEN DATEADD(HOUR, -@downloadThreshholdWindow, @date) AND @date AND DeviceID = @deviceID) as thingy)

    DECLARE @message nvarchar(MAX) = (SELECT TOP 1 LastErrorMessage FROM inserted)
    DECLARE @name nvarchar(max) = (SELECT Name FROM Device WHERE ID = @deviceID)
    DECLARE @downloadDate nvarchar(max) = (SELECT TOP 1 LastDownloadStartTime FROM inserted)
    DECLARE @lastDownloadedFileID INT = (SELECT TOP 1 LastDownloadedFileID FROM inserted)
    DECLARE @lastSuccess DateTime = (SELECT LastRun FROM StatusLog WHERE DeviceID = @deviceID)
    DECLARE @lastFailure DateTime = (SELECT LastFailure FROM StatusLog WHERE DeviceID = @deviceID)
    DECLARE @fileSize int = (SELECT TOP 1 FileSize FROM DownloadedFile WHERE DeviceID = @deviceID ORDER BY ID DESC)
    DECLARE @fileDate DateTime = (SELECT TOP 1 Timestamp FROM DownloadedFile WHERE ID = @lastDownloadedFileID)


    DECLARE @downloadDateDiff int = (SELECT DATEDIFF(HOUR, (Select LastDownloadStartTime FROM #inserted), (SELECT LastDownloadEndTime From #inserted)))
    DECLARE @successDateDiff int = (SELECT DATEDIFF(HOUR, @lastSuccess, @lastFailure))
    DECLARE @emailFlag bit = 0;
    DECLARE @intro nvarchar(max) = N''
    DECLARE @emailCountToday int = (SELECT COUNT(*) FROM SentEmail WHERE DeviceID = @deviceID AND Timestamp > CAST(@date as DATE))

    IF @downloadThreshhold > 0 AND @downloadCount > @downloadThreshhold AND @enabled = 1
    BEGIN
        DECLARE @countText NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @downloadCount)
        DECLARE @windowText NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @downloadThreshholdWindow)
        DECLARE @hourText NVARCHAR(MAX) = CASE @downloadThreshholdWindow WHEN 1 THEN 'hour' ELSE 'hours' END
        DECLARE @detailText NVARCHAR(MAX) = @countText + ' files in the last ' + @windowText + ' ' + @hourText
        SET @intro = @intro + N'<div>'+ @Name+' has been disabled due to excessive downloads (' + @detailText + ').</div><br>'
        SET @emailFlag = 1
        UPDATE Device set [Enabled] = 0 WHERE ID = @deviceID
    END

    IF @fileDate > DATEADD(HOUR, 24, @date) AND @enabled = 1 AND @emailCountToday = 0
    BEGIN
        SET @intro = @intro + N'<div>'+ @Name+' has produced a record in the future.</div><br>'
        SET @emailFlag = 1
    END

    IF @downloadDateDiff > 12 AND @enabled = 1 AND @emailCountToday = 0
    BEGIN
        SET @intro = @intro + N'<div>'+ @Name+' has taken '+ CAST(@downloadDateDiff as nvarchar(100)) +' hours to download a file.  The meter may require attention.</div><br>'
        SET @emailFlag = 1
    END

    IF @successDateDiff > 24 AND @enabled = 1 AND @emailCountToday = 0
    BEGIN
        SET @intro = @intro + N'<div>'+ @Name+' has not had a successful connection in  '+ CAST(@successDateDiff as nvarchar(100)) +' hours.  The meter may require attention.</div><br>'
        SET @emailFlag = 1
    END

    IF @fileSize > 1024*1024*50 AND @enabled = 1 AND @emailCountToday = 0 -- email on greater than 50 MB
    BEGIN
        SET @intro = @intro + N'<div>'+ @Name+' has produced a record that is too large.</div><br>'
        SET @emailFlag = 1
    END


    IF @emailFlag = 1
    BEGIN
        SET @html = @intro + @html;
        DECLARE @subject nvarchar(max) = N'OpenMIC '+ @Name +' problems ...'
        EXEC msdb.dbo.sp_send_dbmail
            @recipients= @recipients,
            @subject = @subject,
            @body = @html,
            @body_format = 'HTML';

        INSERT INTO SentEmail (DeviceID, [Message],[Timestamp]) VALUES ( @deviceID, @html, @date)

    END

    DROP Table #inserted

END
GO