Transaction Management

/**
 * A transaction-aware SqlDriver wrapper which can begin a Transaction on the current connection
 */
interface Transacter : TransacterBase {
    /**
     * Starts a Transaction and runs bodyWithReturn in that transaction
     * @param noEnclosing If true, throws IllegalStateException if there is already an active Transaction on this thread
     * @param bodyWithReturn Lambda that executes within the transaction and returns a value
     * @returns The value returned by bodyWithReturn
     * @throws IllegalStateException if noEnclosing is true and there is already an active Transaction on this thread
     */
    fun <R> transactionWithResult(
        noEnclosing: Boolean = false,
        bodyWithReturn: TransactionWithReturn<R>.() -> R
    ): R
    
    /**
     * Starts a Transaction and runs body in that transaction
     * @param noEnclosing If true, throws IllegalStateException if there is already an active Transaction on this thread
     * @param body Lambda that executes within the transaction
     * @throws IllegalStateException if noEnclosing is true and there is already an active Transaction on this thread
     */
    fun transaction(
        noEnclosing: Boolean = false,
        body: TransactionWithoutReturn.() -> Unit
    )
}

import app.cash.sqldelight.Transacter

// Simple transaction without return value
database.transaction {
    userQueries.insertUser("Alice", "alice@example.com")
    userQueries.updateLastLogin("Alice", System.currentTimeMillis())
    // Both operations succeed or both are rolled back
}

// Transaction with return value
val newUserId: Long = database.transactionWithResult {
    userQueries.insertUser("Bob", "bob@example.com")
    val userId = userQueries.lastInsertRowId().executeAsOne()
    profileQueries.createProfile(userId, "Bob's Profile")
    userId // Return the new user ID
}

// Explicit rollback with return value
val result: String = database.transactionWithResult {
    userQueries.insertUser("Charlie", "charlie@example.com")
    val user = userQueries.selectByEmail("charlie@example.com").executeAsOneOrNull()
    if (user == null) {
        rollback("User creation failed")
    }
    "User created successfully"
}

// Manual rollback without return value
database.transaction {
    val userCount = userQueries.countUsers().executeAsOne()
    if (userCount > 1000) {
        rollback() // Stop transaction without creating more users
    }
    userQueries.insertUser("David", "david@example.com")
}

/**
 * A transaction-aware SqlDriver wrapper which can begin a Transaction on the current connection
 */
interface SuspendingTransacter : TransacterBase {
    /**
     * Starts a Transaction and runs bodyWithReturn in that transaction
     * @param noEnclosing If true, throws IllegalStateException if there is already an active Transaction on this thread
     * @param bodyWithReturn Suspending lambda that executes within the transaction and returns a value
     * @returns The value returned by bodyWithReturn
     * @throws IllegalStateException if noEnclosing is true and there is already an active Transaction on this thread
     */
    suspend fun <R> transactionWithResult(
        noEnclosing: Boolean = false,
        bodyWithReturn: suspend SuspendingTransactionWithReturn<R>.() -> R
    ): R
    
    /**
     * Starts a Transaction and runs body in that transaction
     * @param noEnclosing If true, throws IllegalStateException if there is already an active Transaction on this thread
     * @param body Suspending lambda that executes within the transaction
     * @throws IllegalStateException if noEnclosing is true and there is already an active Transaction on this thread
     */
    suspend fun transaction(
        noEnclosing: Boolean = false,
        body: suspend SuspendingTransactionWithoutReturn.() -> Unit
    )
}

import app.cash.sqldelight.SuspendingTransacter
import kotlinx.coroutines.delay

// Async transaction without return value
suspendingDatabase.transaction {
    userQueries.insertUser("Alice", "alice@example.com")
    delay(100) // Simulate async operation
    userQueries.updateLastLogin("Alice", System.currentTimeMillis())
}

// Async transaction with return value
val newUserId: Long = suspendingDatabase.transactionWithResult {
    userQueries.insertUser("Bob", "bob@example.com")
    delay(50) // Simulate async validation
    val userId = userQueries.lastInsertRowId().executeAsOne()
    profileQueries.createProfile(userId, "Bob's Profile")
    userId
}

// Conditional rollback in async context
val result: String = suspendingDatabase.transactionWithResult {
    userQueries.insertUser("Charlie", "charlie@example.com")
    val validationResult = validateUserAsync("charlie@example.com")
    if (!validationResult.isValid) {
        rollback("Validation failed: ${validationResult.error}")
    }
    "User created successfully"
}

/**
 * Transaction context that supports returning values and nested transactions
 */
interface TransactionWithReturn<R> : TransactionCallbacks {
    /**
     * Rolls back this transaction with a return value
     */
    fun rollback(returnValue: R): Nothing
    
    /**
     * Begin an inner transaction that returns a value
     */
    fun <R> transaction(body: TransactionWithReturn<R>.() -> R): R
}

/**
 * Transaction context that doesn't return values but supports nested transactions
 */
interface TransactionWithoutReturn : TransactionCallbacks {
    /**
     * Rolls back this transaction
     */
    fun rollback(): Nothing
    
    /**
     * Begin an inner transaction without return value
     */
    fun transaction(body: TransactionWithoutReturn.() -> Unit)
}

/**
 * Suspending transaction context that supports returning values and nested transactions
 */
interface SuspendingTransactionWithReturn<R> : TransactionCallbacks {
    /**
     * Rolls back this transaction with a return value
     */
    fun rollback(returnValue: R): Nothing
    
    /**
     * Begin an inner suspending transaction that returns a value
     */
    suspend fun <R> transaction(body: suspend SuspendingTransactionWithReturn<R>.() -> R): R
}

/**
 * Suspending transaction context that doesn't return values but supports nested transactions
 */
interface SuspendingTransactionWithoutReturn : TransactionCallbacks {
    /**
     * Rolls back this transaction
     */
    fun rollback(): Nothing
    
    /**
     * Begin an inner suspending transaction without return value
     */
    suspend fun transaction(body: suspend SuspendingTransactionWithoutReturn.() -> Unit)
}

/**
 * Base implementation for synchronous transaction management
 */
abstract class TransacterImpl(driver: SqlDriver) : BaseTransacterImpl(driver), Transacter

/**
 * Base implementation for asynchronous transaction management  
 */
abstract class SuspendingTransacterImpl(driver: SqlDriver) : BaseTransacterImpl(driver), SuspendingTransacter

/**
 * Common base class for both synchronous and asynchronous transacter implementations
 */
abstract class BaseTransacterImpl(protected val driver: SqlDriver) {
    /**
     * Notify query listeners that results have changed for the given identifier and tables
     */
    protected fun notifyQueries(identifier: Int, tableProvider: ((String) -> Unit) -> Unit)
    
    /**
     * Create SQL parameter placeholders for the given count
     */
    protected fun createArguments(count: Int): String
}

import app.cash.sqldelight.TransacterImpl

// Custom database implementation
abstract class MyDatabase : TransacterImpl(driver) {
    // Generated query interfaces would be properties here
    abstract val userQueries: UserQueries
    abstract val profileQueries: ProfileQueries
    
    companion object {
        fun create(driver: SqlDriver): MyDatabase {
            return MyDatabaseImpl(driver)
        }
    }
}

// For async databases  
abstract class MyAsyncDatabase : SuspendingTransacterImpl(driver) {
    abstract val userQueries: UserQueries
    abstract val profileQueries: ProfileQueries
    
    companion object {
        fun create(driver: SqlDriver): MyAsyncDatabase {
            return MyAsyncDatabaseImpl(driver) 
        }
    }
}

/**
 * Base interface for transaction callback management
 */
interface TransactionCallbacks {
    /**
     * Queues function to be run after this transaction successfully commits
     */
    fun afterCommit(function: () -> Unit)
    
    /**
     * Queues function to be run after this transaction rolls back
     */
    fun afterRollback(function: () -> Unit)
}

/**
 * A SQL transaction with lifecycle management and thread confinement
 */
abstract class Transacter.Transaction : TransactionCallbacks {
    /**
     * The parent transaction, if there is any
     */
    protected abstract val enclosingTransaction: Transaction?
    
    /**
     * Signal to the underlying SQL driver that this transaction should be finished
     * @param successful Whether the transaction completed successfully or not
     */
    protected abstract fun endTransaction(successful: Boolean): QueryResult<Unit>
    
    /**
     * Queues function to be run after this transaction successfully commits
     */
    override fun afterCommit(function: () -> Unit)
    
    /**
     * Queues function to be run after this transaction rolls back
     */
    override fun afterRollback(function: () -> Unit)
}

import app.cash.sqldelight.TransactionCallbacks

// Using commit and rollback callbacks
database.transaction {
    afterCommit {
        println("Transaction committed successfully")
        notifyUI("Data saved")
        clearCache()
    }
    
    afterRollback {
        println("Transaction was rolled back")
        notifyUI("Save failed")
        resetFormData()
    }
    
    userQueries.insertUser("Alice", "alice@example.com")
    profileQueries.createProfile(userId = 1, name = "Alice Profile")
}

// Multiple callbacks
database.transaction {
    afterCommit {
        updateSearchIndex()
    }
    
    afterCommit {
        sendNotificationEmail()
    }
    
    afterRollback {
        logError("User creation failed")
    }
    
    userQueries.insertUser("Bob", "bob@example.com")
}

// Nested transactions
database.transaction {
    userQueries.insertUser("Parent", "parent@example.com")
    
    // Inner transaction - will be committed with outer transaction
    transaction {
        profileQueries.createProfile(1, "Parent Profile")
        settingsQueries.createUserSettings(1, theme = "dark")
    }
    
    // Another inner transaction
    val configId: Long = transactionWithResult {
        configQueries.insertConfig("user_1_config")
        configQueries.lastInsertRowId().executeAsOne()
    }
    
    userQueries.updateConfigId(1, configId)
}

// Independent nested transaction
database.transactionWithResult {
    val userId = userQueries.insertUser("Independent", "independent@example.com")
    
    // This inner transaction can be rolled back independently
    try {
        transaction(noEnclosing = true) {
            riskyQueries.performRiskyOperation(userId)
        }
    } catch (e: IllegalStateException) {
        // Inner transaction conflicts are handled separately
        println("Inner transaction failed: ${e.message}")
    }
    
    userId
}

// Basic error handling
try {
    database.transaction {
        userQueries.insertUser("Test", "test@example.com")
        // This will cause a constraint violation if email already exists
        userQueries.insertUser("Test2", "test@example.com") 
    }
} catch (e: SQLException) {
    println("Transaction failed: ${e.message}")
    // Transaction was automatically rolled back
}

// Error handling with rollback callbacks
database.transaction {
    afterRollback {
        println("Cleaning up after transaction failure")
        cleanupTempFiles()
        resetState()
    }
    
    try {
        userQueries.insertUser("Risky", "risky@example.com")
        performRiskyOperation()
    } catch (e: RiskyOperationException) {
        // Explicit rollback
        rollback()
    }
}

// Conditional operations with manual rollback
val result = database.transactionWithResult<String> {
    val user = userQueries.insertUser("Conditional", "conditional@example.com")
    val validation = validateUser(user)
    
    if (!validation.isValid) {
        rollback("Validation failed: ${validation.errors.joinToString()}")
    }
    
    "User created successfully"
}

/**
 * Internal function for thread confinement checking
 * Used by transaction implementations to ensure thread safety
 * Note: This is an internal API and should not be used directly
 */
internal expect fun currentThreadId(): Long

// Correct usage - all operations on same thread
database.transaction {
    userQueries.insertUser("ThreadSafe", "safe@example.com")
    val userId = userQueries.lastInsertRowId().executeAsOne()
    profileQueries.createProfile(userId, "Profile")
}

// Incorrect usage - would throw IllegalStateException
database.transaction {
    val transaction = this
    
    // This would fail - transaction used from different thread
    Thread {
        try {
            transaction.afterCommit { 
                println("This will throw an exception")
            }
        } catch (e: IllegalStateException) {
            println("Cannot access transaction from different thread: ${e.message}")
        }
    }.start()
}

// Async operations should use SuspendingTransacter instead
suspendingDatabase.transaction {
    userQueries.insertUser("Async", "async@example.com")
    // This is safe because the coroutine context maintains thread affinity
    withContext(Dispatchers.IO) {
        // Async operations within the same coroutine scope
    }
}