1. Introduction — The Reddit Scale

Reddit is the self-proclaimed "front page of the internet" and one of the most visited websites on the planet. With 1.7 billion+ monthly visits, 100,000+ active subreddits, and 52 million+ daily active users, it represents one of the most complex community platforms ever built. From a simple link aggregator in 2005, Reddit has evolved into a sprawling ecosystem of threaded discussions, multimedia posts, live events, and real-time chat — all governed by a sophisticated karma and moderation system.

Designing a Reddit-like platform is a classic system design interview question because it touches virtually every distributed systems concept: eventual consistency for vote counts, nested data structures for comment threads, real-time feeds with ranking algorithms, content moderation at scale, and multi-tenant isolation through subreddits. This article walks you through every major component — from the data model to the ranking algorithms, from database sharding to multi-region failover — and concludes with a full 300+ line C# implementation.

Key Numbers at Reddit Scale

The design must accommodate a read-to-write ratio of approximately 100:1, with extremely hot paths for feed reads and comment loading, and a surprisingly write-intensive voting system that must update ranking scores in near real-time.

2. Requirements Clarification

Functional Requirements

Non-Functional Requirements

3. Capacity Estimation & Back-of-Envelope

Traffic Estimates

Storage Estimates

Bandwidth Estimates

Media bandwidth dominates at scale. Video content alone can consume 10+ Gbps of egress. This is why Reddit offloads media to CDN-backed object storage.

4. Data Model Design

Entity Relationship Overview

Core Tables — PostgreSQL Schema

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(40) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    avatar_url TEXT,
    bio TEXT,
    karma_post INTEGER DEFAULT 0,
    karma_comment INTEGER DEFAULT 0,
    karma_total INTEGER GENERATED ALWAYS AS (karma_post + karma_comment) STORED,
    is_premium BOOLEAN DEFAULT FALSE,
    premium_expires_at TIMESTAMPTZ,
    is_suspended BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_active_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_users_username ON users(username);

CREATE TABLE subreddits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(21) UNIQUE NOT NULL,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    sidebar TEXT,
    creator_id UUID REFERENCES users(id),
    icon_url TEXT,
    banner_url TEXT,
    subscriber_count INTEGER DEFAULT 0,
    post_count INTEGER DEFAULT 0,
    is_nsfw BOOLEAN DEFAULT FALSE,
    is_restricted BOOLEAN DEFAULT FALSE,
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_subreddits_name ON subreddits(name);
CREATE INDEX idx_subreddits_subscribers ON subreddits(subscriber_count DESC);

CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(300) NOT NULL,
    body TEXT,
    post_type VARCHAR(20) NOT NULL CHECK (post_type IN ('text','link','image','video','gallery','poll','ama')),
    author_id UUID REFERENCES users(id),
    subreddit_id UUID REFERENCES subreddits(id),
    url TEXT,
    media_refs JSONB DEFAULT '[]',
    flair VARCHAR(50),
    is_locked BOOLEAN DEFAULT FALSE,
    is_pinned BOOLEAN DEFAULT FALSE,
    is_nsfw BOOLEAN DEFAULT FALSE,
    is_spoiler BOOLEAN DEFAULT FALSE,
    upvote_count INTEGER DEFAULT 0,
    downvote_count INTEGER DEFAULT 0,
    score INTEGER GENERATED ALWAYS AS (upvote_count - downvote_count) STORED,
    comment_count INTEGER DEFAULT 0,
    hot_score DOUBLE PRECISION DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_posts_subreddit ON posts(subreddit_id, created_at DESC);
CREATE INDEX idx_posts_author ON posts(author_id, created_at DESC);
CREATE INDEX idx_posts_hot ON posts(hot_score DESC);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    body TEXT NOT NULL,
    author_id UUID REFERENCES users(id),
    post_id UUID REFERENCES posts(id),
    parent_comment_id UUID REFERENCES comments(id),
    depth INTEGER DEFAULT 0,
    path LTREE,
    is_deleted BOOLEAN DEFAULT FALSE,
    is_stickied BOOLEAN DEFAULT FALSE,
    upvote_count INTEGER DEFAULT 0,
    downvote_count INTEGER DEFAULT 0,
    score INTEGER GENERATED ALWAYS AS (upvote_count - downvote_count) STORED,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_comments_post ON comments(post_id, created_at);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id);
CREATE INDEX idx_comments_path ON comments USING GIST(path);

CREATE TABLE votes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    target_id UUID NOT NULL,
    target_type VARCHAR(10) NOT NULL CHECK (target_type IN ('post','comment')),
    value SMALLINT NOT NULL CHECK (value IN (-1, 0, 1)),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, target_id, target_type)
);
CREATE INDEX idx_votes_target ON votes(target_id, target_type);
CREATE INDEX idx_votes_user ON votes(user_id);

CREATE TABLE awards (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    icon_url TEXT,
    cost_coins INTEGER NOT NULL,
    karma_bonus INTEGER DEFAULT 0,
    is_premium_only BOOLEAN DEFAULT FALSE
);

CREATE TABLE user_awards (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    award_id UUID REFERENCES awards(id),
    target_id UUID NOT NULL,
    target_type VARCHAR(10) NOT NULL,
    awarded_by UUID REFERENCES users(id),
    message TEXT,
    awarded_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_user_awards_user ON user_awards(user_id, awarded_at DESC);

CREATE TABLE subreddit_members (
    user_id UUID REFERENCES users(id),
    subreddit_id UUID REFERENCES subreddits(id),
    role VARCHAR(20) CHECK (role IN ('member','moderator','admin')),
    joined_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY(user_id, subreddit_id)
);

5. API Design

RESTful Endpoints

Example: Create Post Request/Response

POST /api/v1/subreddits/programming/posts
Authorization: Bearer <jwt_token>
Content-Type: application/json

{
    "title": "How to Design a Rate Limiter — A Deep Dive",
    "body": "In this article, we explore sliding window algorithms...",
    "post_type": "text",
    "flair": "Article"
}

// Response 201 Created
{
    "id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    "title": "How to Design a Rate Limiter — A Deep Dive",
    "author": {
        "username": "ayodhyya",
        "karma": 45230
    },
    "subreddit": "programming",
    "score": 0,
    "comment_count": 0,
    "created_at": "2026-07-14T10:30:00Z",
    "permalink": "/r/programming/comments/a1b2c3d4/"
}

gRPC Internal APIs

For inter-service communication, we use gRPC with Protocol Buffers:

syntax = "proto3";
package reddit.voting;

service VotingService {
    rpc CastVote(CastVoteRequest) returns (CastVoteResponse);
    rpc GetUserVotes(GetUserVotesRequest) returns (GetUserVotesResponse);
    rpc BulkUpdateScores(BulkUpdateRequest) returns (BulkUpdateResponse);
}

message CastVoteRequest {
    string user_id = 1;
    string target_id = 2;
    TargetType target_type = 3;
    int32 value = 4;
}

enum TargetType { POST = 0; COMMENT = 1; }

message CastVoteResponse {
    bool success = 1;
    int32 new_score = 2;
    string vote_id = 3;
}

6. High-Level Architecture

Architecture Principles

7. Post Creation & Storage Pipeline

Post creation is a multi-step pipeline that goes beyond a simple database insert. We must handle content validation, spam detection, media processing, and index updates — all while keeping the user-facing latency under 500ms.

Post Type Handling

Content Validation Pipeline

public class PostValidationPipeline
{
    private readonly IContentValidator _titleValidator;
    private readonly ISpamDetector _spamDetector;
    private readonly IImageClassifier _nsfwClassifier;

    public async Task<ValidationResult> ValidateAsync(CreatePostRequest request)
    {
        var errors = new List<string>();

        var titleResult = await _titleValidator.ValidateAsync(request.Title);
        if (!titleResult.IsValid)
            errors.AddRange(titleResult.Errors);

        if (request.PostType == PostType.Link && !string.IsNullOrEmpty(request.Url))
        {
            if (!Uri.TryCreate(request.Url, UriKind.Absolute, out _))
                errors.Add("Invalid URL format");
        }

        if (request.PostType == PostType.Image && request.MediaIds?.Any() == true)
        {
            foreach (var mediaId in request.MediaIds)
            {
                var nsfwScore = await _nsfwClassifier.ClassifyAsync(mediaId);
                if (nsfwScore > 0.85) request.IsNsfw = true;
            }
        }

        var spamScore = await _spamDetector.GetSpamScoreAsync(
            request.Title, request.Body, request.AuthorId);

        return new ValidationResult
        {
            IsValid = errors.Count == 0,
            Errors = errors,
            SpamScore = spamScore,
            RequiresReview = spamScore > 0.6
        };
    }
}

8. Comment Thread System

Reddit's nested comment system is one of its defining features. Comments form a tree where each comment can reply to any other comment, creating threads that can nest 10+ levels deep. The design must support efficient tree traversal, collapsing, and sorting.

Tree Representation Strategies

Comment Tree Fetch Query

WITH RECURSIVE comment_tree AS (
    SELECT c.id, c.body, c.author_id, c.parent_comment_id,
        c.depth, c.path, c.upvote_count, c.downvote_count,
        c.upvote_count - c.downvote_count AS score,
        c.created_at, u.username AS author_name,
        ARRAY[c.path] AS sort_path
    FROM comments c
    JOIN users u ON c.author_id = u.id
    WHERE c.post_id = @postId
      AND c.parent_comment_id IS NULL
      AND c.is_deleted = FALSE

    UNION ALL

    SELECT c.id, c.body, c.author_id, c.parent_comment_id,
        c.depth, c.path, c.upvote_count, c.downvote_count,
        c.upvote_count - c.downvote_count AS score,
        c.created_at, u.username AS author_name,
        ct.sort_path || c.path
    FROM comments c
    JOIN users u ON c.author_id = u.id
    JOIN comment_tree ct ON c.parent_comment_id = ct.id
    WHERE c.is_deleted = FALSE AND c.depth <= 10
)
SELECT * FROM comment_tree ORDER BY sort_path;

Sort Options for Comments

Wilson Score for "Best" Sort

The Wilson score interval gives us the lower bound of a 95% confidence interval for the true proportion of upvotes. This naturally favors comments with more votes and higher upvote ratios, preventing a comment with 1 upvote from outranking one with 1000 upvotes.

9. Voting System & Karma Calculation

The voting system is the engine that powers Reddit's content ranking. Every user can upvote (+1), downvote (-1), or retract (0) their vote on any post or comment. Votes must be idempotent and must update the target's score in near real-time.

Vote Ingestion Pipeline

At peak, Reddit processes 250,000+ votes per second. Writing each vote directly to PostgreSQL would overwhelm the database. Instead:

1. Redis Write-Through: The vote is immediately written to Redis as the source of truth for the current user's vote state. This ensures idempotency on rapid clicks.
2. Kafka Buffer: A VoteChanged event is published to Kafka, decoupling the user-facing response from database writes.
3. Batch Aggregator: A consumer groups votes by target_id and batch-updates PostgreSQL every 5 seconds.
4. Score Recalculation: After batch update, the new score is written back to Redis and triggers feed cache invalidation.

Karma Calculation

public class KarmaCalculator
{
    public int CalculatePostKarma(int totalUpvotes, int totalDownvotes)
    {
        int score = totalUpvotes - totalDownvotes;
        if (score <= 0) return 0;

        int karma = 0;
        int remaining = score;

        // Tier 1: 1-10 upvotes = 1 karma each
        int tier1 = Math.Min(remaining, 10);
        karma += tier1;
        remaining -= tier1;

        // Tier 2: 11-100 upvotes = 1 karma per 2 upvotes
        int tier2 = Math.Min(remaining, 90);
        karma += tier2 / 2;
        remaining -= tier2;

        // Tier 3: 100+ upvotes = 1 karma per 10 upvotes
        karma += remaining / 10;

        return karma;
    }

    public int CalculateCommentKarma(int totalUpvotes, int totalDownvotes)
    {
        int score = totalUpvotes - totalDownvotes;
        if (score <= 0) return 0;

        int karma = 0;
        int remaining = score;
        int tier1 = Math.Min(remaining, 50);
        karma += tier1;
        remaining -= tier1;
        karma += remaining / 5;
        return karma;
    }

    public double CalculateControversy(int upvotes, int downvotes)
    {
        if (upvotes + downvotes < 10) return 0;
        int total = upvotes + downvotes;
        double balance = (double)Math.Min(upvotes, downvotes) /
                         Math.Max(upvotes, downvotes);
        return Math.Log10(total) * balance;
    }
}

Anti-Abuse Measures

10. Feed Ranking Algorithms

Reddit's ranking algorithms are the heart of the content discovery experience. The "Hot" algorithm is the most famous, combining score, time decay, and comment activity to surface the most engaging content.

Hot Ranking Algorithm

The log10 function ensures that the difference between 10 and 100 votes matters more than between 10,000 and 10,010 votes. The time component pushes newer content upward, creating a natural rotation.

All Ranking Algorithms

public class FeedRanker
{
    public double CalculateHotScore(int upvotes, int downvotes, DateTime createdAt)
    {
        int score = upvotes - downvotes;
        double order = Math.Log10(Math.Max(Math.Abs(score), 1));
        double sign = score > 0 ? 1.0 : score < 0 ? -1.0 : 0.0;
        double seconds = createdAt.ToUniversalTime()
            .Subtract(new DateTime(1970, 1, 1)).TotalSeconds;
        return sign * order + (seconds / 45000.0);
    }

    public double CalculateTopScore(int upvotes, int downvotes, TimeWindow window)
    {
        int score = upvotes - downvotes;
        double decayFactor = window switch
        {
            TimeWindow.Hour => 1.0,
            TimeWindow.Day => 0.5,
            TimeWindow.Week => 0.2,
            TimeWindow.Month => 0.08,
            _ => 1.0
        };
        return score * decayFactor;
    }

    public double CalculateRisingScore(
        int upvotes, int downvotes, DateTime createdAt, int commentCount)
    {
        double ageHours = DateTime.UtcNow.Subtract(createdAt).TotalHours;
        if (ageHours > 12) return 0;
        int score = upvotes - downvotes;
        double velocity = (score + commentCount * 2.0) / Math.Max(ageHours, 0.1);
        double freshness = Math.Max(0, 12 - ageHours) / 12.0;
        return velocity * freshness;
    }

    public double CalculateControversialScore(int upvotes, int downvotes)
    {
        if (upvotes + downvotes < 10) return 0;
        int total = upvotes + downvotes;
        double ratio = (double)Math.Min(upvotes, downvotes) / Math.Max(upvotes, downvotes);
        return Math.Log10(total) * ratio;
    }
}

public enum TimeWindow { Hour, Day, Week, Month, AllTime }

Ranking Comparison Table

11. Subreddit Management & Moderation

Subreddits are the fundamental organizational unit of Reddit. Each subreddit is an independent community with its own rules, moderators, flairs, and culture.

Moderation Hierarchy

AutoModerator Rules Engine

Ban System

public class ModerationService
{
    public async Task<BanResult> BanUserAsync(
        string moderatorId, string targetUserId,
        string subredditId, BanRequest request)
    {
        var modRole = await _membershipService
            .GetRoleAsync(moderatorId, subredditId);
        if (modRole != ModeratorRole.Moderator &&
            modRole != ModeratorRole.Creator)
            throw new UnauthorizedException("Insufficient permissions");

        var targetRole = await _membershipService
            .GetRoleAsync(targetUserId, subredditId);
        if (targetRole >= modRole)
            throw new InvalidOperationException(
                "Cannot ban a moderator of equal or higher rank");

        var ban = new SubredditBan
        {
            Id = Guid.NewGuid(),
            SubredditId = subredditId,
            UserId = targetUserId,
            BannedBy = moderatorId,
            Reason = request.Reason,
            Duration = request.Duration,
            ExpiresAt = request.Duration.HasValue
                ? DateTime.UtcNow.Add(request.Duration.Value)
                : null,
            CreatedAt = DateTime.UtcNow
        };

        await _database.Bans.InsertAsync(ban);
        await _postService.RemoveAllByUserInSubredditAsync(
            targetUserId, subredditId);

        return new BanResult { Success = true, BanId = ban.Id };
    }
}

12. Search System

Reddit's search must handle millions of queries daily across posts, comments, subreddits, and users. The system combines full-text search with faceted filtering and autocomplete.

Search Ranking Signals

13. Content Feed Generation

Feed generation is the most read-intensive operation in the system. Every page load requires fetching, ranking, and rendering a personalized set of posts.

Feed Types and Their Generation Strategy

Cursor-Based Pagination

public class FeedCursor
{
    public string Encode(FeedItem lastItem)
    {
        var payload = $"{lastItem.Id}|{lastItem.CreatedAt.Ticks}|{lastItem.Score}";
        return Convert.ToBase64String(Encoding.UTF8.GetBytes(payload));
    }

    public FeedCursorData Decode(string cursor)
    {
        var bytes = Convert.FromBase64String(cursor);
        var parts = Encoding.UTF8.GetString(bytes).Split('|');
        return new FeedCursorData
        {
            LastId = parts[0],
            LastTimestamp = new DateTime(long.Parse(parts[1])),
            LastScore = double.Parse(parts[2])
        };
    }
}

public async Task<FeedResult> GetFeedAsync(
    string userId, string sortType, string cursor, int limit = 25)
{
    var cursorData = cursor != null ? _cursor.Decode(cursor) : null;
    var cacheKey = $"feed:{userId}:{sortType}";
    var cached = await _redis.GetAsync<List<FeedItem>>(cacheKey);

    if (cached != null)
    {
        var offset = cursorData != null
            ? cached.FindIndex(f => f.Id == cursorData.LastId) + 1 : 0;
        return new FeedResult
        {
            Items = cached.Skip(offset).Take(limit).ToList(),
            NextCursor = cached.Count > offset + limit
                ? _cursor.Encode(cached[offset + limit - 1]) : null,
            HasMore = cached.Count > offset + limit
        };
    }

    var posts = await _database.QueryAsync<Post>(
        BuildFeedQuery(userId, sortType, cursorData, limit + 1));
    return new FeedResult
    {
        Items = posts.Take(limit).ToList(),
        NextCursor = posts.Count > limit ? _cursor.Encode(posts.Last()) : null,
        HasMore = posts.Count > limit
    };
}

14. Real-Time Updates

Reddit supports real-time features including live comment updates, notification delivery, live threads for major events, and real-time chat in subreddits.

Event Channels

15. Award & Premium System

Reddit's award system provides monetization and social recognition. Users purchase coins with real money, then spend coins to give awards to posts and comments.

Award Tiers

Transaction Flow

public class AwardService
{
    public async Task<AwardResult> GiveAwardAsync(
        string giverId, string targetId, string awardId)
    {
        var award = await _cache.GetAwardAsync(awardId);
        var giver = await _userService.GetUserAsync(giverId);

        if (giver.Coins < award.CostCoins)
            throw new InsufficientCoinsException(
                $"Need {award.CostCoins} coins, have {giver.Coins}");

        await using var tx = await _database.BeginTransactionAsync();
        try
        {
            await _database.ExecuteAsync(
                "UPDATE users SET coins = coins - @Cost WHERE id = @Id",
                new { Cost = award.CostCoins, Id = giverId });

            var recipientId = await GetTargetAuthorAsync(targetId);
            await _database.ExecuteAsync(
                @"INSERT INTO user_awards
                  (user_id, award_id, target_id, target_type, awarded_by)
                  VALUES (@UserId, @AwardId, @TargetId, @TargetType, @AwardedBy)",
                new { UserId = recipientId, AwardId = awardId,
                      TargetId = targetId, TargetType = GetTargetType(targetId),
                      AwardedBy = giverId });

            if (award.KarmaBonus > 0)
            {
                await _database.ExecuteAsync(
                    @"UPDATE users SET karma_total = karma_total + @Bonus
                      WHERE id = @Id",
                    new { Bonus = award.KarmaBonus, Id = recipientId });
            }

            if (award.CoinsToRecipient > 0)
            {
                await _database.ExecuteAsync(
                    @"UPDATE users SET coins = coins + @Credit WHERE id = @Id",
                    new { Credit = award.CoinsToRecipient, Id = recipientId });
            }

            await tx.CommitAsync();
            return new AwardResult { Success = true,
                RemainingCoins = giver.Coins - award.CostCoins };
        }
        catch { await tx.RollbackAsync(); throw; }
    }
}

16. Media Upload Pipeline

Reddit hosts billions of images, videos, and GIFs. The media pipeline handles upload, virus scanning, content moderation, transcoding, and CDN distribution.

Media Constraints

17. Spam Detection & Content Policy

Spam is a persistent challenge for any community platform. Reddit faces spam from automated bots, coordinated inauthentic behavior, and human spammers.

Spam Detection Layers

18. Recommendation Engine

The recommendation engine powers three key features: post recommendations, subreddit suggestions, and home feed personalization.

Feature Engineering

Personalization Algorithm

public class PersonalizationRanker
{
    public double CalculatePersonalizedScore(
        Post post, UserProfile user, UserFeatures features)
    {
        double baseScore = post.HotScore;

        double subscriptionBoost = features.Subscriptions
            .Contains(post.SubredditId) ? 1.5 : 0.3;

        double authorBoost = features.AuthorAffinities
            .GetValueOrDefault(post.AuthorId, 0.5);

        double categoryBoost = features.CategoryAffinities
            .GetValueOrDefault(post.Category, 0.5);

        double timeBoost = IsUserActiveHour(user, DateTime.UtcNow)
            ? 1.2 : 0.8;

        double diversityPenalty = CalculateDiversityPenalty(
            post, features.RecentPostEmbeddings);

        if (post.IsNsfw && !user.ShowNsfw) return 0;

        return baseScore * subscriptionBoost * authorBoost
            * categoryBoost * timeBoost * diversityPenalty;
    }

    private double CalculateDiversityPenalty(
        Post post, List<float[]> recentEmbeddings)
    {
        if (!recentEmbeddings.Any()) return 1.0;
        double maxSimilarity = recentEmbeddings
            .Max(e => CosineSimilarity(post.Embedding, e));
        return maxSimilarity > 0.9 ? 0.3
             : maxSimilarity > 0.7 ? 0.7 : 1.0;
    }
}

19. Database Sharding Strategy

At Reddit's scale, a single PostgreSQL instance cannot hold all data. We shard across multiple dimensions depending on the entity type.

Sharding by Entity Type

20. Caching Strategy

Multi-Level Cache Architecture

Cache Keys and TTLs

21. Multi-Region Design

Data Replication Strategy

22. Cost Estimation

Infrastructure Cost Breakdown (Monthly)

23. Interview Q&A

24. Full C# Implementation

Production-grade C# implementation covering Post Service, Comment Service, Voting Service, Feed Service, Moderation Service, and Award Service with Redis caching, PostgreSQL persistence, and Kafka event publishing.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using Npgsql;
using StackExchange.Redis;
using Confluent.Kafka;

namespace RedditPlatform.Core
{
    public enum PostType { Text, Link, Image, Video, Gallery, Poll, Ama }
    public enum VoteDirection { Downvote = -1, None = 0, Upvote = 1 }
    public enum SortType { Hot, New, Top, Rising, Controversial, Best }
    public enum TimeWindow { Hour, Day, Week, Month, AllTime }
    public enum ModerationAction { Remove, Approve, Ban, Flag, Flair, Lock }

    public class User
    {
        public Guid Id { get; set; }
        public string Username { get; set; } = "";
        public string Email { get; set; } = "";
        public int KarmaPost { get; set; }
        public int KarmaComment { get; set; }
        public int KarmaTotal => KarmaPost + KarmaComment;
        public bool IsPremium { get; set; }
        public int Coins { get; set; }
        public bool IsSuspended { get; set; }
        public DateTime CreatedAt { get; set; }
    }

    public class Post
    {
        public Guid Id { get; set; }
        public string Title { get; set; } = "";
        public string? Body { get; set; }
        public PostType PostType { get; set; }
        public Guid AuthorId { get; set; }
        public Guid SubredditId { get; set; }
        public string? Url { get; set; }
        public List<string> MediaRefs { get; set; } = new();
        public string? Flair { get; set; }
        public bool IsLocked { get; set; }
        public bool IsPinned { get; set; }
        public bool IsNsfw { get; set; }
        public bool IsSpoiler { get; set; }
        public int UpvoteCount { get; set; }
        public int DownvoteCount { get; set; }
        public int Score => UpvoteCount - DownvoteCount;
        public int CommentCount { get; set; }
        public double HotScore { get; set; }
        public DateTime CreatedAt { get; set; }
        public DateTime UpdatedAt { get; set; }
    }

    public class Comment
    {
        public Guid Id { get; set; }
        public string Body { get; set; } = "";
        public Guid AuthorId { get; set; }
        public Guid PostId { get; set; }
        public Guid? ParentCommentId { get; set; }
        public int Depth { get; set; }
        public string Path { get; set; } = "";
        public bool IsDeleted { get; set; }
        public int UpvoteCount { get; set; }
        public int DownvoteCount { get; set; }
        public int Score => UpvoteCount - DownvoteCount;
        public DateTime CreatedAt { get; set; }
    }

    public class Vote
    {
        public Guid Id { get; set; }
        public Guid UserId { get; set; }
        public Guid TargetId { get; set; }
        public string TargetType { get; set; } = "post";
        public VoteDirection Value { get; set; }
        public DateTime CreatedAt { get; set; }
    }

    public class Award
    {
        public Guid Id { get; set; }
        public string Name { get; set; } = "";
        public string? Description { get; set; }
        public string? IconUrl { get; set; }
        public int CostCoins { get; set; }
        public int KarmaBonus { get; set; }
        public int CoinsToRecipient { get; set; }
    }

    public class ModerationLog
    {
        public Guid Id { get; set; }
        public ModerationAction Action { get; set; }
        public Guid ModeratorId { get; set; }
        public Guid? TargetUserId { get; set; }
        public Guid? TargetPostId { get; set; }
        public Guid? TargetCommentId { get; set; }
        public Guid SubredditId { get; set; }
        public string? Reason { get; set; }
        public DateTime CreatedAt { get; set; }
    }

    public record CreatePostRequest(
        string Title, string? Body, PostType PostType,
        string? Url, List<string>? MediaRefs,
        string? Flair, bool IsNsfw, bool IsSpoiler);
    public record CreateCommentRequest(string Body, Guid? ParentCommentId);
    public record CastVoteRequest(Guid TargetId, string TargetType, VoteDirection Value);
    public record FeedItem(Post Post, string SubredditName, string AuthorUsername,
        string? AuthorAvatar, int AuthorKarma, VoteDirection? UserVote);
    public record FeedResult(List<FeedItem> Items, string? NextCursor, bool HasMore);
    public record VoteResult(bool Success, int NewScore, bool Changed);

    public class PlatformConfig
    {
        public string DatabaseConnectionString { get; set; } = "";
        public string RedisConnectionString { get; set; } = "";
        public string KafkaBootstrapServers { get; set; } = "";
        public int FeedCacheTtlMinutes { get; set; } = 5;
        public int PostCacheTtlMinutes { get; set; } = 15;
        public int MaxCommentDepth { get; set; } = 10;
        public int FeedPageSize { get; set; } = 25;
    }

    public class FeedRanker
    {
        public double CalculateHotScore(int upvotes, int downvotes, DateTime createdAt)
        {
            int score = upvotes - downvotes;
            double order = Math.Log10(Math.Max(Math.Abs(score), 1));
            double sign = score > 0 ? 1.0 : score < 0 ? -1.0 : 0.0;
            double seconds = createdAt.ToUniversalTime()
                .Subtract(new DateTime(1970, 1, 1)).TotalSeconds;
            return sign * order + (seconds / 45000.0);
        }

        public double CalculateTopScore(int upvotes, int downvotes, TimeWindow window)
        {
            int score = upvotes - downvotes;
            double decay = window switch
            {
                TimeWindow.Hour => 1.0, TimeWindow.Day => 0.5,
                TimeWindow.Week => 0.2, TimeWindow.Month => 0.08, _ => 1.0
            };
            return score * decay;
        }

        public double CalculateRisingScore(
            int upvotes, int downvotes, DateTime createdAt, int commentCount)
        {
            double ageHours = DateTime.UtcNow.Subtract(createdAt).TotalHours;
            if (ageHours > 12) return 0;
            int score = upvotes - downvotes;
            double velocity = (score + commentCount * 2.0) / Math.Max(ageHours, 0.1);
            return velocity * Math.Max(0, 12 - ageHours) / 12.0;
        }

        public double CalculateControversialScore(int upvotes, int downvotes)
        {
            if (upvotes + downvotes < 10) return 0;
            double ratio = (double)Math.Min(upvotes, downvotes) / Math.Max(upvotes, downvotes);
            return Math.Log10(upvotes + downvotes) * ratio;
        }
    }

    public class KarmaCalculator
    {
        public int CalculatePostKarma(int totalUpvotes, int totalDownvotes)
        {
            int score = totalUpvotes - totalDownvotes;
            if (score <= 0) return 0;
            int karma = 0, remaining = score;
            int t1 = Math.Min(remaining, 10); karma += t1; remaining -= t1;
            int t2 = Math.Min(remaining, 90); karma += t2 / 2; remaining -= t2;
            karma += remaining / 10;
            return karma;
        }

        public int CalculateCommentKarma(int totalUpvotes, int totalDownvotes)
        {
            int score = totalUpvotes - totalDownvotes;
            if (score <= 0) return 0;
            int t1 = Math.Min(score, 50);
            return t1 + (score - t1) / 5;
        }
    }

    public class PostService
    {
        private readonly PlatformConfig _config;
        private readonly NpgsqlConnection _db;
        private readonly IConnectionMultiplexer _redis;
        private readonly IProducer<Null, string> _kafka;
        private readonly FeedRanker _ranker = new();

        public PostService(PlatformConfig config, NpgsqlConnection db,
            IConnectionMultiplexer redis, IProducer<Null, string> kafka)
        {
            _config = config; _db = db; _redis = redis; _kafka = kafka;
        }

        public async Task<Post> CreatePostAsync(
            Guid authorId, Guid subredditId, CreatePostRequest request)
        {
            var post = new Post
            {
                Id = Guid.NewGuid(), Title = request.Title, Body = request.Body,
                PostType = request.PostType, AuthorId = authorId,
                SubredditId = subredditId, Url = request.Url,
                MediaRefs = request.MediaRefs ?? new(), Flair = request.Flair,
                IsNsfw = request.IsNsfw, IsSpoiler = request.IsSpoiler,
                CreatedAt = DateTime.UtcNow, UpdatedAt = DateTime.UtcNow
            };
            post.HotScore = _ranker.CalculateHotScore(0, 0, post.CreatedAt);

            await using var cmd = new NpgsqlCommand(@"
                INSERT INTO posts (id, title, body, post_type, author_id,
                    subreddit_id, url, media_refs, flair, is_nsfw,
                    is_spoiler, hot_score, created_at, updated_at)
                VALUES (@id, @title, @body, @post_type, @author_id,
                    @subreddit_id, @url, @media_refs::jsonb, @flair,
                    @is_nsfw, @is_spoiler, @hot_score, @created_at, @updated_at)", _db);
            cmd.Parameters.AddWithValue("id", post.Id);
            cmd.Parameters.AddWithValue("title", post.Title);
            cmd.Parameters.AddWithValue("body", (object?)post.Body ?? DBNull.Value);
            cmd.Parameters.AddWithValue("post_type", post.PostType.ToString().ToLower());
            cmd.Parameters.AddWithValue("author_id", post.AuthorId);
            cmd.Parameters.AddWithValue("subreddit_id", post.SubredditId);
            cmd.Parameters.AddWithValue("url", (object?)post.Url ?? DBNull.Value);
            cmd.Parameters.AddWithValue("media_refs", JsonSerializer.Serialize(post.MediaRefs));
            cmd.Parameters.AddWithValue("flair", (object?)post.Flair ?? DBNull.Value);
            cmd.Parameters.AddWithValue("is_nsfw", post.IsNsfw);
            cmd.Parameters.AddWithValue("is_spoiler", post.IsSpoiler);
            cmd.Parameters.AddWithValue("hot_score", post.HotScore);
            cmd.Parameters.AddWithValue("created_at", post.CreatedAt);
            cmd.Parameters.AddWithValue("updated_at", post.UpdatedAt);
            await cmd.ExecuteNonQueryAsync();

            var cache = _redis.GetDatabase();
            await cache.KeyDeleteAsync($"subreddit:{subredditId}:hot");
            await _kafka.ProduceAsync("post-events", new Message<Null, string>
            {
                Value = JsonSerializer.Serialize(new
                {
                    EventType = "PostCreated", PostId = post.Id.ToString(),
                    AuthorId = authorId.ToString(), Title = post.Title
                })
            });
            return post;
        }

        public async Task<Post?> GetPostAsync(Guid postId)
        {
            var cache = _redis.GetDatabase();
            var cached = await cache.StringGetAsync($"post:{postId}");
            if (cached.HasValue)
                return JsonSerializer.Deserialize<Post>(cached!);

            await using var cmd = new NpgsqlCommand(@"
                SELECT id, title, body, post_type, author_id, subreddit_id,
                    url, media_refs, flair, is_locked, is_pinned, is_nsfw,
                    is_spoiler, upvote_count, downvote_count, comment_count,
                    hot_score, created_at, updated_at
                FROM posts WHERE id = @id", _db);
            cmd.Parameters.AddWithValue("id", postId);
            await using var reader = await cmd.ExecuteReaderAsync();
            if (!await reader.ReadAsync()) return null;

            var post = new Post
            {
                Id = reader.GetGuid(0), Title = reader.GetString(1),
                Body = reader.IsDBNull(2) ? null : reader.GetString(2),
                PostType = Enum.Parse<PostType>(reader.GetString(3), true),
                AuthorId = reader.GetGuid(4), SubredditId = reader.GetGuid(5),
                Url = reader.IsDBNull(6) ? null : reader.GetString(6),
                MediaRefs = JsonSerializer.Deserialize<List<string>>(reader.GetString(7)) ?? new(),
                Flair = reader.IsDBNull(8) ? null : reader.GetString(8),
                IsLocked = reader.GetBoolean(9), IsPinned = reader.GetBoolean(10),
                IsNsfw = reader.GetBoolean(11), IsSpoiler = reader.GetBoolean(12),
                UpvoteCount = reader.GetInt32(13), DownvoteCount = reader.GetInt32(14),
                CommentCount = reader.GetInt32(15), HotScore = reader.GetDouble(16),
                CreatedAt = reader.GetDateTime(17), UpdatedAt = reader.GetDateTime(18)
            };
            await cache.StringSetAsync($"post:{postId}",
                JsonSerializer.Serialize(post),
                TimeSpan.FromMinutes(_config.PostCacheTtlMinutes));
            return post;
        }
    }

    public class CommentService
    {
        private readonly PlatformConfig _config;
        private readonly NpgsqlConnection _db;
        private readonly IConnectionMultiplexer _redis;
        private readonly IProducer<Null, string> _kafka;

        public CommentService(PlatformConfig config, NpgsqlConnection db,
            IConnectionMultiplexer redis, IProducer<Null, string> kafka)
        {
            _config = config; _db = db; _redis = redis; _kafka = kafka;
        }

        public async Task<Comment> CreateCommentAsync(
            Guid authorId, Guid postId, CreateCommentRequest request)
        {
            string path = ""; int depth = 0;
            if (request.ParentCommentId.HasValue)
            {
                var parent = await GetCommentAsync(request.ParentCommentId.Value);
                if (parent == null)
                    throw new InvalidOperationException("Parent comment not found");
                if (parent.Depth >= _config.MaxCommentDepth)
                    throw new InvalidOperationException($"Max depth exceeded");
                path = $"{parent.Path}.{parent.Id}"; depth = parent.Depth + 1;
            }
            else { path = "0"; depth = 0; }

            var comment = new Comment
            {
                Id = Guid.NewGuid(), Body = request.Body, AuthorId = authorId,
                PostId = postId, ParentCommentId = request.ParentCommentId,
                Depth = depth, Path = path, CreatedAt = DateTime.UtcNow
            };

            await using var cmd = new NpgsqlCommand(@"
                INSERT INTO comments (id, body, author_id, post_id,
                    parent_comment_id, depth, path, created_at)
                VALUES (@id, @body, @author_id, @post_id,
                    @parent_id, @depth, @path::ltree, @created_at)", _db);
            cmd.Parameters.AddWithValue("id", comment.Id);
            cmd.Parameters.AddWithValue("body", comment.Body);
            cmd.Parameters.AddWithValue("author_id", comment.AuthorId);
            cmd.Parameters.AddWithValue("post_id", comment.PostId);
            cmd.Parameters.AddWithValue("parent_id",
                (object?)comment.ParentCommentId ?? DBNull.Value);
            cmd.Parameters.AddWithValue("depth", comment.Depth);
            cmd.Parameters.AddWithValue("path", comment.Path);
            cmd.Parameters.AddWithValue("created_at", comment.CreatedAt);
            await cmd.ExecuteNonQueryAsync();

            await using var countCmd = new NpgsqlCommand(@"
                UPDATE posts SET comment_count = comment_count + 1,
                    updated_at = NOW() WHERE id = @id", _db);
            countCmd.Parameters.AddWithValue("id", postId);
            await countCmd.ExecuteNonQueryAsync();

            var cache = _redis.GetDatabase();
            await cache.KeyDeleteAsync($"post:{postId}");
            await cache.KeyDeleteAsync($"comments:{postId}");

            await _kafka.ProduceAsync("comment-events", new Message<Null, string>
            {
                Value = JsonSerializer.Serialize(new
                {
                    EventType = "CommentCreated", CommentId = comment.Id.ToString(),
                    PostId = postId.ToString(), AuthorId = authorId.ToString()
                })
            });
            return comment;
        }

        public async Task<List<Comment>> GetCommentTreeAsync(Guid postId)
        {
            var cache = _redis.GetDatabase();
            var cached = await cache.StringGetAsync($"comments:{postId}");
            if (cached.HasValue)
                return JsonSerializer.Deserialize<List<Comment>>(cached!) ?? new();

            var comments = new List<Comment>();
            await using var cmd = new NpgsqlCommand(@"
                WITH RECURSIVE tree AS (
                    SELECT id, body, author_id, post_id, parent_comment_id,
                        depth, path, upvote_count, downvote_count, created_at
                    FROM comments WHERE post_id = @postId
                        AND parent_comment_id IS NULL AND is_deleted = FALSE
                    UNION ALL
                    SELECT c.id, c.body, c.author_id, c.post_id,
                        c.parent_comment_id, c.depth, c.path,
                        c.upvote_count, c.downvote_count, c.created_at
                    FROM comments c JOIN tree t ON c.parent_comment_id = t.id
                    WHERE c.is_deleted = FALSE AND c.depth <= @maxDepth
                ) SELECT * FROM tree ORDER BY path", _db);
            cmd.Parameters.AddWithValue("postId", postId);
            cmd.Parameters.AddWithValue("maxDepth", _config.MaxCommentDepth);
            await using var reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                comments.Add(new Comment
                {
                    Id = reader.GetGuid(0), Body = reader.GetString(1),
                    AuthorId = reader.GetGuid(2), PostId = reader.GetGuid(3),
                    ParentCommentId = reader.IsDBNull(4) ? null : reader.GetGuid(4),
                    Depth = reader.GetInt32(5), Path = reader.GetString(6),
                    UpvoteCount = reader.GetInt32(7), DownvoteCount = reader.GetInt32(8),
                    CreatedAt = reader.GetDateTime(9)
                });
            }
            await cache.StringSetAsync($"comments:{postId}",
                JsonSerializer.Serialize(comments), TimeSpan.FromMinutes(10));
            return comments;
        }

        private async Task<Comment?> GetCommentAsync(Guid id)
        {
            await using var cmd = new NpgsqlCommand(@"
                SELECT id, body, author_id, post_id, parent_comment_id,
                    depth, path, upvote_count, downvote_count, created_at
                FROM comments WHERE id = @id", _db);
            cmd.Parameters.AddWithValue("id", id);
            await using var r = await cmd.ExecuteReaderAsync();
            if (!await r.ReadAsync()) return null;
            return new Comment
            {
                Id = r.GetGuid(0), Body = r.GetString(1), AuthorId = r.GetGuid(2),
                PostId = r.GetGuid(3),
                ParentCommentId = r.IsDBNull(4) ? null : r.GetGuid(4),
                Depth = r.GetInt32(5), Path = r.GetString(6),
                UpvoteCount = r.GetInt32(7), DownvoteCount = r.GetInt32(8),
                CreatedAt = r.GetDateTime(9)
            };
        }
    }

    public class VotingService
    {
        private readonly PlatformConfig _config;
        private readonly NpgsqlConnection _db;
        private readonly IConnectionMultiplexer _redis;
        private readonly IProducer<Null, string> _kafka;

        public VotingService(PlatformConfig config, NpgsqlConnection db,
            IConnectionMultiplexer redis, IProducer<Null, string> kafka)
        {
            _config = config; _db = db; _redis = redis; _kafka = kafka;
        }

        public async Task<VoteResult> CastVoteAsync(
            Guid userId, CastVoteRequest request)
        {
            var cache = _redis.GetDatabase();
            var voteKey = $"vote:{userId}:{request.TargetType}:{request.TargetId}";
            var existing = await cache.StringGetAsync(voteKey);
            var currentVote = existing.HasValue
                ? Enum.Parse<VoteDirection>(existing!) : VoteDirection.None;

            if (currentVote == request.Value)
                return new VoteResult(true, 0, false);

            await cache.StringSetAsync(voteKey, request.Value.ToString(),
                TimeSpan.FromHours(24));
            int delta = (int)request.Value - (int)currentVote;

            await _kafka.ProduceAsync("vote-events", new Message<Null, string>
            {
                Value = JsonSerializer.Serialize(new
                {
                    EventType = "VoteChanged", UserId = userId.ToString(),
                    TargetId = request.TargetId.ToString(),
                    TargetType = request.TargetType, Delta = delta
                })
            });
            return new VoteResult(true, delta, true);
        }

        public async Task BatchUpdateScoresAsync()
        {
            await using var postCmd = new NpgsqlCommand(@"
                UPDATE posts SET upvote_count = sub.up, downvote_count = sub.down
                FROM (SELECT target_id,
                    COUNT(*) FILTER (WHERE value = 1) AS up,
                    COUNT(*) FILTER (WHERE value = -1) AS down
                FROM votes WHERE target_type = 'post'
                GROUP BY target_id) sub
                WHERE posts.id = sub.target_id", _db);
            await postCmd.ExecuteNonQueryAsync();

            await using var cmtCmd = new NpgsqlCommand(@"
                UPDATE comments SET upvote_count = sub.up, downvote_count = sub.down
                FROM (SELECT target_id,
                    COUNT(*) FILTER (WHERE value = 1) AS up,
                    COUNT(*) FILTER (WHERE value = -1) AS down
                FROM votes WHERE target_type = 'comment'
                GROUP BY target_id) sub
                WHERE comments.id = sub.target_id", _db);
            await cmtCmd.ExecuteNonQueryAsync();
        }
    }

    public class FeedService
    {
        private readonly PlatformConfig _config;
        private readonly NpgsqlConnection _db;
        private readonly IConnectionMultiplexer _redis;
        private readonly FeedRanker _ranker = new();

        public FeedService(PlatformConfig config, NpgsqlConnection db,
            IConnectionMultiplexer redis)
        {
            _config = config; _db = db; _redis = redis;
        }

        public async Task<FeedResult> GetFeedAsync(
            string userId, SortType sortType, string? cursor, int limit = 25)
        {
            var cache = _redis.GetDatabase();
            var cacheKey = $"feed:{userId}:{sortType}";
            var cached = await cache.StringGetAsync(cacheKey);

            List<FeedItem> feedItems;
            if (cached.HasValue)
                feedItems = JsonSerializer.Deserialize<List<FeedItem>>(cached!) ?? new();
            else
            {
                feedItems = await BuildFeedFromDbAsync(userId, sortType);
                await cache.StringSetAsync(cacheKey,
                    JsonSerializer.Serialize(feedItems),
                    TimeSpan.FromMinutes(_config.FeedCacheTtlMinutes));
            }

            int offset = 0;
            if (!string.IsNullOrEmpty(cursor))
            {
                var cursorBytes = Convert.FromBase64String(cursor);
                var lastId = Encoding.UTF8.GetString(cursorBytes).Split('|')[0];
                offset = feedItems.FindIndex(f => f.Post.Id.ToString() == lastId) + 1;
            }

            var page = feedItems.Skip(offset).Take(limit).ToList();
            string? nextCursor = null;
            if (offset + limit < feedItems.Count)
            {
                var last = feedItems[offset + limit - 1];
                nextCursor = Convert.ToBase64String(Encoding.UTF8.GetBytes(
                    $"{last.Post.Id}|{last.Post.CreatedAt.Ticks}|{last.Post.Score}"));
            }
            return new FeedResult(page, nextCursor, offset + limit < feedItems.Count);
        }

        public async Task<FeedResult> GetSubredditFeedAsync(
            Guid subredditId, SortType sortType, string? cursor, int limit = 25)
        {
            var orderBy = sortType switch
            {
                SortType.New => "created_at DESC",
                SortType.Top => "score DESC",
                _ => "hot_score DESC"
            };
            await using var cmd = new NpgsqlCommand($@"
                SELECT p.id, p.title, p.body, p.post_type, p.author_id,
                    p.subreddit_id, p.url, p.upvote_count, p.downvote_count,
                    p.comment_count, p.hot_score, p.created_at, u.username
                FROM posts p JOIN users u ON p.author_id = u.id
                WHERE p.subreddit_id = @subId
                ORDER BY {orderBy} LIMIT @limit", _db);
            cmd.Parameters.AddWithValue("subId", subredditId);
            cmd.Parameters.AddWithValue("limit", limit + 1);

            var items = new List<FeedItem>();
            await using var r = await cmd.ExecuteReaderAsync();
            bool hasMore = false; int count = 0;
            while (await r.ReadAsync())
            {
                count++;
                if (count > limit) { hasMore = true; break; }
                var post = new Post
                {
                    Id = r.GetGuid(0), Title = r.GetString(1),
                    Body = r.IsDBNull(2) ? null : r.GetString(2),
                    PostType = Enum.Parse<PostType>(r.GetString(3), true),
                    AuthorId = r.GetGuid(4), SubredditId = r.GetGuid(5),
                    Url = r.IsDBNull(6) ? null : r.GetString(6),
                    UpvoteCount = r.GetInt32(7), DownvoteCount = r.GetInt32(8),
                    CommentCount = r.GetInt32(9), HotScore = r.GetDouble(10),
                    CreatedAt = r.GetDateTime(11)
                };
                items.Add(new FeedItem(post, "", r.GetString(12), null, 0, null));
            }
            return new FeedResult(items, null, hasMore);
        }

        private async Task<List<FeedItem>> BuildFeedFromDbAsync(
            string userId, SortType sortType)
        {
            var orderBy = sortType switch
            {
                SortType.New => "p.created_at DESC",
                SortType.Top => "p.score DESC",
                _ => "p.hot_score DESC"
            };
            await using var cmd = new NpgsqlCommand($@"
                SELECT p.id, p.title, p.body, p.post_type, p.author_id,
                    p.subreddit_id, p.url, p.upvote_count, p.downvote_count,
                    p.comment_count, p.hot_score, p.created_at, p.is_nsfw, u.username
                FROM posts p JOIN users u ON p.author_id = u.id
                WHERE p.is_nsfw = FALSE
                ORDER BY {orderBy} LIMIT 500", _db);
            var items = new List<FeedItem>();
            await using var r = await cmd.ExecuteReaderAsync();
            while (await r.ReadAsync())
            {
                var post = new Post
                {
                    Id = r.GetGuid(0), Title = r.GetString(1),
                    Body = r.IsDBNull(2) ? null : r.GetString(2),
                    PostType = Enum.Parse<PostType>(r.GetString(3), true),
                    AuthorId = r.GetGuid(4), SubredditId = r.GetGuid(5),
                    Url = r.IsDBNull(6) ? null : r.GetString(6),
                    UpvoteCount = r.GetInt32(7), DownvoteCount = r.GetInt32(8),
                    CommentCount = r.GetInt32(9), HotScore = r.GetDouble(10),
                    CreatedAt = r.GetDateTime(11), IsNsfw = r.GetBoolean(12)
                };
                items.Add(new FeedItem(post, "", r.GetString(13), null, 0, null));
            }
            return items;
        }
    }

    public class ModerationService
    {
        private readonly PlatformConfig _config;
        private readonly NpgsqlConnection _db;
        private readonly IConnectionMultiplexer _redis;
        private readonly IProducer<Null, string> _kafka;

        public ModerationService(PlatformConfig config, NpgsqlConnection db,
            IConnectionMultiplexer redis, IProducer<Null, string> kafka)
        {
            _config = config; _db = db; _redis = redis; _kafka = kafka;
        }

        public async Task<ModerationLog> ModerateAsync(
            Guid moderatorId, Guid subredditId,
            ModerationAction action, string? reason,
            Guid? targetUserId = null, Guid? targetPostId = null)
        {
            var log = new ModerationLog
            {
                Id = Guid.NewGuid(), Action = action,
                ModeratorId = moderatorId, TargetUserId = targetUserId,
                TargetPostId = targetPostId, SubredditId = subredditId,
                Reason = reason, CreatedAt = DateTime.UtcNow
            };
            await using var cmd = new NpgsqlCommand(@"
                INSERT INTO moderation_logs
                    (id, action, moderator_id, target_user_id,
                     target_post_id, subreddit_id, reason, created_at)
                VALUES (@id, @action, @mod_id, @user_id,
                    @post_id, @sub_id, @reason, @created_at)", _db);
            cmd.Parameters.AddWithValue("id", log.Id);
            cmd.Parameters.AddWithValue("action", log.Action.ToString());
            cmd.Parameters.AddWithValue("mod_id", log.ModeratorId);
            cmd.Parameters.AddWithValue("user_id",
                (object?)log.TargetUserId ?? DBNull.Value);
            cmd.Parameters.AddWithValue("post_id",
                (object?)log.TargetPostId ?? DBNull.Value);
            cmd.Parameters.AddWithValue("sub_id", log.SubredditId);
            cmd.Parameters.AddWithValue("reason",
                (object?)log.Reason ?? DBNull.Value);
            cmd.Parameters.AddWithValue("created_at", log.CreatedAt);
            await cmd.ExecuteNonQueryAsync();

            if (action == ModerationAction.Remove && targetPostId.HasValue)
            {
                await using var del = new NpgsqlCommand(
                    "DELETE FROM posts WHERE id = @id", _db);
                del.Parameters.AddWithValue("id", targetPostId.Value);
                await del.ExecuteNonQueryAsync();
                await _redis.GetDatabase().KeyDeleteAsync($"post:{targetPostId}");
            }
            return log;
        }

        public async Task<bool> IsUserBannedAsync(Guid userId, Guid subredditId)
        {
            await using var cmd = new NpgsqlCommand(@"
                SELECT COUNT(*) FROM subreddit_bans
                WHERE user_id = @userId AND subreddit_id = @subId
                  AND (expires_at IS NULL OR expires_at > NOW())", _db);
            cmd.Parameters.AddWithValue("userId", userId);
            cmd.Parameters.AddWithValue("subId", subredditId);
            return (long)(await cmd.ExecuteScalarAsync() ?? 0L) > 0;
        }
    }

    public class AwardService
    {
        private readonly PlatformConfig _config;
        private readonly NpgsqlConnection _db;
        private readonly IConnectionMultiplexer _redis;

        public AwardService(PlatformConfig config, NpgsqlConnection db,
            IConnectionMultiplexer redis)
        {
            _config = config; _db = db; _redis = redis;
        }

        public async Task<bool> GiveAwardAsync(
            string giverId, string targetId, Guid awardId)
        {
            await using var tx = await _db.BeginTransactionAsync();
            try
            {
                await using var ac = new NpgsqlCommand(@"
                    SELECT cost_coins, karma_bonus, coins_to_recipient
                    FROM awards WHERE id = @id", _db);
                ac.Parameters.AddWithValue("id", awardId);
                await using var ar = await ac.ExecuteReaderAsync();
                if (!await ar.ReadAsync()) { await tx.RollbackAsync(); return false; }
                int cost = ar.GetInt32(0), karma = ar.GetInt32(1), credit = ar.GetInt32(2);
                await ar.Close();

                await using var bc = new NpgsqlCommand(
                    "SELECT coins FROM users WHERE id = @id", _db);
                bc.Parameters.AddWithValue("id", Guid.Parse(giverId));
                if ((int)(await bc.ExecuteScalarAsync() ?? 0) < cost)
                { await tx.RollbackAsync(); return false; }

                await using var dc = new NpgsqlCommand(
                    "UPDATE users SET coins = coins - @c WHERE id = @id", _db);
                dc.Parameters.AddWithValue("c", cost);
                dc.Parameters.AddWithValue("id", Guid.Parse(giverId));
                await dc.ExecuteNonQueryAsync();

                if (credit > 0)
                {
                    await using var cc = new NpgsqlCommand(
                        "UPDATE users SET coins = coins + @c WHERE id = @id", _db);
                    cc.Parameters.AddWithValue("c", credit);
                    cc.Parameters.AddWithValue("id", Guid.Parse(targetId));
                    await cc.ExecuteNonQueryAsync();
                }
                if (karma > 0)
                {
                    await using var kc = new NpgsqlCommand(
                        "UPDATE users SET karma_total = karma_total + @k WHERE id = @id", _db);
                    kc.Parameters.AddWithValue("k", karma);
                    kc.Parameters.AddWithValue("id", Guid.Parse(targetId));
                    await kc.ExecuteNonQueryAsync();
                }
                await tx.CommitAsync();
                return true;
            }
            catch { await tx.RollbackAsync(); throw; }
        }
    }

    public class RedditPlatform
    {
        public PostService Posts { get; }
        public CommentService Comments { get; }
        public VotingService Voting { get; }
        public FeedService Feed { get; }
        public ModerationService Moderation { get; }
        public AwardService Awards { get; }

        public RedditPlatform(PlatformConfig config)
        {
            var db = new NpgsqlConnection(config.DatabaseConnectionString);
            var redis = ConnectionMultiplexer.Connect(config.RedisConnectionString);
            var kafka = new ProducerBuilder<Null, string>(
                new ProducerConfig { BootstrapServers = config.KafkaBootstrapServers }).Build();

            Posts = new PostService(config, db, redis, kafka);
            Comments = new CommentService(config, db, redis, kafka);
            Voting = new VotingService(config, db, redis, kafka);
            Feed = new FeedService(config, db, redis);
            Moderation = new ModerationService(config, db, redis, kafka);
            Awards = new AwardService(config, db, redis);
        }
    }
}

25. Conclusion

Designing a Reddit-scale community platform is a masterclass in distributed systems engineering. Every major subsystem — from the voting pipeline to the comment tree, from feed generation to content moderation — involves deep trade-offs between consistency, availability, latency, and cost.

The C# implementation demonstrates how these concepts translate into production code: post creation with Kafka events, comment trees with LTREE paths, voting with Redis write-through, and feeds with multi-level caching and cursor-based pagination.

For senior+ engineers, the key insight is that every design decision is a trade-off. Pre-computed feeds trade freshness for latency. Eventual consistency trades accuracy for availability. Database sharding trades cross-query flexibility for write scalability. The art of system design is choosing the right trade-offs for your specific constraints and scale.