Query System

/**
 * Creates a listenable, typed query generated by SQLDelight
 * @param identifier Unique identifier for driver-side caching
 * @param queryKeys Array of table names this query depends on for change notifications
 * @param driver Database driver for execution
 * @param query SQL query string
 * @param mapper Function to convert cursor rows to typed objects
 * @returns Query instance that supports listener registration
 */
fun <RowType : Any> Query(
    identifier: Int,
    queryKeys: Array<out String>,
    driver: SqlDriver,
    query: String,
    mapper: (SqlCursor) -> RowType
): Query<RowType>

/**
 * Creates a listenable, typed query with file and label information
 * @param identifier Unique identifier for driver-side caching
 * @param queryKeys Array of table names this query depends on
 * @param driver Database driver for execution
 * @param fileName Source file name for debugging
 * @param label Query label for debugging
 * @param query SQL query string
 * @param mapper Function to convert cursor rows to typed objects
 * @returns Query instance that supports listener registration
 */
fun <RowType : Any> Query(
    identifier: Int,
    queryKeys: Array<out String>,
    driver: SqlDriver,
    fileName: String,
    label: String,
    query: String,
    mapper: (SqlCursor) -> RowType
): Query<RowType>

/**
 * Creates an executable query without listener support
 * @param identifier Unique identifier for driver-side caching
 * @param driver Database driver for execution
 * @param query SQL query string
 * @param mapper Function to convert cursor rows to typed objects
 * @returns ExecutableQuery instance for one-time execution
 */
fun <RowType : Any> Query(
    identifier: Int,
    driver: SqlDriver,
    query: String,
    mapper: (SqlCursor) -> RowType
): ExecutableQuery<RowType>

/**
 * Creates an executable query with debugging information but without listener support
 * @param identifier Unique identifier for driver-side caching
 * @param driver Database driver for execution
 * @param fileName Source file name for debugging
 * @param label Query label for debugging
 * @param query SQL query string
 * @param mapper Function to convert cursor rows to typed objects
 * @returns ExecutableQuery instance for one-time execution
 */
fun <RowType : Any> Query(
    identifier: Int,
    driver: SqlDriver,
    fileName: String,
    label: String,
    query: String,
    mapper: (SqlCursor) -> RowType
): ExecutableQuery<RowType>

import app.cash.sqldelight.Query
import app.cash.sqldelight.db.SqlDriver

// Create a listenable query
val userQuery = Query(
    identifier = 1,
    queryKeys = arrayOf("users"),
    driver = database.driver,
    query = "SELECT * FROM users WHERE active = 1",
    mapper = { cursor ->
        User(
            id = cursor.getLong(0)!!,
            name = cursor.getString(1)!!,
            email = cursor.getString(2)!!
        )
    }
)

// Execute and get all results
val activeUsers: List<User> = userQuery.executeAsList()

// Execute and get single result
val firstUser: User? = userQuery.executeAsOneOrNull()

/**
 * A listenable, typed query generated by SQLDelight
 * @param RowType the type that this query can map its result set to
 * @property mapper The mapper this Query was created with
 */
abstract class Query<out RowType : Any>(
    mapper: (SqlCursor) -> RowType
) : ExecutableQuery<RowType>(mapper) {
    /**
     * Register a listener to be notified of future changes in the result set
     */
    abstract fun addListener(listener: Listener)
    
    /**
     * Remove a listener to no longer be notified of future changes in the result set
     */
    abstract fun removeListener(listener: Listener)
    
    /**
     * An interface for listening to changes in the result set of a query
     */
    fun interface Listener {
        /**
         * Called whenever the query this listener was attached to is dirtied.
         * Calls are made synchronously on the thread where the updated occurred, 
         * after the update applied successfully.
         */
        fun queryResultsChanged()
    }
}

/**
 * Base class for queries that can be executed
 * @param RowType the type that this query can map its result set to
 * @property mapper Function to convert cursor rows to typed objects
 */
abstract class ExecutableQuery<out RowType : Any>(
    val mapper: (SqlCursor) -> RowType
) {
    /**
     * Execute the underlying statement. The resulting cursor is passed to the given block.
     * The cursor is closed automatically after the block returns.
     */
    abstract fun <R> execute(mapper: (SqlCursor) -> QueryResult<R>): QueryResult<R>
    
    /**
     * @return The result set of the underlying SQL statement as a list of [RowType]
     */
    fun executeAsList(): List<RowType>
    
    /**
     * @return The only row of the result set for the underlying SQL statement as a non null [RowType]
     * @throws NullPointerException if when executed this query has no rows in its result set
     * @throws IllegalStateException if when executed this query has multiple rows in its result set
     */
    fun executeAsOne(): RowType
    
    /**
     * @return The first row of the result set for the underlying SQL statement as a non null 
     *   [RowType] or null if the result set has no rows
     * @throws IllegalStateException if when executed this query has multiple rows in its result set
     */
    fun executeAsOneOrNull(): RowType?
}

import app.cash.sqldelight.Query

// Multiple result handling
val users: List<User> = userQueries.selectAll().executeAsList()
println("Found ${users.size} users")

// Single result handling
try {
    val user: User = userQueries.selectById(123).executeAsOne()
    println("User: ${user.name}")
} catch (e: NullPointerException) {
    println("User not found")
} catch (e: IllegalStateException) {
    println("Multiple users found - data integrity issue")
}

// Optional single result
val user: User? = userQueries.selectById(123).executeAsOneOrNull()
if (user != null) {
    println("User: ${user.name}")
} else {
    println("User not found")
}

// Custom result processing
val userCount: Int = userQueries.selectAll().execute { cursor ->
    var count = 0
    while (cursor.next().value) {
        count++
    }
    QueryResult.Value(count)
}.value

/**
 * An interface for listening to changes in the result set of a query
 */
fun interface Query.Listener {
    /**
     * Called whenever the query this listener was attached to is dirtied.
     * Calls are made synchronously on the thread where the updated occurred, 
     * after the update applied successfully.
     */
    fun queryResultsChanged()
}

import app.cash.sqldelight.Query

// Simple listener implementation
val userQuery = userQueries.selectAll()
val listener = Query.Listener {
    println("User data has changed, refreshing UI...")
    refreshUserList()
}

// Register listener
userQuery.addListener(listener)

// Make changes that trigger notifications
database.transaction {
    userQueries.insertUser("New User", "new@example.com")
    // Listener will be called after transaction commits
}

// Remove listener when no longer needed
userQuery.removeListener(listener)

// Anonymous listener for one-time setup
userQueries.selectByStatus("active").addListener {
    updateActiveUserCount()
}

import app.cash.sqldelight.db.QueryResult

// Custom aggregation
val avgAge: Double = userQueries.selectAll().execute { cursor ->
    var total = 0.0
    var count = 0
    while (cursor.next().value) {
        total += cursor.getLong(2)?.toDouble() ?: 0.0  // age column
        count++
    }
    QueryResult.Value(if (count > 0) total / count else 0.0)
}.value

// Streaming processing for large result sets
userQueries.selectAll().execute { cursor ->
    val results = mutableListOf<String>()
    while (cursor.next().value && results.size < 10) {
        val name = cursor.getString(1) // name column
        if (name?.startsWith("A") == true) {
            results.add(name)
        }
    }
    QueryResult.Value(results)
}.value

// Error handling during execution
try {
    val result = complexQuery.execute { cursor ->
        // Custom processing that might throw
        if (!cursor.next().value) {
            throw IllegalStateException("Expected at least one result")
        }
        QueryResult.Value(processComplexData(cursor))
    }.value
} catch (e: IllegalStateException) {
    // Handle custom execution errors
    handleQueryError(e)
}