DECLARE @ticketid int
DECLARE @agentid int
DECLARE @noteid int
DECLARE @userid int
DECLARE @tickettype int
DECLARE @ticketpriority int

SET @ticketid = (SELECT TOP 1 [a].[ticketid]
                 FROM
	                 [htblhistory] [a]
		                 JOIN [htblticket] [h] ON [a].[ticketid] = [h].[ticketid]
		                 JOIN [htblusers] [u] ON [h].[fromuserid] = [u].[userid]
		                 JOIN [htblagents] [ha] ON [u].[userid] = [ha].[userid]
                 WHERE
	                   [typeid] = 11
                 AND [h].[agentid] IS NULL
	               AND [a].[userid] IN ([ha].[userid])
	               AND [a].[date] >= DATEADD(MI,-1,getdate()))

SET @agentid =
	(SELECT TOP 1 [g].[agentid]
	 FROM
		 [htblhistory] [a]
			 JOIN [htblnotes] [h] ON [a].[noteid] = [h].[noteid]
			 JOIN [htblusers] [u] ON [h].[userid] = [u].[userid]
			 JOIN [htblagents] [g] ON [u].[userid] = [g].[userid]
	 WHERE [a].[ticketid] = @ticketid AND [a].[typeid] = 11 AND a.[userid] IN ([g].[userid]))


SET @noteid =
	(SELECT TOP 1 [noteid] FROM [htblnotes] WHERE [note] LIKE '%Ticket auto assigned%' ORDER BY [noteid] DESC)

SET @userid = (SELECT [fromuserid] FROM [htblticket] [a] WHERE [ticketid] = @ticketid)

SET @tickettype = (SELECT [tickettypeid] FROM [htblticket] WHERE [ticketid] = @ticketid)

SET @ticketpriority = (SELECT [priority] FROM [htblticket] WHERE [ticketid] = @ticketid)
--------------------------------------------------------------------------------------------------
-- Creates a new entry in the notes table, this will show on the ticket page
--------------------------------------------------------------------------------------------------
INSERT INTO
	[htblnotes] ([ticketid],[userid],[date],[note],[notetype],[emailmessageid],[servicechange],[timeworkeduserid]
	            ,[timeworkeddate],[timeworked])

SELECT
	@ticketid
, @userid
, GETDATE()
, '<b>Ticket auto assigned</b>' -- this is bold lettering for the comment
, 2 -- this is a private note and not public
, 'unique_id@domain' -- this value is found in the htblnotes table, I used the same that closes the tickets
, 'false'
, NULL
, NULL
, NULL
--------------------------------------------------------------------------------------------------
-- Creates new line in the history table
--------------------------------------------------------------------------------------------------
INSERT INTO
	[htblhistory]([action]
	             ,[userid]
	             ,[ticketid]
	             ,[date]
	             ,[typeid]
	             ,[status]
	             ,[reason]
	             ,[ticketstateid]
	             ,[tickettypeid]
	             ,[priority]
	             ,[teamid]
	             ,[agentid]
	             ,[assetid]
	             ,[scheduleid]
	             ,[noteid]
	             ,[fieldid]
	             ,[ruleid]
	             ,[userid2]
	             ,[userid3]
	             ,[agentid2]
	             ,[type]
	             ,[unauthorised]
	             ,[luceneadded]
	             ,[newvalue])

SELECT
	NULL
, @userid
, @ticketid
, GETDATE()
, 5 -- this value is for assigned
, 1
, NULL
, 2
, @tickettype
, @ticketpriority
, 1
, @agentid
, NULL
, NULL
, @noteid
, NULL
, NULL
, @userid
, NULL
, @agentid
, NULL
, 'false'
, 'true'
, NULL
--------------------------------------------------------------------------------------------------
-- Updates the ticket to change the assigned agent from no one to the agent who replied
--------------------------------------------------------------------------------------------------
UPDATE [htblticket]
SET [agentid] = @agentid
WHERE [ticketid] = @ticketid
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------