public void DeleteQueue(int queueId)
        {
            const string sql = "DELETE FROM [Messenger].[Queues] WHERE [QueueId] = @queueId";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                db.Connection.Execute(sql, new { queueId });
                db.Complete();
            }
        }
        public int? FetchQueueId(string queueName)
        {
            const string sql = "SELECT [QueueId] FROM [Messenger].[Queues] (nolock) WHERE [Name] = @queueName";

            int? queueId;

            using (var db = new FatQueueDatabase(_connectionFactory))
            {
                queueId = db.Connection.ExecuteScalar<int?>(sql, new { queueName });
                db.Complete();
            }

            return queueId;
        }
        public int? FetchQueueId(string queueName)
        {
            const string sql = "SELECT QueueId FROM Messenger.Queues WHERE Name = :queueName";

            int? queueId;

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
            {
                queueId = db.Connection.ExecuteScalar<int?>(sql, new {queueName});
                db.Complete();
            }

            return queueId;
        }
        public int CreateQueue(string queueName)
        {
            const string fetchSql = "SELECT [QueueId] FROM [Messenger].[Queues] WITH (UPDLOCK, ROWLOCK, HOLDLOCK) WHERE [Name] = @queueName";
            const string insertSql =
                "INSERT INTO [Messenger].[Queues] ([Name]) VALUES(@queueName); SELECT SCOPE_IDENTITY();";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                var queueId = db.Connection.ExecuteScalar<int?>(fetchSql, new { queueName });
                if (!queueId.HasValue)
                    queueId = db.Connection.ExecuteScalar<int>(insertSql, new { queueName });
                db.Complete();
                return queueId.Value;
            }
        }
        public int CreateQueue(string queueName)
        {
            const string fetchSql = "SELECT QueueId FROM Messenger.Queues WHERE Name = :queueName";
            const string insertSql = "INSERT INTO Messenger.Queues (Name) VALUES(:queueName); SELECT LASTVAL();";
            const string lockSql = "LOCK TABLE Messenger.Queues";

            int? queueId;
            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                queueId = db.Connection.ExecuteScalar<int?>(fetchSql, new {queueName});
                if (!queueId.HasValue)
                {
                    db.Connection.Execute(lockSql);
                    queueId = db.Connection.ExecuteScalar<int?>(fetchSql, new {queueName});
                    if (!queueId.HasValue)
                    {
                        queueId = db.Connection.ExecuteScalar<int>(insertSql, new {queueName});
                    }
                }
                db.Complete();
            }
            return queueId.Value;
        }
        public void ReleaseProcessLock(params string [] processNames)
        {
            if (processNames == null || processNames.Length == 0)
                return;

            const string sqlUpdate = "update [Messenger].[Queues] set ProcessName = null, ProcessingStarted = null, NextTryTime = null where ProcessName in @processNames";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                db.Connection.Execute(sqlUpdate, new {processNames});
                db.Complete();
            }
        }
        public void PurgeCompletedMessages(DateTime olderThan)
        {
            const string sql = "delete from Messenger.CompletedMessages where CompletedDate < @olderThan";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                db.Connection.Execute(sql, new { olderThan });
                db.Complete();
            }
        }
        public void Heartbeat(string processName)
        {
            const string sql =
                //"MERGE [Messenger].[Heartbeat] WITH (HOLDLOCK) AS target " +
                "MERGE [Messenger].[Heartbeat] AS target " +
                "USING (SELECT @processName) AS source (ProcessName) " +
                "ON (target.ProcessName = source.ProcessName) " +
                "WHEN MATCHED THEN " +
                "   UPDATE SET LastBeat = SYSUTCDATETIME() " +
                "WHEN NOT MATCHED THEN " +
                "   INSERT (ProcessName, LastBeat) " +
                "   VALUES (source.ProcessName, SYSUTCDATETIME());";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                db.Connection.Execute(sql, new { processName });
                db.Complete();
            }
        }
        public void ClearStaleProcesses(DateTime olderThan)
        {
            const string sql = "select ProcessName from [Messenger].[Heartbeat] where LastBeat < @olderThan";
            const string sqlUpdate = "update [Messenger].[Queues] set ProcessName = null, ProcessingStarted = null, NextTryTime = null where ProcessName in @processNames";
            const string sqlDelete = "delete from [Messenger].[Heartbeat] where ProcessName in @processNames";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                var processNames = db.Connection.Query<string>(sql, new { olderThan }).ToArray();
                if (processNames.Length > 0)
                {
                    db.Connection.Execute(sqlUpdate, new {processNames});
                    db.Connection.Execute(sqlDelete, new {processNames});
                }
                db.Complete();
            }
        }
        public QueueInfo LockQueue(string processName)
        {
/*
            const string sql = "update top (1) Messenger.Queues with (rowlock) " +
                               "set ProcessingStarted = SYSUTCDATETIME() " +
                               ", ProcessName = @processName " +
                               "output inserted.Name, inserted.QueueId, inserted.Retries " +
                               "where QueueId = ( " +
                               "		select top 1 q.QueueId  " +
                               "		from Messenger.Queues q (readpast) " +
                               "		join Messenger.Messages m with (readpast) on m.QueueId = q.QueueId " +
                               "		where q.ProcessingStarted is null and (q.NextTryTime is null or q.NextTryTime < SYSUTCDATETIME()) " +
                               "		order by m.CreateDate asc)";
*/

            const string fetchSql =
                "select top 1 q.QueueId " +
                "from Messenger.Queues q with (updlock, readpast, rowlock) " +
                "join Messenger.Messages m (nolock) on m.QueueId = q.QueueId " +
                "where q.ProcessingStarted is null and m.StartDate <= SYSUTCDATETIME() and (q.NextTryTime is null or q.NextTryTime < SYSUTCDATETIME()) " +
                "order by m.StartDate asc";

            const string updateSql = "update Messenger.Queues " +
                                     "set ProcessingStarted = SYSUTCDATETIME() " +
                                     ", ProcessName = @processName " +
                                     ", ProcessedAt = SYSUTCDATETIME() " +
                                     "output inserted.Name, inserted.QueueId, inserted.Retries " +
                                     "where QueueId = @queueId";

            QueueInfo queueInfo = null;
            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                var queueId = db.Connection.Query<int?>(fetchSql).FirstOrDefault();
                if (queueId.HasValue)
                {
                    queueInfo = db.Connection.Query<QueueInfo>(updateSql, new {queueId, processName }).FirstOrDefault();
                }

                //var queue = db.Connection.Query<QueueInfo>(sql, new {processName}).FirstOrDefault();
                db.Complete();
            }
            return queueInfo;
        }
        public void CreateMessage(int queueId, string contentType, string content, string context, string contextFactory, int delayInSeconds, Guid identity)
        {
            const string sql =
                "INSERT INTO Messenger.Messages (QueueId, ContentType, Content, StartDate, Context, Identity) " +
                "VALUES(:QueueId, :ContentType, :Content, CURRENT_TIMESTAMP + INTERVAL '{0} second', :Context, :Identity, :ContextFactory)";

            var message = new
            {
                QueueId = queueId,
                ContentType = contentType,
                Content = content,
                DelayInSeconds = delayInSeconds,
                Context = context,
                Identity = identity,
                ContextFactory = contextFactory
            };

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required))
            {
                db.Connection.Execute(string.Format(sql, delayInSeconds), message);
                db.Complete();
            }
        }
        public void RemoveMessage(int [] messageId, ITransaction transaction = null)
        {
            if (messageId == null || messageId.Length == 0)
                return;

            const string sql =
                "delete from Messenger.Messages " +
                "where MessageId in @messageId";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction))
            {
                db.Connection.Execute(sql, new { messageId });
                db.Complete();
            }
        }
        public IEnumerable<ProcessStatus> GetActiveProcesses()
        {
            const string sql = "select ProcessName, LastBeat as LastHeartbeat from Messenger.Heartbeat";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
            {
                var statuses = db.Connection.Query<ProcessStatus>(sql);
                db.Complete();
                return statuses;
            }
        }
        public int CancelMessages(Guid identity)
        {
            const string sql1 = "delete from [Messenger].[Messages] where [Identity] = @identity and StartDate > SYSUTCDATETIME()";
            const string sql2 = "delete from [Messenger].[FailedMessages] where [Identity] = @identity";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.Serializable))
            {
                var affectedRows = db.Connection.Execute(sql1, new { identity });
                if (affectedRows == 0)
                    affectedRows = db.Connection.Execute(sql2, new {identity});
                db.Complete();
                return affectedRows;
            }
        }
        public void RemoveMessage(int messageId, bool archiveMessage, ITransaction transaction = null)
        {
            const string sql = "delete from [Messenger].[Messages] where MessageId = @messageId";

            const string sqlArchive =
                "insert into [Messenger].[CompletedMessages] (ContentType, Content, CreateDate, Context, CompletedDate, [Identity], ContextFactory) " +
                "select ContentType, Content, StartDate, Context, SYSUTCDATETIME(), [Identity], ContextFactory " +
                "from [Messenger].[Messages] " +
                "where MessageId = @messageId";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction))
            {
                if (archiveMessage)
                {
                    db.Connection.Execute(sqlArchive, new {messageId});
                }

                db.Connection.Execute(sql, new {messageId});
                db.Complete();
            }
        }
        public IList<MessageInfo> FetchQueueMessages(int queueId, int messageCount)
        {
            const string sql = "select top(@messageCount) MessageId, Content, Context, StartDate, [Identity], ContextFactory " +
                               "from Messenger.Messages " +
                               "where QueueId = @queueId and StartDate <= SYSUTCDATETIME() " +
                               "order by StartDate asc, MessageId asc";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Suppress))
            {
                var messages = db.Connection.Query<MessageInfo>(sql, new { queueId, messageCount }).ToList();
                db.Complete();
                return messages;
            }
        }
        public void SetQueueFailure(int queueId, int retries, string formattedError, DateTime nextTryTime)
        {
            const string sql = "update [Messenger].[Queues] " +
                               "set ProcessingStarted = null " +
                               ", ProcessName = null " +
                               ", Error = @formattedError " +
                               ", Retries = @retries " +
                               ", NextTryTime = @nextTryTime " +
                               "where QueueId = @queueId";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                db.Connection.Execute(sql, new { queueId, retries, formattedError, nextTryTime });
                db.Complete();
            }
        }
        public void ReleaseQueue(int queueId, ITransaction transaction = null)
        {
            const string sql =
                "update Messenger.Queues " +
                "set ProcessingStarted = null " +
                ", ProcessName = null " +
                ", Error = null " +
                ", Retries = null " +
                ", NextTryTime = null " +
                "where QueueId = @queueId";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction))
            {
                db.Connection.Execute(sql, new {queueId});
                db.Complete();
            }
        }
        // ------------- QUEUE MANAGEMENT -----------------------

        public IEnumerable<MessengerStatus> GetMessengerStatus()
        {
            const string sql = "select 'Failed' as Status, count(*) as MessageCount from Messenger.FailedMessages " +
                               "union " +
                               "select 'Completed', count(*) from Messenger.CompletedMessages " +
                               "union " +
                               "select 'Ready', count(*) from Messenger.Messages";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
            {
                var status = db.Connection.Query<MessengerStatus>(sql);
                db.Complete();
                return status;
            }
        }
        public IEnumerable<MessageDetails> GetFailedMessages(int pageNo, int pageSize)
        {
            const string sql = "select FailedMessageId as MessageId, QueueId, ContentType, Content, CreateDate as StartDate, Context, [Identity], Error, FailedDate as CompletedDate, 'Failed' as State, ContextFactory " +
                               "from Messenger.FailedMessages " +
                               "order by CreateDate asc " +
                               "offset @offset rows " +
                               "fetch next @pageSize rows only";

            int offset = (pageNo - 1) * pageSize;

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
            {
                var statuses = db.Connection.Query<MessageDetails>(sql, new { offset, pageSize });
                db.Complete();
                return statuses;
            }
        }
        public IEnumerable<QueueStatus> GetQueueStatuses()
        {
            const string sql =
                "select q.QueueId, q.Name, q.ProcessedAt, q.ProcessingStarted, q.ProcessName, q.Retries, q.NextTryTime, q.Error " +
                ", sum(case when m.MessageId is null then 0 else 1 end) as MessageCount " +
                "from Messenger.Queues q " +
                "left join Messenger.Messages m on m.QueueId = q.QueueId " +
                "group by q.QueueId, q.Name, q.ProcessedAt, q.ProcessingStarted, q.ProcessName, q.Retries, q.NextTryTime, q.Error";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
            {
                var statuses = db.Connection.Query<QueueStatus>(sql);
                db.Complete();
                return statuses;
            }
        }
        public void RemoveMessages(params Guid[] identity)
        {
            if (identity == null || identity.Length == 0)
                return;

            const string readySql = "delete from Messenger.Messages where [Identity] in @identity";
            const string failedSql = "delete from Messenger.FailedMessages where [Identity] in @identity";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
            {
                db.Connection.Execute(readySql, new { identity });
                db.Connection.Execute(failedSql, new { identity });
                db.Complete();
            }
        }
        public void CopyMessageToFailed(int messageId, ITransaction transaction = null)
        {
            const string sql =
                "insert into Messenger.FailedMessages (ContentType, Content, CreateDate, Error, FailedDate, Context, [Identity], ContextFactory, QueueId) " +
                "select m.ContentType, m.Content, m.StartDate, q.Error, SYSUTCDATETIME(), m.Context, m.[Identity], m.ContextFactory, m.QueueId " +
                "from Messenger.Messages m " +
                "join Messenger.Queues q on q.QueueId = m.QueueId " +
                "where m.MessageId = @messageId ";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction))
            {
                db.Connection.Execute(sql, new { messageId });
                db.Complete();
            }            
        }
        public void InsertMessage(int queueId, string contentType, string content, string contextFactory, string context, Guid identity)
        {
            const string selectSql =
                "SELECT [StartDate] " +
                "FROM [Messenger].[Messages] " +
                "WHERE [QueueId] = @queueId " +
                "ORDER BY [StartDate] ASC";

            const string sql =
                "INSERT INTO [Messenger].[Messages] ([QueueId], [ContentType], [Content], [StartDate], [Context], [Identity], [ContextFactory]) " +
                "VALUES(@QueueId, @ContentType, @Content, @StartDate, @Context, @Identity, @ContextFactory)";

            using (var db = new FatQueueDatabase(_connectionFactory))
            {
                var createDate = db.Connection.ExecuteScalar<DateTime?>(selectSql, new {queueId});

                var parameters = new DynamicParameters();
                parameters.Add("QueueId", queueId);
                parameters.Add("ContentType", contentType);
                parameters.Add("Content", content);
                parameters.Add("StartDate", createDate.GetValueOrDefault(DateTime.UtcNow), DbType.DateTime2);
                parameters.Add("Context", context);
                parameters.Add("Identity", identity);
                parameters.Add("ContextFactory", contextFactory);

                db.Connection.Execute(sql, parameters);
                db.Complete();
            }
        }
        public MessageDetails GetMessageDetails(Guid identity)
        {
            const string sql =
                "select * from( " +
                "   select MessageId, QueueId, ContentType, Content, StartDate, Context, [Identity], Error, null as CompletedDate, 'Ready' as State, ContextFactory from Messenger.Messages " +
                "   union " +
                "   select CompletedMessageId, null, ContentType, Content, CreateDate, Context, [Identity], null as Error, CompletedDate, 'Completed', ContextFactory from Messenger.CompletedMessages " +
                "   union " +
                "   select FailedMessageId, QueueId, ContentType, Content, CreateDate, Context, [Identity], Error, FailedDate, 'Failed', ContextFactory from Messenger.FailedMessages " +
                ") as msg " +
                "where [Identity] = @identity";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
            {
                var statuses = db.Connection.Query<MessageDetails>(sql, new { identity });
                db.Complete();
                return statuses.FirstOrDefault();
            }
        }
        public MessageDetails GetMessageDetails(Guid identity)
        {
            const string sql = "select *  " +
                               "from Messenger.Messages " +
                               "where Identity = :identity ";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
            {
                var statuses = db.Connection.Query<MessageDetails>(sql, new { identity });
                db.Complete();
                return statuses.FirstOrDefault();
            }
        }
        public void ReenqueueFailedMessages(int queueId, params Guid[] identity)
        {
            const string sql = "insert into Messenger.Messages (QueueId, ContentType, Content, StartDate, Context, [Identity], ContextFactory) " +
                               "select ISNULL(QueueId, @queueId), ContentType, Content, SYSUTCDATETIME(), Context, [Identity], ContextFactory " +
                               "from Messenger.FailedMessages " +
                               "where [Identity] in @identity";

            const string sqlDelete = "delete from Messenger.FailedMessages where [Identity] in @identity";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew))
            {
                db.Connection.Execute(sql, new { queueId, identity });
                db.Connection.Execute(sqlDelete, new { queueId, identity });
                db.Complete();
            }
        }
        public IEnumerable<MessageDetails> GetFailedMessages(int pageNo, int pageSize)
        {
            const string sql = "select *  " +
                               "from Messenger.FailedMessages " +
                               "order by CreateDate asc " +
                               "limit :pageSize " +
                               "offset :offset";

            int offset = (pageNo - 1) * pageSize;

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted))
            {
                var statuses = db.Connection.Query<MessageDetails>(sql, new { offset, pageSize });
                db.Complete();
                return statuses;
            }
        }
        public void CreateMessageIfNotExists(int queueId, string contentType, string content, string contextFactory, string context, int delayInSeconds, Guid identity)
        {
            const string sql =
                "INSERT INTO [Messenger].[Messages] ([QueueId], [ContentType], [Content], [StartDate], [Context], [Identity], [ContextFactory]) " +
                "SELECT @QueueId, @ContentType, @Content, DATEADD(second, @DelayInSeconds, SYSUTCDATETIME()), @Context, @Identity, @ContextFactory " +
                "WHERE NOT EXISTS (SELECT * FROM [Messenger].[Messages] WHERE [Identity] = @Identity)";

            var message = new
            {
                QueueId = queueId,
                ContentType = contentType,
                Content = content,
                DelayInSeconds = delayInSeconds,
                Context = context,
                Identity = identity,
                ContextFactory = contextFactory
            };

            using (var db = new FatQueueDatabase(_connectionFactory))
            {
                db.Connection.Execute(sql, message);
                db.Complete();
            }
        }
        public void MoveMessageToEnd(int messageId, ITransaction transaction = null)
        {
            const string sql = "update Messenger.Messages " +
                               "set StartDate = SYSUTCDATETIME() " +
                               "where MessageId = @messageId";

            using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction))
            {
                db.Connection.Execute(sql, new { messageId });
                db.Complete();
            }
        }