Exemplos de Banco de Dados Android Kotlin

// Android Kotlin SQLite Connection Examples
// Using Android SQLiteOpenHelper and SQLite API

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.database.sqlite.SQLiteDatabase.CursorFactory
import android.content.ContentValues
import android.database.Cursor

// 1. Database Helper Class
class UserDatabaseHelper(
    context: Context,
    databaseName: String = "UserDatabase.db",
    factory: CursorFactory? = null,
    version: Int = 1
) : SQLiteOpenHelper(context, databaseName, factory, version) {

    companion object {
        private const val DATABASE_NAME = "UserDatabase.db"
        private const val DATABASE_VERSION = 1

        // Table names
        private const val TABLE_USERS = "users"
        private const val TABLE_PROFILES = "profiles"

        // Users table columns
        private const val COLUMN_USER_ID = "user_id"
        private const val COLUMN_USERNAME = "username"
        private const val COLUMN_EMAIL = "email"
        private const val COLUMN_PASSWORD = "password"
        private const val COLUMN_CREATED_AT = "created_at"

        // Profiles table columns
        private const val COLUMN_PROFILE_ID = "profile_id"
        private const val COLUMN_USER_ID_FK = "user_id"
        private const val COLUMN_FULL_NAME = "full_name"
        private const val COLUMN_BIO = "bio"
        private const val COLUMN_AGE = "age"
    }

    override fun onCreate(db: SQLiteDatabase) {
        // Create users table
        val createUsersTable = """
            CREATE TABLE $TABLE_USERS (
                $COLUMN_USER_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_USERNAME TEXT UNIQUE NOT NULL,
                $COLUMN_EMAIL TEXT UNIQUE NOT NULL,
                $COLUMN_PASSWORD TEXT NOT NULL,
                $COLUMN_CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """.trimIndent()

        // Create profiles table
        val createProfilesTable = """
            CREATE TABLE $TABLE_PROFILES (
                $COLUMN_PROFILE_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_USER_ID_FK INTEGER,
                $COLUMN_FULL_NAME TEXT,
                $COLUMN_BIO TEXT,
                $COLUMN_AGE INTEGER,
                FOREIGN KEY($COLUMN_USER_ID_FK) REFERENCES $TABLE_USERS($COLUMN_USER_ID)
                    ON DELETE CASCADE
            )
        """.trimIndent()

        // Create indexes
        val createUsernameIndex = """
            CREATE INDEX idx_username ON $TABLE_USERS($COLUMN_USERNAME)
        """.trimIndent()

        val createEmailIndex = """
            CREATE INDEX idx_email ON $TABLE_USERS($COLUMN_EMAIL)
        """.trimIndent()

        db.execSQL(createUsersTable)
        db.execSQL(createProfilesTable)
        db.execSQL(createUsernameIndex)
        db.execSQL(createEmailIndex)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Handle database upgrades
        when (oldVersion) {
            1 -> {
                // Upgrade from version 1 to 2
                upgradeToVersion2(db)
            }
            2 -> {
                // Upgrade from version 2 to 3
                upgradeToVersion3(db)
            }
            // Add more upgrade paths as needed
        }
    }

    private fun upgradeToVersion2(db: SQLiteDatabase) {
        // Example: Add new column
        db.execSQL("ALTER TABLE $TABLE_USERS ADD COLUMN phone TEXT")
    }

    private fun upgradeToVersion3(db: SQLiteDatabase) {
        // Example: Create new table
        db.execSQL("""
            CREATE TABLE user_settings (
                setting_id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                setting_key TEXT,
                setting_value TEXT,
                FOREIGN KEY(user_id) REFERENCES users(user_id)
            )
        """.trimIndent())
    }

    override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Handle database downgrades
        // In production, you might want to backup data first
        onConfigure(db)
        onCreate(db)
    }

    override fun onConfigure(db: SQLiteDatabase) {
        super.onConfigure(db)
        // Enable foreign key constraints
        db.setForeignKeyConstraintsEnabled(true)

        // Enable Write-Ahead Logging (WAL) for better concurrency
        // Note: Available on API 16+
        // db.enableWriteAheadLogging()
    }

    override fun onOpen(db: SQLiteDatabase) {
        super.onOpen(db)
        // Perform any operations needed when database is opened
    }
}

// 2. Database Connection Manager
class DatabaseConnectionManager(private val context: Context) {

    private var dbHelper: UserDatabaseHelper? = null
    private var database: SQLiteDatabase? = null

    // Open database for reading
    fun openReadable(): SQLiteDatabase? {
        if (database == null || database?.isOpen == false) {
            dbHelper = UserDatabaseHelper(context)
            database = dbHelper?.readableDatabase
        }
        return database
    }

    // Open database for writing
    fun openWritable(): SQLiteDatabase? {
        if (database == null || database?.isOpen == false) {
            dbHelper = UserDatabaseHelper(context)
            database = dbHelper?.writableDatabase
        }
        return database
    }

    // Close database connection
    fun close() {
        dbHelper?.close()
        database = null
        dbHelper = null
    }

    // Check if database is open
    fun isOpen(): Boolean {
        return database?.isOpen == true
    }

    // Get database path
    fun getDatabasePath(): String {
        return context.getDatabasePath("UserDatabase.db").absolutePath
    }

    // Get database size
    fun getDatabaseSize(): Long {
        val file = context.getDatabasePath("UserDatabase.db")
        return if (file.exists()) file.length() else 0L
    }

    // Check if database exists
    fun databaseExists(): Boolean {
        val file = context.getDatabasePath("UserDatabase.db")
        return file.exists()
    }

    // Delete database
    fun deleteDatabase(): Boolean {
        close()
        return context.deleteDatabase("UserDatabase.db")
    }
}

// 3. Database Configuration
class DatabaseConfig(private val context: Context) {

    // Configure database with specific settings
    fun configureDatabase(): UserDatabaseHelper {
        val helper = UserDatabaseHelper(context)

        // Get writable database to apply configurations
        val db = helper.writableDatabase

        // Set database locale
        db.setLocale(java.util.Locale.getDefault())

        // Set maximum database size (in bytes)
        val maxSize = 10 * 1024 * 1024 // 10 MB
        db.setMaxSqlCacheSize(maxSize)

        return helper
    }

    // Create in-memory database for testing
    fun createInMemoryDatabase(): SQLiteDatabase {
        return SQLiteDatabase.create(
            SQLiteDatabase.OpenParams.Builder().build()
        )
    }

    // Create database with specific configuration
    fun createCustomDatabase(
        name: String,
        version: Int,
        enableForeignKeys: Boolean = true,
        enableWAL: Boolean = true
    ): UserDatabaseHelper {
        val helper = UserDatabaseHelper(context, name, null, version)
        val db = helper.writableDatabase

        db.setForeignKeyConstraintsEnabled(enableForeignKeys)

        if (enableWAL) {
            // db.enableWriteAheadLogging()
        }

        return helper
    }
}

// 4. Database Lifecycle Management
class DatabaseLifecycleManager(private val context: Context) {

    private val connectionManagers = mutableListOf<DatabaseConnectionManager>()

    // Register a connection manager
    fun registerConnection(manager: DatabaseConnectionManager) {
        connectionManagers.add(manager)
    }

    // Close all connections
    fun closeAllConnections() {
        connectionManagers.forEach { it.close() }
        connectionManagers.clear()
    }

    // Backup database
    fun backupDatabase(backupPath: String): Boolean {
        val dbFile = context.getDatabasePath("UserDatabase.db")
        val backupFile = java.io.File(backupPath)

        return try {
            java.io.FileInputStream(dbFile).use { input ->
                java.io.FileOutputStream(backupFile).use { output ->
                    input.copyTo(output)
                }
            }
            true
        } catch (e: Exception) {
            println("Backup failed: ${e.message}")
            false
        }
    }

    // Restore database
    fun restoreDatabase(backupPath: String): Boolean {
        val backupFile = java.io.File(backupPath)
        val dbFile = context.getDatabasePath("UserDatabase.db")

        return try {
            // Close existing connection
            closeAllConnections()

            java.io.FileInputStream(backupFile).use { input ->
                java.io.FileOutputStream(dbFile).use { output ->
                    input.copyTo(output)
                }
            }
            true
        } catch (e: Exception) {
            println("Restore failed: ${e.message}")
            false
        }
    }

    // Vacuum database (reclaim space)
    fun vacuumDatabase(): Boolean {
        val manager = DatabaseConnectionManager(context)
        val db = manager.openWritable()
        return try {
            db?.execSQL("VACUUM")
            true
        } catch (e: Exception) {
            println("Vacuum failed: ${e.message}")
            false
        } finally {
            manager.close()
        }
    }

    // Analyze database (update statistics)
    fun analyzeDatabase(): Boolean {
        val manager = DatabaseConnectionManager(context)
        val db = manager.openWritable()
        return try {
            db?.execSQL("ANALYZE")
            true
        } catch (e: Exception) {
            println("Analyze failed: ${e.message}")
            false
        } finally {
            manager.close()
        }
    }
}

// 5. Database Information
class DatabaseInfo(private val context: Context) {

    // Get database version
    fun getDatabaseVersion(): Int {
        val helper = UserDatabaseHelper(context)
        return helper.readableDatabase.version
    }

    // Get database page size
    fun getDatabasePageSize(): Int {
        val manager = DatabaseConnectionManager(context)
        val db = manager.openReadable()
        var pageSize = 0

        val cursor = db?.rawQuery("PRAGMA page_size", null)
        cursor?.use {
            if (it.moveToFirst()) {
                pageSize = it.getInt(0)
            }
        }

        manager.close()
        return pageSize
    }

    // Get database encoding
    fun getDatabaseEncoding(): String {
        val manager = DatabaseConnectionManager(context)
        val db = manager.openReadable()
        var encoding = ""

        val cursor = db?.rawQuery("PRAGMA encoding", null)
        cursor?.use {
            if (it.moveToFirst()) {
                encoding = it.getString(0)
            }
        }

        manager.close()
        return encoding
    }

    // List all tables
    fun listTables(): List<String> {
        val manager = DatabaseConnectionManager(context)
        val db = manager.openReadable()
        val tables = mutableListOf<String>()

        val cursor = db?.rawQuery(
            "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",
            null
        )
        cursor?.use {
            while (it.moveToNext()) {
                tables.add(it.getString(0))
            }
        }

        manager.close()
        return tables
    }

    // Get table info
    fun getTableInfo(tableName: String): List<ColumnInfo> {
        val manager = DatabaseConnectionManager(context)
        val db = manager.openReadable()
        val columns = mutableListOf<ColumnInfo>()

        val cursor = db?.rawQuery("PRAGMA table_info($tableName)", null)
        cursor?.use {
            val indexName = it.getColumnIndex("name")
            val indexType = it.getColumnIndex("type")
            val indexNotNull = it.getColumnIndex("notnull")
            val indexDefault = it.getColumnIndex("dflt_value")

            while (it.moveToNext()) {
                columns.add(
                    ColumnInfo(
                        name = it.getString(indexName),
                        type = it.getString(indexType),
                        notNull = it.getInt(indexNotNull) == 1,
                        defaultValue = it.getString(indexDefault)
                    )
                )
            }
        }

        manager.close()
        return columns
    }

    // Get table count
    fun getTableCount(tableName: String): Int {
        val manager = DatabaseConnectionManager(context)
        val db = manager.openReadable()
        var count = 0

        val cursor = db?.rawQuery("SELECT COUNT(*) FROM $tableName", null)
        cursor?.use {
            if (it.moveToFirst()) {
                count = it.getInt(0)
            }
        }

        manager.close()
        return count
    }
}

// Column Info Data Class
data class ColumnInfo(
    val name: String,
    val type: String,
    val notNull: Boolean,
    val defaultValue: String?
)

// Main demonstration
fun demonstrateSQLiteConnection(context: Context) {
    println("=== Android Kotlin SQLite Connection Examples ===\n")

    // 1. Create database helper
    println("--- 1. Create Database Helper ---")
    val dbHelper = UserDatabaseHelper(context)
    println("Database helper created")
    println("Database name: ${UserDatabaseHelper.DATABASE_NAME}")
    println("Database version: ${UserDatabaseHelper.DATABASE_VERSION}")

    // 2. Connection manager
    println("\n--- 2. Connection Manager ---")
    val connManager = DatabaseConnectionManager(context)

    // Open for reading
    val readDb = connManager.openReadable()
    println("Database opened for reading: ${readDb != null}")

    // Open for writing
    val writeDb = connManager.openWritable()
    println("Database opened for writing: ${writeDb != null}")

    println("Is database open: ${connManager.isOpen()}")
    println("Database exists: ${connManager.databaseExists()}")
    println("Database path: ${connManager.getDatabasePath()}")
    println("Database size: ${connManager.getDatabaseSize()} bytes")

    // 3. Database configuration
    println("\n--- 3. Database Configuration ---")
    val config = DatabaseConfig(context)
    val configuredHelper = config.configureDatabase()
    println("Database configured")

    // 4. Database information
    println("\n--- 4. Database Information ---")
    val dbInfo = DatabaseInfo(context)

    println("Database version: ${dbInfo.getDatabaseVersion()}")
    println("Page size: ${dbInfo.getDatabasePageSize()}")
    println("Encoding: ${dbInfo.getDatabaseEncoding()}")

    println("\nTables:")
    val tables = dbInfo.listTables()
    tables.forEach { println("  - $it") }

    if (tables.isNotEmpty()) {
        println("\nTable info for '${tables[0]}':")
        val columns = dbInfo.getTableInfo(tables[0])
        columns.forEach { col ->
            println("  - ${col.name}: ${col.type} ${if (col.notNull) "NOT NULL" else ""}")
        }

        println("\nRow count: ${dbInfo.getTableCount(tables[0])}")
    }

    // 5. Lifecycle management
    println("\n--- 5. Lifecycle Management ---")
    val lifecycleManager = DatabaseLifecycleManager(context)
    lifecycleManager.registerConnection(connManager)

    // Note: Backup operations need appropriate storage permissions
    // val backupSuccess = lifecycleManager.backupDatabase("/path/to/backup.db")
    // println("Backup successful: $backupSuccess")

    // 6. Cleanup
    println("\n--- 6. Cleanup ---")
    connManager.close()
    println("Database connection closed")

    println("\n=== All SQLite Connection Examples Completed ===")
}

// Android Kotlin Execute SQL Queries Examples
// Using SQLiteDatabase and ContentValues

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.content.ContentValues
import android.database.Cursor

// Database helper (same as connection example)
class UserDatabaseHelper(context: Context) : SQLiteOpenHelper(context, "UserDatabase.db", null, 1) {

    companion object {
        private const val TABLE_USERS = "users"
        private const val COLUMN_ID = "user_id"
        private const val COLUMN_USERNAME = "username"
        private const val COLUMN_EMAIL = "email"
        private const val COLUMN_AGE = "age"
        private const val COLUMN_SCORE = "score"
    }

    override fun onCreate(db: SQLiteDatabase) {
        val createTable = """
            CREATE TABLE $TABLE_USERS (
                $COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_USERNAME TEXT NOT NULL,
                $COLUMN_EMAIL TEXT NOT NULL,
                $COLUMN_AGE INTEGER DEFAULT 0,
                $COLUMN_SCORE INTEGER DEFAULT 0
            )
        """.trimIndent()
        db.execSQL(createTable)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {}
}

// 1. INSERT Operations
class InsertOperations(private val dbHelper: UserDatabaseHelper) {

    // Insert single row using ContentValues
    fun insertUser(username: String, email: String, age: Int): Long {
        val db = dbHelper.writableDatabase
        val values = ContentValues().apply {
            put(COLUMN_USERNAME, username)
            put(COLUMN_EMAIL, email)
            put(COLUMN_AGE, age)
            put(COLUMN_SCORE, 0)
        }

        val rowId = db.insert(TABLE_USERS, null, values)
        println("Inserted user with row ID: $rowId")
        return rowId
    }

    // Insert multiple rows using transaction
    fun insertMultipleUsers(users: List<User>): Int {
        val db = dbHelper.writableDatabase
        var count = 0

        db.beginTransaction()
        try {
            for (user in users) {
                val values = ContentValues().apply {
                    put(COLUMN_USERNAME, user.username)
                    put(COLUMN_EMAIL, user.email)
                    put(COLUMN_AGE, user.age)
                }

                val rowId = db.insert(TABLE_USERS, null, values)
                if (rowId > 0) count++
            }

            db.setTransactionSuccessful()
        } finally {
            db.endTransaction()
        }

        println("Inserted $count users")
        return count
    }

    // Insert using raw SQL
    fun insertUserRawSQL(username: String, email: String, age: Int): Boolean {
        val db = dbHelper.writableDatabase
        val sql = "INSERT INTO $TABLE_USERS ($COLUMN_USERNAME, $COLUMN_EMAIL, $COLUMN_AGE) VALUES (?, ?, ?)"

        try {
            db.execSQL(sql, arrayOf(username, email, age))
            println("Inserted user using raw SQL: $username")
            return true
        } catch (e: Exception) {
            println("Error inserting user: ${e.message}")
            return false
        }
    }

    // Insert or replace (upsert)
    fun insertOrReplaceUser(id: Int, username: String, email: String): Long {
        val db = dbHelper.writableDatabase
        val values = ContentValues().apply {
            put(COLUMN_ID, id)
            put(COLUMN_USERNAME, username)
            put(COLUMN_EMAIL, email)
        }

        val rowId = db.insertWithOnConflict(
            TABLE_USERS,
            null,
            values,
            SQLiteDatabase.CONFLICT_REPLACE
        )

        println("Inserted or replaced user with row ID: $rowId")
        return rowId
    }

    // Insert with null column handling
    fun insertUserWithOptionalFields(
                    username: String,
                    email: String?,
                    age: Int?
    ): Long {
        val db = dbHelper.writableDatabase
        val values = ContentValues().apply {
            put(COLUMN_USERNAME, username)
            put(COLUMN_EMAIL, email)
            put(COLUMN_AGE, age)
        }

        return db.insert(TABLE_USERS, null, values)
    }
}

// 2. SELECT Operations
class SelectOperations(private val dbHelper: UserDatabaseHelper) {

    // Query all users
    fun getAllUsers(): List<User> {
        val db = dbHelper.readableDatabase
        val users = mutableListOf<User>()

        val cursor: Cursor = db.query(
            TABLE_USERS,
            null,  // Select all columns
            null,  // No WHERE clause
            null,  // No WHERE arguments
            null,  // No GROUP BY
            null,  // No HAVING
            "$COLUMN_USERNAME ASC"  // Order by username
        )

        cursor.use {
            while (it.moveToNext()) {
                users.add(cursorToUser(it))
            }
        }

        println("Retrieved ${users.size} users")
        return users
    }

    // Query with WHERE clause
    fun getUsersByAge(minAge: Int): List<User> {
        val db = dbHelper.readableDatabase
        val users = mutableListOf<User>()

        val selection = "$COLUMN_AGE >= ?"
        val selectionArgs = arrayOf(minAge.toString())

        val cursor = db.query(
            TABLE_USERS,
            null,
            selection,
            selectionArgs,
            null,
            null,
            "$COLUMN_AGE DESC"
        )

        cursor.use {
            while (it.moveToNext()) {
                users.add(cursorToUser(it))
            }
        }

        return users
    }

    // Query with multiple conditions
    fun getUsersWithConditions(
                    minAge: Int,
                    maxScore: Int,
                    usernamePattern: String
    ): List<User> {
        val db = dbHelper.readableDatabase
        val users = mutableListOf<User>()

        val selection = "$COLUMN_AGE >= ? AND $COLUMN_SCORE <= ? AND $COLUMN_USERNAME LIKE ?"
        val selectionArgs = arrayOf(minAge.toString(), maxScore.toString(), "$usernamePattern%")

        val cursor = db.query(
            TABLE_USERS,
            null,
            selection,
            selectionArgs,
            null,
            null,
            null
        )

        cursor.use {
            while (it.moveToNext()) {
                users.add(cursorToUser(it))
            }
        }

        return users
    }

    // Query specific columns
    fun getUsernamesOnly(): List<String> {
        val db = dbHelper.readableDatabase
        val usernames = mutableListOf<String>()

        val columns = arrayOf(COLUMN_USERNAME)
        val cursor = db.query(TABLE_USERS, columns, null, null, null, null, null)

        cursor.use {
            while (it.moveToNext()) {
                usernames.add(it.getString(0))
            }
        }

        return usernames
    }

    // Query with raw SQL
    fun getUsersRawSQL(minAge: Int, maxAge: Int): List<User> {
        val db = dbHelper.readableDatabase
        val users = mutableListOf<User>()

        val sql = "SELECT * FROM $TABLE_USERS WHERE $COLUMN_AGE BETWEEN ? AND ? ORDER BY $COLUMN_USERNAME"
        val cursor = db.rawQuery(sql, arrayOf(minAge.toString(), maxAge.toString()))

        cursor.use {
            while (it.moveToNext()) {
                users.add(cursorToUser(it))
            }
        }

        return users
    }

    // Query single user
    fun getUserById(id: Int): User? {
        val db = dbHelper.readableDatabase

        val selection = "$COLUMN_ID = ?"
        val selectionArgs = arrayOf(id.toString())

        val cursor = db.query(
            TABLE_USERS,
            null,
            selection,
            selectionArgs,
            null,
            null,
            null
        )

        cursor.use {
            if (it.moveToFirst()) {
                return cursorToUser(it)
            }
        }

        return null
    }

    // Count query
    fun getUserCount(): Int {
        val db = dbHelper.readableDatabase

        val cursor = db.query(
            TABLE_USERS,
            arrayOf("COUNT(*)"),
            null,
            null,
            null,
            null,
            null
        )

        cursor.use {
            if (it.moveToFirst()) {
                return it.getInt(0)
            }
        }

        return 0
    }

    // Helper: Convert cursor to User object
    private fun cursorToUser(cursor: Cursor): User {
        return User(
            id = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)),
            username = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USERNAME)),
            email = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_EMAIL)),
            age = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_AGE)),
            score = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_SCORE))
        )
    }
}

// 3. UPDATE Operations
class UpdateOperations(private val dbHelper: UserDatabaseHelper) {

    // Update single field
    fun updateUserEmail(userId: Int, newEmail: String): Int {
        val db = dbHelper.writableDatabase

        val values = ContentValues().apply {
            put(COLUMN_EMAIL, newEmail)
        }

        val whereClause = "$COLUMN_ID = ?"
        val whereArgs = arrayOf(userId.toString())

        val rowsAffected = db.update(TABLE_USERS, values, whereClause, whereArgs)
        println("Updated email for user $userId: $rowsAffected rows affected")

        return rowsAffected
    }

    // Update multiple fields
    fun updateUser(userId: Int, username: String, email: String, age: Int): Int {
        val db = dbHelper.writableDatabase

        val values = ContentValues().apply {
            put(COLUMN_USERNAME, username)
            put(COLUMN_EMAIL, email)
            put(COLUMN_AGE, age)
        }

        val whereClause = "$COLUMN_ID = ?"
        val whereArgs = arrayOf(userId.toString())

        return db.update(TABLE_USERS, values, whereClause, whereArgs)
    }

    // Update with expression
    fun incrementUserScore(userId: Int, increment: Int): Int {
        val db = dbHelper.writableDatabase

        val sql = "UPDATE $TABLE_USERS SET $COLUMN_SCORE = $COLUMN_SCORE + ? WHERE $COLUMN_ID = ?"
        db.execSQL(sql, arrayOf(increment.toString(), userId.toString()))

        // Return rows affected (need separate query)
        return 1
    }

    // Update multiple rows
    fun updateAllScoresByAge(minAge: Int, bonusScore: Int): Int {
        val db = dbHelper.writableDatabase

        val values = ContentValues().apply {
            put(COLUMN_SCORE, bonusScore)
        }

        val whereClause = "$COLUMN_AGE >= ?"
        val whereArgs = arrayOf(minAge.toString())

        val rowsAffected = db.update(TABLE_USERS, values, whereClause, whereArgs)
        println("Updated scores for ${rowsAffected} users aged $minAge and above")

        return rowsAffected
    }

    // Update using raw SQL
    fun updateUserRawSQL(userId: Int, newUsername: String): Boolean {
        val db = dbHelper.writableDatabase

        try {
            val sql = "UPDATE $TABLE_USERS SET $COLUMN_USERNAME = ? WHERE $COLUMN_ID = ?"
            db.execSQL(sql, arrayOf(newUsername, userId.toString()))
            return true
        } catch (e: Exception) {
            println("Error updating user: ${e.message}")
            return false
        }
    }
}

// 4. DELETE Operations
class DeleteOperations(private val dbHelper: UserDatabaseHelper) {

    // Delete single row
    fun deleteUserById(userId: Int): Int {
        val db = dbHelper.writableDatabase

        val whereClause = "$COLUMN_ID = ?"
        val whereArgs = arrayOf(userId.toString())

        val rowsDeleted = db.delete(TABLE_USERS, whereClause, whereArgs)
        println("Deleted user $userId: $rowsDeleted rows deleted")

        return rowsDeleted
    }

    // Delete with condition
    fun deleteUsersByAge(maxAge: Int): Int {
        val db = dbHelper.writableDatabase

        val whereClause = "$COLUMN_AGE < ?"
        val whereArgs = arrayOf(maxAge.toString())

        val rowsDeleted = db.delete(TABLE_USERS, whereClause, whereArgs)
        println("Deleted $rowsDeleted users under age $maxAge")

        return rowsDeleted
    }

    // Delete with multiple conditions
    fun deleteUsersByScoreRange(minScore: Int, maxScore: Int): Int {
        val db = dbHelper.writableDatabase

        val whereClause = "$COLUMN_SCORE BETWEEN ? AND ?"
        val whereArgs = arrayOf(minScore.toString(), maxScore.toString())

        return db.delete(TABLE_USERS, whereClause, whereArgs)
    }

    // Delete all rows
    fun deleteAllUsers(): Int {
        val db = dbHelper.writableDatabase

        val rowsDeleted = db.delete(TABLE_USERS, null, null)
        println("Deleted all users: $rowsDeleted rows deleted")

        return rowsDeleted
    }

    // Delete using raw SQL
    fun deleteUsersRawSQL(usernamePattern: String): Int {
        val db = dbHelper.writableDatabase

        val sql = "DELETE FROM $TABLE_USERS WHERE $COLUMN_USERNAME LIKE ?"
        db.execSQL(sql, arrayOf("$usernamePattern%"))

        // Get count (simplified)
        return 1
    }
}

// 5. Prepared Statements
class PreparedStatements(private val dbHelper: UserDatabaseHelper) {

    // Insert with parameters
    fun insertUserPrepared(username: String, email: String, age: Int): Long {
        val db = dbHelper.writableDatabase
        val sql = "INSERT INTO $TABLE_USERS ($COLUMN_USERNAME, $COLUMN_EMAIL, $COLUMN_AGE) VALUES (?, ?, ?)"

        val stmt = db.compileStatement(sql)
        stmt.clearBindings()
        stmt.bindString(1, username)
        stmt.bindString(2, email)
        stmt.bindLong(3, age.toLong())

        val rowId = stmt.executeInsert()
        stmt.close()

        return rowId
    }

    // Query with parameters
    fun findUsersPrepared(ageMin: Int, ageMax: Int): Cursor? {
        val db = dbHelper.readableDatabase
        val sql = """
            SELECT * FROM $TABLE_USERS
            WHERE $COLUMN_AGE BETWEEN ? AND ?
            ORDER BY $COLUMN_USERNAME
        """.trimIndent()

        return db.rawQuery(sql, arrayOf(ageMin.toString(), ageMax.toString()))
    }
}

// User data class
data class User(
    val id: Int = 0,
    val username: String,
    val email: String,
    val age: Int = 0,
    val score: Int = 0
)

// Main demonstration
fun demonstrateExecuteSQLQueries(context: Context) {
    println("=== Android Kotlin Execute SQL Queries Examples ===\n")

    val dbHelper = UserDatabaseHelper(context)
    val db = dbHelper.writableDatabase

    // 1. INSERT examples
    println("--- 1. INSERT Operations ---")
    val insertOps = InsertOperations(dbHelper)

    val id1 = insertOps.insertUser("alice", "[email protected]", 25)
    val id2 = insertOps.insertUser("bob", "[email protected]", 30)
    val id3 = insertOps.insertUser("charlie", "[email protected]", 28)

    insertOps.insertUserRawSQL("diana", "[email protected]", 22)

    val users = listOf(
        User(0, "eve", "[email protected]", 26),
        User(0, "frank", "[email protected]", 35)
    )
    insertOps.insertMultipleUsers(users)

    // 2. SELECT examples
    println("\n--- 2. SELECT Operations ---")
    val selectOps = SelectOperations(dbHelper)

    val allUsers = selectOps.getAllUsers()
    println("All users (${allUsers.size}):")
    allUsers.take(3).forEach { println("  - ${it.id}: ${it.username}, ${it.email}, age ${it.age}") }

    val usersOver25 = selectOps.getUsersByAge(25)
    println("\nUsers aged 25+: ${usersOver25.size}")

    val user = selectOps.getUserById(id1.toInt())
    println("\nUser by ID $id1: ${user?.username}")

    val count = selectOps.getUserCount()
    println("\nTotal user count: $count")

    // 3. UPDATE examples
    println("\n--- 3. UPDATE Operations ---")
    val updateOps = UpdateOperations(dbHelper)

    updateOps.updateUserEmail(id1.toInt(), "[email protected]")
    updateOps.incrementUserScore(id2.toInt(), 10)
    updateOps.updateAllScoresByAge(30, 50)

    // 4. DELETE examples
    println("\n--- 4. DELETE Operations ---")
    val deleteOps = DeleteOperations(dbHelper)

    deleteOps.deleteUserById(id3.toInt())
    deleteOps.deleteUsersByAge(21)

    // 5. Prepared statements
    println("\n--- 5. Prepared Statements ---")
    val prepStmt = PreparedStatements(dbHelper)

    val prepId = prepStmt.insertUserPrepared("grace", "[email protected]", 29)
    println("Inserted with prepared statement: $prepId")

    // Cleanup
    dbHelper.close()
    println("\nDatabase cleaned up")

    println("\n=== All Execute SQL Queries Examples Completed ===")
}

// Android Kotlin Transaction Processing Examples
// Using SQLiteDatabase transactions

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.content.ContentValues
import android.database.Cursor

// Database helper
class BankDatabaseHelper(context: Context) : SQLiteOpenHelper(context, "BankDatabase.db", null, 1) {

    companion object {
        private const val TABLE_ACCOUNTS = "accounts"
        private const val TABLE_TRANSACTIONS = "transactions"

        private const val COLUMN_ACCOUNT_ID = "account_id"
        private const val COLUMN_ACCOUNT_NAME = "account_name"
        private const val COLUMN_BALANCE = "balance"

        private const val COLUMN_TX_ID = "tx_id"
        private const val COLUMN_FROM_ACCOUNT = "from_account"
        private const val COLUMN_TO_ACCOUNT = "to_account"
        private const val COLUMN_AMOUNT = "amount"
        private const val COLUMN_TX_DATE = "tx_date"
    }

    override fun onCreate(db: SQLiteDatabase) {
        // Create accounts table
        db.execSQL("""
            CREATE TABLE $TABLE_ACCOUNTS (
                $COLUMN_ACCOUNT_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_ACCOUNT_NAME TEXT UNIQUE NOT NULL,
                $COLUMN_BALANCE REAL DEFAULT 0.0 CHECK($COLUMN_BALANCE >= 0)
            )
        """.trimIndent())

        // Create transactions table
        db.execSQL("""
            CREATE TABLE $TABLE_TRANSACTIONS (
                $COLUMN_TX_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_FROM_ACCOUNT INTEGER,
                $COLUMN_TO_ACCOUNT INTEGER,
                $COLUMN_AMOUNT REAL,
                $COLUMN_TX_DATE DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY($COLUMN_FROM_ACCOUNT) REFERENCES $TABLE_ACCOUNTS($COLUMN_ACCOUNT_ID),
                FOREIGN KEY($COLUMN_TO_ACCOUNT) REFERENCES $TABLE_ACCOUNTS($COLUMN_ACCOUNT_ID)
            )
        """.trimIndent())
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {}
}

// 1. Basic Transaction
class BasicTransaction(private val dbHelper: BankDatabaseHelper) {

    // Simple transaction with multiple inserts
    fun performBatchInsert(accounts: List<Account>): Boolean {
        val db = dbHelper.writableDatabase

        db.beginTransaction()
        try {
            for (account in accounts) {
                val values = ContentValues().apply {
                    put(COLUMN_ACCOUNT_NAME, account.name)
                    put(COLUMN_BALANCE, account.balance)
                }

                db.insert(TABLE_ACCOUNTS, null, values)
            }

            // Mark transaction as successful
            db.setTransactionSuccessful()
            println("Batch insert completed: ${accounts.size} accounts")
            return true

        } catch (e: Exception) {
            println("Batch insert failed: ${e.message}")
            return false
        } finally {
            db.endTransaction()
        }
    }

    // Transaction with explicit control
    fun transferFundsBasic(fromId: Int, toId: Int, amount: Double): Boolean {
        val db = dbHelper.writableDatabase

        // Start transaction
        db.beginTransaction()

        try {
            // Deduct from source account
            db.execSQL(
                "UPDATE $TABLE_ACCOUNTS SET $COLUMN_BALANCE = $COLUMN_BALANCE - ? WHERE $COLUMN_ACCOUNT_ID = ?",
                arrayOf(amount.toString(), fromId.toString())
            )

            // Add to destination account
            db.execSQL(
                "UPDATE $TABLE_ACCOUNTS SET $COLUMN_BALANCE = $COLUMN_BALANCE + ? WHERE $COLUMN_ACCOUNT_ID = ?",
                arrayOf(amount.toString(), toId.toString())
            )

            // Mark as successful
            db.setTransactionSuccessful()
            println("Transfer completed: $amount from account $fromId to $toId")
            return true

        } catch (e: Exception) {
            println("Transfer failed: ${e.message}")
            // Transaction will be rolled back
            return false
        } finally {
            db.endTransaction()
        }
    }
}

// 2. Transaction with Error Handling
class SafeTransaction(private val dbHelper: BankDatabaseHelper) {

    // Transfer with validation and rollback
    fun transferFundsSafe(fromId: Int, toId: Int, amount: Double): TransferResult {
        val db = dbHelper.writableDatabase

        // Validate amount
        if (amount <= 0) {
            return TransferResult(false, "Amount must be positive")
        }

        db.beginTransaction()

        try {
            // Check source account balance
            val cursor = db.query(
                TABLE_ACCOUNTS,
                arrayOf(COLUMN_BALANCE),
                "$COLUMN_ACCOUNT_ID = ?",
                arrayOf(fromId.toString()),
                null, null, null
            )

            cursor.use {
                if (!it.moveToFirst()) {
                    return TransferResult(false, "Source account not found")
                }

                val currentBalance = it.getDouble(0)
                if (currentBalance < amount) {
                    return TransferResult(false, "Insufficient funds")
                }
            }

            // Check destination account exists
            val destCursor = db.query(
                TABLE_ACCOUNTS,
                arrayOf(COLUMN_ACCOUNT_ID),
                "$COLUMN_ACCOUNT_ID = ?",
                arrayOf(toId.toString()),
                null, null, null
            )

            destCursor.use {
                if (!it.moveToFirst()) {
                    return TransferResult(false, "Destination account not found")
                }
            }

            // Perform transfer
            db.execSQL(
                "UPDATE $TABLE_ACCOUNTS SET $COLUMN_BALANCE = $COLUMN_BALANCE - ? WHERE $COLUMN_ACCOUNT_ID = ? AND $COLUMN_BALANCE >= ?",
                arrayOf(amount.toString(), fromId.toString(), amount.toString())
            )

            db.execSQL(
                "UPDATE $TABLE_ACCOUNTS SET $COLUMN_BALANCE = $COLUMN_BALANCE + ? WHERE $COLUMN_ACCOUNT_ID = ?",
                arrayOf(amount.toString(), toId.toString())
            )

            // Record transaction
            val txValues = ContentValues().apply {
                put(COLUMN_FROM_ACCOUNT, fromId)
                put(COLUMN_TO_ACCOUNT, toId)
                put(COLUMN_AMOUNT, amount)
            }

            db.insert(TABLE_TRANSACTIONS, null, txValues)

            // Mark as successful
            db.setTransactionSuccessful()

            println("Transfer successful: $amount from $fromId to $toId")
            return TransferResult(true, "Transfer completed successfully")

        } catch (e: Exception) {
            println("Transfer failed: ${e.message}")
            return TransferResult(false, "Transaction error: ${e.message}")

        } finally {
            db.endTransaction()
        }
    }

    // Batch operation with partial failure handling
    fun batchUpdateBalances(updates: List<BalanceUpdate>): BatchResult {
        val db = dbHelper.writableDatabase
        val results = mutableListOf<SingleResult>()

        db.beginTransaction()

        try {
            for (update in updates) {
                try {
                    val rows = db.update(
                        TABLE_ACCOUNTS,
                        ContentValues().apply { put(COLUMN_BALANCE, update.newBalance) },
                        "$COLUMN_ACCOUNT_ID = ?",
                        arrayOf(update.accountId.toString())
                    )

                    if (rows > 0) {
                        results.add(SingleResult(update.accountId, true, null))
                    } else {
                        results.add(SingleResult(update.accountId, false, "Account not found"))
                    }
                } catch (e: Exception) {
                    results.add(SingleResult(update.accountId, false, e.message))
                }
            }

            db.setTransactionSuccessful()
            return BatchResult(true, results)

        } catch (e: Exception) {
            return BatchResult(false, results)

        } finally {
            db.endTransaction()
        }
    }
}

// 3. Nested Transactions (Savepoints)
class NestedTransaction(private val dbHelper: BankDatabaseHelper) {

    // Transfer using savepoints
    fun transferWithSavepoint(fromId: Int, toId: Int, amount: Double): Boolean {
        val db = dbHelper.writableDatabase

        db.beginTransaction()

        try {
            // First, deduct from source
            db.execSQL(
                "UPDATE $TABLE_ACCOUNTS SET $COLUMN_BALANCE = $COLUMN_BALANCE - ? WHERE $COLUMN_ACCOUNT_ID = ?",
                arrayOf(amount.toString(), fromId.toString())
            )

            // Create savepoint
            val savepointName = "SP_" + System.currentTimeMillis()
            db.execSQL("SAVEPOINT $savepointName")

            try {
                // Try to add to destination
                db.execSQL(
                    "UPDATE $TABLE_ACCOUNTS SET $COLUMN_BALANCE = $COLUMN_BALANCE + ? WHERE $COLUMN_ACCOUNT_ID = ?",
                    arrayOf(amount.toString(), toId.toString())
                )

                // If successful, release savepoint
                db.execSQL("RELEASE SAVEPOINT $savepointName")

                db.setTransactionSuccessful()
                return true

            } catch (e: Exception) {
                // Rollback to savepoint
                db.execSQL("ROLLBACK TO SAVEPOINT $savepointName")
                throw e
            }

        } catch (e: Exception) {
            println("Transfer failed: ${e.message}")
            return false
        } finally {
            db.endTransaction()
        }
    }
}

// 4. Transaction Isolation Levels
class IsolatedTransaction(private val dbHelper: BankDatabaseHelper) {

    // Immediate lock (exclusive access)
    fun transferWithImmediateLock(fromId: Int, toId: Int, amount: Double): Boolean {
        val db = dbHelper.writableDatabase

        db.beginTransaction()

        try {
            // Lock accounts table with immediate lock
            db.execSQL("BEGIN IMMEDIATE TRANSACTION")

            // Perform transfer operations
            // ... transfer logic ...

            db.setTransactionSuccessful()
            return true

        } catch (e: Exception) {
            println("Transfer failed: ${e.message}")
            return false
        } finally {
            db.endTransaction()
        }
    }

    // Exclusive lock
    fun transferWithExclusiveLock(fromId: Int, toId: Int, amount: Double): Boolean {
        val db = dbHelper.writableDatabase

        try {
            // Lock database exclusively
            db.execSQL("BEGIN EXCLUSIVE TRANSACTION")

            // Perform transfer operations
            // ... transfer logic ...

            db.setTransactionSuccessful()
            return true

        } catch (e: Exception) {
            println("Transfer failed: ${e.message}")
            return false
        } finally {
            db.endTransaction()
        }
    }
}

// 5. Non-Transactional (Exclusive) Operations
class NonTransactionalOperation(private val dbHelper: BankDatabaseHelper) {

    // Quick update without transaction overhead
    fun quickBalanceUpdate(accountId: Int, newBalance: Double): Boolean {
        val db = dbHelper.writableDatabase

        try {
            // Use execSQL for faster, non-transactional operation
            db.execSQL(
                "UPDATE $TABLE_ACCOUNTS SET $COLUMN_BALANCE = ? WHERE $COLUMN_ACCOUNT_ID = ?",
                arrayOf(newBalance.toString(), accountId.toString())
            )
            return true
        } catch (e: Exception) {
            println("Quick update failed: ${e.message}")
            return false
        }
    }
}

// 6. Transaction Monitoring
class TransactionMonitor(private val dbHelper: BankDatabaseHelper) {

    // Check if database is in transaction
    fun isInTransaction(): Boolean {
        val db = dbHelper.readableDatabase
        return db.inTransaction()
    }

    // Perform operation with transaction status logging
    fun performWithLogging(operation: () -> Boolean): Boolean {
        val db = dbHelper.writableDatabase

        println("Transaction status before: ${db.inTransaction()}")

        db.beginTransaction()
        println("Transaction started")

        try {
            val result = operation()
            if (result) {
                db.setTransactionSuccessful()
                println("Transaction marked as successful")
            }
            return result
        } finally {
            db.endTransaction()
            println("Transaction ended. Status: ${db.inTransaction()}")
        }
    }
}

// Data classes
data class Account(val name: String, val balance: Double)

data class BalanceUpdate(val accountId: Int, val newBalance: Double)

data class TransferResult(val success: Boolean, val message: String)

data class BatchResult(val success: Boolean, val results: List<SingleResult>)

data class SingleResult(val accountId: Int, val success: Boolean, val error: String?)

// Main demonstration
fun demonstrateTransactionProcessing(context: Context) {
    println("=== Android Kotlin Transaction Processing Examples ===\n")

    val dbHelper = BankDatabaseHelper(context)

    // 1. Basic transaction
    println("--- 1. Basic Transaction ---")
    val basicTx = BasicTransaction(dbHelper)

    val accounts = listOf(
        Account("Alice", 1000.0),
        Account("Bob", 500.0),
        Account("Charlie", 750.0)
    )

    basicTx.performBatchInsert(accounts)

    // 2. Safe transaction with validation
    println("\n--- 2. Safe Transaction ---")
    val safeTx = SafeTransaction(dbHelper)

    val result1 = safeTx.transferFundsSafe(1, 2, 200.0)
    println("Transfer result: ${result1.success} - ${result1.message}")

    val result2 = safeTx.transferFundsSafe(1, 2, 5000.0)
    println("Transfer result (excess): ${result2.success} - ${result2.message}")

    // 3. Batch operations
    println("\n--- 3. Batch Operations ---")
    val updates = listOf(
        BalanceUpdate(1, 800.0),
        BalanceUpdate(2, 700.0),
        BalanceUpdate(3, 750.0)
    )

    val batchResult = safeTx.batchUpdateBalances(updates)
    println("Batch result: ${batchResult.success}")
    batchResult.results.forEach { r ->
        println("  Account ${r.accountId}: ${if (r.success) "Success" else "Failed - ${r.error}"}")
    }

    // 4. Transaction monitoring
    println("\n--- 4. Transaction Monitoring ---")
    val monitor = TransactionMonitor(dbHelper)

    println("In transaction (before): ${monitor.isInTransaction()}")

    monitor.performWithLogging {
        val transfer = basicTx.transferFundsBasic(1, 2, 50.0)
        transfer
    }

    println("In transaction (after): ${monitor.isInTransaction()}")

    // 5. Non-transactional quick operation
    println("\n--- 5. Non-Transactional Quick Operation ---")
    val nonTx = NonTransactionalOperation(dbHelper)

    nonTx.quickBalanceUpdate(1, 850.0)
    println("Quick update completed")

    // Cleanup
    dbHelper.close()
    println("\nDatabase cleaned up")

    println("\n=== All Transaction Processing Examples Completed ===")
}