# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "rails", "~> 8.0"
  gem "sqlite3"
end

require "active_record"
require "action_controller/railtie"
require "logger"

# Set up ActiveRecord logging to see the queries
ActiveRecord::Base.logger = Logger.new($stdout)
ActiveRecord::Base.logger.level = Logger::DEBUG

# Connect to an in-memory SQLite database
ActiveRecord::Base.establish_connection(
  adapter: "sqlite3",
  database: ":memory:"
)

# Define the schema
ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.string :title
  end

  create_table :comments, force: true do |t|
    t.references :post
    t.string :body
    t.integer :score, default: 0
  end
end

# Models
class Post < ActiveRecord::Base
  has_many :comments

  # This instance method uses `where`, which cannot be optimized by `includes`
  # because it creates a new query each time, bypassing any preloaded data
  def high_score_comments
    comments.where("score > ?", 5)
  end
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

# Seed data
puts "\n" + "=" * 60
puts "SEEDING DATA"
puts "=" * 60 + "\n\n"

5.times do |i|
  post = Post.create!(title: "Post #{i + 1}")
  10.times do |j|
    Comment.create!(post: post, body: "Comment #{j + 1}", score: rand(1..10))
  end
end

puts "\n" + "=" * 60
puts "DEMONSTRATION 1: The N+1 Problem"
puts "=" * 60
puts "Fetching all posts and calling high_score_comments on each..."
puts "Watch for repeated SELECT statements on comments table:\n\n"

posts = Post.all
posts.each do |post|
  post.high_score_comments.to_a
end

puts "\n" + "=" * 60
puts "DEMONSTRATION 2: includes() Does NOT Fix This"
puts "=" * 60
puts "Now trying with includes(:comments)..."
puts "The N+1 still happens because where() bypasses preloaded data:\n\n"

posts_with_includes = Post.includes(:comments).all
posts_with_includes.each do |post|
  # Even though comments are preloaded, calling where() on them
  # creates a new query - the preloaded data is ignored
  post.high_score_comments.to_a
end

puts "\n" + "=" * 60
puts "CONCLUSION"
puts "=" * 60
puts <<~EXPLANATION

  The `includes` method preloads the association into memory, but when you
  call `where()` on that association, ActiveRecord issues a new database
  query instead of filtering the preloaded records in Ruby.

  This is a common source of N+1 queries that cannot be fixed with eager
  loading. Possible solutions include:

  1. Filter in Ruby: comments.select { |c| c.score > 5 }
  2. Use a separate association with a scope
  3. Preload into a hash and filter manually

EXPLANATION