// a method to send a private message to a user, invoked by other methods
        public static bool SendPrivateMessage(string sender, string recipient, string subject, string body)
        {
            using (var db = new voatEntities())
            {
                try
                {
                    var privateMessage = new Privatemessage
                    {
                        Sender = sender,
                        Recipient = recipient,
                        Timestamp = DateTime.Now,
                        Subject = subject,
                        Body = body,
                        Status = true,
                        Markedasunread = true
                    };

                    db.Privatemessages.Add(privateMessage);
                    db.SaveChanges();

                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }

        // returns true if saved, false otherwise
        public static bool? CheckIfSaved(string userToCheck, int messageId)
        {
            using (voatEntities db = new voatEntities())
            {

                var cmd = db.Database.Connection.CreateCommand();
                cmd.CommandText = "SELECT COUNT(*) FROM Savingtracker WITH (NOLOCK) WHERE UserName = @UserName AND MessageId = @MessageId";

                var param = cmd.CreateParameter();
                param.ParameterName = "UserName";
                param.DbType = System.Data.DbType.String;
                param.Value = userToCheck;
                cmd.Parameters.Add(param);

                param = cmd.CreateParameter();
                param.ParameterName = "MessageId";
                param.DbType = System.Data.DbType.String;
                param.Value = messageId;
                cmd.Parameters.Add(param);

                if (cmd.Connection.State != System.Data.ConnectionState.Open)
                {
                    cmd.Connection.Open();
                }

                int count = (int)cmd.ExecuteScalar();

                return count > 0;
            }

            //using (var db = new voatEntities())
            //{
            //    return db.Savingtrackers.Where(u => u.UserName == userToCheck && u.MessageId == messageId).AsNoTracking().Any();
            //}
        }

        public ApiBadge BadgeInfo(string badgeId)
        {
            ApiBadge badgeInfo = CacheHandler.Register<ApiBadge>(String.Format("LegacyApi.ApiBadge.{0}", badgeId),
             new Func<ApiBadge>(() =>
             {
                 using (voatEntities db = new voatEntities(CONSTANTS.CONNECTION_READONLY))
                 {
                     var badge = _db.Badges.Find(badgeId);

                     if (badge == null)
                     {
                         throw new HttpResponseException(HttpStatusCode.NotFound);
                     }

                     var resultModel = new ApiBadge
                     {
                         BadgeId = badge.BadgeId,
                         BadgeGraphics = badge.BadgeGraphics,
                         Name = badge.BadgeName,
                         Title = badge.BadgeTitle
                     };

                     return resultModel;
                 }
             }), TimeSpan.FromHours(5));
            return badgeInfo;
        }

        // a user wishes to save a submission, save it
        public static void SaveSubmission(int submissionId, string userWhichSaved)
        {
            var result = CheckIfSaved(userWhichSaved, submissionId);

            using (var db = new voatEntities())
            {
                if (result == true)
                {
                    // Already saved, unsave
                    UnSaveSubmission(userWhichSaved, submissionId);
                }
                else
                {
                    // register save
                    var tmpSavingTracker = new Savingtracker
                    {
                        MessageId = submissionId,
                        UserName = userWhichSaved,
                        Timestamp = DateTime.Now
                    };
                    db.Savingtrackers.Add(tmpSavingTracker);
                    db.SaveChanges();
                }
            }
        }

        // a user wishes to save a comment, save it
        public static void SaveComment(int commentId, string userWhichSaved)
        {
            var result = CheckIfSavedComment(userWhichSaved, commentId);

            using (var db = new voatEntities())
            {
                if (result == true)
                {
                    // Already saved, unsave
                    UnSaveComment(userWhichSaved, commentId);
                }
                else
                {
                    // register save
                    var tmpSavingTracker = new Commentsavingtracker
                    {
                        CommentId = commentId,
                        UserName = userWhichSaved,
                        Timestamp = DateTime.Now
                    };
                    db.Commentsavingtrackers.Add(tmpSavingTracker);
                    db.SaveChanges();
                }
            }
        }

 // check if a given user does not want to see custom CSS styles
 public static bool CustomCssDisabledForUser(string userName)
 {
     using (var db = new voatEntities())
     {
         var result = db.Userpreferences.Find(userName);
         return result != null && result.Disable_custom_css;
     }
 }

 // check if a given user wants to see NSFW (adult) content
 public static bool AdultContentEnabled(string userName)
 {
     using (var db = new voatEntities())
     {
         var result = db.Userpreferences.Find(userName);
         return result != null && result.Enable_adult_content;
     }
 }

 // get total number of comment replies for a given user
 public static int CommentRepliesCount(string userName)
 {
     using (var db = new voatEntities())
     {
         var commentReplies = db.Commentreplynotifications.Where(s => s.Recipient.Equals(userName, StringComparison.OrdinalIgnoreCase));
         if (!commentReplies.Any()) return 0;
         return commentReplies.Any() ? commentReplies.Count() : 0;
     }
 }

        // returns -1:downvoted, 1:upvoted, 0:not voted
        public static int CheckIfVoted(string userToCheck, int messageId)
        {
            using (var db = new voatEntities())
            {
                var checkResult = db.Votingtrackers.Where(u => u.UserName == userToCheck && u.MessageId == messageId)
                        .AsNoTracking()
                        .FirstOrDefault();

                int intCheckResult = checkResult != null ? checkResult.VoteStatus.Value : 0;
                return intCheckResult;
            }
        }

        public static async Task SendUserMentionNotification(string user, Comment comment)
        {
            if (comment != null)
            {
                if (!User.UserExists(user))
                {
                    return;
                }
                try
                {
                    string recipient = User.OriginalUsername(user);

                    var commentReplyNotification = new Commentreplynotification();
                    using (var _db = new voatEntities())
                    {
                        var submission = DataCache.Submission.Retrieve(comment.MessageId);
                        var subverse = DataCache.Subverse.Retrieve(submission.Subverse);

                        commentReplyNotification.CommentId = comment.Id;
                        commentReplyNotification.SubmissionId = comment.MessageId.Value;
                        commentReplyNotification.Recipient = recipient;
                        if (submission.Anonymized || subverse.anonymized_mode)
                        {
                            commentReplyNotification.Sender = (new Random()).Next(10000, 20000).ToString(CultureInfo.InvariantCulture);
                        }
                        else
                        {
                            commentReplyNotification.Sender = comment.Name;
                        }
                        commentReplyNotification.Body = comment.CommentContent;
                        commentReplyNotification.Subverse = subverse.name;
                        commentReplyNotification.Status = true;
                        commentReplyNotification.Timestamp = DateTime.Now;

                        commentReplyNotification.Subject = String.Format("@{0} mentioned you in a comment", comment.Name, submission.Title);

                        _db.Commentreplynotifications.Add(commentReplyNotification);
                       
                        await _db.SaveChangesAsync();
                    }

                    // get count of unread notifications
                    int unreadNotifications = User.UnreadTotalNotificationsCount(commentReplyNotification.Recipient);

                    // send SignalR realtime notification to recipient
                    var hubContext = GlobalHost.ConnectionManager.GetHubContext<MessagingHub>();
                    hubContext.Clients.User(commentReplyNotification.Recipient).setNotificationsPending(unreadNotifications);
                }
                catch (Exception ex) {
                    throw ex;
                }
            }
        }

 public IEnumerable<string> BannedHostnames()
 {
     IEnumerable<string> bannedSubs = CacheHandler.Register<IEnumerable<string>>("LegacyApi.BannedHostnames",
       new Func<IList<string>>(() =>
       {
           using (voatEntities db = new voatEntities(CONSTANTS.CONNECTION_READONLY))
           {
               var bannedHostnames = db.Banneddomains.OrderBy(s => s.Added_on).ToList();
               return bannedHostnames.Select(item => "Hostname: " + item.Hostname + ", reason: " + item.Reason + ", added on: " + item.Added_on + ", added by: " + item.Added_by).ToList();
           }
       }), TimeSpan.FromHours(12));
     return bannedSubs;
 }

        // returns -1:downvoted, 1:upvoted, or 0:not voted
        public static int CheckIfVotedComment(string userToCheck, int commentId)
        {
            int intCheckResult = 0;

            using (var db = new voatEntities())
            {
                var checkResult = db.Commentvotingtrackers.FirstOrDefault(b => b.CommentId == commentId && b.UserName == userToCheck);

                intCheckResult = checkResult != null ? checkResult.VoteStatus.Value : 0;

                return intCheckResult;
            }
        }

        // get comment contribution points for a user
        public static int CommentKarma(string userName)
        {
            string cacheKey = CacheKey(userName, KarmaCacheType.Comment);

            object cacheData = Cache[cacheKey];
            if (cacheData != null)
            {
                return (int)cacheData;
            }

            int count = 0;
            using (voatEntities db = new voatEntities())
            {

                var cmd = db.Database.Connection.CreateCommand();
                cmd.CommandText = "SELECT ISNULL(SUM(Likes - Dislikes), 0) FROM Comments WITH (NOLOCK) WHERE Name = @Name";

                var param = cmd.CreateParameter();
                param.ParameterName = "Name";
                param.DbType = System.Data.DbType.String;
                param.Value = userName;
                cmd.Parameters.Add(param);

                if (cmd.Connection.State != System.Data.ConnectionState.Open)
                {
                    cmd.Connection.Open();
                }
                long l = (long)cmd.ExecuteScalar();
                count = (int)l;
                Cache.Insert(cacheKey, count, null, DateTime.Now.AddSeconds(cacheTimeInSeconds), System.Web.Caching.Cache.NoSlidingExpiration);

            }

            return count;

            //using (var db = new voatEntities())
            //{
            //    try
            //    {
            //        return db.Comments.Where(c => c.Name.Trim().Equals(userName, StringComparison.OrdinalIgnoreCase))
            //            .Select(c => c.Likes - c.Dislikes)
            //            .Sum();
            //    }
            //    catch (Exception)
            //    {
            //        return 0;
            //    }
            //}
        }

        // get session count for given subverse
        //HACK: This query is expensive. Cache results.
        public static int ActiveSessionsForSubverse(string subverseName)
        {
            try
            {
                string cacheKey = String.Format("activeSubSessions_{0}", subverseName);

                object cacheData = System.Web.HttpContext.Current.Cache[cacheKey];
                if (cacheData != null)
                {
                    return (int)cacheData;
                }

                int count = 0;
                using (voatEntities db = new voatEntities())
                {

                    var cmd = db.Database.Connection.CreateCommand();
                    cmd.CommandText = "SELECT ISNULL(COUNT(*),0) FROM [dbo].[Sessiontracker] WITH (NOLOCK) WHERE [Subverse] = @Subverse";
                    var param = cmd.CreateParameter();
                    param.ParameterName = "Subverse";
                    param.DbType = System.Data.DbType.String;
                    param.Value = subverseName;
                    cmd.Parameters.Add(param);

                    if (cmd.Connection.State != System.Data.ConnectionState.Open)
                    {
                        cmd.Connection.Open();
                    }
                    count = (int)cmd.ExecuteScalar();
                    System.Web.HttpContext.Current.Cache.Insert(cacheKey, count, null, DateTime.Now.AddSeconds(120), System.Web.Caching.Cache.NoSlidingExpiration);

                }

                return count;

                //using (var db = new voatEntities())
                //{
                //    var result = from sessions in db.Sessiontrackers
                //                 where sessions.Subverse.Equals(subverseName)
                //                 select sessions;

                //    return result.Count();
                //}
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public static bool IsHostnameBanned(string hostnameToCheck)
        {
            using (var db = new voatEntities())
            {
                var bannedHostname = db.Banneddomains.FirstOrDefault(r => r.Hostname.Equals(hostnameToCheck, StringComparison.OrdinalIgnoreCase));

                // manual ban for blogspot
                if (hostnameToCheck.Contains("blogspot"))
                {
                    return true;
                }

                // look for exact match
                return bannedHostname != null;
            }
        }

        // a method to mark single or all private messages as read for a given user
        public static async Task<bool> MarkPrivateMessagesAsRead(bool? markAll, string userName, int? itemId)
        {
            using (var db = new voatEntities())
            {
                try
                {
                    // mark all items as read
                    if (markAll != null && (bool) markAll)
                    {
                        IQueryable<Privatemessage> unreadPrivateMessages = db.Privatemessages
                                                                            .Where(s => s.Recipient.Equals(userName, StringComparison.OrdinalIgnoreCase) && s.Status)
                                                                            .OrderByDescending(s => s.Timestamp)
                                                                            .ThenBy(s => s.Sender);

                        if (!unreadPrivateMessages.Any()) return false;

                        foreach (var singleMessage in unreadPrivateMessages.ToList())
                        {
                            singleMessage.Status = false;
                        }
                        await db.SaveChangesAsync();
                        return true;
                    }

                    // mark single item as read
                    if (itemId != null)
                    {
                        var privateMessageToMarkAsread = db.Privatemessages.FirstOrDefault(s => s.Recipient.Equals(userName, StringComparison.OrdinalIgnoreCase) && s.Status && s.Id == itemId);
                        if (privateMessageToMarkAsread == null) return false;

                        var item = db.Privatemessages.Find(itemId);
                        item.Status = false;
                        await db.SaveChangesAsync();
                        return true;
                    }
                    return false;
                }
                catch (Exception)
                {
                    return false;
                }
            }

        }

        public static async Task SendUserMentionNotification(string user, Message message)
        {
            if (message != null)
            {
                if (!User.UserExists(user))
                {
                    return;
                }

                string recipient = User.OriginalUsername(user);

                var commentReplyNotification = new Commentreplynotification();
                using (var _db = new voatEntities())
                {
                    //commentReplyNotification.CommentId = comment.Id;
                    commentReplyNotification.SubmissionId = message.Id;
                    commentReplyNotification.Recipient = recipient;
                    if (message.Anonymized || message.Subverses.anonymized_mode)
                    {
                        commentReplyNotification.Sender = (new Random()).Next(10000, 20000).ToString(CultureInfo.InvariantCulture);
                    }
                    else
                    {
                        commentReplyNotification.Sender = message.Name;
                    }
                    commentReplyNotification.Body = message.MessageContent;
                    commentReplyNotification.Subverse = message.Subverse;
                    commentReplyNotification.Status = true;
                    commentReplyNotification.Timestamp = DateTime.Now;

                    commentReplyNotification.Subject = String.Format("@{0} mentioned you in post '{1}'", message.Name, message.Title);

                    _db.Commentreplynotifications.Add(commentReplyNotification);
                    await _db.SaveChangesAsync();
                }

                // get count of unread notifications
                int unreadNotifications = User.UnreadTotalNotificationsCount(commentReplyNotification.Recipient);

                // send SignalR realtime notification to recipient
                var hubContext = GlobalHost.ConnectionManager.GetHubContext<MessagingHub>();
                hubContext.Clients.User(commentReplyNotification.Recipient).setNotificationsPending(unreadNotifications);
            }
        }

        public static Subverse Retrieve(string subverse)
        {
            if (!String.IsNullOrEmpty(subverse)) {
                string cacheKey = CacheHandler.Keys.SubverseInfo(subverse);

                Subverse sub = (Subverse)CacheHandler.Retrieve(cacheKey);
                if (sub == null) {
                    sub = (Subverse)CacheHandler.Register(cacheKey, new Func<object>(() => {

                        using (voatEntities db = new voatEntities()) {
                            return db.Subverses.Where(x => x.name == subverse).FirstOrDefault();
                        }

                    }), TimeSpan.FromMinutes(5), 50);
                }
                return sub;
            }
            return null;
        }

        // block a subverse
        public static void BlockSubverse(string userName, string subverse)
        {
            using (var db = new voatEntities())
            {
                // unblock if subverse is already blocked
                if (IsUserBlockingSubverse(userName, subverse))
                {
                    var subverseBlock = db.UserBlockedSubverses.FirstOrDefault(n => n.SubverseName.ToLower() == subverse.ToLower() && n.Username == userName);
                    if (subverseBlock != null) db.UserBlockedSubverses.Remove(subverseBlock);
                    db.SaveChanges();
                    return;
                }

                // add a new block
                var blockedSubverse = new UserBlockedSubverse { Username = userName, SubverseName = subverse };
                db.UserBlockedSubverses.Add(blockedSubverse);
                db.SaveChanges();
            }
        }

        public static int CommentCount(int submissionID)
        {
            int count = 0;

            string cacheKey = String.Format("comment.count.{0}", submissionID).ToString();
            object data = CacheHandler.Retrieve(cacheKey);
            if (data == null)
            {

                data = CacheHandler.Register(cacheKey, new Func<object>(() =>
                {
                    using (voatEntities db = new voatEntities())
                    {
                        var cmd = db.Database.Connection.CreateCommand();
                        cmd.CommandText = "SELECT COUNT(*) FROM Comments WITH (NOLOCK) WHERE MessageID = @MessageID AND Name != 'deleted'";
                        var param = cmd.CreateParameter();
                        param.ParameterName = "MessageID";
                        param.DbType = System.Data.DbType.Int32;
                        param.Value = submissionID;
                        cmd.Parameters.Add(param);

                        if (cmd.Connection.State != System.Data.ConnectionState.Open)
                        {
                            cmd.Connection.Open();
                        }
                        return (int)cmd.ExecuteScalar();
                    }

                }), TimeSpan.FromMinutes(2), 1);

                count = (int)data;
            }
            else {
                count = (int)data;
            }
            return count;
        }