2 files changed, 13 insertions, 2 deletions
diff --git a/shrine/repositories/letter.go b/shrine/repositories/letter.go
index b9c81aa..a52f5bc 100644
--- a/shrine/repositories/letter.go
+++ b/shrine/repositories/letter.go
@@ -118,6 +118,17 @@ func GetLetterMessages(letterID uint, p meta.Pagination) ([]models.LetterMessage
 	return messages, total
 }
 
+func CountUnreadLetters(userID uint) int64 {
+	var count int64
+	database.DB.Model(&models.LetterParticipant{}).
+		Joins("JOIN letters ON letters.id = letter_participants.letter_id").
+		Joins("JOIN letter_messages ON letter_messages.letter_id = letters.id AND letter_messages.deleted_at IS NULL").
+		Where("letter_participants.user_id = ? AND (letter_participants.last_read_at IS NULL OR letter_messages.created_at > letter_participants.last_read_at)", userID).
+		Distinct("letter_participants.letter_id").
+		Count(&count)
+	return count
+}
+
 func ListLettersForUser(userID uint, p meta.Pagination) ([]models.Letter, int64) {
 	var letters []models.Letter
 	var total int64
diff --git a/shrine/repositories/user.go b/shrine/repositories/user.go
index 73973da..39b75b5 100644
--- a/shrine/repositories/user.go
+++ b/shrine/repositories/user.go
@@ -49,13 +49,13 @@ func UpdateLastSeen(user *models.User) {
 
 func CountCitizens() int64 {
 	var count int64
-	database.DB.Model(&models.User{}).Where("email_verified = ?", true).Count(&count)
+	database.DB.Model(&models.User{}).Where("email_verified = ? AND account_banned = ? AND account_disabled = ?", true, false, false).Count(&count)
 	return count
 }
 
 func CountOnline() int64 {
 	var count int64
-	database.DB.Model(&models.User{}).Where("last_seen_at > ?", time.Now().Add(-5*time.Minute)).Count(&count)
+	database.DB.Model(&models.User{}).Where("last_seen_at > ? AND account_banned = ? AND account_disabled = ?", time.Now().Add(-5*time.Minute), false, false).Count(&count)
 	return count
 }