# Load and execute
week = AppQuery[:weekly_sales].with_binds(week: 1, year: 2025)
week.entries
#=> [{"week" => 2025-01-13, "category" => "Electronics", "revenue" => 12500, "target_met" => true}, ...]

# Filter results (query wraps in CTE, :_ references it)
week.count
#=> 5
week.count("SELECT * FROM :_ WHERE NOT target_met")
#=> 3

# Extract a column efficiently (only fetches that column)
week.column(:category)
#=> ["Electronics", "Clothing", "Home & Garden"]

# Named binds with defaults
AppQuery[:weekly_sales].select_all(binds: {min_revenue: 5000})

# ERB templating
AppQuery("SELECT * FROM contracts <%= order_by(ordering) %>")
  .render(ordering: {year: :desc}).select_all

# Custom type casting
AppQuery("SELECT metadata FROM products").select_all(cast: {metadata: :json})

# Inspect/mock CTEs for testing
query.prepend_cte("sales AS (SELECT * FROM mock_data)")

AppQuery[:weekly_sales].select_all(binds: {week: 1, year: 2025})
#=> [{"week" => 1, "category" => "Electronics", "revenue" => 12500}, ...]

class WeeklySalesQuery < ApplicationQuery
  include AppQuery::Paginatable
  per_page 25

  bind :week
  bind :year, default: 2026

  cast metadata: :json

  # add factory methods for specific purposes
  def self.build(page: 1, week:, year: 2026)
    new(week:, year:).paginate(page:)
  end
end

WeeklySalesQuery.build(week: 1).entries

# Testdrive from console
[postgresql]> AppQuery(%{select date('now') as today}).select_all.entries
=> [{"today" => Fri, 02 Jan 2026}]

[postgresql]> AppQuery(%{select date('now') as today}).select_one
=> {"today" => Fri, 02 Jan 2026}

[postgresql]> AppQuery(%{select date('now') as today}).select_value
=> Fri, 02 Jan 2026

# AppQuery
AppQuery(%{select date('now') as today}).select_one
=> {"today" => Fri, 02 Jan 2026}

# Compare with raw ActiveRecord
ActiveRecord::Base.connection.select_one(%{select date('now') as today})
=> {"today" => "2025-12-20"}  # String, not Date!

# Custom casting
AppQuery("SELECT metadata FROM products").select_all(cast: {metadata: :json})

# Named binds
AppQuery(%{select now() - (:interval)::interval as date})
  .select_value(binds: {interval: '2 days'})

# Binds default to nil - add SQL defaults via COALESCE
AppQuery(<<~SQL).select_all(binds: {ts1: 2.days.ago, ts2: Time.now})
  SELECT generate_series(
    :ts1::timestamp,
    :ts2::timestamp,
    COALESCE(:interval, '5 minutes')::interval
  ) AS series
SQL

articles = [
  [1, "Using my new static site generator", 2.months.ago.to_date],
  [2, "Let's learn SQL", 1.month.ago.to_date],
]

q = AppQuery(<<~SQL, cast: {published_on: :date}).render(articles:)
  WITH articles(id, title, published_on) AS (<%= values(articles) %>)
  SELECT * FROM articles ORDER BY id DESC
SQL

# Query the CTE directly
q.select_all("SELECT * FROM articles WHERE id < 2")

# Query the result (via :_ placeholder)
q.select_one("SELECT * FROM :_ LIMIT 1")
q.first  # shorthand

# Rewrite CTEs
q.replace_cte("settings(cutoff) AS (VALUES(DATE '2024-01-01'))")
q.prepend_cte("mock_data AS (SELECT 1)")
q.append_cte("extra AS (SELECT 2)")

# Dynamic ORDER BY
q = AppQuery("SELECT * FROM articles <%= order_by(ordering) %>")
q.render(ordering: {published_on: :desc, title: :asc}).select_all

# Pagination
AppQuery("SELECT * FROM users <%= paginate(page: page, per_page: per_page) %>")
  .render(page: 2, per_page: 25).select_all

# Optional clauses using instance variables
AppQuery(<<~SQL).render(order: nil)  # @order is nil, clause is skipped
  SELECT * FROM articles
  <%= @order.presence && order_by(order) %>
SQL

# Return as string
csv = AppQuery[:users].copy_to
#=> "id,name\n1,Alice\n2,Bob\n..."

# Write to file
AppQuery[:users].copy_to(to: "export.csv")

# Stream to IO
File.open("users.csv.gz", "wb") do |f|
  gz = Zlib::GzipWriter.new(f)
  AppQuery[:users].copy_to(to: gz)
  gz.close
end

# spec/queries/reports/weekly_query_spec.rb
RSpec.describe Reports::WeeklyQuery, type: :query, binds: {since: 3.weeks.ago} do
  describe "CTE articles" do
    specify do
      expect(described_query.entries).to \
        include(a_hash_including("article_id" => 1))
    end
  end
end