#[cfg(test)]
mod tests {
    use super::*;
    use rusqlite::Connection;
    use tempfile::NamedTempFile;

    fn create_test_connection() -> Connection {
        let temp_file = NamedTempFile::new().unwrap();
        let conn = Connection::open(temp_file.path()).unwrap();
        
        // 配置数据库设置
        conn.pragma_update(None, "foreign_keys", "ON").unwrap();
        conn.pragma_update(None, "journal_mode", "WAL").unwrap();
        
        conn
    }

    #[test]
    fn test_migration_manager_creation() {
        let manager = MigrationManager::new();
        let latest_version = manager.get_latest_version();
        
        // 应该有至少8个迁移版本
        assert!(latest_version >= 8, "应该有至少8个迁移版本，实际: {}", latest_version);
    }

    #[test]
    fn test_initial_version_is_zero() {
        let conn = create_test_connection();
        let manager = MigrationManager::new();
        
        let current_version = manager.get_current_version(&conn).unwrap();
        assert_eq!(current_version, 0, "新数据库的版本应该是0");
    }

    #[test]
    fn test_migration_table_creation() {
        let conn = create_test_connection();
        let manager = MigrationManager::new();
        
        // 确保迁移表存在
        manager.ensure_migration_table(&conn).unwrap();
        
        // 验证表是否存在
        let table_exists: bool = conn.query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='schema_migrations'",
            [],
            |row| row.get::<_, i32>(0)
        ).unwrap() > 0;
        
        assert!(table_exists, "schema_migrations表应该存在");
    }

    #[test]
    fn test_full_migration() {
        let conn = create_test_connection();
        let manager = MigrationManager::new();
        
        // 执行所有迁移
        manager.migrate(&conn).unwrap();
        
        // 验证版本已更新
        let current_version = manager.get_current_version(&conn).unwrap();
        let latest_version = manager.get_latest_version();
        
        assert_eq!(current_version, latest_version, "迁移后版本应该是最新版本");
        
        // 验证一些关键表是否存在
        let tables = vec![
            "projects", "materials", "material_segments", "models", 
            "ai_classifications", "templates", "template_materials",
            "tracks", "track_segments", "video_classification_records"
        ];
        
        for table in tables {
            let table_exists: bool = conn.query_row(
                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?1",
                [table],
                |row| row.get::<_, i32>(0)
            ).unwrap() > 0;
            
            assert!(table_exists, "表 {} 应该存在", table);
        }
    }

    #[test]
    fn test_migration_history() {
        let conn = create_test_connection();
        let manager = MigrationManager::new();
        
        // 执行迁移
        manager.migrate(&conn).unwrap();
        
        // 获取迁移历史
        let history = manager.get_migration_history(&conn).unwrap();
        
        // 应该有迁移记录
        assert!(!history.is_empty(), "应该有迁移历史记录");
        
        // 所有迁移都应该成功
        for (version, description, _applied_at, success) in history {
            assert!(success, "迁移 v{}: {} 应该成功", version, description);
        }
    }

    #[test]
    fn test_idempotent_migration() {
        let conn = create_test_connection();
        let manager = MigrationManager::new();
        
        // 第一次迁移
        manager.migrate(&conn).unwrap();
        let version_after_first = manager.get_current_version(&conn).unwrap();
        
        // 第二次迁移（应该是幂等的）
        manager.migrate(&conn).unwrap();
        let version_after_second = manager.get_current_version(&conn).unwrap();
        
        assert_eq!(version_after_first, version_after_second, "重复迁移应该是幂等的");
    }

    #[test]
    fn test_specific_migration_features() {
        let conn = create_test_connection();
        let manager = MigrationManager::new();
        
        // 执行迁移
        manager.migrate(&conn).unwrap();
        
        // 测试特定的迁移功能
        
        // 1. 检查materials表是否有model_id字段（迁移2）
        let has_model_id = conn.prepare("SELECT model_id FROM materials LIMIT 1").is_ok();
        assert!(has_model_id, "materials表应该有model_id字段");
        
        // 2. 检查template_materials表是否有file_exists字段（迁移3）
        let has_file_exists = conn.prepare("SELECT file_exists FROM template_materials LIMIT 1").is_ok();
        assert!(has_file_exists, "template_materials表应该有file_exists字段");
        
        // 3. 检查track_segments表是否有matching_rule字段（迁移8）
        let has_matching_rule = conn.prepare("SELECT matching_rule FROM track_segments LIMIT 1").is_ok();
        assert!(has_matching_rule, "track_segments表应该有matching_rule字段");
        
        // 4. 检查material_segments表是否有thumbnail_path字段（迁移8）
        let has_thumbnail_path = conn.prepare("SELECT thumbnail_path FROM material_segments LIMIT 1").is_ok();
        assert!(has_thumbnail_path, "material_segments表应该有thumbnail_path字段");
    }

    #[test]
    fn test_migration_sql_parsing() {
        let manager = MigrationManager::new();
        
        // 测试SQL语句分割
        let test_sql = "CREATE TABLE test1 (id INTEGER); CREATE TABLE test2 (id INTEGER);";
        let conn = create_test_connection();
        
        // 这应该不会出错
        manager.execute_migration_sql(&conn, test_sql).unwrap();
        
        // 验证两个表都被创建
        let table1_exists: bool = conn.query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='test1'",
            [],
            |row| row.get::<_, i32>(0)
        ).unwrap() > 0;
        
        let table2_exists: bool = conn.query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='test2'",
            [],
            |row| row.get::<_, i32>(0)
        ).unwrap() > 0;
        
        assert!(table1_exists, "test1表应该存在");
        assert!(table2_exists, "test2表应该存在");
    }
}
