// 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 whoaverseEntities())
            {
                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;
                }
            }
        }

            public void PullsDefaultFrontpage(whoaverseEntities db, Defaultsubverse defaultsubverse, List<Message> messages)
            {
                // Arrange
                // TODO: See if we can coax Autofixture into handling this for us
                if (defaultsubverse.name.Length > 20)
                    defaultsubverse.name = defaultsubverse.name.Substring(0, 19);
                db.Defaultsubverses.Add(defaultsubverse);
                db.Subverses.Add(new Subverse {name = defaultsubverse.name, title = new Guid().ToString()});
                db.SaveChanges();

                foreach (var message in messages)
                {
                    // TODO: See if we can coax Autofixture into handling this for us
                    if (message.Thumbnail.Length > 40)
                        message.Thumbnail = message.Thumbnail.Substring(0, 39);
                    message.Subverse = defaultsubverse.name;
                }
                db.Messages.AddRange(messages);
                db.SaveChanges();

                var homeController = new HomeController(db);
                homeController.ControllerContext = GetControllerContext(false);

                // Act
                var actionResult = (ViewResult) homeController.Index(null);
                var model = (PaginatedList<Message>) actionResult.Model;

                // Assert
                Assert.True(model.Count == messages.Count);
            }

 // returns true if saved, false otherwise
 public static bool? CheckIfSavedComment(string userToCheck, int commentId)
 {
     using (var db = new whoaverseEntities())
     {
         return db.Commentsavingtrackers.Where(b => b.CommentId == commentId && b.UserName == userToCheck).AsNoTracking().Any();
     }
 }

        public ApiBadge BadgeInfo(string badgeId)
        {
            ApiBadge badgeInfo = CacheHandler.Register<ApiBadge>(String.Format("LegacyApi.ApiBadge.{0}", badgeId),
             new Func<ApiBadge>(() =>
             {
                 using (whoaverseEntities db = new whoaverseEntities(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 comment, save it
        public static void SaveComment(int commentId, string userWhichSaved)
        {
            var result = CheckIfSavedComment(userWhichSaved, commentId);

            using (var db = new whoaverseEntities())
            {
                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();
                }
            }
        }

 // returns true if saved, false otherwise
 public static bool? CheckIfSaved(string userToCheck, int messageId)
 {
     using (var db = new whoaverseEntities())
     {
         return db.Savingtrackers.Where(u => u.UserName == userToCheck && u.MessageId == messageId).AsNoTracking().Any();
     }
 }

        // returns true if saved, false otherwise
        public static bool? CheckIfSavedComment(string userToCheck, int commentId)
        {
            using (whoaverseEntities db = new whoaverseEntities())
            {

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

                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 = "CommentId";
                param.DbType = System.Data.DbType.String;
                param.Value = commentId;
                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 whoaverseEntities())
            //{
            //    return db.Commentsavingtrackers.Where(b => b.CommentId == commentId && b.UserName == userToCheck).AsNoTracking().Any();
            //}
        }

        // 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 whoaverseEntities())
            {
                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();
                }
            }
        }

 // check if a given user does not want to see custom CSS styles
 public static bool CustomCssDisabledForUser(string userName)
 {
     using (var db = new whoaverseEntities())
     {
         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 whoaverseEntities())
     {
         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 whoaverseEntities())
     {
         var commentReplies = db.Commentreplynotifications.Where(s => s.Recipient.Equals(userName, StringComparison.OrdinalIgnoreCase));
         if (!commentReplies.Any()) return 0;
         return commentReplies.Any() ? commentReplies.Count() : 0;
     }
 }

        // a user has saved this comment earlier and wishes to unsave it, delete the record
        private static void UnSaveComment(string userWhichSaved, int commentId)
        {
            using (var db = new whoaverseEntities())
            {
                var votingTracker = db.Commentsavingtrackers.FirstOrDefault(b => b.CommentId == commentId && b.UserName == userWhichSaved);

                if (votingTracker == null) return;
                // delete vote history
                db.Commentsavingtrackers.Remove(votingTracker);
                db.SaveChanges();
            }
        }

        // a user has saved this submission earlier and wishes to unsave it, delete the record
        private static void UnSaveSubmission(string userWhichSaved, int messageId)
        {
            using (var db = new whoaverseEntities())
            {
                var saveTracker = db.Savingtrackers.FirstOrDefault(b => b.MessageId == messageId && b.UserName == userWhichSaved);

                if (saveTracker == null) return;
                //delete vote history
                db.Savingtrackers.Remove(saveTracker);
                db.SaveChanges();
            }
        }

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

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

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

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

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

                return intCheckResult;
            }
        }

 public IEnumerable<string> BannedHostnames()
 {
     IEnumerable<string> bannedSubs = CacheHandler.Register<IEnumerable<string>>("LegacyApi.BannedHostnames",
       new Func<IList<string>>(() =>
       {
           using (whoaverseEntities db = new whoaverseEntities(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;
 }

        // 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 (whoaverseEntities db = new whoaverseEntities())
            {

                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 whoaverseEntities())
            //{
            //    try
            //    {
            //        return db.Comments.Where(c => c.Name.Trim().Equals(userName, StringComparison.OrdinalIgnoreCase))
            //            .Select(c => c.Likes - c.Dislikes)
            //            .Sum();
            //    }
            //    catch (Exception)
            //    {
            //        return 0;
            //    }
            //}
        }

        public static bool IsHostnameBanned(string hostnameToCheck)
        {
            using (var db = new whoaverseEntities())
            {
                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 whoaverseEntities())
            {
                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 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 (whoaverseEntities db = new whoaverseEntities()) {
                            return db.Subverses.Where(x => x.name == subverse).FirstOrDefault();
                        }

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