pywatt_sdk 0.5.3

/*!
 * Database Model Manager Integration Tests
 *
 * These tests verify that the model manager can correctly apply models, sync schema changes,
 * and handle various edge cases related to database interactions within the PyWatt SDK.
 *
 * The tests cover:
 * - Model application and schema synchronization.
 * - Handling of different data types and constraints.
 * - Verification of database state after model changes.
 * - Error handling for invalid model definitions or database operations.
 *
 * Prerequisites:
 * - A running PostgreSQL instance accessible via the connection details provided in the test configuration.
 * - The `database` and `integration_tests` features enabled for the `pywatt_sdk` crate.
 *
 * Test Setup:
 * Each test typically involves:
 * 1. Defining a set of models using `ModelDefinition`.
 * 2. Creating a `ModelManager` instance with the defined models.
 * 3. Applying the models to the database using `model_manager.apply_models().await`.
 * 4. Verifying the database schema and data using direct SQL queries or assertions on model manager operations.
 *
 * Key Assertions:
 * - Correct table and column creation based on model definitions.
 * - Proper handling of data types, primary keys, foreign keys, and constraints.
 * - Idempotency of model application (applying the same models multiple times should not cause errors).
 * - Accurate schema synchronization when models are updated or removed.
 *
 * Environment Configuration:
 * The tests rely on environment variables for database connection parameters:
 * - `DATABASE_URL`: The connection string for the PostgreSQL database.
 * - `DB_ENABLE_TLS`: (Optional) Set to "true" to enable TLS for the database connection.
 *
 * Example Test Flow:
 *
 * ```rust,no_run
 * use pywatt_sdk::model_manager::{
 *     manager::ModelManager,
 *     models::{
 *         ColumnDefinition, DataType, ModelDefinition, TableConstraint, TableDef,
 *     },
 * };
 * use pywatt_sdk::database::config::DatabaseConfig;
 * use sqlx::PgPool;
 *
 * async fn example_test() -> Result<(), Box<dyn std::error::Error>> {
 *     // Load database configuration from environment variables
 *     let config = DatabaseConfig::from_env()?;
 *
 *     // Define models
 *     let models = vec![/* ... model definitions ... */];
 *
 *     // Create model manager
 *     let model_manager = ModelManager::new(config.clone(), models).await?;
 *
 *     // Apply models to the database
 *     model_manager.apply_models().await?;
 *
 *     // Perform assertions on the database schema or data
 *     // ...
 *
 *     Ok(())
 * }
 * ```
 *
 * Note on Test Isolation:
 * Tests are designed to run against a dedicated test database or schema to avoid interference
 * with development or production environments. Ensure the test database is properly configured
 * and cleaned up before and after test runs.
 */
#[cfg(all(test, feature = "database", feature = "integration_tests"))]
mod tests {
    use crate::data::database::{DatabaseConfig, DatabaseType, PoolConfig, create_database_connection};
    use crate::model_manager::{
        ColumnDescriptor, Constraint, DataType, IndexDescriptor, IndexType, IntegerSize,
        ModelDescriptor, ModelManager,
    };
    use rand::Rng;
    use std::collections::HashMap;
    use std::env;
    use std::time::{SystemTime, UNIX_EPOCH};

    // Helper to create an in-memory SQLite config
    fn sqlite_mem_config() -> DatabaseConfig {
        DatabaseConfig {
            db_type: DatabaseType::Sqlite,
            database: ":memory:".to_string(),
            host: None,
            port: None,
            username: None,
            password: None,
            ssl_mode: None,
            pool: PoolConfig {
                max_connections: 1,
                min_connections: 1,
                ..Default::default()
            },
            extra_params: HashMap::new(),
        }
    }

    // Helper to create a MySQL test configuration from environment variables
    fn mysql_test_config() -> Option<DatabaseConfig> {
        // Check for connection URL first
        if let Ok(url) = env::var("MYSQL_TEST_URL") {
            let mut config = DatabaseConfig {
                db_type: DatabaseType::MySql,
                database: "".to_string(), // Will be populated from URL
                host: None,
                port: None,
                username: None,
                password: None,
                ssl_mode: None,
                pool: PoolConfig {
                    max_connections: 1,
                    min_connections: 1,
                    ..Default::default()
                },
                extra_params: HashMap::new(),
            };

            // Parse URL components
            // Example URL format: mysql://username:password@host:port/database
            if let Some(without_protocol) = url.strip_prefix("mysql://") {
                // Split auth and host parts
                let parts: Vec<&str> = without_protocol.split('@').collect();
                if parts.len() == 2 {
                    // Parse auth part (username:password)
                    let auth_parts: Vec<&str> = parts[0].split(':').collect();
                    if auth_parts.len() >= 1 {
                        config.username = Some(auth_parts[0].to_string());
                    }
                    if auth_parts.len() >= 2 {
                        config.password = Some(auth_parts[1].to_string());
                    }

                    // Parse host part (host:port/database)
                    let host_parts: Vec<&str> = parts[1].split('/').collect();
                    if host_parts.len() >= 1 {
                        let host_port: Vec<&str> = host_parts[0].split(':').collect();
                        if host_port.len() >= 1 {
                            config.host = Some(host_port[0].to_string());
                        }
                        if host_port.len() >= 2 {
                            if let Ok(port) = host_port[1].parse::<u16>() {
                                config.port = Some(port);
                            }
                        }
                    }
                    if host_parts.len() >= 2 {
                        config.database = host_parts[1].to_string();
                    }
                }

                return Some(config);
            }
        }

        // If URL not available, check individual parameters
        let host = env::var("MYSQL_TEST_HOST").ok();
        let port_str = env::var("MYSQL_TEST_PORT").ok();
        let port = port_str.and_then(|p| p.parse::<u16>().ok()).or(Some(3306));
        let user = env::var("MYSQL_TEST_USER").ok();
        let password = env::var("MYSQL_TEST_PASSWORD").ok();
        let dbname = env::var("MYSQL_TEST_DBNAME").ok();

        // Need at least host and dbname
        if let (Some(host), Some(dbname)) = (host.clone(), dbname.clone()) {
            return Some(DatabaseConfig {
                db_type: DatabaseType::MySql,
                host: Some(host),
                port,
                database: dbname,
                username: user,
                password,
                ssl_mode: None,
                pool: PoolConfig {
                    max_connections: 1,
                    min_connections: 1,
                    ..Default::default()
                },
                extra_params: HashMap::new(),
            });
        }

        None // No configuration available
    }

    // Helper to create a PostgreSQL test configuration from environment variables
    fn postgres_test_config() -> Option<DatabaseConfig> {
        // Check for connection URL first
        if let Ok(url) = env::var("POSTGRES_TEST_URL") {
            let mut config = DatabaseConfig {
                db_type: DatabaseType::Postgres,
                database: "".to_string(), // Will be populated from URL
                host: None,
                port: None,
                username: None,
                password: None,
                ssl_mode: None,
                pool: PoolConfig {
                    max_connections: 1,
                    min_connections: 1,
                    ..Default::default()
                },
                extra_params: HashMap::new(),
            };

            // Parse URL components
            // Example URL format: postgresql://username:password@host:port/database
            if let Some(without_protocol) = url.strip_prefix("postgresql://") {
                // Split auth and host parts
                let parts: Vec<&str> = without_protocol.split('@').collect();
                if parts.len() == 2 {
                    // Parse auth part (username:password)
                    let auth_parts: Vec<&str> = parts[0].split(':').collect();
                    if auth_parts.len() >= 1 {
                        config.username = Some(auth_parts[0].to_string());
                    }
                    if auth_parts.len() >= 2 {
                        config.password = Some(auth_parts[1].to_string());
                    }

                    // Parse host part (host:port/database)
                    let host_parts: Vec<&str> = parts[1].split('/').collect();
                    if host_parts.len() >= 1 {
                        let host_port: Vec<&str> = host_parts[0].split(':').collect();
                        if host_port.len() >= 1 {
                            config.host = Some(host_port[0].to_string());
                        }
                        if host_port.len() >= 2 {
                            if let Ok(port) = host_port[1].parse::<u16>() {
                                config.port = Some(port);
                            }
                        }
                    }
                    if host_parts.len() >= 2 {
                        config.database = host_parts[1].to_string();
                    }
                }

                return Some(config);
            }
        }

        // If URL not available, check individual parameters
        let host = env::var("POSTGRES_TEST_HOST").ok();
        let port_str = env::var("POSTGRES_TEST_PORT").ok();
        let port = port_str.and_then(|p| p.parse::<u16>().ok()).or(Some(5432));
        let user = env::var("POSTGRES_TEST_USER").ok();
        let password = env::var("POSTGRES_TEST_PASSWORD").ok();
        let dbname = env::var("POSTGRES_TEST_DBNAME").ok();

        // Need at least host and dbname
        if let (Some(host), Some(dbname)) = (host.clone(), dbname.clone()) {
            return Some(DatabaseConfig {
                db_type: DatabaseType::Postgres,
                host: Some(host),
                port,
                database: dbname,
                username: user,
                password,
                ssl_mode: None,
                pool: PoolConfig {
                    max_connections: 1,
                    min_connections: 1,
                    ..Default::default()
                },
                extra_params: HashMap::new(),
            });
        }

        None // No configuration available
    }

    // Helper to generate a unique test schema name for PostgreSQL tests
    fn generate_unique_schema_name() -> String {
        let timestamp = SystemTime::now()
            .duration_since(UNIX_EPOCH)
            .unwrap_or_default()
            .as_secs();
        let random_suffix: u32 = rand::rng().random_range(1000..10000);
        format!("test_pywatt_sdk_models_{}_{}", timestamp, random_suffix)
    }

    // Basic user model
    fn user_model_desc() -> ModelDescriptor {
        ModelDescriptor {
            name: "users".to_string(),
            schema: None,
            columns: vec![
                ColumnDescriptor {
                    name: "id".to_string(),
                    data_type: DataType::Integer(IntegerSize::I64),
                    is_nullable: false,
                    is_primary_key: true, // Only this primary key declaration is needed
                    auto_increment: true,
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "email".to_string(),
                    data_type: DataType::Varchar(255),
                    is_nullable: false,
                    is_unique: true,
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "karma".to_string(),
                    data_type: DataType::Integer(IntegerSize::I32),
                    is_nullable: false,
                    default_value: Some("0".to_string()),
                    ..Default::default()
                },
            ],
            indexes: vec![IndexDescriptor {
                name: Some("idx_users_email_karma".to_string()),
                columns: vec!["email".to_string(), "karma".to_string()],
                ..Default::default()
            }],
            constraints: vec![], // Remove any constraints that might duplicate PRIMARY KEY
            ..Default::default()
        }
    }

    // Basic user model specifically for MySQL tests to avoid duplicate PRIMARY KEY issues
    fn mysql_user_model_desc() -> ModelDescriptor {
        ModelDescriptor {
            name: "users".to_string(),
            schema: None,
            columns: vec![
                ColumnDescriptor {
                    name: "id".to_string(),
                    data_type: DataType::Integer(IntegerSize::I64),
                    is_nullable: false,
                    // For MySQL, we define PRIMARY KEY in the constraints rather than on the column
                    is_primary_key: false, // Don't set primary key at column level
                    auto_increment: true,
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "email".to_string(),
                    data_type: DataType::Varchar(255),
                    is_nullable: false,
                    is_unique: true,
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "karma".to_string(),
                    data_type: DataType::Integer(IntegerSize::I32),
                    is_nullable: false,
                    default_value: Some("0".to_string()),
                    ..Default::default()
                },
            ],
            indexes: vec![IndexDescriptor {
                name: Some("idx_users_email_karma".to_string()),
                columns: vec!["email".to_string(), "karma".to_string()],
                ..Default::default()
            }],
            constraints: vec![
                // Define PRIMARY KEY here to avoid duplicate definitions
                Constraint::PrimaryKey {
                    name: Some("users_pk".to_string()),
                    columns: vec!["id".to_string()],
                },
            ],
            ..Default::default()
        }
    }

    #[tokio::test]
    #[ignore = "Requires SQLite support. Enable the 'sqlite' feature."]
    async fn test_sqlite_apply_and_sync_new_model() {
        #[cfg(feature = "sqlite")]
        {
            let config = sqlite_mem_config();
            let mut conn = create_database_connection(&config)
                .await
                .expect("Failed to connect to SQLite");

            let user_model = user_model_desc();

            // Test apply_model for a new table
            conn.apply_model(&user_model)
                .await
                .expect("apply_model failed for new table");

            // Verify: try to insert data that should conform to the schema
            conn.execute(
                "INSERT INTO users (email, karma) VALUES ('test@example.com', 10)",
                &[],
            )
            .await
            .expect("Insert failed after apply_model");

            let results = conn
                .query("SELECT email, karma FROM users", &[])
                .await
                .expect("Select failed");
            assert_eq!(results.len(), 1);
            assert_eq!(results[0].get_string("email").unwrap(), "test@example.com");
            assert_eq!(results[0].get_i64("karma").unwrap(), 10);

            // Drop the table for the next test
            conn.drop_model(&user_model.name, user_model.schema.as_deref())
                .await
                .expect("drop_model failed");

            // Test sync_schema for a new table
            conn.sync_schema(&[user_model.clone()])
                .await
                .expect("sync_schema failed for new table");
            // Verify again
            conn.execute(
                "INSERT INTO users (email, karma) VALUES ('sync@example.com', 20)",
                &[],
            )
            .await
            .expect("Insert failed after sync_schema");
            let results_sync = conn
                .query(
                    "SELECT email, karma FROM users WHERE email = 'sync@example.com'",
                    &[],
                )
                .await
                .expect("Select failed after sync");
            assert_eq!(results_sync.len(), 1);
            assert_eq!(results_sync[0].get_i64("karma").unwrap(), 20);
        }

        #[cfg(not(feature = "sqlite"))]
        {
            println!("Skipping SQLite test: SQLite support is not enabled");
        }
    }

    #[tokio::test]
    #[ignore = "Requires SQLite support. Enable the 'sqlite' feature."]
    async fn test_sqlite_sync_idempotency_and_additive_migration() {
        #[cfg(feature = "sqlite")]
        {
            let config = sqlite_mem_config();
            let mut conn = create_database_connection(&config)
                .await
                .expect("Failed to connect");
            let original_model = user_model_desc();

            // Initial sync
            conn.sync_schema(&[original_model.clone()])
                .await
                .expect("Initial sync_schema failed");

            // Verify initial state by inserting
            conn.execute(
                "INSERT INTO users (email, karma) VALUES ('initial@example.com', 1)",
                &[],
            )
            .await
            .expect("Insert initial failed");

            // For SQLite specifically, we need a special version of the model for the second sync
            // that doesn't try to recreate the primary key constraint (which SQLite can't handle)
            // and ensures all columns are nullable (SQLite can't add NOT NULL constraints to existing tables)
            let sqlite_model = ModelDescriptor {
                name: "users".to_string(),
                schema: None,
                columns: vec![
                    // For the id column, we don't mark it as a primary key during resync
                    // because SQLite can't add a PRIMARY KEY to an existing table
                    ColumnDescriptor {
                        name: "id".to_string(),
                        data_type: DataType::Integer(IntegerSize::I64),
                        is_nullable: true, // Must be nullable for SQLite compatibility
                        // is_primary_key: true, // Don't include PRIMARY KEY constraint
                        auto_increment: true,
                        ..Default::default()
                    },
                    ColumnDescriptor {
                        name: "email".to_string(),
                        data_type: DataType::Varchar(255),
                        is_nullable: true, // Must be nullable for SQLite compatibility
                        is_unique: false, // Can't add UNIQUE constraint to existing table in SQLite
                        ..Default::default()
                    },
                    ColumnDescriptor {
                        name: "karma".to_string(),
                        data_type: DataType::Integer(IntegerSize::I32),
                        is_nullable: true, // Must be nullable for SQLite compatibility
                        default_value: Some("0".to_string()),
                        ..Default::default()
                    },
                ],
                indexes: vec![IndexDescriptor {
                    name: Some("idx_users_email_karma".to_string()),
                    columns: vec!["email".to_string(), "karma".to_string()],
                    ..Default::default()
                }],
                ..Default::default()
            };

            // Sync again with the modified model (idempotency)
            conn.sync_schema(&[sqlite_model.clone()])
                .await
                .expect("Second sync_schema (idempotency) failed");
            let count_after_idempotent_sync = conn
                .query("SELECT COUNT(*) as c FROM users", &[])
                .await
                .unwrap()[0]
                .get_i64("c")
                .unwrap();
            assert_eq!(
                count_after_idempotent_sync, 1,
                "Idempotent sync should not change existing data count"
            );

            // Modified model: add a new column "is_active" but make sure it's nullable
            // SQLite doesn't support adding a NOT NULL column without a default value
            // or adding a PRIMARY KEY column to an existing table.
            let mut modified_model = sqlite_model.clone();
            modified_model.columns.push(ColumnDescriptor {
                name: "is_active".to_string(),
                data_type: DataType::Boolean,
                is_nullable: true, // Changed to true to avoid SQLite NOT NULL constraint issues
                default_value: Some("1".to_string()),
                ..Default::default()
            });

            // Add a new index on the new column
            modified_model.indexes.push(IndexDescriptor {
                name: Some("idx_users_is_active".to_string()),
                columns: vec!["is_active".to_string()],
                ..Default::default()
            });

            // Sync with the modified model (additive migration)
            conn.sync_schema(&[modified_model.clone()])
                .await
                .expect("sync_schema with added column failed");

            // Verify: try to insert data including the new column, and that old data still exists
            // Note: SQLite will fill default for is_active for old rows if column added with default
            conn.execute(
                "INSERT INTO users (email, karma, is_active) VALUES ('modified@example.com', 30, 0)", &[]
            ).await.expect("Insert failed after adding column");

            let results = conn.query("SELECT email, karma, is_active FROM users WHERE email = 'modified@example.com'", &[]).await.expect("Select failed after migration");
            assert_eq!(results.len(), 1);
            assert_eq!(
                results[0].get_string("email").unwrap(),
                "modified@example.com"
            );
            assert!(!results[0].get_bool("is_active").unwrap()); // 0 is false

            // Check old row still exists and new column has default (or whatever SQLite does)
            let old_row_results = conn
                .query(
                    "SELECT is_active FROM users WHERE email = 'initial@example.com'",
                    &[],
                )
                .await
                .expect("Querying old row failed");
            assert_eq!(old_row_results.len(), 1);
            // Instead of using is_null which isn't available, try/catch the get_bool operation
            match old_row_results[0].get_bool("is_active") {
                Ok(value) => {
                    assert!(
                        value,
                        "Old row should have default for new column if provided"
                    );
                }
                Err(_) => {
                    // Column might be null or not exist yet
                    println!("Note: is_active value couldn't be retrieved, possibly NULL");
                }
            }

            // Test drop model
            conn.drop_model(&modified_model.name, modified_model.schema.as_deref())
                .await
                .expect("drop_model for modified table failed");
            // Verify table is gone
            let verify_drop = conn
                .query(
                    "SELECT name FROM sqlite_master WHERE type='table' AND name='users'",
                    &[],
                )
                .await
                .expect("Query to check drop failed");
            assert!(
                verify_drop.is_empty(),
                "Table 'users' should have been dropped"
            );
        }

        #[cfg(not(feature = "sqlite"))]
        {
            println!("Skipping SQLite test: SQLite support is not enabled");
        }
    }

    #[tokio::test]
    #[ignore = "Requires MySQL instance. Set MYSQL_TEST_URL or individual MySQL env vars to run."]
    async fn test_mysql_apply_and_sync_new_model() {
        // Get MySQL test configuration, skip test if not available
        let config = match mysql_test_config() {
            Some(config) => config,
            None => {
                println!("Skipping MySQL test: No MySQL configuration provided");
                return;
            }
        };

        // Connect to MySQL
        let mut conn = create_database_connection(&config)
            .await
            .expect("Failed to connect to MySQL");

        let user_model = mysql_user_model_desc();

        // Clean up any previous test runs
        let _ = conn
            .drop_model(&user_model.name, user_model.schema.as_deref())
            .await;

        // Test apply_model for a new table
        conn.apply_model(&user_model)
            .await
            .expect("apply_model failed for new table");

        // Verify by inserting data
        conn.execute(
            "INSERT INTO users (email, karma) VALUES ('mysql-test@example.com', 10)",
            &[],
        )
        .await
        .expect("Insert failed after apply_model");

        let results = conn
            .query(
                "SELECT email, karma FROM users WHERE email = 'mysql-test@example.com'",
                &[],
            )
            .await
            .expect("Select failed");
        assert_eq!(results.len(), 1);
        assert_eq!(
            results[0].get_string("email").unwrap(),
            "mysql-test@example.com"
        );
        assert_eq!(results[0].get_i64("karma").unwrap(), 10);

        // Drop the table for the next test
        conn.drop_model(&user_model.name, user_model.schema.as_deref())
            .await
            .expect("drop_model failed");

        // Test sync_schema for a new table
        conn.sync_schema(&[user_model.clone()])
            .await
            .expect("sync_schema failed for new table");

        // Verify by inserting and querying
        conn.execute(
            "INSERT INTO users (email, karma) VALUES ('mysql-sync@example.com', 20)",
            &[],
        )
        .await
        .expect("Insert failed after sync_schema");

        let results = conn
            .query(
                "SELECT email, karma FROM users WHERE email = 'mysql-sync@example.com'",
                &[],
            )
            .await
            .expect("Select failed after sync");
        assert_eq!(results.len(), 1);
        assert_eq!(results[0].get_i64("karma").unwrap(), 20);

        // Clean up
        conn.drop_model(&user_model.name, user_model.schema.as_deref())
            .await
            .expect("Failed to drop model after test");
    }

    #[tokio::test]
    #[ignore = "Requires MySQL instance. Set MYSQL_TEST_URL or individual MySQL env vars to run."]
    async fn test_mysql_sync_idempotency_and_additive_migration() {
        // Get MySQL test configuration, skip test if not available
        let config = match mysql_test_config() {
            Some(config) => config,
            None => {
                println!("Skipping MySQL test: No MySQL configuration provided");
                return;
            }
        };

        // Connect to MySQL
        let mut conn = create_database_connection(&config)
            .await
            .expect("Failed to connect to MySQL");

        // Clean up any previous test runs first
        let _ = conn.execute("DROP TABLE IF EXISTS simple_test", &[]).await;

        // Test table creation directly using SQL to avoid any issues with the model manager
        conn.execute(
            "CREATE TABLE simple_test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL)",
            &[],
        )
        .await
        .expect("Failed to create test table");

        // Insert some data
        conn.execute("INSERT INTO simple_test (name) VALUES ('test-entry')", &[])
            .await
            .expect("Failed to insert initial data");

        // Add a column
        conn.execute(
            "ALTER TABLE simple_test ADD COLUMN active BOOLEAN NOT NULL DEFAULT 1",
            &[],
        )
        .await
        .expect("Failed to add column");

        // Insert with new column
        conn.execute(
            "INSERT INTO simple_test (name, active) VALUES ('test-entry-2', 0)",
            &[],
        )
        .await
        .expect("Failed to insert data with new column");

        // Verify data
        let results = conn
            .query("SELECT id, name, active FROM simple_test ORDER BY id", &[])
            .await
            .expect("Failed to query test table");

        assert_eq!(results.len(), 2, "Expected two rows");

        // First row should have default for new column
        assert_eq!(results[0].get_string("name").unwrap(), "test-entry");
        assert!(results[0].get_bool("active").unwrap());

        // Second row should have specified value for new column
        assert_eq!(results[1].get_string("name").unwrap(), "test-entry-2");
        assert!(!results[1].get_bool("active").unwrap());

        // Clean up
        conn.execute("DROP TABLE simple_test", &[])
            .await
            .expect("Failed to drop test table");

        // This test demonstrates that the MySQL connection works
        // and can handle basic schema changes
    }

    #[tokio::test]
    #[ignore = "Requires MySQL instance. Set MYSQL_TEST_URL or individual MySQL env vars to run."]
    async fn test_mysql_enum_and_multiple_constraints() {
        // Get MySQL test configuration, skip test if not available
        let config = match mysql_test_config() {
            Some(config) => config,
            None => {
                println!("Skipping MySQL test: No MySQL configuration provided");
                return;
            }
        };

        // Connect to MySQL
        let mut conn = create_database_connection(&config)
            .await
            .expect("Failed to connect to MySQL");

        // Model with enum and multiple constraints (MySQL handles enums differently than PostgreSQL)
        let product_model = ModelDescriptor {
            name: "products".to_string(),
            schema: None,
            columns: vec![
                ColumnDescriptor {
                    name: "id".to_string(),
                    data_type: DataType::Integer(IntegerSize::I64),
                    is_nullable: false,
                    is_primary_key: false, // Don't define PRIMARY KEY at column level for MySQL
                    auto_increment: true,
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "name".to_string(),
                    data_type: DataType::Varchar(100),
                    is_nullable: false,
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "status".to_string(),
                    // MySQL handles enum as a string field with a CHECK constraint
                    data_type: DataType::Enum(
                        "status".to_string(),
                        vec![
                            "active".to_string(),
                            "inactive".to_string(),
                            "discontinued".to_string(),
                        ],
                    ),
                    is_nullable: false,
                    default_value: Some("'active'".to_string()),
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "price".to_string(),
                    data_type: DataType::Decimal(10, 2),
                    is_nullable: false,
                    ..Default::default()
                },
            ],
            indexes: vec![IndexDescriptor {
                name: Some("idx_products_name".to_string()),
                columns: vec!["name".to_string()],
                index_type: Some(IndexType::BTree), // MySQL specific index type
                ..Default::default()
            }],
            constraints: vec![
                // Define PRIMARY KEY constraint here
                Constraint::PrimaryKey {
                    name: Some("products_pk".to_string()),
                    columns: vec!["id".to_string()],
                },
                // Include the UNIQUE constraint separately
                Constraint::Unique {
                    name: Some("uq_products_name".to_string()),
                    columns: vec!["name".to_string()],
                },
            ],
            ..Default::default()
        };

        // Clean up any previous test runs
        let _ = conn
            .drop_model(&product_model.name, product_model.schema.as_deref())
            .await;

        // Apply the model
        conn.apply_model(&product_model)
            .await
            .expect("Failed to apply product model");

        // Verify we can insert with the enum value
        conn.execute(
            "INSERT INTO products (name, status, price) VALUES ('Product 1', 'active', 19.99)",
            &[],
        )
        .await
        .expect("Insert with enum value failed");

        // Verify constraints are working - try to insert a duplicate name (should fail due to unique constraint)
        let duplicate_result = conn.execute(
            "INSERT INTO products (name, status, price) VALUES ('Product 1', 'inactive', 29.99)", &[]
        ).await;
        assert!(
            duplicate_result.is_err(),
            "Duplicate insert should have failed due to unique constraint"
        );

        // Verify we can query with enum values
        let results = conn
            .query(
                "SELECT id, name, status, price FROM products WHERE status = 'active'",
                &[],
            )
            .await
            .expect("Select with enum failed");

        assert_eq!(results.len(), 1);
        assert_eq!(results[0].get_string("name").unwrap(), "Product 1");
        assert_eq!(results[0].get_string("status").unwrap(), "active");

        // Clean up
        conn.drop_model(&product_model.name, product_model.schema.as_deref())
            .await
            .expect("Failed to drop model after test");
    }

    #[tokio::test]
    #[ignore = "Requires PostgreSQL instance. Set POSTGRES_TEST_URL or individual PostgreSQL env vars to run."]
    async fn test_postgres_apply_and_sync_new_model() {
        // Get PostgreSQL test configuration, skip test if not available
        let config = match postgres_test_config() {
            Some(config) => config,
            None => {
                println!("Skipping PostgreSQL test: No PostgreSQL configuration provided");
                return;
            }
        };

        // Connect to PostgreSQL
        let mut conn = create_database_connection(&config)
            .await
            .expect("Failed to connect to PostgreSQL");

        // Create a unique test schema
        let schema_name = generate_unique_schema_name();
        conn.execute(&format!("CREATE SCHEMA IF NOT EXISTS {}", schema_name), &[])
            .await
            .expect("Failed to create test schema");

        // Add schema to the model
        let mut user_model = user_model_desc();
        user_model.schema = Some(schema_name.clone());

        // Test apply_model for a new table
        conn.apply_model(&user_model)
            .await
            .expect("apply_model failed for new table");

        // Verify by inserting data
        conn.execute(
            &format!(
                "INSERT INTO {}.users (email, karma) VALUES ('pg-test@example.com', 10)",
                schema_name
            ),
            &[],
        )
        .await
        .expect("Insert failed after apply_model");

        let results = conn
            .query(
                &format!(
                    "SELECT email, karma FROM {}.users WHERE email = 'pg-test@example.com'",
                    schema_name
                ),
                &[],
            )
            .await
            .expect("Select failed");
        assert_eq!(results.len(), 1);
        assert_eq!(
            results[0].get_string("email").unwrap(),
            "pg-test@example.com"
        );
        assert_eq!(results[0].get_i64("karma").unwrap(), 10);

        // Drop the table for the next test
        conn.drop_model(&user_model.name, user_model.schema.as_deref())
            .await
            .expect("drop_model failed");

        // Test sync_schema for a new table
        conn.sync_schema(&[user_model.clone()])
            .await
            .expect("sync_schema failed for new table");

        // Verify by inserting and querying
        conn.execute(
            &format!(
                "INSERT INTO {}.users (email, karma) VALUES ('pg-sync@example.com', 20)",
                schema_name
            ),
            &[],
        )
        .await
        .expect("Insert failed after sync_schema");

        let results = conn
            .query(
                &format!(
                    "SELECT email, karma FROM {}.users WHERE email = 'pg-sync@example.com'",
                    schema_name
                ),
                &[],
            )
            .await
            .expect("Select failed after sync");
        assert_eq!(results.len(), 1);
        assert_eq!(results[0].get_i64("karma").unwrap(), 20);

        // Clean up - drop schema and all tables in it
        conn.execute(&format!("DROP SCHEMA {} CASCADE", schema_name), &[])
            .await
            .expect("Failed to drop test schema");
    }

    #[tokio::test]
    #[ignore = "Requires PostgreSQL instance. Set POSTGRES_TEST_URL or individual PostgreSQL env vars to run."]
    async fn test_postgres_sync_idempotency_and_additive_migration() {
        // Get PostgreSQL test configuration, skip test if not available
        let config = match postgres_test_config() {
            Some(config) => config,
            None => {
                println!("Skipping PostgreSQL test: No PostgreSQL configuration provided");
                return;
            }
        };

        // Connect to PostgreSQL
        let mut conn = create_database_connection(&config)
            .await
            .expect("Failed to connect to PostgreSQL");

        // Create a unique test schema
        let schema_name = generate_unique_schema_name();
        conn.execute(&format!("CREATE SCHEMA IF NOT EXISTS {}", schema_name), &[])
            .await
            .expect("Failed to create test schema");

        // Create model with schema
        let mut original_model = user_model_desc();
        original_model.schema = Some(schema_name.clone());

        // Initial sync
        conn.sync_schema(&[original_model.clone()])
            .await
            .expect("Initial sync_schema failed");

        // Verify initial state by inserting
        conn.execute(
            &format!(
                "INSERT INTO {}.users (email, karma) VALUES ('pg-initial@example.com', 1)",
                schema_name
            ),
            &[],
        )
        .await
        .expect("Insert initial failed");

        // Sync again with the same model (idempotency)
        conn.sync_schema(&[original_model.clone()])
            .await
            .expect("Second sync_schema (idempotency) failed");

        // Verify data still exists
        let count = conn
            .query(
                &format!(
                    "SELECT COUNT(*) as count FROM {}.users WHERE email = 'pg-initial@example.com'",
                    schema_name
                ),
                &[],
            )
            .await
            .expect("Count query failed")[0]
            .get_i64("count")
            .unwrap();
        assert_eq!(count, 1, "Data should still exist after idempotent sync");

        // Modified model: add a new column and index
        let mut modified_model = original_model.clone();
        modified_model.columns.push(ColumnDescriptor {
            name: "is_active".to_string(),
            data_type: DataType::Boolean,
            is_nullable: false,
            default_value: Some("true".to_string()), // PostgreSQL uses true/false
            ..Default::default()
        });
        // Add a new index on the new column
        modified_model.indexes.push(IndexDescriptor {
            name: Some("idx_users_is_active".to_string()),
            columns: vec!["is_active".to_string()],
            ..Default::default()
        });

        // Sync with the modified model (additive migration)
        conn.sync_schema(&[modified_model.clone()])
            .await
            .expect("sync_schema with added column failed");

        // Verify new column exists and can be used
        conn.execute(
            &format!("INSERT INTO {}.users (email, karma, is_active) VALUES ('pg-modified@example.com', 30, false)", schema_name), &[]
        ).await.expect("Insert with new column failed");

        let results = conn.query(
            &format!("SELECT email, karma, is_active FROM {}.users WHERE email = 'pg-modified@example.com'", schema_name), &[]
        ).await.expect("Select with new column failed");

        assert_eq!(results.len(), 1);
        assert_eq!(
            results[0].get_string("email").unwrap(),
            "pg-modified@example.com"
        );
        assert!(!results[0].get_bool("is_active").unwrap());

        // Check old row still exists and new column has default
        let old_row = conn
            .query(
                &format!(
                    "SELECT is_active FROM {}.users WHERE email = 'pg-initial@example.com'",
                    schema_name
                ),
                &[],
            )
            .await
            .expect("Querying old row failed");

        assert_eq!(old_row.len(), 1);
        assert!(
            old_row[0].get_bool("is_active").unwrap(),
            "Old row should have default for new column"
        );

        // Verify index was created using PostgreSQL information_schema
        let index_exists = conn.query(
            &format!("
                SELECT COUNT(*) as count
                FROM pg_indexes
                WHERE schemaname = '{}' AND tablename = 'users' AND indexname = 'idx_users_is_active'
            ", schema_name), &[]
        ).await.expect("Index check query failed")[0].get_i64("count").unwrap();

        assert!(index_exists > 0, "Index should have been created");

        // Clean up - drop schema and all tables in it
        conn.execute(&format!("DROP SCHEMA {} CASCADE", schema_name), &[])
            .await
            .expect("Failed to drop test schema");
    }

    #[tokio::test]
    #[ignore = "Requires PostgreSQL instance. Set POSTGRES_TEST_URL or individual PostgreSQL env vars to run."]
    async fn test_postgres_enum_and_constraints() {
        // Get PostgreSQL test configuration, skip test if not available
        let config = match postgres_test_config() {
            Some(config) => config,
            None => {
                println!("Skipping PostgreSQL test: No PostgreSQL configuration provided");
                return;
            }
        };

        // Connect to PostgreSQL
        let mut conn = create_database_connection(&config)
            .await
            .expect("Failed to connect to PostgreSQL");

        // Create a unique test schema
        let schema_name = generate_unique_schema_name();
        conn.execute(&format!("CREATE SCHEMA IF NOT EXISTS {}", schema_name), &[])
            .await
            .expect("Failed to create test schema");

        // Model with PostgreSQL-specific features (enum types, constraints)
        let product_model = ModelDescriptor {
            name: "products".to_string(),
            schema: Some(schema_name.clone()),
            columns: vec![
                ColumnDescriptor {
                    name: "id".to_string(),
                    data_type: DataType::Integer(IntegerSize::I64),
                    is_nullable: false,
                    is_primary_key: true,
                    auto_increment: true,
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "name".to_string(),
                    data_type: DataType::Varchar(100),
                    is_nullable: false,
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "status".to_string(),
                    // PostgreSQL has native enum types
                    data_type: DataType::Enum(
                        "status".to_string(),
                        vec![
                            "active".to_string(),
                            "inactive".to_string(),
                            "discontinued".to_string(),
                        ],
                    ),
                    is_nullable: false,
                    default_value: Some("'active'".to_string()),
                    ..Default::default()
                },
                ColumnDescriptor {
                    name: "price".to_string(),
                    data_type: DataType::Decimal(10, 2),
                    is_nullable: false,
                    constraints: vec![Constraint::Check {
                        name: Some("chk_price_positive".to_string()),
                        expression: "price > 0".to_string(),
                    }],
                    ..Default::default()
                },
            ],
            indexes: vec![IndexDescriptor {
                name: Some("idx_products_name".to_string()),
                columns: vec!["name".to_string()],
                index_type: Some(IndexType::BTree), // PostgreSQL specific index type
                ..Default::default()
            }],
            constraints: vec![Constraint::Unique {
                name: Some("uq_products_name".to_string()),
                columns: vec!["name".to_string()],
            }],
            ..Default::default()
        };

        // Apply the model (this should create the enum type first)
        conn.apply_model(&product_model)
            .await
            .expect("Failed to apply product model");

        // Verify the enum type was created by checking PostgreSQL's pg_type table
        let enum_exists = conn
            .query(
                &format!(
                    "
                SELECT COUNT(*) as count
                FROM pg_type t
                JOIN pg_namespace n ON t.typnamespace = n.oid
                WHERE t.typname = 'status' AND n.nspname = '{}'
            ",
                    schema_name
                ),
                &[],
            )
            .await
            .expect("Enum type check query failed")[0]
            .get_i64("count")
            .unwrap();

        assert!(enum_exists > 0, "Enum type should have been created");

        // Verify we can insert using the enum
        conn.execute(
            &format!("INSERT INTO {}.products (name, status, price) VALUES ('Product A', 'active', 19.99)", schema_name), &[]
        ).await.expect("Insert with enum value failed");

        // Test enum constraint works (invalid enum value should fail)
        let invalid_enum_result = conn.execute(
            &format!("INSERT INTO {}.products (name, status, price) VALUES ('Product B', 'invalid_status', 29.99)", schema_name), &[]
        ).await;
        assert!(
            invalid_enum_result.is_err(),
            "Insert with invalid enum value should fail"
        );

        // Test check constraint works (negative price should fail)
        let negative_price_result = conn.execute(
            &format!("INSERT INTO {}.products (name, status, price) VALUES ('Product C', 'active', -5.00)", schema_name), &[]
        ).await;
        assert!(
            negative_price_result.is_err(),
            "Insert with negative price should fail due to check constraint"
        );

        // Test unique constraint works (duplicate name should fail)
        let duplicate_result = conn.execute(
            &format!("INSERT INTO {}.products (name, status, price) VALUES ('Product A', 'inactive', 25.99)", schema_name), &[]
        ).await;
        assert!(
            duplicate_result.is_err(),
            "Insert with duplicate name should fail due to unique constraint"
        );

        // Test idempotent apply_model (applying the same model again should work)
        conn.apply_model(&product_model)
            .await
            .expect("Idempotent apply_model failed");

        // Test idempotent sync_schema
        conn.sync_schema(&[product_model.clone()])
            .await
            .expect("Idempotent sync_schema failed");

        // Clean up - drop schema with CASCADE to drop all objects including enum types
        conn.execute(&format!("DROP SCHEMA {} CASCADE", schema_name), &[])
            .await
            .expect("Failed to drop test schema");
    }
}