drizzle 0.1.6

#![cfg(any(feature = "rusqlite", feature = "turso", feature = "libsql"))]

use crate::common::schema::sqlite::{InsertSimple, SelectSimple, SimpleSchema, UpdateSimple};
use drizzle::core::expr::*;
use drizzle::error::DrizzleError;
use drizzle::sqlite::connection::SQLiteTransactionType;
use drizzle::sqlite::prelude::*;

#[drizzle::test]
fn test_transaction_commit(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Insert first record
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("user1")])
                .execute()
        )?;

        // Insert second record
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("user2")])
                .execute()
        )?;
        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Transaction commit test should succeed, got: {:?}",
        result.as_ref().err()
    );

    // Verify both records were inserted
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 2);
    assert_eq!(users[0].name, "user1");
    assert_eq!(users[1].name, "user2");
}

#[drizzle::test]
fn test_transaction_rollback(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    // Insert initial record outside transaction

    db.insert(simple)
        .values([InsertSimple::new("initial_user")])
        .execute();

    let result: Result<(), DrizzleError> =
        result!(db.transaction(SQLiteTransactionType::Immediate, |tx| {
            // Insert a record
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("temp_user")])
                    .execute()
            )?;

            // Simulate an error to trigger rollback
            Err(DrizzleError::Other(
                "Intentional rollback".to_string().into(),
            ))
        }));

    assert!(result.is_err());

    // Verify only the initial record exists (transaction was rolled back)
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 1);
    assert_eq!(users[0].name, "initial_user");
}

#[drizzle::test]
fn test_transaction_types(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    // Test different transaction types
    for tx_type in [
        SQLiteTransactionType::Deferred,
        SQLiteTransactionType::Immediate,
        SQLiteTransactionType::Exclusive,
    ] {
        let result = result!(db.transaction(tx_type, |tx| {
            let user_name = format!("user_{:?}", tx_type);
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new(user_name.as_str())])
                    .execute()
            )?;
            Ok(())
        }));

        assert!(result.is_ok(), "Transaction failed for type {:?}", tx_type);
    }

    // Verify all records were inserted
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 3);
}

#[drizzle::test]
fn test_transaction_query_builders(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    // Insert test data

    db.insert(simple)
        .values([
            InsertSimple::new("alice"),
            InsertSimple::new("bob"),
            InsertSimple::new("charlie"),
        ])
        .execute();

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Test SELECT
        let users: Vec<SelectSimple> = result!(
            tx.select(())
                .from(simple)
                .r#where(eq(simple.name, "alice"))
                .all()
        )?;
        assert_eq!(users.len(), 1);

        // Test INSERT
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("dave")])
                .execute()
        )?;

        // Test UPDATE
        result!(
            tx.update(simple)
                .set(UpdateSimple::default().with_name("updated_bob"))
                .r#where(eq(simple.name, "bob"))
                .execute()
        )?;

        // Test DELETE
        result!(
            tx.delete(simple)
                .r#where(eq(simple.name, "charlie"))
                .execute()
        )?;

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Transaction query builders test should succeed, got: {:?}",
        result.as_ref().err()
    );

    // Verify final state
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 3); // alice, dave, updated_bob

    let names: Vec<String> = users.into_iter().map(|u| u.name).collect();
    assert!(names.contains(&"alice".to_string()));
    assert!(names.contains(&"dave".to_string()));
    assert!(names.contains(&"updated_bob".to_string()));
    assert!(!names.contains(&"bob".to_string()));
    assert!(!names.contains(&"charlie".to_string()));
}

#[drizzle::test]
fn test_transaction_database_error_rollback(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    // Insert initial data

    db.insert(simple)
        .values([InsertSimple::new("initial")])
        .execute();

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Insert valid record
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("valid_insert")])
                .execute()
        )?;

        // Try to insert duplicate primary key (should cause DB error)
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("duplicate").with_id(1)]) // Same ID as "initial"
                .execute()
        )?;

        Ok(())
    }));

    assert!(result.is_err());

    // Verify rollback - only initial record should exist
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 1);
    assert_eq!(users[0].name, "initial");
}

#[drizzle::test]
fn test_transaction_panic_rollback(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    // Insert initial data

    db.insert(simple)
        .values([InsertSimple::new("before_panic")])
        .execute();

    // Attempt transaction that will panic
    let result: Result<Result<(), DrizzleError>, _> =
        catch!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
            // Insert a record
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("should_rollback")])
                    .execute()
            )?;

            // Panic!
            panic!("Simulated panic in transaction");
        }));

    assert!(result.is_err()); // Panic occurred

    // Verify rollback - panic should have triggered rollback
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 1);
    assert_eq!(users[0].name, "before_panic");
}

#[drizzle::test]
fn test_nested_transaction_operations(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Immediate, |tx| {
        // Insert users
        result!(
            tx.insert(simple)
                .values([
                    InsertSimple::new("user1"),
                    InsertSimple::new("user2"),
                    InsertSimple::new("user3"),
                ])
                .execute()
        )?;

        // Verify inserts worked
        let count: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
        assert_eq!(count.len(), 3);

        // Update one user
        result!(
            tx.update(simple)
                .set(UpdateSimple::default().with_name("updated_user1"))
                .r#where(eq(simple.name, "user1"))
                .execute()
        )?;

        // Delete one user
        result!(
            tx.delete(simple)
                .r#where(eq(simple.name, "user3"))
                .execute()
        )?;

        // Verify intermediate state
        let remaining: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
        assert_eq!(remaining.len(), 2);

        // If we got this far, everything should commit
        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Nested transaction operations should succeed, got: {:?}",
        result.as_ref().err()
    );

    // Verify final committed state
    let final_users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(final_users.len(), 2);

    let names: Vec<String> = final_users.into_iter().map(|u| u.name).collect();
    assert!(names.contains(&"updated_user1".to_string()));
    assert!(names.contains(&"user2".to_string()));
    assert!(!names.contains(&"user1".to_string()));
    assert!(!names.contains(&"user3".to_string()));
}

#[drizzle::test]
fn test_transaction_with_failed_query_in_middle(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    // Test transaction where a query fails in the middle
    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Insert first record (should succeed)
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("first")])
                .execute()
        )?;

        // Insert second record (should succeed)
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("second")])
                .execute()
        )?;

        // Try invalid operation that should fail
        // Attempt to update non-existent record and verify it returns 0 affected rows
        let affected = result!(
            tx.update(simple)
                .set(UpdateSimple::default().with_name("wont_work"))
                .r#where(eq(simple.name, "nonexistent_user"))
                .execute()
        )?;

        if affected == 0 {
            return Err(DrizzleError::Other(
                "No rows affected by update".to_string().into(),
            ));
        }

        Ok(())
    }));

    assert!(result.is_err());

    // Verify complete rollback - no records should exist
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 0);
}

#[drizzle::test]
fn test_large_transaction_rollback(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    // Test rollback of transaction with many operations
    let result: Result<(), DrizzleError> =
        result!(db.transaction(SQLiteTransactionType::Exclusive, |tx| {
            // Insert many records
            for i in 0..100 {
                let user_name = format!("user_{}", i);
                result!(
                    tx.insert(simple)
                        .values([InsertSimple::new(user_name.as_str())])
                        .execute()
                )?;
            }

            // Verify all were inserted
            let count: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
            assert_eq!(count.len(), 100);

            // Force rollback
            Err(DrizzleError::Other(
                "Intentional rollback of large transaction"
                    .to_string()
                    .into(),
            ))
        }));

    assert!(result.is_err());

    // Verify complete rollback - no records should exist
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 0);
}

#[drizzle::test]
fn test_savepoint_commit(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Insert in outer transaction
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("outer")])
                .execute()
        )?;

        // Savepoint that commits
        result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("inner")])
                    .execute()
            )?;
            Ok(())
        }))?;

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Savepoint commit test should succeed, got: {:?}",
        result.as_ref().err()
    );

    // Both records should exist
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 2);
    let names: Vec<String> = users.into_iter().map(|u| u.name).collect();
    assert!(names.contains(&"outer".to_string()));
    assert!(names.contains(&"inner".to_string()));
}

#[drizzle::test]
fn test_savepoint_rollback_preserves_outer(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Insert in outer transaction
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("outer")])
                .execute()
        )?;

        // Savepoint that rolls back
        let sp_result: Result<(), _> = result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("inner_rollback")])
                    .execute()
            )?;
            Err(DrizzleError::Other("rollback inner".to_string().into()))
        }));

        // Savepoint error should not abort the outer transaction
        assert!(sp_result.is_err());

        // Insert another record after the rolled-back savepoint
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("after_sp")])
                .execute()
        )?;

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Outer transaction should survive rolled-back savepoint, got: {:?}",
        result.as_ref().err()
    );

    // Only outer + after_sp should exist, inner_rollback should be gone
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 2);
    let names: Vec<String> = users.into_iter().map(|u| u.name).collect();
    assert!(names.contains(&"outer".to_string()));
    assert!(names.contains(&"after_sp".to_string()));
    assert!(!names.contains(&"inner_rollback".to_string()));
}

#[drizzle::test]
fn test_savepoint_outer_rollback(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result: Result<(), DrizzleError> =
        result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
            // Insert in outer transaction
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("outer")])
                    .execute()
            )?;

            // Savepoint that commits
            result!(tx.savepoint(|tx| {
                result!(
                    tx.insert(simple)
                        .values([InsertSimple::new("inner")])
                        .execute()
                )?;
                Ok(())
            }))?;

            // Rollback the entire outer transaction
            Err(DrizzleError::Other("rollback outer".to_string().into()))
        }));

    assert!(result.is_err());

    // Everything should be rolled back
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 0);
}

#[drizzle::test]
fn test_nested_savepoints(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("level_0")])
                .execute()
        )?;

        // First level savepoint
        result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("level_1")])
                    .execute()
            )?;

            // Second level savepoint (nested within first)
            result!(tx.savepoint(|tx| {
                result!(
                    tx.insert(simple)
                        .values([InsertSimple::new("level_2")])
                        .execute()
                )?;
                Ok(())
            }))?;

            Ok(())
        }))?;

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Nested savepoints test should succeed, got: {:?}",
        result.as_ref().err()
    );

    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 3);
    let names: Vec<String> = users.into_iter().map(|u| u.name).collect();
    assert!(names.contains(&"level_0".to_string()));
    assert!(names.contains(&"level_1".to_string()));
    assert!(names.contains(&"level_2".to_string()));
}

// Standalone rusqlite-only stress test for deeply nested savepoints.
// Tests 50 levels of recursive savepoint nesting via our Transaction::savepoint() method.
// Uses tx.inner() for raw SQL inserts to avoid complex type-state builder types in recursion.
#[cfg(feature = "rusqlite")]
mod test_deep_savepoint_nesting_rusqlite {
    use crate::common::schema::sqlite::{SelectSimple, SimpleSchema};
    use drizzle::sqlite::connection::SQLiteTransactionType;

    fn nest<S>(
        tx: &drizzle::sqlite::rusqlite::Transaction<'_, S>,
        depth: usize,
        max: usize,
    ) -> drizzle_core::error::Result<()> {
        if depth >= max {
            return Ok(());
        }
        tx.savepoint(|tx| {
            let name = format!("depth_{}", depth);
            tx.inner().execute(
                "INSERT INTO simple (name) VALUES (?1)",
                rusqlite::params![&name],
            )?;
            nest(tx, depth + 1, max)
        })
    }

    #[test]
    fn run() -> Result<(), drizzle::error::DrizzleError> {
        use crate::common::helpers::rusqlite_setup;

        let (mut db, schema) = rusqlite_setup::setup_db::<SimpleSchema>();
        let SimpleSchema { simple } = schema;

        const MAX_DEPTH: usize = 50;

        let result = db.transaction(SQLiteTransactionType::Deferred, |tx| nest(tx, 0, MAX_DEPTH));

        assert!(
            result.is_ok(),
            "50-level nested savepoint failed: {:?}",
            result.err()
        );

        let users: Vec<SelectSimple> = db.select(()).from(simple).all()?;
        assert_eq!(
            users.len(),
            MAX_DEPTH,
            "Expected {} rows, got {}",
            MAX_DEPTH,
            users.len()
        );

        // Verify each depth level inserted exactly one row
        for i in 0..MAX_DEPTH {
            let expected = format!("depth_{}", i);
            assert!(
                users.iter().any(|u| u.name == expected),
                "Missing row for depth {}",
                i
            );
        }

        Ok(())
    }
}

// Test deep nesting with partial rollback at a specific depth.
// Ensures that rolling back an inner savepoint doesn't affect outer levels,
// and that the transaction can continue inserting after a rollback.
#[cfg(feature = "rusqlite")]
mod test_deep_savepoint_partial_rollback_rusqlite {
    use crate::common::schema::sqlite::{SelectSimple, SimpleSchema};
    use drizzle::error::DrizzleError;
    use drizzle::sqlite::connection::SQLiteTransactionType;

    fn nest_with_rollback_at<S>(
        tx: &drizzle::sqlite::rusqlite::Transaction<'_, S>,
        depth: usize,
        max: usize,
        rollback_at: usize,
    ) -> drizzle_core::error::Result<()> {
        if depth >= max {
            return Ok(());
        }
        let sp_result: drizzle_core::error::Result<()> = tx.savepoint(|tx| {
            let name = format!("depth_{}", depth);
            tx.inner().execute(
                "INSERT INTO simple (name) VALUES (?1)",
                rusqlite::params![&name],
            )?;

            if depth == rollback_at {
                return Err(DrizzleError::Other(
                    format!("rollback at depth {}", depth).into(),
                ));
            }

            nest_with_rollback_at(tx, depth + 1, max, rollback_at)
        });

        // If this savepoint rolled back, continue — insert a recovery row outside the savepoint
        if sp_result.is_err() && depth == rollback_at {
            let recovery = format!("recovered_{}", depth);
            tx.inner().execute(
                "INSERT INTO simple (name) VALUES (?1)",
                rusqlite::params![&recovery],
            )?;
        } else {
            sp_result?;
        }

        Ok(())
    }

    #[test]
    fn run() -> Result<(), drizzle::error::DrizzleError> {
        use crate::common::helpers::rusqlite_setup;

        let (mut db, schema) = rusqlite_setup::setup_db::<SimpleSchema>();
        let SimpleSchema { simple } = schema;

        const MAX_DEPTH: usize = 20;
        const ROLLBACK_AT: usize = 15;

        let result = db.transaction(SQLiteTransactionType::Deferred, |tx| {
            nest_with_rollback_at(tx, 0, MAX_DEPTH, ROLLBACK_AT)
        });

        assert!(
            result.is_ok(),
            "Partial rollback test failed: {:?}",
            result.err()
        );

        let users: Vec<SelectSimple> = db.select(()).from(simple).all()?;

        // Depths 0..15 committed successfully
        for i in 0..ROLLBACK_AT {
            let expected = format!("depth_{}", i);
            assert!(
                users.iter().any(|u| u.name == expected),
                "Missing row for depth {}",
                i
            );
        }

        // Depth 15 was rolled back, but a recovery row was inserted at the parent level
        assert!(
            !users
                .iter()
                .any(|u| u.name == format!("depth_{}", ROLLBACK_AT)),
            "depth_{} should have been rolled back",
            ROLLBACK_AT
        );
        assert!(
            users
                .iter()
                .any(|u| u.name == format!("recovered_{}", ROLLBACK_AT)),
            "recovered_{} should exist",
            ROLLBACK_AT
        );

        // Depths 16..20 never ran (they were inside the rolled-back savepoint)
        for i in (ROLLBACK_AT + 1)..MAX_DEPTH {
            assert!(
                !users.iter().any(|u| u.name == format!("depth_{}", i)),
                "depth_{} should not exist (after rollback point)",
                i
            );
        }

        Ok(())
    }
}

#[drizzle::test]
fn test_sequential_sibling_savepoints(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Insert before any savepoints
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("before_sp")])
                .execute()
        )?;

        // First savepoint — commits
        result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("sp1")])
                    .execute()
            )?;
            Ok(())
        }))?;

        // Second savepoint — commits
        result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("sp2")])
                    .execute()
            )?;
            Ok(())
        }))?;

        // Third savepoint — commits
        result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("sp3")])
                    .execute()
            )?;
            Ok(())
        }))?;

        // Insert after all savepoints
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("after_sp")])
                .execute()
        )?;

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Sequential sibling savepoints should succeed, got: {:?}",
        result.as_ref().err()
    );

    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 5);
    let names: Vec<String> = users.into_iter().map(|u| u.name).collect();
    assert!(names.contains(&"before_sp".to_string()));
    assert!(names.contains(&"sp1".to_string()));
    assert!(names.contains(&"sp2".to_string()));
    assert!(names.contains(&"sp3".to_string()));
    assert!(names.contains(&"after_sp".to_string()));
}

#[drizzle::test]
fn test_sequential_savepoints_mixed_outcomes(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // sp1: commits successfully
        result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("sp1_committed")])
                    .execute()
            )?;
            Ok(())
        }))?;

        // sp2: rolls back
        let sp2_result: Result<(), _> = result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("sp2_rolled_back")])
                    .execute()
            )?;
            Err(DrizzleError::Other("sp2 fail".to_string().into()))
        }));
        assert!(sp2_result.is_err());

        // sp3: commits successfully — the transaction recovered from sp2 failure
        result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("sp3_committed")])
                    .execute()
            )?;
            Ok(())
        }))?;

        // sp4: rolls back
        let sp4_result: Result<(), _> = result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("sp4_rolled_back")])
                    .execute()
            )?;
            Err(DrizzleError::Other("sp4 fail".to_string().into()))
        }));
        assert!(sp4_result.is_err());

        // sp5: commits — can still recover after multiple failures
        result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("sp5_committed")])
                    .execute()
            )?;
            Ok(())
        }))?;

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Mixed savepoint outcomes should recover and succeed, got: {:?}",
        result.as_ref().err()
    );

    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    let names: Vec<String> = users.iter().map(|u| u.name.clone()).collect();

    // Committed savepoints should be present
    assert!(names.contains(&"sp1_committed".to_string()));
    assert!(names.contains(&"sp3_committed".to_string()));
    assert!(names.contains(&"sp5_committed".to_string()));

    // Rolled-back savepoints should NOT be present
    assert!(!names.contains(&"sp2_rolled_back".to_string()));
    assert!(!names.contains(&"sp4_rolled_back".to_string()));

    assert_eq!(users.len(), 3, "exactly 3 committed savepoints");
}

#[drizzle::test]
fn test_savepoint_data_visibility(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Insert outside any savepoint
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("outer_data")])
                .execute()
        )?;

        // Inside a savepoint, verify we can see the outer data
        result!(tx.savepoint(|tx| {
            let rows: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
            assert_eq!(rows.len(), 1, "savepoint should see outer data");
            assert_eq!(rows[0].name, "outer_data");

            // Insert inside the savepoint
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("inner_data")])
                    .execute()
            )?;

            // Verify we can see both
            let rows: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
            assert_eq!(rows.len(), 2, "should see both outer and inner");
            Ok(())
        }))?;

        // After savepoint commits, verify outer can see savepoint data
        let rows: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
        assert_eq!(rows.len(), 2, "outer should see committed savepoint data");

        let names: Vec<String> = rows.into_iter().map(|u| u.name).collect();
        assert!(names.contains(&"outer_data".to_string()));
        assert!(names.contains(&"inner_data".to_string()));

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Savepoint data visibility test should succeed, got: {:?}",
        result.as_ref().err()
    );
}

#[drizzle::test]
fn test_savepoint_rolled_back_data_not_visible(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("outer_data")])
                .execute()
        )?;

        // Savepoint that inserts then rolls back
        let sp_result: Result<(), _> = result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("ghost_data")])
                    .execute()
            )?;

            // Verify data is visible inside the savepoint before rollback
            let rows: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
            assert_eq!(rows.len(), 2, "should see both before rollback");

            Err(DrizzleError::Other("rollback".to_string().into()))
        }));
        assert!(sp_result.is_err());

        // After rollback, ghost_data should be gone
        let rows: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
        assert_eq!(
            rows.len(),
            1,
            "only outer_data should remain after rollback"
        );
        assert_eq!(rows[0].name, "outer_data");

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Rolled-back savepoint data test should succeed, got: {:?}",
        result.as_ref().err()
    );
}

#[drizzle::test]
fn test_savepoint_update_and_delete_rollback(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Set up initial data
        result!(
            tx.insert(simple)
                .values([
                    InsertSimple::new("alice"),
                    InsertSimple::new("bob"),
                    InsertSimple::new("charlie"),
                ])
                .execute()
        )?;

        // Savepoint: update + delete, then rollback
        let sp_result: Result<(), _> = result!(tx.savepoint(|tx| {
            // Update alice → alicia
            result!(
                tx.update(simple)
                    .set(UpdateSimple::default().with_name("alicia"))
                    .r#where(eq(simple.name, "alice"))
                    .execute()
            )?;

            // Delete bob
            result!(tx.delete(simple).r#where(eq(simple.name, "bob")).execute())?;

            // Verify the changes took effect inside the savepoint
            let rows: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
            assert_eq!(rows.len(), 2);
            let names: Vec<String> = rows.into_iter().map(|u| u.name).collect();
            assert!(names.contains(&"alicia".to_string()));
            assert!(names.contains(&"charlie".to_string()));
            assert!(!names.contains(&"alice".to_string()));
            assert!(!names.contains(&"bob".to_string()));

            // Now rollback everything
            Err(DrizzleError::Other("undo changes".to_string().into()))
        }));
        assert!(sp_result.is_err());

        // After rollback, original data should be restored
        let rows: Vec<SelectSimple> = result!(tx.select(()).from(simple).all())?;
        assert_eq!(rows.len(), 3, "all 3 original rows should be back");
        let names: Vec<String> = rows.into_iter().map(|u| u.name).collect();
        assert!(
            names.contains(&"alice".to_string()),
            "alice should be back (update rolled back)"
        );
        assert!(
            names.contains(&"bob".to_string()),
            "bob should be back (delete rolled back)"
        );
        assert!(names.contains(&"charlie".to_string()));

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Savepoint update/delete rollback test should succeed, got: {:?}",
        result.as_ref().err()
    );

    // Verify final committed state matches
    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 3);
}

#[drizzle::test]
fn test_nested_savepoint_inner_rollback(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("level_0")])
                .execute()
        )?;

        // First level savepoint
        result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("level_1")])
                    .execute()
            )?;

            // Second level savepoint that rolls back
            let inner_result: Result<(), _> = result!(tx.savepoint(|tx| {
                result!(
                    tx.insert(simple)
                        .values([InsertSimple::new("level_2_rollback")])
                        .execute()
                )?;
                Err(DrizzleError::Other("rollback level 2".to_string().into()))
            }));

            assert!(inner_result.is_err());

            // level_1 should still work after inner rollback
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("after_inner_rollback")])
                    .execute()
            )?;

            Ok(())
        }))?;

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Nested savepoint inner rollback test should succeed, got: {:?}",
        result.as_ref().err()
    );

    let users: Vec<SelectSimple> = db.select(()).from(simple).all();
    assert_eq!(users.len(), 3);
    let names: Vec<String> = users.into_iter().map(|u| u.name).collect();
    assert!(names.contains(&"level_0".to_string()));
    assert!(names.contains(&"level_1".to_string()));
    assert!(names.contains(&"after_inner_rollback".to_string()));
    assert!(!names.contains(&"level_2_rollback".to_string()));
}

// --- Prepared statement + transaction tests ---

#[drizzle::test]
fn test_prepared_outside_transaction(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    // Insert test data

    db.insert(simple)
        .values([InsertSimple::new("Alice"), InsertSimple::new("Bob")])
        .execute();

    // Create an owned prepared statement OUTSIDE the transaction
    let name = simple.name.placeholder("name");
    let prepared = db
        .select(())
        .from(simple)
        .r#where(eq(simple.name, name))
        .prepare()
        .into_owned();

    // Use it INSIDE the transaction
    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        let alice: Vec<SelectSimple> = result!(prepared.all(tx.inner(), [name.bind("Alice")]))?;
        assert_eq!(alice.len(), 1);
        assert_eq!(alice[0].name, "Alice");

        // Reuse with different params in the same transaction
        let bob: Vec<SelectSimple> = result!(prepared.all(tx.inner(), [name.bind("Bob")]))?;
        assert_eq!(bob.len(), 1);
        assert_eq!(bob[0].name, "Bob");

        // No match
        let nobody: Vec<SelectSimple> = result!(prepared.all(tx.inner(), [name.bind("Nobody")]))?;
        assert_eq!(nobody.len(), 0);

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Prepared statement outside transaction should succeed, got: {:?}",
        result.as_ref().err()
    );
}

#[drizzle::test]
fn test_prepared_in_savepoint(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values([InsertSimple::new("Alice")])
        .execute();

    // Prepared statement created outside everything
    let name = simple.name.placeholder("name");
    let prepared = db
        .select(())
        .from(simple)
        .r#where(eq(simple.name, name))
        .prepare()
        .into_owned();

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Insert inside transaction
        result!(
            tx.insert(simple)
                .values([InsertSimple::new("Bob")])
                .execute()
        )?;

        // Use prepared statement inside a savepoint
        result!(tx.savepoint(|tx| {
            let both: Vec<SelectSimple> = result!(prepared.all(tx.inner(), [name.bind("Alice")]))?;
            assert_eq!(both.len(), 1);

            let bob: Vec<SelectSimple> = result!(prepared.all(tx.inner(), [name.bind("Bob")]))?;
            assert_eq!(bob.len(), 1);

            Ok(())
        }))?;

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Prepared statement in savepoint should succeed, got: {:?}",
        result.as_ref().err()
    );
}

#[drizzle::test]
fn test_prepared_survives_savepoint_rollback(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    db.insert(simple)
        .values([InsertSimple::new("Alice")])
        .execute();

    let prepared = db.select(()).from(simple).prepare().into_owned();

    let result = result!(db.transaction(SQLiteTransactionType::Deferred, |tx| {
        // Savepoint that inserts then rolls back
        let sp_result: Result<(), _> = result!(tx.savepoint(|tx| {
            result!(
                tx.insert(simple)
                    .values([InsertSimple::new("Ghost")])
                    .execute()
            )?;

            // Prepared statement sees both rows inside the savepoint
            let rows: Vec<SelectSimple> = result!(prepared.all(tx.inner(), []))?;
            assert_eq!(rows.len(), 2);

            Err(DrizzleError::Other("rollback".into()))
        }));
        assert!(sp_result.is_err());

        // After rollback, prepared statement sees only Alice
        let rows: Vec<SelectSimple> = result!(prepared.all(tx.inner(), []))?;
        assert_eq!(rows.len(), 1);
        assert_eq!(rows[0].name, "Alice");

        Ok(())
    }));

    assert!(
        result.is_ok(),
        "Prepared statement should survive savepoint rollback, got: {:?}",
        result.as_ref().err()
    );
}

// Static assertion: OwnedPreparedStatement is Send + Sync
#[cfg(feature = "rusqlite")]
#[test]
fn test_sqlite_owned_prepared_send_sync() {
    fn assert_send_sync<T: Send + Sync>() {}
    assert_send_sync::<drizzle_sqlite::builder::prepared::OwnedPreparedStatement>();
}