Web Database Rust Samples

// Web Rust SQLite Connection Examples
// Basic SQLite database connection and management
//
// Add to Cargo.toml:
// [dependencies]
// rusqlite = "0.30"

use rusqlite::{Connection, Result as SqliteResult};
use std::path::Path;

// 1. Basic Connection

/// Create or open in-memory database
fn create_memory_db() -> SqliteResult<Connection> {
    Connection::open_in_memory()
}

/// Create or open file-based database
fn create_file_db(db_path: &str) -> SqliteResult<Connection> {
    Connection::open(db_path)
}

/// Create database if it doesn't exist
fn open_or_create_db(db_path: &str) -> SqliteResult<Connection> {
    if !Path::new(db_path).exists() {
        println!("Creating new database: {}", db_path);
    }
    Connection::open(db_path)
}

// 2. Connection Information

/// Get database path
fn get_db_path(conn: &Connection) -> SqliteResult<String> {
    let path = conn.path()
        .and_then(|p| p.to_str())
        .unwrap_or(":memory:")
        .to_string();
    Ok(path)
}

/// Check if database is in-memory
fn is_in_memory(conn: &Connection) -> bool {
    conn.path().is_none()
}

/// Get database version
fn get_db_version(conn: &Connection) -> SqliteResult<String> {
    conn.query_row("SELECT sqlite_version()", [], |row| {
        row.get(0)
    })
}

// 3. Database Settings

/// Enable foreign keys
fn enable_foreign_keys(conn: &Connection) -> SqliteResult<()> {
    conn.execute("PRAGMA foreign_keys = ON", [])?;
    Ok(())
}

/// Set journal mode
fn set_journal_mode(conn: &Connection, mode: &str) -> SqliteResult<()> {
    conn.execute(&format!("PRAGMA journal_mode = {}", mode), [])?;
    Ok(())
}

/// Set synchronous mode
fn set_synchronous(conn: &Connection, mode: &str) -> SqliteResult<()> {
    conn.execute(&format!("PRAGMA synchronous = {}", mode), [])?;
    Ok(())
}

/// Set busy timeout (milliseconds)
fn set_busy_timeout(conn: &Connection, timeout_ms: u32) -> SqliteResult<()> {
    conn.busy_timeout(timeout_ms)
}

/// Set cache size (negative value = KB)
fn set_cache_size(conn: &Connection, size: i32) -> SqliteResult<()> {
    conn.execute(&format!("PRAGMA cache_size = {}", size), [])?;
    Ok(())
}

// 4. Connection Pool (Simple)

/// Simple connection wrapper
struct DatabasePool {
    connections: Vec<Connection>,
}

impl DatabasePool {
    /// Create a new connection pool
    fn new(db_path: &str, pool_size: usize) -> SqliteResult<Self> {
        let mut connections = Vec::new();
        for _ in 0..pool_size {
            connections.push(Connection::open(db_path)?);
        }
        Ok(DatabasePool { connections })
    }

    /// Get a connection from the pool
    fn get_connection(&mut self) -> Option<&Connection> {
        self.connections.first()
    }

    /// Get pool size
    fn size(&self) -> usize {
        self.connections.len()
    }
}

// 5. Database Creation

/// Create a new database with initial schema
fn create_initialized_db(db_path: &str) -> SqliteResult<Connection> {
    let conn = Connection::open(db_path)?;

    // Enable foreign keys
    enable_foreign_keys(&conn)?;

    // Create tables
    conn.execute(
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        )",
        [],
    )?;

    conn.execute(
        "CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            content TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )",
        [],
    )?;

    Ok(conn)
}

// 6. Backup Database

/// Create a backup of the database
fn backup_database(source_path: &str, backup_path: &str) -> SqliteResult<()> {
    let source = Connection::open(source_path)?;
    let backup = Connection::open(backup_path)?;

    let src_path = source.backup(rusqlite::DatabaseName::Main, &backup, rusqlite::DatabaseName::Main, None)?;
    src_path.run_to_completion()
}

// 7. Database Statistics

/// Get database size in bytes
fn get_db_size(conn: &Connection) -> SqliteResult<u64> {
    conn.query_row("SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size()", [], |row| {
        row.get(0)
    })
}

/// Get table count
fn get_table_count(conn: &Connection) -> SqliteResult<usize> {
    conn.query_row("SELECT count(*) FROM sqlite_master WHERE type='table'", [], |row| {
        row.get(0)
    })
}

/// Get database page count
fn get_page_count(conn: &Connection) -> SqliteResult<usize> {
    conn.query_row("PRAGMA page_count", [], |row| {
        row.get(0)
    })
}

// 8. Integrity Check

/// Perform integrity check
fn check_integrity(conn: &Connection) -> SqliteResult<bool> {
    let result: String = conn.query_row("PRAGMA integrity_check", [], |row| {
        row.get(0)
    })?;
    Ok(result == "ok")
}

/// Perform quick check
fn quick_check(conn: &Connection) -> SqliteResult<bool> {
    let result: String = conn.query_row("PRAGMA quick_check", [], |row| {
        row.get(0)
    })?;
    Ok(result == "ok")
}

// 9. Optimize Database

/// Vacuum database to reclaim space
fn vacuum_database(conn: &Connection) -> SqliteResult<()> {
    conn.execute("VACUUM", [])
}

/// Analyze database for query optimization
fn analyze_database(conn: &Connection) -> SqliteResult<()> {
    conn.execute("ANALYZE", [])
}

/// Reindex database
fn reindex_database(conn: &Connection) -> SqliteResult<()> {
    conn.execute("REINDEX", [])
}

// 10. Close and Cleanup

/// Close database properly
fn close_database(conn: Connection) -> SqliteResult<()> {
    // Ensure all changes are committed
    conn.execute("PRAGMA optimize", [])?;
    drop(conn);
    Ok(())
}

// Usage Examples
fn main() -> SqliteResult<()> {
    println!("=== Web Rust SQLite Connection Examples ===\n");

    // 1. Create in-memory database
    println!("--- 1. In-Memory Database ---");
    let mem_conn = create_memory_db()?;
    println!("Created in-memory database");
    println!("Is in-memory: {}", is_in_memory(&mem_conn));

    // 2. Create file-based database
    println!("\n--- 2. File-Based Database ---");
    let db_path = "test.db";
    let file_conn = open_or_create_db(db_path)?;
    println!("Database path: {}", get_db_path(&file_conn)?);
    println!("SQLite version: {}", get_db_version(&file_conn)?);

    // 3. Configure database
    println!("\n--- 3. Database Configuration ---");
    enable_foreign_keys(&file_conn)?;
    set_journal_mode(&file_conn, "WAL")?;
    set_busy_timeout(&file_conn, 5000)?;
    println!("Database configured successfully");

    // 4. Create initialized database
    println!("\n--- 4. Create Database with Schema ---");
    let init_db_path = "initialized.db";
    let init_conn = create_initialized_db(init_db_path)?;
    println!("Database created with tables");
    println!("Table count: {}", get_table_count(&init_conn)?);

    // 5. Database statistics
    println!("\n--- 5. Database Statistics ---");
    println!("Size: {} bytes", get_db_size(&init_conn)?);
    println!("Page count: {}", get_page_count(&init_conn)?);

    // 6. Integrity check
    println!("\n--- 6. Integrity Check ---");
    println!("Integrity check passed: {}", check_integrity(&init_conn)?);
    println!("Quick check passed: {}", quick_check(&init_conn)?);

    // 7. Optimization
    println!("\n--- 7. Database Optimization ---");
    analyze_database(&init_conn)?;
    println!("Database analyzed for optimization");

    // 8. Connection pool
    println!("\n--- 8. Simple Connection Pool ---");
    let mut pool = DatabasePool::new("pool.db", 3)?;
    println!("Pool size: {}", pool.size());
    if let Some(conn) = pool.get_connection() {
        println!("Got connection from pool");
    }

    // 9. Backup
    println!("\n--- 9. Database Backup ---");
    let backup_path = "test_backup.db";
    backup_database(init_db_path, backup_path)?;
    println!("Database backed up to: {}", backup_path);

    // 10. Cleanup
    println!("\n--- 10. Cleanup ---");
    close_database(init_conn)?;
    println!("Database closed properly");

    // Note: In production, you'd want to remove these test files
    // std::fs::remove_file("test.db")?;
    // std::fs::remove_file("initialized.db")?;
    // std::fs::remove_file("test_backup.db")?;
    // std::fs::remove_file("pool.db")?;

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

// Web Rust SQL Query Examples
// Execute various SQL queries with parameter binding
//
// Add to Cargo.toml:
// [dependencies]
// rusqlite = "0.30"

use rusqlite::{Connection, Result as SqliteResult, params};
use serde::Serialize;

// 1. INSERT Operations

/// User struct representing database record
#[derive(Debug, Serialize)]
struct User {
    id: i64,
    name: String,
    email: String,
    created_at: String,
}

/// Insert a single user
fn insert_user(conn: &Connection, name: &str, email: &str) -> SqliteResult<i64> {
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params![name, email],
    )?;
    Ok(conn.last_insert_rowid())
}

/// Insert multiple users using transaction
fn insert_users_batch(conn: &Connection, users: &[(&str, &str)]) -> SqliteResult<Vec<i64>> {
    let mut ids = Vec::new();

    for (name, email) in users {
        conn.execute(
            "INSERT INTO users (name, email) VALUES (?1, ?2)",
            params![name, email],
        )?;
        ids.push(conn.last_insert_rowid());
    }

    Ok(ids)
}

/// Insert user with return value
fn insert_user_and_get(conn: &Connection, name: &str, email: &str) -> SqliteResult<User> {
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params![name, email],
    )?;

    let id = conn.last_insert_rowid();
    get_user_by_id(conn, id)
}

/// Insert or replace (upsert)
fn insert_or_replace_user(conn: &Connection, id: i64, name: &str, email: &str) -> SqliteResult<()> {
    conn.execute(
        "INSERT OR REPLACE INTO users (id, name, email) VALUES (?1, ?2, ?3)",
        params![id, name, email],
    )?;
    Ok(())
}

// 2. SELECT Operations

/// Get user by ID
fn get_user_by_id(conn: &Connection, id: i64) -> SqliteResult<User> {
    conn.query_row(
        "SELECT id, name, email, created_at FROM users WHERE id = ?1",
        params![id],
        |row| {
            Ok(User {
                id: row.get(0)?,
                name: row.get(1)?,
                email: row.get(2)?,
                created_at: row.get(3)?,
            })
        },
    )
}

/// Get user by email
fn get_user_by_email(conn: &Connection, email: &str) -> SqliteResult<User> {
    conn.query_row(
        "SELECT id, name, email, created_at FROM users WHERE email = ?1",
        params![email],
        |row| {
            Ok(User {
                id: row.get(0)?,
                name: row.get(1)?,
                email: row.get(2)?,
                created_at: row.get(3)?,
            })
        },
    )
}

/// Get all users
fn get_all_users(conn: &Connection) -> SqliteResult<Vec<User>> {
    let mut stmt = conn.prepare(
        "SELECT id, name, email, created_at FROM users ORDER BY created_at DESC"
    )?;

    let user_iter = stmt.query_map([], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
            created_at: row.get(3)?,
        })
    })?;

    user_iter.collect()
}

/// Get users with pagination
fn get_users_paginated(conn: &Connection, limit: usize, offset: usize) -> SqliteResult<Vec<User>> {
    let mut stmt = conn.prepare(
        "SELECT id, name, email, created_at FROM users ORDER BY id LIMIT ?1 OFFSET ?2"
    )?;

    let user_iter = stmt.query_map(params![limit, offset], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
            created_at: row.get(3)?,
        })
    })?;

    user_iter.collect()
}

/// Search users by name
fn search_users_by_name(conn: &Connection, name_pattern: &str) -> SqliteResult<Vec<User>> {
    let mut stmt = conn.prepare(
        "SELECT id, name, email, created_at FROM users WHERE name LIKE ?1 ORDER BY name"
    )?;

    let user_iter = stmt.query_map(params![format!("%{}%", name_pattern)], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
            created_at: row.get(3)?,
        })
    })?;

    user_iter.collect()
}

// 3. UPDATE Operations

/// Update user name
fn update_user_name(conn: &Connection, id: i64, new_name: &str) -> SqliteResult<usize> {
    conn.execute(
        "UPDATE users SET name = ?1 WHERE id = ?2",
        params![new_name, id],
    )
}

/// Update user email
fn update_user_email(conn: &Connection, id: i64, new_email: &str) -> SqliteResult<usize> {
    conn.execute(
        "UPDATE users SET email = ?1 WHERE id = ?2",
        params![new_email, id],
    )
}

/// Update multiple fields
fn update_user(conn: &Connection, id: i64, name: &str, email: &str) -> SqliteResult<usize> {
    conn.execute(
        "UPDATE users SET name = ?1, email = ?2 WHERE id = ?3",
        params![name, email, id],
    )
}

/// Increment user access counter
#[allow(dead_code)]
fn increment_user_counter(conn: &Connection, id: i64) -> SqliteResult<usize> {
    conn.execute(
        "UPDATE users SET access_count = COALESCE(access_count, 0) + 1 WHERE id = ?1",
        params![id],
    )
}

// 4. DELETE Operations

/// Delete user by ID
fn delete_user(conn: &Connection, id: i64) -> SqliteResult<usize> {
    conn.execute("DELETE FROM users WHERE id = ?1", params![id])
}

/// Delete users by condition
fn delete_users_by_name(conn: &Connection, name: &str) -> SqliteResult<usize> {
    conn.execute("DELETE FROM users WHERE name = ?1", params![name])
}

/// Delete all users (use with caution!)
fn delete_all_users(conn: &Connection) -> SqliteResult<usize> {
    conn.execute("DELETE FROM users", [])
}

// 5. COUNT and Aggregates

/// Count total users
fn count_users(conn: &Connection) -> SqliteResult<usize> {
    conn.query_row("SELECT COUNT(*) FROM users", [], |row| {
        row.get(0)
    })
}

/// Count users by name pattern
fn count_users_by_pattern(conn: &Connection, pattern: &str) -> SqliteResult<usize> {
    conn.query_row(
        "SELECT COUNT(*) FROM users WHERE name LIKE ?1",
        params![format!("%{}%", pattern)],
        |row| row.get(0)
    )
}

/// Get user statistics
#[derive(Debug)]
struct UserStats {
    total_users: usize,
    users_today: usize,
    avg_name_length: f64,
}

fn get_user_stats(conn: &Connection) -> SqliteResult<UserStats> {
    conn.query_row(
        "SELECT
            COUNT(*) as total,
            COUNT(CASE WHEN date(created_at) = date('now') THEN 1 END) as today,
            AVG(LENGTH(name)) as avg_length
        FROM users",
        [],
        |row| {
            Ok(UserStats {
                total_users: row.get(0)?,
                users_today: row.get(1)?,
                avg_name_length: row.get(2)?,
            })
        },
    )
}

// 6. Complex Queries

/// Get users with their post counts
#[derive(Debug)]
struct UserWithPostCount {
    id: i64,
    name: String,
    email: String,
    post_count: i64,
}

fn get_users_with_post_count(conn: &Connection) -> SqliteResult<Vec<UserWithPostCount>> {
    let mut stmt = conn.prepare(
        "SELECT u.id, u.name, u.email, COUNT(p.id) as post_count
         FROM users u
         LEFT JOIN posts p ON u.id = p.user_id
         GROUP BY u.id
         ORDER BY post_count DESC"
    )?;

    let user_iter = stmt.query_map([], |row| {
        Ok(UserWithPostCount {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
            post_count: row.get(3)?,
        })
    })?;

    user_iter.collect()
}

/// Get recent activity
#[derive(Debug)]
struct RecentActivity {
    user_name: String,
    action_type: String,
    title: String,
    created_at: String,
}

fn get_recent_activity(conn: &Connection, limit: usize) -> SqliteResult<Vec<RecentActivity>> {
    let mut stmt = conn.prepare(
        "SELECT u.name, 'post' as type, p.title, p.created_at
         FROM posts p
         JOIN users u ON p.user_id = u.id
         ORDER BY p.created_at DESC
         LIMIT ?1"
    )?;

    let activity_iter = stmt.query_map(params![limit], |row| {
        Ok(RecentActivity {
            user_name: row.get(0)?,
            action_type: row.get(1)?,
            title: row.get(2)?,
            created_at: row.get(3)?,
        })
    })?;

    activity_iter.collect()
}

// Usage Examples
fn main() -> SqliteResult<()> {
    println!("=== Web Rust SQL Query Examples ===\n");

    let conn = Connection::open_in_memory()?;

    // Create tables
    conn.execute(
        "CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        )",
        [],
    )?;

    conn.execute(
        "CREATE TABLE posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            content TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )",
        [],
    )?;

    // 1. INSERT examples
    println!("--- 1. INSERT Operations ---");
    let id1 = insert_user(&conn, "Alice", "[email protected]")?;
    println!("Inserted user with ID: {}", id1);

    let id2 = insert_user(&conn, "Bob", "[email protected]")?;
    println!("Inserted user with ID: {}", id2);

    let batch_ids = insert_users_batch(&conn, &[
        ("Charlie", "[email protected]"),
        ("Diana", "[email protected]"),
    ])?;
    println!("Batch inserted users: {:?}", batch_ids);

    // 2. SELECT examples
    println!("\n--- 2. SELECT Operations ---");
    match get_user_by_id(&conn, id1) {
        Ok(user) => println!("User by ID: {:?}", user),
        Err(e) => println!("Error: {}", e),
    }

    let all_users = get_all_users(&conn)?;
    println!("All users: {} found", all_users.len());
    for user in &all_users {
        println!("  - {} ({})", user.name, user.email);
    }

    // 3. SEARCH examples
    println!("\n--- 3. SEARCH Operations ---");
    let search_results = search_users_by_name(&conn, "a")?;
    println!("Users matching 'a': {} found", search_results.len());

    // 4. UPDATE examples
    println!("\n--- 4. UPDATE Operations ---");
    let updated = update_user_name(&conn, id1, "Alice Smith")?;
    println!("Updated {} row(s)", updated);

    // 5. COUNT and stats
    println!("\n--- 5. COUNT and Statistics ---");
    let count = count_users(&conn)?;
    println!("Total users: {}", count);

    let stats = get_user_stats(&conn)?;
    println!("User stats: {:?}", stats);

    // 6. DELETE examples
    println!("\n--- 6. DELETE Operations ---");
    let deleted = delete_user(&conn, id2)?;
    println!("Deleted {} user(s)", deleted);

    println!("\n=== All SQL Query Examples Completed ===");
    Ok(())
}

// Web Rust Transaction Handling Examples
// Transaction management with commit and rollback
//
// Add to Cargo.toml:
// [dependencies]
// rusqlite = "0.30"

use rusqlite::{Connection, Result as SqliteResult, Transaction, params};

// 1. Basic Transaction

/// Execute code within a transaction
fn execute_in_transaction<F>(conn: &Connection, mut operation: F) -> SqliteResult<()>
where
    F: FnMut(&Transaction) -> SqliteResult<()>,
{
    let tx = conn.unchecked_transaction()?;
    operation(&tx)?;
    tx.commit()?;
    Ok(())
}

/// Create tables within a transaction
fn create_tables_transaction(conn: &Connection) -> SqliteResult<()> {
    execute_in_transaction(conn, |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS accounts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                balance INTEGER NOT NULL DEFAULT 0
            )",
            [],
        )?;

        tx.execute(
            "CREATE TABLE IF NOT EXISTS transactions_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                from_account INTEGER,
                to_account INTEGER,
                amount INTEGER NOT NULL,
                timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (from_account) REFERENCES accounts(id),
                FOREIGN KEY (to_account) REFERENCES accounts(id)
            )",
            [],
        )?;

        Ok(())
    })
}

// 2. Transfer Money (Classic Transaction Example)

/// Transfer money between accounts with transaction
fn transfer_money(
    conn: &Connection,
    from_id: i64,
    to_id: i64,
    amount: i64,
) -> SqliteResult<()> {
    let tx = conn.unchecked_transaction()?;

    // Check from account has sufficient balance
    let balance: i64 = tx.query_row(
        "SELECT balance FROM accounts WHERE id = ?1",
        params![from_id],
        |row| row.get(0),
    )?;

    if balance < amount {
        return Err(rusqlite::Error::SqliteFailure(
            rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_CONSTRAINT),
            Some("Insufficient funds".to_string()),
        ));
    }

    // Deduct from sender
    tx.execute(
        "UPDATE accounts SET balance = balance - ?1 WHERE id = ?2",
        params![amount, from_id],
    )?;

    // Add to receiver
    tx.execute(
        "UPDATE accounts SET balance = balance + ?1 WHERE id = ?2",
        params![amount, to_id],
    )?;

    // Log transaction
    tx.execute(
        "INSERT INTO transactions_log (from_account, to_account, amount) VALUES (?1, ?2, ?3)",
        params![from_id, to_id, amount],
    )?;

    // Commit transaction
    tx.commit()?;
    Ok(())
}

/// Transfer with automatic rollback on error
fn transfer_safe(
    conn: &Connection,
    from_id: i64,
    to_id: i64,
    amount: i64,
) -> Result<String, Box<dyn std::error::Error>> {
    let mut tx = conn.unchecked_transaction()?;

    // Perform operations
    let result: SqliteResult<()> = (|| {
        // Deduct from sender
        tx.execute(
            "UPDATE accounts SET balance = balance - ?1 WHERE id = ?2",
            params![amount, from_id],
        )?;

        // Add to receiver
        tx.execute(
            "UPDATE accounts SET balance = balance + ?1 WHERE id = ?2",
            params![amount, to_id],
        )?;

        // Log
        tx.execute(
            "INSERT INTO transactions_log (from_account, to_account, amount) VALUES (?1, ?2, ?3)",
            params![from_id, to_id, amount],
        )?;

        Ok(())
    })();

    match result {
        Ok(_) => {
            tx.commit()?;
            Ok("Transfer successful".to_string())
        }
        Err(e) => {
            tx.rollback()?;
            Err(format!("Transfer failed: {}", e).into())
        }
    }
}

// 3. Nested Transactions (Savepoints)

/// Create nested transaction using savepoint
fn create_account_with_initial_balance(
    tx: &Transaction,
    name: &str,
    initial_balance: i64,
) -> SqliteResult<i64> {
    tx.execute(
        "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
        params![name, initial_balance],
    )?;
    Ok(tx.last_insert_rowid())
}

/// Batch transfer with savepoints
fn batch_transfer(
    conn: &Connection,
    transfers: Vec<(i64, i64, i64)>,
) -> SqliteResult<usize> {
    let tx = conn.unchecked_transaction()?;
    let mut success_count = 0;

    for (from, to, amount) in transfers {
        // Use savepoint for each transfer
        let sp = tx.savepoint()?;
        match sp.execute(
            "UPDATE accounts SET balance = balance - ?1 WHERE id = ?2",
            params![amount, from],
        ) {
            Ok(_) => {
                sp.execute(
                    "UPDATE accounts SET balance = balance + ?1 WHERE id = ?2",
                    params![amount, to],
                )?;
                sp.commit()?;
                success_count += 1;
            }
            Err(_) => {
                sp.rollback()?;
                continue;
            }
        }
    }

    tx.commit()?;
    Ok(success_count)
}

// 4. Transaction Isolation Levels

/// Set transaction isolation level
fn set_isolation_level(conn: &Connection, level: &str) -> SqliteResult<()> {
    conn.execute(&format!("PRAGMA read_unauthorized = {}", level), [])?;
    Ok(())
}

/// Read-only transaction
fn read_only_transaction<F, R>(conn: &Connection, operation: F) -> SqliteResult<R>
where
    F: FnOnce(&Transaction) -> SqliteResult<R>,
{
    let tx = conn.transaction()?;
    let result = operation(&tx)?;
    tx.commit()?;
    Ok(result)
}

// 5. Batch Operations

/// Insert multiple records in a transaction
fn batch_insert_accounts(
    conn: &Connection,
    accounts: Vec<(&str, i64)>,
) -> SqliteResult<Vec<i64>> {
    let tx = conn.unchecked_transaction()?;
    let mut ids = Vec::new();

    for (name, balance) in accounts {
        tx.execute(
            "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
            params![name, balance],
        )?;
        ids.push(tx.last_insert_rowid());
    }

    tx.commit()?;
    Ok(ids)
}

/// Batch update with transaction
fn batch_update_balances(
    conn: &Connection,
    updates: Vec<(i64, i64)>,
) -> SqliteResult<usize> {
    let tx = conn.unchecked_transaction()?;
    let mut total_updated = 0;

    for (id, new_balance) in updates {
        total_updated += tx.execute(
            "UPDATE accounts SET balance = ?1 WHERE id = ?2",
            params![new_balance, id],
        )?;
    }

    tx.commit()?;
    Ok(total_updated)
}

// 6. Transaction with Retry

/// Execute transaction with retry on busy
fn transaction_with_retry<F>(
    conn: &Connection,
    mut operation: F,
    max_retries: u32,
) -> SqliteResult<()>
where
    F: FnMut(&Transaction) -> SqliteResult<()>,
{
    let mut retries = 0;

    loop {
        let tx = conn.unchecked_transaction()?;

        match operation(&tx) {
            Ok(_) => {
                return tx.commit();
            }
            Err(rusqlite::Error::SqliteFailure(err, _))
                if err.code == rusqlite::ffi::ErrorCode::DatabaseBusy =>
            {
                retries += 1;
                if retries >= max_retries {
                    return Err(rusqlite::Error::SqliteFailure(err, None));
                }
                std::thread::sleep(std::time::Duration::from_millis(100));
                continue;
            }
            Err(e) => return Err(e),
        }
    }
}

// 7. Deferred Transaction

/// Begin deferred transaction (starts on first statement)
fn deferred_transaction<F>(conn: &Connection, operation: F) -> SqliteResult<()>
where
    F: FnOnce(&Transaction) -> SqliteResult<()>,
{
    let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Deferred)?;
    operation(&tx)?;
    tx.commit()?;
    Ok(())
}

// 8. Immediate Transaction

/// Begin immediate transaction (locks immediately)
fn immediate_transaction<F>(conn: &Connection, operation: F) -> SqliteResult<()>
where
    F: FnOnce(&Transaction) -> SqliteResult<()>,
{
    let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
    operation(&tx)?;
    tx.commit()?;
    Ok(())
}

// 9. Exclusive Transaction

/// Begin exclusive transaction (exclusive lock)
fn exclusive_transaction<F>(conn: &Connection, operation: F) -> SqliteResult<()>
where
    F: FnOnce(&Transaction) -> SqliteResult<()>,
{
    let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Exclusive)?;
    operation(&tx)?;
    tx.commit()?;
    Ok(())
}

// 10. Transaction Status

/// Check if transaction is active
fn is_in_transaction(conn: &Connection) -> SqliteResult<bool> {
    conn.query_row("PRAGMA transaction_status", [], |row| {
        let status: String = row.get(0)?;
        Ok(status != "0")
    })
}

/// Get current transaction state
fn get_transaction_state(conn: &Connection) -> SqliteResult<String> {
    conn.query_row("PRAGMA transaction_status", [], |row| {
        row.get(0)
    })
}

// Usage Examples
fn main() -> SqliteResult<()> {
    println!("=== Web Rust Transaction Handling Examples ===\n");

    let conn = Connection::open_in_memory()?;

    // Create tables
    create_tables_transaction(&conn)?;
    println!("Created tables in transaction");

    // 1. Basic transaction
    println!("\n--- 1. Basic Transaction ---");
    execute_in_transaction(&conn, |tx| {
        tx.execute(
            "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
            params!["Alice", 1000],
        )?;
        tx.execute(
            "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
            params!["Bob", 500],
        )?;
        Ok(())
    })?;
    println!("Inserted initial accounts");

    // 2. Transfer money
    println!("\n--- 2. Transfer Money ---");
    let from_id = 1;
    let to_id = 2;
    match transfer_money(&conn, from_id, to_id, 200) {
        Ok(_) => println!("Transfer successful"),
        Err(e) => println!("Transfer failed: {}", e),
    }

    // 3. Safe transfer with rollback
    println!("\n--- 3. Safe Transfer ---");
    match transfer_safe(&conn, from_id, to_id, 100) {
        Ok(msg) => println!("{}", msg),
        Err(e) => println!("Error: {}", e),
    }

    // 4. Batch operations
    println!("\n--- 4. Batch Insert ---");
    let accounts = vec![
        ("Charlie", 750),
        ("Diana", 1200),
        ("Eve", 300),
    ];
    match batch_insert_accounts(&conn, accounts) {
        Ok(ids) => println!("Batch inserted {} accounts", ids.len()),
        Err(e) => println!("Batch insert failed: {}", e),
    }

    // 5. Batch transfer
    println!("\n--- 5. Batch Transfer ---");
    let transfers = vec![
        (1, 3, 50),   // Alice -> Charlie
        (2, 4, 100),  // Bob -> Diana
        (3, 5, 25),   // Charlie -> Eve
    ];
    match batch_transfer(&conn, transfers) {
        Ok(count) => println!("Completed {} transfers", count),
        Err(e) => println!("Batch transfer failed: {}", e),
    }

    // 6. Transaction status
    println!("\n--- 6. Transaction Status ---");
    println!("In transaction: {}", is_in_transaction(&conn)?);
    println!("Transaction state: {}", get_transaction_state(&conn)?);

    // 7. Read-only transaction
    println!("\n--- 7. Read-Only Transaction ---");
    let balance: i64 = read_only_transaction(&conn, |tx| {
        tx.query_row("SELECT SUM(balance) FROM accounts", [], |row| row.get(0))
    })?;
    println!("Total balance: {}", balance);

    // 8. Deferred transaction
    println!("\n--- 8. Deferred Transaction ---");
    deferred_transaction(&conn, |tx| {
        tx.execute(
            "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
            params!["Frank", 600],
        )?;
        Ok(())
    })?;
    println!("Inserted account with deferred transaction");

    println!("\n=== All Transaction Handling Examples Completed ===");
    Ok(())
}