CREATE TABLE Setting
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(200) NULL,
    Value VARCHAR(MAX) NULL,
    DefaultValue VARCHAR(MAX) NULL,
	Description varchar(Max) null
)
GO

INSERT INTO Setting VALUES('EmailRecipients', '', '', 'Semicolon-separated list of email addresses')
GO

INSERT INTO Setting VALUES('MaxDownloadThresholdTimeWindow', '24', '24', 'In hours')
GO

INSERT INTO Setting VALUES('MaxDownloadThreshold', '300', '300', 'Max number of files in threshold time window')
GO

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 @downloadCount int = (SELECT COUNT(*) FROM DownloadedFile WHERE Timestamp BETWEEN DATEADD(HOUR, -@downloadThreshholdWindow, GETDATE()) AND GETDATE() AND DeviceID = @deviceID)

	DECLARE @message nvarchar(MAX) = (SELECT TOP 1 Message FROM inserted)
	DECLARE @name nvarchar(max) = (SELECT Name FROM Device WHERE ID = @deviceID)
	DECLARE @lastFile nvarchar(max) = (SELECT TOP 1 LastFile FROM inserted)
	DECLARE @downloadDate nvarchar(max) = (SELECT TOP 1 FileDownloadTimestamp FROM inserted)
	DECLARE @lastSuccess DateTime = (SELECT LastSuccess 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 FileDownloadTimeStamp FROM #inserted)


	DECLARE @downloadDateDiff int = (SELECT DATEDIFF(HOUR, @fileDate, @downloadDate))
	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(GETDATE() as DATE))

	IF @downloadThreshhold > 0 AND @downloadCount > @downloadThreshhold AND @enabled = 1
	BEGIN
		SET @intro = @intro + N'<div>'+ @Name+' has been disabled due to excessive downloads.</div><br>'
		SET @emailFlag = 1
		UPDATE Device set [Enabled] = 0 WHERE ID = @deviceID
	END

	IF @fileDate > GETDATE() 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 > 1028*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, GETDATE())

	END

	DROP Table #inserted

END