Query objects for complex database queries

class PostAnalyticsQuery
  attr_reader :relation

  def initialize(relation = Post.all)
    @relation = relation
  end

  def popular_posts(timeframe: 1.week.ago)
    @relation
      .where('created_at >= ?', timeframe)
      .where('views_count > ?', 1000)
      .order(views_count: :desc)
  end

  def trending_posts
    @relation
      .joins(:comments)
      .where('comments.created_at >= ?', 24.hours.ago)
      .group('posts.id')
      .having('COUNT(comments.id) > ?', 5)
      .order('COUNT(comments.id) DESC')
  end

  def by_category_stats
    @relation
      .joins(:category)
      .group('categories.name')
      .select(
        'categories.name as category_name',
        'COUNT(posts.id) as post_count',
        'AVG(posts.views_count) as avg_views',
        'MAX(posts.views_count) as max_views'
      )
  end

  def author_performance(min_posts: 5)
    @relation
      .joins(:user)
      .group('users.id')
      .having('COUNT(posts.id) >= ?', min_posts)
      .select(
        'users.name',
        'COUNT(posts.id) as total_posts',
        'SUM(posts.views_count) as total_views',
        'AVG(posts.views_count) as avg_views_per_post'
      )
      .order('total_views DESC')
  end

  def search_with_relevance(term)
    @relation
      .where("title ILIKE :term OR content ILIKE :term", term: "%#{term}%")
      .select(
        'posts.*',
        "CASE
           WHEN title ILIKE :exact THEN 3
           WHEN title ILIKE :term THEN 2
           WHEN content ILIKE :term THEN 1
           ELSE 0
         END as relevance",
        exact: term,
        term: "%#{term}%"
      )
      .order('relevance DESC, views_count DESC')
  end
end

# Chainable query objects
class BaseQuery
  attr_reader :relation

  def initialize(relation)
    @relation = relation
  end

  def call
    @relation
  end
end

class ActivePostsQuery < BaseQuery
  def call
    @relation.where(status: 'published')
  end
end

class RecentPostsQuery < BaseQuery
  def call(days = 7)
    @relation.where('created_at >= ?', days.days.ago)
  end
end

# Chaining:
# posts = Post.all
# posts = ActivePostsQuery.new(posts).call
# posts = RecentPostsQuery.new(posts).call(30)