Database Driver Interface

/**
 * Maintains connections to an underlying SQL database and provides APIs for managing 
 * transactions and executing SQL statements
 */
interface SqlDriver : Closeable {
    /**
     * Execute a SQL statement and evaluate its result set using the given block
     * @param identifier Opaque, unique value for driver-side caching of prepared statements. If null, a fresh statement is required
     * @param sql The SQL string to be executed
     * @param mapper Lambda called with the cursor when the statement is executed successfully. The cursor must not escape the block scope
     * @param parameters The number of bindable parameters sql contains
     * @param binders Lambda called before execution to bind any parameters to the SQL statement
     * @return Generic result of the mapper lambda
     */
    fun <R> executeQuery(
        identifier: Int?,
        sql: String,
        mapper: (SqlCursor) -> QueryResult<R>,
        parameters: Int,
        binders: (SqlPreparedStatement.() -> Unit)? = null
    ): QueryResult<R>
    
    /**
     * Execute a SQL statement
     * @param identifier Opaque, unique value for driver-side caching of prepared statements. If null, a fresh statement is required
     * @param sql The SQL string to be executed
     * @param parameters The number of bindable parameters sql contains
     * @param binders Lambda called before execution to bind any parameters to the SQL statement
     * @return The number of rows updated for an INSERT/DELETE/UPDATE, or 0 for other SQL statements
     */
    fun execute(
        identifier: Int?,
        sql: String,
        parameters: Int,
        binders: (SqlPreparedStatement.() -> Unit)? = null
    ): QueryResult<Long>
    
    /**
     * Start a new Transaction on the database
     * @return QueryResult containing the new Transaction instance
     */
    fun newTransaction(): QueryResult<Transacter.Transaction>
    
    /**
     * The currently open Transaction on the database
     * @return Current transaction or null if no transaction is active
     */
    fun currentTransaction(): Transacter.Transaction?
    
    /**
     * Register a listener for changes to specific query keys
     * @param queryKeys Table/view names to listen for changes
     * @param listener Listener to notify when changes occur
     */
    fun addListener(vararg queryKeys: String, listener: Query.Listener)
    
    /**
     * Remove a listener for specific query keys
     * @param queryKeys Table/view names to stop listening for changes
     * @param listener Listener to remove
     */
    fun removeListener(vararg queryKeys: String, listener: Query.Listener)
    
    /**
     * Notify all listeners registered for the specified query keys
     * @param queryKeys Table/view names that have changed
     */
    fun notifyListeners(vararg queryKeys: String)
}

/**
 * Represents a SQL result set which can be iterated through with next().
 * Initially the cursor will not point to any row, and calling next() once will iterate to the first row
 */
interface SqlCursor {
    /**
     * Move to the next row in the result set
     * @return QueryResult containing true if the cursor successfully moved to a new row, false if there was no row to iterate to
     */
    fun next(): QueryResult<Boolean>
    
    /**
     * Get the string or null value of column index for the current row of the result set
     * @param index Zero-based column index
     * @return String value or null
     */
    fun getString(index: Int): String?
    
    /**
     * Get the long or null value of column index for the current row of the result set
     * @param index Zero-based column index
     * @return Long value or null
     */
    fun getLong(index: Int): Long?
    
    /**
     * Get the bytes or null value of column index for the current row of the result set
     * @param index Zero-based column index
     * @return ByteArray value or null
     */
    fun getBytes(index: Int): ByteArray?
    
    /**
     * Get the double or null value of column index for the current row of the result set
     * @param index Zero-based column index
     * @return Double value or null
     */
    fun getDouble(index: Int): Double?
    
    /**
     * Get the boolean or null value of column index for the current row of the result set
     * @param index Zero-based column index
     * @return Boolean value or null
     */
    fun getBoolean(index: Int): Boolean?
}

/**
 * Represents a SQL statement that has been prepared by a driver to be executed.
 * This type is not thread safe unless otherwise specified by the driver.
 * Prepared statements should not be cached by client code. Drivers can implement 
 * caching by using the integer identifier passed to SqlDriver.execute or SqlDriver.executeQuery
 */
interface SqlPreparedStatement {
    /**
     * Bind bytes to the underlying statement at index
     * @param index One-based parameter index
     * @param bytes ByteArray value or null to bind
     */
    fun bindBytes(index: Int, bytes: ByteArray?)
    
    /**
     * Bind long to the underlying statement at index
     * @param index One-based parameter index
     * @param long Long value or null to bind
     */
    fun bindLong(index: Int, long: Long?)
    
    /**
     * Bind double to the underlying statement at index
     * @param index One-based parameter index
     * @param double Double value or null to bind
     */
    fun bindDouble(index: Int, double: Double?)
    
    /**
     * Bind string to the underlying statement at index
     * @param index One-based parameter index
     * @param string String value or null to bind
     */
    fun bindString(index: Int, string: String?)
    
    /**
     * Bind boolean to the underlying statement at index
     * @param index One-based parameter index
     * @param boolean Boolean value or null to bind
     */
    fun bindBoolean(index: Int, boolean: Boolean?)
}

/**
 * The returned value is the result of a database query or other database operation.
 * This interface enables drivers to be based on non-blocking APIs where the result 
 * can be obtained using the suspending await method
 */
sealed interface QueryResult<T> {
    /**
     * Immediate access to the result value. 
     * Throws IllegalStateException if the driver is asynchronous and generateAsync is not configured
     */
    val value: T
    
    /**
     * Suspending access to the result value, works with both sync and async drivers
     * @return The result value
     */
    suspend fun await(): T
    
    /**
     * Immediate result wrapper for synchronous operations
     */
    @JvmInline
    value class Value<T>(override val value: T) : QueryResult<T> {
        override suspend fun await() = value
    }
    
    /**
     * Asynchronous result wrapper for suspending operations
     */
    @JvmInline
    value class AsyncValue<T>(private val getter: suspend () -> T) : QueryResult<T> {
        override suspend fun await() = getter()
    }
    
    companion object {
        /**
         * A QueryResult representation of a Kotlin Unit for convenience.
         * Equivalent to QueryResult.Value(Unit)
         */
        val Unit = Value(kotlin.Unit)
    }
}

import app.cash.sqldelight.db.*

// Implementing a custom SqlDriver
class MyCustomDriver : SqlDriver {
    private val connection: DatabaseConnection = createConnection()
    private val listeners = mutableMapOf<String, MutableList<Query.Listener>>()
    private var currentTx: Transacter.Transaction? = null
    
    override fun <R> executeQuery(
        identifier: Int?,
        sql: String,
        mapper: (SqlCursor) -> QueryResult<R>,
        parameters: Int,
        binders: (SqlPreparedStatement.() -> Unit)?
    ): QueryResult<R> {
        val preparedStatement = connection.prepareStatement(sql)
        binders?.invoke(preparedStatement)
        
        val cursor = preparedStatement.executeQuery()
        return mapper(cursor)
    }
    
    override fun execute(
        identifier: Int?,
        sql: String,
        parameters: Int,
        binders: (SqlPreparedStatement.() -> Unit)?
    ): QueryResult<Long> {
        val preparedStatement = connection.prepareStatement(sql)
        binders?.invoke(preparedStatement)
        
        val rowsAffected = preparedStatement.executeUpdate()
        return QueryResult.Value(rowsAffected.toLong())
    }
    
    override fun newTransaction(): QueryResult<Transacter.Transaction> {
        val transaction = MyTransaction(connection, currentTx)
        currentTx = transaction
        return QueryResult.Value(transaction)
    }
    
    override fun currentTransaction(): Transacter.Transaction? = currentTx
    
    override fun addListener(vararg queryKeys: String, listener: Query.Listener) {
        queryKeys.forEach { key ->
            listeners.getOrPut(key) { mutableListOf() }.add(listener)
        }
    }
    
    override fun removeListener(vararg queryKeys: String, listener: Query.Listener) {
        queryKeys.forEach { key ->
            listeners[key]?.remove(listener)
        }
    }
    
    override fun notifyListeners(vararg queryKeys: String) {
        queryKeys.forEach { key ->
            listeners[key]?.forEach { listener ->
                listener.queryResultsChanged()
            }
        }
    }
    
    override fun close() {
        connection.close()
    }
}

// Using SqlDriver for raw database operations
class DatabaseManager(private val driver: SqlDriver) {
    
    fun createUser(name: String, email: String): Long {
        return driver.execute(
            identifier = 1,
            sql = "INSERT INTO users (name, email) VALUES (?, ?)",
            parameters = 2
        ) { statement ->
            statement.bindString(1, name)
            statement.bindString(2, email)
        }.value
    }
    
    fun findUsersByStatus(active: Boolean): List<User> {
        return driver.executeQuery(
            identifier = 2,
            sql = "SELECT id, name, email, active FROM users WHERE active = ?",
            mapper = { cursor ->
                val users = mutableListOf<User>()
                while (cursor.next().value) {
                    users.add(
                        User(
                            id = cursor.getLong(0)!!,
                            name = cursor.getString(1)!!,
                            email = cursor.getString(2)!!,
                            active = cursor.getBoolean(3)!!
                        )
                    )
                }
                QueryResult.Value(users)
            },
            parameters = 1
        ) { statement ->
            statement.bindBoolean(1, active)
        }.value
    }
    
    fun getUserCount(): Int {
        return driver.executeQuery(
            identifier = 3,
            sql = "SELECT COUNT(*) FROM users",
            mapper = { cursor ->
                cursor.next()
                val count = cursor.getLong(0)?.toInt() ?: 0
                QueryResult.Value(count)
            },
            parameters = 0
        ).value
    }
    
    // Working with transactions at the driver level
    fun transferUserData(fromUserId: Long, toUserId: Long) {
        val transaction = driver.newTransaction().value
        try {
            // Perform operations within transaction
            val userData = getUserData(fromUserId)
            deleteUser(fromUserId)
            createUserWithData(toUserId, userData)
            
            // Commit is handled by transaction lifecycle
        } catch (e: Exception) {
            // Rollback is handled automatically
            throw e
        }
    }
}

// Async driver example
class AsyncDatabaseManager(private val driver: SqlDriver) {
    
    suspend fun createUserAsync(name: String, email: String): Long {
        return driver.execute(
            identifier = 1,
            sql = "INSERT INTO users (name, email) VALUES (?, ?)",
            parameters = 2
        ) { statement ->
            statement.bindString(1, name)
            statement.bindString(2, email)
        }.await()
    }
    
    suspend fun findUsersAsync(active: Boolean): List<User> {
        return driver.executeQuery(
            identifier = 2,
            sql = "SELECT id, name, email, active FROM users WHERE active = ?",
            mapper = { cursor ->
                QueryResult.AsyncValue {
                    val users = mutableListOf<User>()
                    while (cursor.next().await()) {
                        users.add(
                            User(
                                id = cursor.getLong(0)!!,
                                name = cursor.getString(1)!!,
                                email = cursor.getString(2)!!,
                                active = cursor.getBoolean(3)!!
                            )
                        )
                    }
                    users
                }
            },
            parameters = 1
        ) { statement ->
            statement.bindBoolean(1, active)
        }.await()
    }
}