// Define a reusable column group
@Selection
struct Timestamps {
    let createdAt: Date
    let updatedAt: Date?
}

// Use in multiple tables
@Table
nonisolated struct RemindersList: Identifiable {
    let id: UUID
    var title = ""
    let timestamps: Timestamps  // Embedded column group
}

@Table
nonisolated struct Reminder: Identifiable {
    let id: UUID
    var title = ""
    var isCompleted = false
    let timestamps: Timestamps  // Same group, reused
}

CREATE TABLE "remindersLists" (
    "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
    "title" TEXT NOT NULL DEFAULT '',
    "createdAt" TEXT NOT NULL,
    "updatedAt" TEXT
) STRICT

// Query a field inside the group
RemindersList
    .where { $0.timestamps.createdAt <= cutoffDate }
    .fetchAll(db)

// Compare entire group (flattens to tuple in SQL)
RemindersList
    .where {
        $0.timestamps <= Timestamps(createdAt: date1, updatedAt: date2)
    }

@Selection
struct Row {
    let reminderTitle: String
    let listTitle: String
    let timestamps: Timestamps  // Nested group
}

let results = try Reminder
    .join(RemindersList.all) { $0.remindersListID.eq($1.id) }
    .select {
        Row.Columns(
            reminderTitle: $0.title,
            listTitle: $1.title,
            timestamps: $0.timestamps  // Pass entire group
        )
    }
    .fetchAll(db)

import CasePaths

@Table
nonisolated struct Attachment: Identifiable {
    let id: UUID
    let kind: Kind

    @CasePathable @Selection
    enum Kind {
        case link(URL)
        case note(String)
        case image(URL)
    }
}

CREATE TABLE "attachments" (
    "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
    "link" TEXT,
    "note" TEXT,
    "image" TEXT
) STRICT

// Fetch all — decoding determines which case
let attachments = try Attachment.all.fetchAll(db)

// Filter by case
let images = try Attachment
    .where { $0.kind.image.isNot(nil) }
    .fetchAll(db)

try Attachment.insert {
    Attachment.Draft(kind: .note("Hello world!"))
}
.execute(db)
// Inserts: (id, NULL, 'Hello world!', NULL)

try Attachment.find(id).update {
    $0.kind = .link(URL(string: "https://example.com")!)
}
.execute(db)
// Sets link column, NULLs note and image columns

@Table
nonisolated struct Attachment: Identifiable {
    let id: UUID
    let kind: Kind

    @CasePathable @Selection
    enum Kind {
        case link(URL)
        case note(String)
        case image(Attachment.Image)  // Fully qualify nested types
    }

    @Selection
    struct Image {
        var caption = ""
        var url: URL
    }
}

CREATE TABLE "attachments" (
    "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
    "link" TEXT,
    "note" TEXT,
    "caption" TEXT,
    "url" TEXT
) STRICT

@DatabaseFunction
func isPastDue(reminder: Reminder) -> Bool {
    !reminder.isCompleted && reminder.dueDate < Date()
}

// Use in queries — columns are flattened/reconstituted automatically
let pastDue = try Reminder
    .where { $isPastDue(reminder: $0) }
    .fetchAll(db)

@Model class Attachment { var isActive: Bool }
@Model class Link: Attachment { var url: URL }
@Model class Note: Attachment { var note: String }
@Model class Image: Attachment { var url: URL }
// Each needs explicit init calling super.init

extension Item {
    // Common filters as static properties
    static let active = Item.where { !$0.isArchived && !$0.isDeleted }
    static let inStock = Item.where(\.isInStock)
    static let outOfStock = Item.where { !$0.isInStock }

    // Parameterized scopes as static methods
    static func createdAfter(_ date: Date) -> Where<Item> {
        Item.where { $0.createdAt > date }
    }

    static func inCategory(_ category: String) -> Where<Item> {
        Item.where { $0.category.eq(category) }
    }
}

// Chain scopes together
let results = try Item.active
    .inStock
    .order(by: \.title)
    .fetchAll(db)

// Combine with additional filtering
let recent = try Item.active
    .createdAfter(lastWeek)
    .inCategory("Electronics")
    .fetchAll(db)

extension Item {
    // Standard "all visible" query
    static let visible = Item
        .where { !$0.isDeleted }
        .order(by: \.position)

    // With eager-loaded relationships
    static let withCategory = Item
        .join(Category.all) { $0.categoryID.eq($1.id) }
}

// Use as base for all queries
@FetchAll(Item.visible) var items

extension Where<Item> {
    // Add filters to existing queries
    func onlyActive() -> Where<Item> {
        self.where { !$0.isArchived }
    }

    func matching(_ search: String) -> Where<Item> {
        self.where { $0.title.contains(search) || $0.notes.contains(search) }
    }
}

// Chain compositions
let results = try Item.inStock
    .onlyActive()
    .matching(searchText)
    .fetchAll(db)

extension Item {
    // Fetch with common options
    static func search(
        _ query: String,
        category: String? = nil,
        limit: Int = 50
    ) -> some Statement<Item> {
        var base = Item.active.where { $0.title.contains(query) }
        if let category {
            base = base.where { $0.category.eq(category) }
        }
        return base.order(by: \.title).limit(limit)
    }
}

// Clean call sites
let results = try Item.search("phone", category: "Electronics").fetchAll(db)

struct PlayersRequest: FetchKeyRequest {
    struct Value {
        let injuredPlayerCount: Int
        let players: [Player]
    }

    func fetch(_ db: Database) throws -> Value {
        try Value(
            injuredPlayerCount: Player
                .where(\.isInjured)
                .fetchCount(db),
            players: Player
                .where { !$0.isInjured }
                .order(by: \.name)
                .limit(10)
                .fetchAll(db)
        )
    }
}

// Use in SwiftUI views
struct PlayersView: View {
    @Fetch(PlayersRequest()) var response

    var body: some View {
        ForEach(response.players) { player in
            Text(player.name)
        }
        Button("View injured players (\(response.injuredPlayerCount))") {
            // ...
        }
    }
}

struct DashboardRequest: FetchKeyRequest {
    struct Value: Sendable {
        let totalItems: Int
        let activeItems: [Item]
        let categories: [Category]
        let recentActivity: [ActivityLog]
    }

    func fetch(_ db: Database) throws -> Value {
        try Value(
            totalItems: Item.count().fetchOne(db) ?? 0,
            activeItems: Item
                .where { !$0.isArchived }
                .order(by: \.updatedAt.desc())
                .limit(10)
                .fetchAll(db),
            categories: Category
                .order(by: \.name)
                .fetchAll(db),
            recentActivity: ActivityLog
                .order(by: \.timestamp.desc())
                .limit(20)
                .fetchAll(db)
        )
    }
}

@Fetch(DashboardRequest()) var dashboard

@Fetch var searchResults = SearchRequest.Value()

// Load with initial query
.task {
    try? await $searchResults.load(SearchRequest(query: "Swift"))
}

// Reload with new query
Button("Search") {
    Task {
        try? await $searchResults.load(SearchRequest(query: newQuery))
    }
}

@Fetch(
    PlayersRequest(),
    animation: .default
) var response

// Case conversion
let upper = try Item
    .select { $0.title.upper() }
    .fetchAll(db)

let lower = try Item
    .select { $0.title.lower() }
    .fetchAll(db)

// Trimming whitespace
let trimmed = try Item
    .select { $0.title.trim() }       // Both sides
    .fetchAll(db)

let leftTrimmed = try Item
    .select { $0.title.ltrim() }      // Left only
    .fetchAll(db)

// Substring extraction
let firstThree = try Item
    .select { $0.title.substr(0, 3) }  // Start index, length
    .fetchAll(db)

// String replacement
let cleaned = try Item
    .select { $0.title.replace("old", "new") }
    .fetchAll(db)

// String length
let lengths = try Item
    .select { ($0.title, $0.title.length()) }
    .fetchAll(db)

// Find substring position (1-indexed, 0 if not found)
let positions = try Item
    .where { $0.title.instr("search") > 0 }
    .fetchAll(db)

// Pattern matching
let matches = try Item
    .where { $0.title.like("%phone%") }           // SQL LIKE
    .fetchAll(db)

let prefixed = try Item
    .where { $0.title.hasPrefix("iPhone") }       // Starts with
    .fetchAll(db)

let suffixed = try Item
    .where { $0.title.hasSuffix("Pro") }          // Ends with
    .fetchAll(db)

let containing = try Item
    .where { $0.title.contains("Max") }           // Contains
    .fetchAll(db)

// Case-insensitive comparison
let caseInsensitive = try Item
    .where { $0.title.collate(.nocase).eq("IPHONE") }
    .fetchAll(db)

// Coalesce — return first non-null value
let displayName = try User
    .select { $0.nickname ?? $0.firstName ?? "Anonymous" }
    .fetchAll(db)

// ifnull — alternative if null
let safePrice = try Item
    .select { $0.discountPrice.ifnull($0.price) }
    .fetchAll(db)

// Check for null
let withDueDate = try Reminder
    .where { $0.dueDate.isNot(nil) }
    .fetchAll(db)

let noDueDate = try Reminder
    .where { $0.dueDate.is(nil) }
    .fetchAll(db)

// Null-safe comparison in ordering
let sorted = try Item
    .order { $0.priority.desc(nulls: .last) }  // Nulls at end
    .fetchAll(db)

// IN — check if value is in a set
let selected = try Item
    .where { $0.id.in(selectedIds) }
    .fetchAll(db)

// IN with subquery
let itemsInActiveCategories = try Item
    .where { $0.categoryID.in(
        Category.where(\.isActive).select(\.id)
    )}
    .fetchAll(db)

// NOT IN
let excluded = try Item
    .where { !$0.id.in(excludedIds) }
    .fetchAll(db)

// BETWEEN — range check
let midRange = try Item
    .where { $0.price.between(10, and: 100) }
    .fetchAll(db)

// Swift range syntax
let inRange = try Item
    .where { (10...100).contains($0.price) }
    .fetchAll(db)

struct ContentView: View {
    @Fetch(Search(), animation: .default)
    private var results = Search.Value()

    @State var query = ""

    var body: some View {
        List { /* ... */ }
            .searchable(text: $query)
            .task(id: query) {
                try await $results.load(Search(query: query), animation: .default)
            }
    }
}

struct Search: FetchKeyRequest {
    var query = ""
    struct Value { var items: [Item] = [] }

    func fetch(_ db: Database) throws -> Value {
        let search = Item
            .where { $0.title.contains(query) }
            .order { $0.title }
        return try Value(items: search.fetchAll(db))
    }
}

// Get unique categories
let categories = try Item
    .select(\.category)
    .distinct()
    .fetchAll(db)

// Distinct with multiple columns
let uniquePairs = try Item
    .select { ($0.category, $0.status) }
    .distinct()
    .fetchAll(db)