//! PostgreSQL Integration Tests for TideORM
//!
//! These tests require a running PostgreSQL instance with:
//! - Host: localhost
//! - Port: 5432
//! - User: postgres
//! - Password: postgres
//! - Database: test_tide_orm
//!
//! Run with: cargo test --test postgres_integration_tests

use std::sync::{LazyLock, Mutex};
use tideorm::prelude::*;
use tideorm::{Database, TideConfig};

#[path = "support/postgres_test_config.rs"]
mod test_config;
use test_config::test_database_url;

// =============================================================================
// TEST MODELS
// =============================================================================

#[derive(Model, PartialEq)]
#[tideorm(table = "test_users")]
pub struct TestUser {
    #[tideorm(primary_key, auto_increment)]
    pub id: i64,
    pub email: String,
    pub name: String,
    pub age: i32,
    pub active: bool,
}

static CALLBACK_EVENTS: LazyLock<Mutex<Vec<&'static str>>> =
    LazyLock::new(|| Mutex::new(Vec::new()));

#[derive(Model, PartialEq)]
#[tideorm(table = "callback_users")]
pub struct CallbackUser {
    #[tideorm(primary_key, auto_increment)]
    pub id: i64,
    pub email: String,
    pub name: String,
}

impl Callbacks for CallbackUser {
    fn before_validation(&mut self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("before_validation");
        Ok(())
    }

    fn after_validation(&self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("after_validation");
        Ok(())
    }

    fn before_save(&mut self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("before_save");
        self.email = self.email.to_lowercase();
        Ok(())
    }

    fn after_save(&self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("after_save");
        Ok(())
    }

    fn before_create(&mut self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("before_create");
        Ok(())
    }

    fn after_create(&self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("after_create");
        Ok(())
    }

    fn before_update(&mut self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("before_update");
        Ok(())
    }

    fn after_update(&self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("after_update");
        Ok(())
    }

    fn before_delete(&self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("before_delete");
        Ok(())
    }

    fn after_delete(&self) -> tideorm::Result<()> {
        CALLBACK_EVENTS.lock().unwrap().push("after_delete");
        Ok(())
    }
}

#[tideorm::model(table = "test_posts")]
pub struct TestPost {
    #[tideorm(primary_key, auto_increment)]
    pub id: i64,
    pub user_id: i64,
    pub title: String,
    pub content: String,
    pub published: bool,
}

#[tideorm::model(table = "test_soft_deletes", soft_delete)]
pub struct TestSoftDelete {
    #[tideorm(primary_key, auto_increment)]
    pub id: i64,
    pub name: String,
    pub deleted_at: Option<chrono::DateTime<chrono::Utc>>,
}

#[derive(Model, PartialEq)]
#[tideorm(table = "timestamp_users")]
pub struct TimestampUser {
    #[tideorm(primary_key, auto_increment)]
    pub id: i64,
    pub email: String,
    pub name: String,
    pub login_count: i32,
    pub created_at: chrono::DateTime<chrono::Utc>,
    pub updated_at: chrono::DateTime<chrono::Utc>,
}

// =============================================================================
// SINGLE INTEGRATION TEST - Runs all scenarios sequentially
// =============================================================================

#[tokio::test]
async fn postgres_integration_tests() {
    // =========================================================================
    // SETUP
    // =========================================================================
    println!(" Starting PostgreSQL Integration Tests...\n");

    TideConfig::init()
        .database(test_database_url())
        .max_connections(10)
        .min_connections(2)
        .connect()
        .await
        .expect("Failed to connect to database");

    // Create tables
    let _ = Database::execute("DROP TABLE IF EXISTS test_soft_deletes CASCADE").await;
    let _ = Database::execute("DROP TABLE IF EXISTS test_posts CASCADE").await;
    let _ = Database::execute("DROP TABLE IF EXISTS test_raw_json_types CASCADE").await;
    let _ = Database::execute("DROP TABLE IF EXISTS test_users CASCADE").await;
    let _ = Database::execute("DROP TABLE IF EXISTS timestamp_users CASCADE").await;
    let _ = Database::execute("DROP TABLE IF EXISTS callback_users CASCADE").await;

    Database::execute(
        r#"
        CREATE TABLE test_users (
            id BIGSERIAL PRIMARY KEY,
            email VARCHAR(255) NOT NULL,
            name VARCHAR(255) NOT NULL,
            age INTEGER NOT NULL,
            active BOOLEAN NOT NULL DEFAULT true
        )
    "#,
    )
    .await
    .expect("Failed to create test_users table");

    Database::execute(
        r#"
        CREATE TABLE test_posts (
            id BIGSERIAL PRIMARY KEY,
            user_id BIGINT NOT NULL,
            title VARCHAR(255) NOT NULL,
            content TEXT NOT NULL,
            published BOOLEAN NOT NULL DEFAULT false
        )
    "#,
    )
    .await
    .expect("Failed to create test_posts table");

    Database::execute(
        r#"
        CREATE TABLE test_soft_deletes (
            id BIGSERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            deleted_at TIMESTAMPTZ
        )
    "#,
    )
    .await
    .expect("Failed to create test_soft_deletes table");

    Database::execute(
        r#"
        CREATE TABLE timestamp_users (
            id BIGSERIAL PRIMARY KEY,
            email VARCHAR(255) NOT NULL UNIQUE,
            name VARCHAR(255) NOT NULL,
            login_count INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMPTZ NOT NULL,
            updated_at TIMESTAMPTZ NOT NULL
        )
    "#,
    )
    .await
    .expect("Failed to create timestamp_users table");

    Database::execute(
        r#"
        CREATE TABLE test_raw_json_types (
            id BIGSERIAL PRIMARY KEY,
            enabled BOOLEAN NOT NULL,
            payload JSONB NOT NULL,
            amount NUMERIC(10,2) NOT NULL,
            created_at TIMESTAMPTZ NOT NULL,
            uuid_value UUID NOT NULL
        )
    "#,
    )
    .await
    .expect("Failed to create test_raw_json_types table");

    Database::execute(
        r#"
        CREATE TABLE callback_users (
            id BIGSERIAL PRIMARY KEY,
            email VARCHAR(255) NOT NULL,
            name VARCHAR(255) NOT NULL
        )
    "#,
    )
    .await
    .expect("Failed to create callback_users table");

    println!(" Database setup complete\n");

    // =========================================================================
    // CONNECTION TESTS
    // =========================================================================
    println!("📡 Testing: Database Connection");
    {
        let db = tideorm::require_db().unwrap();
        assert!(db.ping().await.is_ok(), "Database ping failed");
        println!("   ✓ Ping successful");

        let result = Database::execute("SELECT 1").await;
        assert!(result.is_ok(), "Raw SQL execution failed");
        println!("   ✓ Raw SQL execution works");
    }
    println!();

    // =========================================================================
    // RAW JSON TESTS
    // =========================================================================
    println!("🧪 Testing: Raw JSON Typed Decoding");
    {
        let probe_uuid = uuid::Uuid::parse_str("6d8f4a4e-5f60-4c5f-b8fb-7ddc7310df2a")
            .expect("UUID literal should parse");
        let db = tideorm::require_db().expect("database should be available");

        db.__execute_with_params(
            "INSERT INTO test_raw_json_types (enabled, payload, amount, created_at, uuid_value) VALUES ($1, $2, $3::numeric, $4::timestamptz, $5::uuid)",
            vec![
                tideorm::internal::Value::Bool(Some(true)),
                tideorm::internal::Value::Json(Some(Box::new(serde_json::json!({
                    "kind": "probe",
                    "count": 2
                })))),
                tideorm::internal::Value::String(Some("12.34".to_string())),
                tideorm::internal::Value::String(Some("2026-03-21T10:11:12+00:00".to_string())),
                tideorm::internal::Value::String(Some(probe_uuid.to_string())),
            ],
        )
        .await
        .expect("typed raw-json probe insert should succeed");

        let rows = db
            .__raw_json_with_params(
                "SELECT enabled, payload, amount, created_at, uuid_value FROM test_raw_json_types ORDER BY id ASC",
                vec![],
            )
            .await
            .expect("typed raw-json probe query should succeed");

        assert_eq!(
            rows,
            vec![serde_json::json!({
                "enabled": true,
                "payload": {
                    "kind": "probe",
                    "count": 2
                },
                "amount": serde_json::to_value(
                    rust_decimal::Decimal::from_str_exact("12.34")
                        .expect("decimal literal should parse")
                ).expect("decimal should serialize to JSON"),
                "created_at": serde_json::to_value(
                    chrono::DateTime::parse_from_rfc3339("2026-03-21T10:11:12+00:00")
                        .expect("timestamp literal should parse")
                ).expect("timestamp should serialize to JSON"),
                "uuid_value": serde_json::to_value(probe_uuid)
                    .expect("uuid should serialize to JSON"),
            })]
        );
        println!(
            "   ✓ raw_json preserves PostgreSQL boolean, JSONB, numeric, timestamptz, and UUID types"
        );
    }
    println!();

    // =========================================================================
    // CRUD TESTS
    // =========================================================================
    println!("📝 Testing: CRUD Operations");

    // Create and Find
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        let user = TestUser {
            id: 0,
            email: "test@example.com".to_string(),
            name: "Test User".to_string(),
            age: 25,
            active: true,
        };

        let saved_user = user.save().await.expect("Failed to save user");
        assert!(saved_user.id > 0, "User should have an auto-generated ID");
        assert_eq!(saved_user.email, "test@example.com");

        let found = TestUser::find(saved_user.id)
            .await
            .expect("Failed to find user");
        assert!(found.is_some(), "User should be found");
        let found_user = found.unwrap();
        assert_eq!(found_user.email, "test@example.com");
        assert_eq!(found_user.name, "Test User");
        println!("   ✓ Create and Find");
    }

    // Update
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        let user = TestUser {
            id: 0,
            email: "update@example.com".to_string(),
            name: "Original Name".to_string(),
            age: 30,
            active: true,
        };
        let mut saved_user = user.save().await.expect("Failed to save user");

        saved_user.name = "Updated Name".to_string();
        saved_user.age = 31;
        let updated_user = saved_user.update().await.expect("Failed to update user");

        assert_eq!(updated_user.name, "Updated Name");
        assert_eq!(updated_user.age, 31);

        let reloaded = TestUser::find(updated_user.id)
            .await
            .expect("Failed to reload")
            .unwrap();
        assert_eq!(reloaded.name, "Updated Name");
        println!("   ✓ Update");
    }

    // Delete
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        let user = TestUser {
            id: 0,
            email: "delete@example.com".to_string(),
            name: "To Delete".to_string(),
            age: 25,
            active: true,
        };
        let saved_user = user.save().await.expect("Failed to save user");
        let user_id = saved_user.id;

        let deleted_count = saved_user.delete().await.expect("Failed to delete");
        assert_eq!(deleted_count, 1);

        let found = TestUser::find(user_id).await.expect("Find failed");
        assert!(found.is_none(), "User should be deleted");
        println!("   ✓ Delete");
    }

    // Destroy by ID
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        let user = TestUser {
            id: 0,
            email: "destroy@example.com".to_string(),
            name: "To Destroy".to_string(),
            age: 25,
            active: true,
        };
        let saved_user = user.save().await.expect("Failed to save user");

        let deleted = TestUser::destroy(saved_user.id)
            .await
            .expect("Failed to destroy");
        assert_eq!(deleted, 1);

        let found = TestUser::find(saved_user.id).await.expect("Find failed");
        assert!(found.is_none());
        println!("   ✓ Destroy by ID");
    }
    println!();

    // =========================================================================
    // QUERY BUILDER TESTS
    // =========================================================================
    println!("🔍 Testing: Query Builder");

    // Where Equal
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=5 {
            let user = TestUser {
                id: 0,
                email: format!("user{i}@example.com"),
                name: format!("User {i}"),
                age: 20 + i,
                active: i % 2 == 0,
            };
            user.save().await.expect("Failed to save");
        }

        let active_users = TestUser::query()
            .where_eq("active", true)
            .get()
            .await
            .expect("Query failed");

        assert_eq!(active_users.len(), 2, "Should have 2 active users");
        for user in &active_users {
            assert!(user.active, "All users should be active");
        }
        println!("   ✓ where_eq");
    }

    // Where Greater Than / Less Than
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=5 {
            let user = TestUser {
                id: 0,
                email: format!("age{i}@example.com"),
                name: format!("User {i}"),
                age: 20 + (i * 5), // Ages: 25, 30, 35, 40, 45
                active: true,
            };
            user.save().await.expect("Failed to save");
        }

        let older_users = TestUser::query()
            .where_gt("age", 30)
            .get()
            .await
            .expect("Query failed");
        assert_eq!(older_users.len(), 3, "Should have 3 users with age > 30");

        let younger_users = TestUser::query()
            .where_lt("age", 35)
            .get()
            .await
            .expect("Query failed");
        assert_eq!(younger_users.len(), 2, "Should have 2 users with age < 35");
        println!("   ✓ where_gt / where_lt");
    }

    // Where Like
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        TestUser {
            id: 0,
            email: "john@gmail.com".into(),
            name: "John Doe".into(),
            age: 25,
            active: true,
        }
        .save()
        .await
        .ok();
        TestUser {
            id: 0,
            email: "jane@gmail.com".into(),
            name: "Jane Doe".into(),
            age: 30,
            active: true,
        }
        .save()
        .await
        .ok();
        TestUser {
            id: 0,
            email: "bob@yahoo.com".into(),
            name: "Bob Smith".into(),
            age: 35,
            active: true,
        }
        .save()
        .await
        .ok();

        let gmail_users = TestUser::query()
            .where_like("email", "%gmail%")
            .get()
            .await
            .expect("Query failed");
        assert_eq!(gmail_users.len(), 2, "Should have 2 gmail users");

        let doe_users = TestUser::query()
            .where_like("name", "%Doe%")
            .get()
            .await
            .expect("Query failed");
        assert_eq!(doe_users.len(), 2, "Should have 2 Doe users");
        println!("   ✓ where_like");
    }

    // Where In
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=5 {
            let user = TestUser {
                id: 0,
                email: format!("in_test{i}@example.com"),
                name: format!("User {i}"),
                age: 20 + i,
                active: true,
            };
            user.save().await.expect("Failed to save");
        }

        let users = TestUser::query()
            .where_in("age", vec![21, 23, 25])
            .get()
            .await
            .expect("Query failed");
        assert_eq!(users.len(), 3, "Should have 3 users with ages 21, 23, 25");
        println!("   ✓ where_in");
    }

    // Order and Limit
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=10 {
            let user = TestUser {
                id: 0,
                email: format!("order{i}@example.com"),
                name: format!("User {i:02}"),
                age: 20 + i,
                active: true,
            };
            user.save().await.expect("Failed to save");
        }

        let users = TestUser::query()
            .order_by("age", Order::Desc)
            .limit(3)
            .get()
            .await
            .expect("Query failed");

        assert_eq!(users.len(), 3, "Should have 3 users");
        assert_eq!(users[0].age, 30, "First user should be oldest");
        assert_eq!(users[1].age, 29);
        assert_eq!(users[2].age, 28);
        println!("   ✓ order_by / limit");
    }

    // Pagination
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=20 {
            let user = TestUser {
                id: 0,
                email: format!("page{i}@example.com"),
                name: format!("User {i:02}"),
                age: 20 + i,
                active: true,
            };
            user.save().await.expect("Failed to save");
        }

        let page2 = TestUser::query()
            .order_by("age", Order::Asc)
            .page(2, 5)
            .get()
            .await
            .expect("Query failed");

        assert_eq!(page2.len(), 5, "Should have 5 users on page 2");
        assert_eq!(page2[0].age, 26, "First user on page 2 should have age 26");
        println!("   ✓ pagination");
    }

    // Count
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=10 {
            let user = TestUser {
                id: 0,
                email: format!("count{i}@example.com"),
                name: format!("User {i}"),
                age: 20 + i,
                active: i <= 6,
            };
            user.save().await.expect("Failed to save");
        }

        let total = TestUser::count().await.expect("Count failed");
        assert_eq!(total, 10, "Should have 10 total users");

        let active_count = TestUser::query()
            .where_eq("active", true)
            .count()
            .await
            .expect("Count failed");
        assert_eq!(active_count, 6, "Should have 6 active users");
        println!("   ✓ count");
    }

    // First
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=5 {
            let user = TestUser {
                id: 0,
                email: format!("first{i}@example.com"),
                name: format!("User {i}"),
                age: 20 + i,
                active: true,
            };
            user.save().await.expect("Failed to save");
        }

        let first = TestUser::query()
            .where_gt("age", 22)
            .order_by("age", Order::Asc)
            .first()
            .await
            .expect("Query failed");

        assert!(first.is_some());
        assert_eq!(
            first.unwrap().age,
            23,
            "First matching user should have age 23"
        );
        println!("   ✓ first");
    }

    // Bulk Delete
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=10 {
            let user = TestUser {
                id: 0,
                email: format!("bulk{i}@example.com"),
                name: format!("User {i}"),
                age: 20 + i,
                active: i <= 5,
            };
            user.save().await.expect("Failed to save");
        }

        let deleted = TestUser::query()
            .where_eq("active", false)
            .delete()
            .await
            .expect("Delete failed");
        assert_eq!(deleted, 5, "Should have deleted 5 inactive users");

        let remaining = TestUser::count().await.expect("Count failed");
        assert_eq!(remaining, 5, "Should have 5 remaining users");
        println!("   ✓ bulk delete");
    }
    println!();

    // =========================================================================
    // SOFT DELETE TESTS
    // =========================================================================
    println!("🗑️  Testing: Soft Delete");
    {
        let _ =
            Database::execute("TRUNCATE TABLE test_soft_deletes RESTART IDENTITY CASCADE").await;

        assert!(
            TestSoftDelete::soft_delete_enabled(),
            "soft_delete should be enabled"
        );

        let record1 = TestSoftDelete {
            id: 0,
            name: "Record 1".into(),
            deleted_at: None,
        }
        .save()
        .await
        .expect("Failed to save");
        let record2 = TestSoftDelete {
            id: 0,
            name: "Record 2".into(),
            deleted_at: None,
        }
        .save()
        .await
        .expect("Failed to save");
        let _record3 = TestSoftDelete {
            id: 0,
            name: "Record 3".into(),
            deleted_at: None,
        }
        .save()
        .await
        .expect("Failed to save");

        // Soft delete
        let deleted_record = record1.soft_delete().await.expect("Failed to soft delete");
        assert!(
            deleted_record.deleted_at.is_some(),
            "deleted_at should be set"
        );
        println!("   ✓ soft_delete sets deleted_at");

        // Query without trashed
        let active = TestSoftDelete::query().get().await.expect("Query failed");
        assert_eq!(active.len(), 2, "Should have 2 active records");
        println!("   ✓ default query excludes soft deleted");

        // Query with trashed
        let all = TestSoftDelete::query()
            .with_trashed()
            .get()
            .await
            .expect("Query failed");
        assert_eq!(all.len(), 3, "Should have 3 total records");
        println!("   ✓ with_trashed includes all");

        // Query only trashed
        let trashed = TestSoftDelete::query()
            .only_trashed()
            .get()
            .await
            .expect("Query failed");
        assert_eq!(trashed.len(), 1, "Should have 1 trashed record");
        assert_eq!(trashed[0].name, "Record 1");
        println!("   ✓ only_trashed works");

        // Restore
        let restored = deleted_record.restore().await.expect("Failed to restore");
        assert!(
            restored.deleted_at.is_none(),
            "deleted_at should be cleared"
        );

        let active_after_restore = TestSoftDelete::query().get().await.expect("Query failed");
        assert_eq!(
            active_after_restore.len(),
            3,
            "Should have 3 active records after restore"
        );
        println!("   ✓ restore works");

        // Force delete
        record2
            .force_delete()
            .await
            .expect("Failed to force delete");

        let final_count = TestSoftDelete::query()
            .with_trashed()
            .count()
            .await
            .expect("Count failed");
        assert_eq!(final_count, 2, "Should have 2 records after force delete");
        println!("   ✓ force_delete works");
    }
    println!();

    // =========================================================================
    // TRANSACTION TESTS
    // =========================================================================
    println!("💳 Testing: Transactions");
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        // Transaction commit
        let result = TestUser::transaction(|_tx| {
            Box::pin(async move {
                let user = TestUser {
                    id: 0,
                    email: "tx_commit@example.com".to_string(),
                    name: "Transaction User".to_string(),
                    age: 25,
                    active: true,
                };
                let saved = user.save().await?;
                Ok(saved.id)
            })
        })
        .await;

        assert!(result.is_ok(), "Transaction should succeed");

        let found = TestUser::query()
            .where_eq("email", "tx_commit@example.com")
            .first()
            .await
            .expect("Query failed");
        assert!(found.is_some(), "User should exist after commit");
        println!("   ✓ transaction commit");

        // Transaction rollback
        let db = tideorm::require_db().unwrap();
        let result: tideorm::Result<i64> = TestUser::transaction(|_tx| {
            Box::pin(async move { Err(tideorm::Error::query("Intentional rollback")) })
        })
        .await;
        assert!(result.is_err(), "Transaction should fail");

        let result2: tideorm::Result<()> = db.transaction(|tx| Box::pin(async move {
            use sea_orm::ConnectionTrait;
            tx.__internal_transaction()
                .execute_unprepared("INSERT INTO test_users (email, name, age, active) VALUES ('tx_test@example.com', 'TX User', 30, true)")
                .await
                .map_err(|e| tideorm::Error::query(e.to_string()))?;
            Err(tideorm::Error::query("Intentional rollback"))
        })).await;
        assert!(result2.is_err(), "Transaction should fail");

        let found = TestUser::query()
            .where_eq("email", "tx_test@example.com")
            .first()
            .await
            .expect("Query failed");
        assert!(found.is_none(), "User should not exist after rollback");
        println!("   ✓ transaction rollback");

        let baseline = TestUser {
            id: 0,
            email: "tx_baseline@example.com".to_string(),
            name: "Baseline User".to_string(),
            age: 41,
            active: true,
        }
        .save()
        .await
        .expect("Failed to save baseline transaction user");

        let save_result: tideorm::Result<()> = TestUser::transaction(|_tx| {
            Box::pin(async move {
                TestUser {
                    id: 0,
                    email: "tx_model_save@example.com".to_string(),
                    name: "Transaction Save".to_string(),
                    age: 22,
                    active: true,
                }
                .save()
                .await?;

                Err(tideorm::Error::query("Intentional rollback after save"))
            })
        })
        .await;
        assert!(save_result.is_err(), "save transaction should roll back");

        let rolled_back_save = TestUser::query()
            .where_eq("email", "tx_model_save@example.com")
            .first()
            .await
            .expect("Failed to query rolled back save");
        assert!(
            rolled_back_save.is_none(),
            "saved model row should not persist after rollback"
        );
        println!("   ✓ transaction rollback via model save");

        let update_result: tideorm::Result<()> = TestUser::transaction(|_tx| {
            let baseline = TestUser {
                id: baseline.id,
                email: baseline.email.clone(),
                name: baseline.name.clone(),
                age: baseline.age,
                active: baseline.active,
            };
            Box::pin(async move {
                TestUser {
                    name: "Updated In Transaction".to_string(),
                    age: 99,
                    ..baseline
                }
                .update()
                .await?;

                Err(tideorm::Error::query("Intentional rollback after update"))
            })
        })
        .await;
        assert!(
            update_result.is_err(),
            "update transaction should roll back"
        );

        let unchanged = TestUser::find(baseline.id)
            .await
            .expect("Failed to reload baseline user")
            .expect("Baseline user should still exist");
        assert_eq!(unchanged.name, "Baseline User");
        assert_eq!(unchanged.age, 41);
        println!("   ✓ transaction rollback via model update");

        let delete_result: tideorm::Result<()> = TestUser::transaction(|_tx| {
            let baseline = TestUser {
                id: unchanged.id,
                email: unchanged.email.clone(),
                name: unchanged.name.clone(),
                age: unchanged.age,
                active: unchanged.active,
            };
            Box::pin(async move {
                baseline.delete().await?;
                Err(tideorm::Error::query("Intentional rollback after delete"))
            })
        })
        .await;
        assert!(
            delete_result.is_err(),
            "delete transaction should roll back"
        );

        let still_present = TestUser::find(baseline.id)
            .await
            .expect("Failed to reload baseline user after delete rollback")
            .expect("Baseline user should remain after delete rollback");
        assert_eq!(still_present.email, "tx_baseline@example.com");
        println!("   ✓ transaction rollback via model delete");
    }
    println!();

    // =========================================================================
    // RAW SQL TESTS
    // =========================================================================
    println!("📜 Testing: Raw SQL");
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=3 {
            let user = TestUser {
                id: 0,
                email: format!("raw{i}@example.com"),
                name: format!("Raw User {i}"),
                age: 20 + i,
                active: true,
            };
            user.save().await.expect("Failed to save");
        }

        let users: Vec<TestUser> = Database::raw_with_params::<TestUser>(
            "SELECT * FROM test_users WHERE age > $1 ORDER BY age",
            vec![21.into()],
        )
        .await
        .expect("Raw query failed");
        assert_eq!(users.len(), 2, "Should have 2 users with age > 21");
        println!("   ✓ raw_with_params query");
    }

    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=5 {
            let user = TestUser {
                id: 0,
                email: format!("exec{i}@example.com"),
                name: format!("Exec User {i}"),
                age: 20 + i,
                active: true,
            };
            user.save().await.expect("Failed to save");
        }

        let affected = Database::execute_with_params(
            "UPDATE test_users SET active = false WHERE age > $1",
            vec![23.into()],
        )
        .await
        .expect("Execute failed");
        assert_eq!(affected, 2, "Should have updated 2 users");

        let inactive = TestUser::query()
            .where_eq("active", false)
            .count()
            .await
            .expect("Count failed");
        assert_eq!(inactive, 2);
        println!("   ✓ execute_with_params");
    }
    println!();

    // =========================================================================
    // BATCH OPERATIONS TESTS
    // =========================================================================
    println!(" Testing: Batch Operations");
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        let users = vec![
            TestUser {
                id: 0,
                email: "batch1@example.com".into(),
                name: "Batch 1".into(),
                age: 25,
                active: true,
            },
            TestUser {
                id: 0,
                email: "batch2@example.com".into(),
                name: "Batch 2".into(),
                age: 30,
                active: true,
            },
            TestUser {
                id: 0,
                email: "batch3@example.com".into(),
                name: "Batch 3".into(),
                age: 35,
                active: false,
            },
        ];

        let inserted = TestUser::insert_all(users)
            .await
            .expect("Insert all failed");

        assert_eq!(inserted.len(), 3, "Should have inserted 3 users");
        for user in &inserted {
            assert!(user.id > 0, "Each user should have an ID");
        }

        let count = TestUser::count().await.expect("Count failed");
        assert_eq!(count, 3, "Should have 3 users in database");
        println!("   ✓ insert_all");
    }
    println!();

    // =========================================================================
    // UPSERT / ON-CONFLICT TESTS
    // =========================================================================
    println!("♻️  Testing: Upsert / On-Conflict");
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        let user = TestUser {
            id: 1,
            email: "upsert@example.com".into(),
            name: "Initial Upsert".into(),
            age: 28,
            active: true,
        };
        let inserted = TestUser::insert_or_update(user, vec!["id"])
            .await
            .expect("insert_or_update should insert when missing");
        assert_eq!(
            inserted.id, 1,
            "Insert should respect primary key conflict target"
        );
        assert_eq!(inserted.name, "Initial Upsert");

        let user_update = TestUser {
            id: 1,
            email: "upsert@example.com".into(),
            name: "Updated Upsert".into(),
            age: 29,
            active: false,
        };
        let updated = TestUser::insert_or_update(user_update, vec!["id"])
            .await
            .expect("insert_or_update should update on conflict");
        assert_eq!(updated.id, 1, "Conflict should keep same primary key");
        assert_eq!(updated.name, "Updated Upsert");
        assert_eq!(updated.age, 29);
        assert!(
            !updated.active,
            "Active flag should update when included in update set"
        );

        let selective_model = TestUser {
            id: 1,
            email: "upsert@example.com".into(),
            name: "Selective Update".into(),
            age: 31,
            active: true,
        };
        let selective = TestUser::on_conflict(vec!["id"])
            .update_columns(vec!["name", "age"])
            .insert(selective_model)
            .await
            .expect("on_conflict builder should update chosen columns");
        assert_eq!(selective.id, 1);
        assert_eq!(selective.name, "Selective Update");
        assert_eq!(selective.age, 31);
        assert!(
            !selective.active,
            "Active should remain from previous update when column excluded"
        );

        let reloaded = TestUser::find(1)
            .await
            .expect("Reload failed")
            .expect("User should exist after upsert");
        assert_eq!(reloaded.name, "Selective Update");
        assert_eq!(reloaded.age, 31);
        assert!(
            !reloaded.active,
            "Active should be preserved when not updated"
        );

        let quoted_payload = "Robert'); DROP TABLE test_users; --";
        let injected_like = TestUser {
            id: 1,
            email: "upsert@example.com".into(),
            name: quoted_payload.into(),
            age: 32,
            active: true,
        };
        let quoted = TestUser::insert_or_update(injected_like, vec!["id"])
            .await
            .expect("upsert should treat quoted payload as data");
        assert_eq!(quoted.name, quoted_payload);

        println!("   ✓ insert_or_update and on_conflict");
    }
    println!();

    println!("🕒  Testing: Upsert With Timestamp Columns");
    {
        let _ = Database::execute("TRUNCATE TABLE timestamp_users RESTART IDENTITY CASCADE").await;

        let created_at = chrono::Utc::now();
        let updated_at = created_at + chrono::TimeDelta::minutes(15);

        let inserted = TimestampUser::insert_or_update(
            TimestampUser {
                id: 0,
                email: "typed-upsert@example.com".into(),
                name: "Initial Timestamp User".into(),
                login_count: 1,
                created_at,
                updated_at,
            },
            vec!["email"],
        )
        .await
        .expect("insert_or_update should preserve timestamp parameter types on insert");

        assert!(inserted.id > 0, "Upsert insert should assign a primary key");
        assert_eq!(inserted.email, "typed-upsert@example.com");
        assert_eq!(inserted.login_count, 1);
        assert!(
            inserted.created_at <= inserted.updated_at,
            "Auto-managed timestamps should remain ordered"
        );

        let next_updated_at = updated_at + chrono::TimeDelta::minutes(30);
        let updated = TimestampUser::insert_or_update(
            TimestampUser {
                id: inserted.id,
                email: "typed-upsert@example.com".into(),
                name: "Updated Timestamp User".into(),
                login_count: 2,
                created_at,
                updated_at: next_updated_at,
            },
            vec!["email"],
        )
        .await
        .expect("insert_or_update should preserve timestamp parameter types on conflict update");

        assert_eq!(updated.id, inserted.id);
        assert_eq!(updated.name, "Updated Timestamp User");
        assert_eq!(updated.login_count, 2);
        assert!(
            updated.created_at >= inserted.created_at,
            "Conflict updates should keep a valid created_at timestamp"
        );
        assert!(
            updated.updated_at >= inserted.updated_at,
            "Conflict updates should keep updated_at monotonic"
        );

        println!("   ✓ insert_or_update preserves timestamp column types");
    }
    println!();

    // =========================================================================
    // BATCH UPDATE TESTS
    // =========================================================================
    println!("🛠️  Testing: Batch Update");
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 0..5 {
            let user = TestUser {
                id: 0,
                email: format!("batch-update-{i}@example.com"),
                name: format!("Batch Update {i}"),
                age: 24 + (i * 3), // 24, 27, 30, 33, 36
                active: true,
            };
            user.save()
                .await
                .expect("Failed to seed user for batch update");
        }

        let affected = TestUser::update_all()
            .set("active", false)
            .where_gt("age", 30)
            .execute()
            .await
            .expect("Batch update should succeed");
        assert_eq!(affected, 2, "Two users have age > 30");

        let inactive = TestUser::query()
            .where_eq("active", false)
            .count()
            .await
            .expect("Count inactive failed");
        assert_eq!(inactive, 2, "Two users should now be inactive");

        let active = TestUser::query()
            .where_eq("active", true)
            .count()
            .await
            .expect("Count active failed");
        assert_eq!(active, 3, "Three users should remain active");

        let trusted_raw_affected = TestUser::update_all()
            .set_trusted_raw("name", "'trusted-batch-update'")
            .where_eq("id", 1)
            .execute()
            .await
            .expect("set_trusted_raw should execute trusted SQL");
        assert_eq!(trusted_raw_affected, 1, "One row should be updated");

        let trusted_name = TestUser::find_or_fail(1)
            .await
            .expect("Reload trusted raw update failed");
        assert_eq!(trusted_name.name, "trusted-batch-update");

        println!("   ✓ batch update builder");
    }
    println!();

    // =========================================================================
    // CALLBACK TESTS
    // =========================================================================
    println!("🪝 Testing: Callbacks");
    {
        CALLBACK_EVENTS.lock().unwrap().clear();
        let created = CallbackUser {
            id: 0,
            email: "UPPER@EXAMPLE.COM".into(),
            name: "Callback User".into(),
        }
        .save()
        .await
        .expect("Callback save should succeed");

        assert_eq!(created.email, "upper@example.com");
        assert_eq!(
            CALLBACK_EVENTS.lock().unwrap().clone(),
            vec![
                "before_validation",
                "after_validation",
                "before_save",
                "before_create",
                "after_create",
                "after_save"
            ]
        );

        CALLBACK_EVENTS.lock().unwrap().clear();
        let updated = CallbackUser {
            id: created.id,
            email: "SECOND@EXAMPLE.COM".into(),
            name: "Callback User Updated".into(),
        }
        .update()
        .await
        .expect("Callback update should succeed");

        assert_eq!(updated.email, "second@example.com");
        assert_eq!(
            CALLBACK_EVENTS.lock().unwrap().clone(),
            vec![
                "before_validation",
                "after_validation",
                "before_save",
                "before_update",
                "after_update",
                "after_save"
            ]
        );

        CALLBACK_EVENTS.lock().unwrap().clear();
        let deleted = updated
            .delete()
            .await
            .expect("Callback delete should succeed");
        assert_eq!(deleted, 1);
        assert_eq!(
            CALLBACK_EVENTS.lock().unwrap().clone(),
            vec!["before_delete", "after_delete"]
        );
        println!("   ✓ save/update/delete callbacks");
    }
    println!();

    // =========================================================================
    // SCOPES TESTS
    // =========================================================================
    println!("🎯 Testing: Scopes");
    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=10 {
            let user = TestUser {
                id: 0,
                email: format!("scope{i}@example.com"),
                name: format!("Scope User {i}"),
                age: 20 + i,
                active: i <= 5,
            };
            user.save().await.expect("Failed to save");
        }

        fn active_scope(q: QueryBuilder<TestUser>) -> QueryBuilder<TestUser> {
            q.where_eq("active", true)
        }

        fn adult_scope(q: QueryBuilder<TestUser>) -> QueryBuilder<TestUser> {
            q.where_gte("age", 25)
        }

        let users = TestUser::query()
            .scope(active_scope)
            .scope(adult_scope)
            .get()
            .await
            .expect("Query failed");

        assert_eq!(users.len(), 1, "Should have 1 user matching both scopes");
        println!("   ✓ scope chaining");
    }

    {
        let _ = Database::execute("TRUNCATE TABLE test_users RESTART IDENTITY CASCADE").await;

        for i in 1..=5 {
            let user = TestUser {
                id: 0,
                email: format!("cond{i}@example.com"),
                name: format!("Conditional User {i}"),
                age: 20 + i,
                active: i <= 3,
            };
            user.save().await.expect("Failed to save");
        }

        let filter_active = true;
        let users = TestUser::query()
            .when(filter_active, |q| q.where_eq("active", true))
            .get()
            .await
            .expect("Query failed");
        assert_eq!(
            users.len(),
            3,
            "Should have 3 active users when filter is true"
        );

        let filter_active = false;
        let users = TestUser::query()
            .when(filter_active, |q| q.where_eq("active", true))
            .get()
            .await
            .expect("Query failed");
        assert_eq!(users.len(), 5, "Should have 5 users when filter is false");

        let min_age: Option<i32> = Some(23);
        let users = TestUser::query()
            .when_some(min_age, |q, age| q.where_gte("age", age))
            .get()
            .await
            .expect("Query failed");
        assert_eq!(users.len(), 3, "Should have 3 users with age >= 23");
        println!("   ✓ conditional scopes (when/when_some)");
    }
    println!();

    // =========================================================================
    // CLEANUP
    // =========================================================================
    println!("🧹 Cleaning up...");
    let _ = Database::execute("DROP TABLE IF EXISTS test_soft_deletes CASCADE").await;
    let _ = Database::execute("DROP TABLE IF EXISTS test_posts CASCADE").await;
    let _ = Database::execute("DROP TABLE IF EXISTS test_users CASCADE").await;

    println!("\n All PostgreSQL integration tests passed!\n");
}
