use crate::data::models::workflow_execution_environment::{
    WorkflowExecutionEnvironment, EnvironmentType, HealthStatus,
    CreateExecutionEnvironmentRequest, UpdateExecutionEnvironmentRequest, ExecutionEnvironmentFilter
};
use crate::infrastructure::database::Database;

use anyhow::{Result, anyhow};
use chrono::{DateTime, Utc};
use rusqlite::Row;
use std::sync::Arc;
use tracing::{debug, info, error, warn};

/// 工作流执行环境数据仓库
/// 遵循 Tauri 开发规范的仓库模式设计
#[derive(Clone)]
pub struct WorkflowExecutionEnvironmentRepository {
    database: Arc<Database>,
}

impl WorkflowExecutionEnvironmentRepository {
    /// 创建新的工作流执行环境仓库实例
    pub fn new(database: Arc<Database>) -> Self {
        Self { database }
    }

    /// 创建工作流执行环境
    pub fn create(&self, request: &CreateExecutionEnvironmentRequest) -> Result<i64> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self.database.acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;

        // 序列化JSON字段
        let connection_config_json = request.connection_config_json.as_ref()
            .map(|config| serde_json::to_string(config))
            .transpose()
            .map_err(|e| anyhow!("序列化connection_config_json失败: {}", e))?;
        
        let supported_workflow_types_json = serde_json::to_string(&request.supported_workflow_types)
            .map_err(|e| anyhow!("序列化supported_workflow_types失败: {}", e))?;
        
        let metadata_json = request.metadata_json.as_ref()
            .map(|metadata| serde_json::to_string(metadata))
            .transpose()
            .map_err(|e| anyhow!("序列化metadata失败: {}", e))?;
        
        let tags_json = request.tags.as_ref()
            .map(|tags| serde_json::to_string(tags))
            .transpose()
            .map_err(|e| anyhow!("序列化tags失败: {}", e))?;

        let environment_type_str = serde_json::to_string(&request.environment_type)
            .map_err(|e| anyhow!("序列化environment_type失败: {}", e))?;

        let now = Utc::now().to_rfc3339();

        let _result = conn.execute(
            "INSERT INTO workflow_execution_environments (
                name, type, description,
                base_url, api_key, connection_config_json,
                supported_workflow_types, max_concurrent_jobs, priority,
                is_active, is_available, health_status,
                max_memory_mb, max_execution_time_seconds,
                metadata_json, tags,
                created_at, updated_at
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18)",
            [
                &request.name as &dyn rusqlite::ToSql,
                &environment_type_str,
                &request.description,
                &request.base_url,
                &request.api_key,
                &connection_config_json,
                &supported_workflow_types_json,
                &request.max_concurrent_jobs.unwrap_or(1),
                &request.priority.unwrap_or(0),
                &true, // is_active 默认为true
                &true, // is_available 默认为true
                &serde_json::to_string(&HealthStatus::Unknown).unwrap(),
                &request.max_memory_mb,
                &request.max_execution_time_seconds,
                &metadata_json,
                &tags_json,
                &now,
                &now,
            ],
        ).map_err(|e| anyhow!("插入工作流执行环境失败: {}", e))?;

        let id = conn.last_insert_rowid();
        info!("成功创建工作流执行环境，ID: {}", id);
        Ok(id)
    }

    /// 根据ID获取工作流执行环境
    pub fn find_by_id(&self, id: i64) -> Result<Option<WorkflowExecutionEnvironment>> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self.database.acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;

        let mut stmt = conn.prepare(
            "SELECT id, name, type, description,
                    base_url, api_key, connection_config_json,
                    supported_workflow_types, max_concurrent_jobs, priority,
                    is_active, is_available, last_health_check, health_status,
                    average_response_time_ms, success_rate, total_executions, failed_executions,
                    max_memory_mb, max_execution_time_seconds,
                    metadata_json, tags,
                    created_at, updated_at
             FROM workflow_execution_environments WHERE id = ?1"
        ).map_err(|e| anyhow!("准备查询语句失败: {}", e))?;

        let environment_iter = stmt.query_map([id], |row| self.row_to_environment(row))
            .map_err(|e| anyhow!("执行查询失败: {}", e))?;

        for environment in environment_iter {
            return Ok(Some(environment.map_err(|e| anyhow!("解析查询结果失败: {}", e))?));
        }

        Ok(None)
    }

    /// 获取所有工作流执行环境（支持筛选）
    pub fn find_all(&self, filter: Option<&ExecutionEnvironmentFilter>) -> Result<Vec<WorkflowExecutionEnvironment>> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self.database.acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;

        let (sql, params) = self.build_query_sql(filter);
        
        let mut stmt = conn.prepare(&sql)
            .map_err(|e| anyhow!("准备查询语句失败: {}", e))?;

        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
        let environment_iter = stmt.query_map(param_refs.as_slice(), |row| self.row_to_environment(row))
            .map_err(|e| anyhow!("执行查询失败: {}", e))?;

        let mut environments = Vec::new();
        for environment in environment_iter {
            environments.push(environment.map_err(|e| anyhow!("解析查询结果失败: {}", e))?);
        }

        debug!("查询到 {} 个工作流执行环境", environments.len());
        Ok(environments)
    }

    /// 获取可用的执行环境（按优先级排序）
    pub fn find_available_environments(&self, workflow_type: Option<&str>) -> Result<Vec<WorkflowExecutionEnvironment>> {
        use tracing::{info, warn, debug};

        info!("查找可用的执行环境，工作流类型: {:?}", workflow_type);

        let mut filter = ExecutionEnvironmentFilter {
            is_active: Some(true),
            is_available: Some(true),
            // 允许健康状态为 Healthy 或 Unknown 的环境（Unknown 通常表示还未进行健康检查）
            // 只排除明确不健康(Unhealthy)或维护中(Maintenance)的环境
            health_status: None, // 我们将在后续手动过滤
            ..Default::default()
        };

        if let Some(wf_type) = workflow_type {
            info!("设置工作流类型过滤器: '{}'", wf_type);
            filter.supported_workflow_type = Some(wf_type.to_string());
        }

        debug!("执行环境查询过滤器: {:?}", filter);
        let mut environments = self.find_all(Some(&filter))?;

        info!("找到 {} 个匹配过滤条件的执行环境", environments.len());

        // 手动过滤健康状态：只排除明确不健康或维护中的环境
        // 允许 Healthy 和 Unknown 状态的环境通过
        environments.retain(|env| {
            match env.health_status {
                HealthStatus::Unhealthy | HealthStatus::Maintenance => {
                    debug!("排除不健康的环境: {} (状态: {:?})", env.name, env.health_status);
                    false
                }
                HealthStatus::Healthy | HealthStatus::Unknown => {
                    debug!("保留可用的环境: {} (状态: {:?})", env.name, env.health_status);
                    true
                }
            }
        });

        info!("健康状态过滤后剩余 {} 个执行环境", environments.len());

        // 如果没有找到环境，尝试不带工作流类型过滤器查询所有环境进行调试
        if environments.is_empty() && workflow_type.is_some() {
            warn!("没有找到支持工作流类型 '{}' 的环境，查询所有环境进行调试", workflow_type.unwrap());
            let all_environments = self.find_all(None)?;
            warn!("数据库中共有 {} 个执行环境:", all_environments.len());

            for env in &all_environments {
                warn!("  环境: {} (ID: {:?})", env.name, env.id);
                warn!("    是否活跃: {}", env.is_active);
                warn!("    是否可用: {}", env.is_available);
                warn!("    健康状态: {:?}", env.health_status);
                warn!("    支持的工作流类型: {:?}", env.supported_workflow_types);

                // 检查是否包含目标工作流类型
                let contains_type = env.supported_workflow_types.iter()
                    .any(|t| t == workflow_type.unwrap());
                warn!("    是否包含目标类型 '{}': {}", workflow_type.unwrap(), contains_type);
            }
        }

        // 按优先级降序排序
        environments.sort_by(|a, b| b.priority.cmp(&a.priority));

        info!("返回 {} 个按优先级排序的可用执行环境", environments.len());
        Ok(environments)
    }

    /// 更新工作流执行环境
    pub fn update(&self, id: i64, request: &UpdateExecutionEnvironmentRequest) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self.database.acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;

        // 构建动态更新SQL
        let mut set_clauses = Vec::new();
        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();

        if let Some(name) = &request.name {
            set_clauses.push("name = ?");
            params.push(Box::new(name.clone()));
        }

        if let Some(description) = &request.description {
            set_clauses.push("description = ?");
            params.push(Box::new(description.clone()));
        }

        if let Some(base_url) = &request.base_url {
            set_clauses.push("base_url = ?");
            params.push(Box::new(base_url.clone()));
        }

        if let Some(api_key) = &request.api_key {
            set_clauses.push("api_key = ?");
            params.push(Box::new(api_key.clone()));
        }

        if let Some(connection_config_json) = &request.connection_config_json {
            let json_str = serde_json::to_string(connection_config_json)
                .map_err(|e| anyhow!("序列化connection_config_json失败: {}", e))?;
            set_clauses.push("connection_config_json = ?");
            params.push(Box::new(json_str));
        }

        if let Some(supported_workflow_types) = &request.supported_workflow_types {
            let json_str = serde_json::to_string(supported_workflow_types)
                .map_err(|e| anyhow!("序列化supported_workflow_types失败: {}", e))?;
            set_clauses.push("supported_workflow_types = ?");
            params.push(Box::new(json_str));
        }

        if let Some(max_concurrent_jobs) = request.max_concurrent_jobs {
            set_clauses.push("max_concurrent_jobs = ?");
            params.push(Box::new(max_concurrent_jobs));
        }

        if let Some(priority) = request.priority {
            set_clauses.push("priority = ?");
            params.push(Box::new(priority));
        }

        if let Some(is_active) = request.is_active {
            set_clauses.push("is_active = ?");
            params.push(Box::new(is_active));
        }

        if let Some(max_memory_mb) = request.max_memory_mb {
            set_clauses.push("max_memory_mb = ?");
            params.push(Box::new(max_memory_mb));
        }

        if let Some(max_execution_time_seconds) = request.max_execution_time_seconds {
            set_clauses.push("max_execution_time_seconds = ?");
            params.push(Box::new(max_execution_time_seconds));
        }

        if let Some(metadata_json) = &request.metadata_json {
            let json_str = serde_json::to_string(metadata_json)
                .map_err(|e| anyhow!("序列化metadata_json失败: {}", e))?;
            set_clauses.push("metadata_json = ?");
            params.push(Box::new(json_str));
        }

        if let Some(tags) = &request.tags {
            let json_str = serde_json::to_string(tags)
                .map_err(|e| anyhow!("序列化tags失败: {}", e))?;
            set_clauses.push("tags = ?");
            params.push(Box::new(json_str));
        }

        if set_clauses.is_empty() {
            info!("没有需要更新的字段，跳过更新，ID: {}", id);
            return Ok(()); // 没有需要更新的字段
        }

        // 添加updated_at字段
        set_clauses.push("updated_at = ?");
        params.push(Box::new(Utc::now().to_rfc3339()));

        // 添加WHERE条件的ID参数
        params.push(Box::new(id));

        let sql = format!(
            "UPDATE workflow_execution_environments SET {} WHERE id = ?",
            set_clauses.join(", ")
        );

        info!("执行更新SQL: {}, 更新字段: {:?}", sql, set_clauses);

        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();

        let rows_affected = conn.execute(&sql, param_refs.as_slice())
            .map_err(|e| anyhow!("更新工作流执行环境失败: {}", e))?;

        info!("成功更新工作流执行环境，ID: {}, 影响行数: {}", id, rows_affected);
        Ok(())
    }

    /// 删除工作流执行环境
    pub fn delete(&self, id: i64) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self.database.acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;

        let affected_rows = conn.execute("DELETE FROM workflow_execution_environments WHERE id = ?1", [id])
            .map_err(|e| anyhow!("删除工作流执行环境失败: {}", e))?;

        if affected_rows == 0 {
            return Err(anyhow!("工作流执行环境不存在，ID: {}", id));
        }

        info!("成功删除工作流执行环境，ID: {}", id);
        Ok(())
    }

    /// 更新健康状态
    pub fn update_health_status(&self, id: i64, health_status: HealthStatus, response_time_ms: Option<i32>) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self.database.acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;

        let health_status_str = serde_json::to_string(&health_status)
            .map_err(|e| anyhow!("序列化health_status失败: {}", e))?;

        let now = Utc::now().to_rfc3339();

        let mut sql = "UPDATE workflow_execution_environments SET
                       health_status = ?,
                       last_health_check = ?,
                       is_available = ?,
                       updated_at = ?".to_string();
        let mut params: Vec<Box<dyn rusqlite::ToSql>> = vec![
            Box::new(health_status_str),
            Box::new(now.clone()),
            Box::new(matches!(health_status, HealthStatus::Healthy)),
            Box::new(now),
        ];

        if let Some(response_time) = response_time_ms {
            sql.push_str(", average_response_time_ms = ?");
            params.push(Box::new(response_time));
        }

        sql.push_str(" WHERE id = ?");
        params.push(Box::new(id));

        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();

        conn.execute(&sql, param_refs.as_slice())
            .map_err(|e| anyhow!("更新健康状态失败: {}", e))?;

        debug!("更新环境健康状态，ID: {}, 状态: {:?}", id, health_status);
        Ok(())
    }

    /// 更新性能统计
    pub fn update_performance_stats(&self, id: i64, success: bool, duration_seconds: Option<i32>) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self.database.acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;

        // 获取当前统计数据
        let mut stmt = conn.prepare(
            "SELECT total_executions, failed_executions, success_rate, average_response_time_ms
             FROM workflow_execution_environments WHERE id = ?1"
        ).map_err(|e| anyhow!("准备查询语句失败: {}", e))?;

        let (total_executions, failed_executions, _current_success_rate, current_avg_response_time): (i32, i32, f64, Option<i32>) =
            stmt.query_row([id], |row| {
                Ok((
                    row.get("total_executions")?,
                    row.get("failed_executions")?,
                    row.get("success_rate")?,
                    row.get("average_response_time_ms")?,
                ))
            }).map_err(|e| anyhow!("查询当前统计数据失败: {}", e))?;

        // 计算新的统计数据
        let new_total_executions = total_executions + 1;
        let new_failed_executions = if success { failed_executions } else { failed_executions + 1 };
        let new_success_rate = (new_total_executions - new_failed_executions) as f64 / new_total_executions as f64;

        let mut sql = "UPDATE workflow_execution_environments SET
                       total_executions = ?,
                       failed_executions = ?,
                       success_rate = ?,
                       updated_at = ?".to_string();
        let mut params: Vec<Box<dyn rusqlite::ToSql>> = vec![
            Box::new(new_total_executions),
            Box::new(new_failed_executions),
            Box::new(new_success_rate),
            Box::new(Utc::now().to_rfc3339()),
        ];

        // 更新平均响应时间（如果提供了duration）
        if let Some(duration_ms) = duration_seconds.map(|s| s * 1000) {
            let new_avg_response_time = if let Some(current_avg) = current_avg_response_time {
                // 计算移动平均
                ((current_avg * total_executions) + duration_ms) / new_total_executions
            } else {
                duration_ms
            };
            sql.push_str(", average_response_time_ms = ?");
            params.push(Box::new(new_avg_response_time));
        }

        sql.push_str(" WHERE id = ?");
        params.push(Box::new(id));

        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();

        conn.execute(&sql, param_refs.as_slice())
            .map_err(|e| anyhow!("更新性能统计失败: {}", e))?;

        debug!("更新环境性能统计，ID: {}, 成功: {}, 新成功率: {:.2}", id, success, new_success_rate);
        Ok(())
    }

    /// 健康检查所有环境
    pub async fn health_check_all(&self) -> Result<Vec<(i64, HealthStatus)>> {
        let environments = self.find_all(Some(&ExecutionEnvironmentFilter {
            is_active: Some(true),
            ..Default::default()
        }))?;

        let mut results = Vec::new();

        for env in environments {
            let health_status = self.perform_health_check(&env).await;
            if let Some(id) = env.id {
                self.update_health_status(id, health_status.clone(), None)?;
                results.push((id, health_status));
            }
        }

        Ok(results)
    }

    /// 执行单个环境的健康检查
    async fn perform_health_check(&self, environment: &WorkflowExecutionEnvironment) -> HealthStatus {
        // 这里可以实现实际的健康检查逻辑
        // 例如发送HTTP请求到环境的健康检查端点
        match environment.environment_type {
            EnvironmentType::LocalComfyui => {
                // 对ComfyUI环境进行健康检查
                self.check_comfyui_health(&environment.base_url).await
            }
            _ => {
                // 对其他类型的环境进行通用健康检查
                self.check_generic_health(&environment.base_url).await
            }
        }
    }

    /// 检查ComfyUI环境健康状态
    async fn check_comfyui_health(&self, base_url: &str) -> HealthStatus {
        info!("检查ComfyUI环境健康状态: {}", base_url);

        // 尝试多个可能的健康检查端点
        let endpoints = vec![
            "/system_stats",      // 标准ComfyUI端点
            "/",                  // API根端点
            "/api/servers/status" // 备用端点
        ];

        for endpoint in endpoints {
            let url = format!("{}{}", base_url.trim_end_matches('/'), endpoint);
            debug!("尝试健康检查端点: {}", url);

            match reqwest::Client::new()
                .get(&url)
                .timeout(std::time::Duration::from_secs(10))
                .send()
                .await
            {
                Ok(response) if response.status().is_success() => {
                    info!("ComfyUI环境健康检查成功: {} (端点: {})", base_url, endpoint);
                    return HealthStatus::Healthy;
                }
                Ok(response) => {
                    debug!("端点 {} 响应状态: {}", endpoint, response.status());
                }
                Err(e) => {
                    debug!("端点 {} 连接失败: {}", endpoint, e);
                }
            }
        }

        warn!("ComfyUI环境健康检查失败: {}", base_url);
        HealthStatus::Unhealthy
    }

    /// 检查通用环境健康状态
    async fn check_generic_health(&self, base_url: &str) -> HealthStatus {
        // 实现通用的健康检查
        match reqwest::get(base_url).await {
            Ok(response) if response.status().is_success() => HealthStatus::Healthy,
            Ok(_) => HealthStatus::Unhealthy,
            Err(_) => HealthStatus::Unhealthy,
        }
    }

    /// 构建查询SQL和参数
    fn build_query_sql(&self, filter: Option<&ExecutionEnvironmentFilter>) -> (String, Vec<Box<dyn rusqlite::ToSql>>) {
        let mut sql = "SELECT id, name, type, description,
                             base_url, api_key, connection_config_json,
                             supported_workflow_types, max_concurrent_jobs, priority,
                             is_active, is_available, last_health_check, health_status,
                             average_response_time_ms, success_rate, total_executions, failed_executions,
                             max_memory_mb, max_execution_time_seconds,
                             metadata_json, tags,
                             created_at, updated_at
                      FROM workflow_execution_environments".to_string();

        let mut where_clauses = Vec::new();
        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();

        if let Some(filter) = filter {
            if let Some(environment_type) = &filter.environment_type {
                let type_str = serde_json::to_string(environment_type).unwrap_or_default();
                where_clauses.push("type = ?");
                params.push(Box::new(type_str));
            }

            if let Some(is_active) = filter.is_active {
                where_clauses.push("is_active = ?");
                params.push(Box::new(is_active));
            }

            if let Some(is_available) = filter.is_available {
                where_clauses.push("is_available = ?");
                params.push(Box::new(is_available));
            }

            if let Some(health_status) = &filter.health_status {
                let status_str = serde_json::to_string(health_status).unwrap_or_default();
                where_clauses.push("health_status = ?");
                params.push(Box::new(status_str));
            }

            if let Some(supported_workflow_type) = &filter.supported_workflow_type {
                where_clauses.push("supported_workflow_types LIKE ?");
                let search_pattern = format!("%\"{}\"%%", supported_workflow_type);
                tracing::debug!("工作流类型查询模式: '{}' -> LIKE '{}'", supported_workflow_type, search_pattern);
                params.push(Box::new(search_pattern));
            }


        }

        if !where_clauses.is_empty() {
            sql.push_str(" WHERE ");
            sql.push_str(&where_clauses.join(" AND "));
        }

        // 默认按优先级降序，然后按创建时间倒序排列
        sql.push_str(" ORDER BY priority DESC, created_at DESC");

        (sql, params)
    }

    /// 安全地解析可选JSON字段的辅助函数
    fn parse_optional_json_field(&self, row: &Row, field_name: &str) -> rusqlite::Result<Option<serde_json::Value>> {
        match row.get::<_, Option<String>>(field_name) {
            Ok(Some(json_str)) => {
                if json_str.trim().is_empty() {
                    Ok(Some(serde_json::Value::Object(serde_json::Map::new())))
                } else {
                    match serde_json::from_str(&json_str) {
                        Ok(parsed_value) => {
                            debug!("Successfully parsed optional {}", field_name);
                            Ok(Some(parsed_value))
                        },
                        Err(e) => {
                            error!("JSON parsing failed for optional {}: '{}', error: {}", field_name, json_str, e);
                            // 如果解析失败，返回空对象而不是错误
                            Ok(Some(serde_json::Value::Object(serde_json::Map::new())))
                        }
                    }
                }
            },
            Ok(None) => Ok(None),
            Err(e) => {
                error!("Error getting {}: {}", field_name, e);
                Err(e)
            }
        }
    }

    /// 解析多种时间戳格式的辅助函数
    fn parse_datetime(&self, datetime_str: &str) -> Result<chrono::DateTime<Utc>, Box<dyn std::error::Error + Send + Sync>> {
        use chrono::{DateTime, NaiveDateTime, Utc};

        // 尝试多种时间格式

        // 1. 尝试 RFC3339 格式 (ISO 8601)
        if let Ok(dt) = DateTime::parse_from_rfc3339(datetime_str) {
            return Ok(dt.with_timezone(&Utc));
        }

        // 2. 尝试常见的数据库格式: "YYYY-MM-DD HH:MM:SS"
        if let Ok(naive_dt) = NaiveDateTime::parse_from_str(datetime_str, "%Y-%m-%d %H:%M:%S") {
            return Ok(DateTime::from_naive_utc_and_offset(naive_dt, Utc));
        }

        // 3. 尝试带毫秒的格式: "YYYY-MM-DD HH:MM:SS.fff"
        if let Ok(naive_dt) = NaiveDateTime::parse_from_str(datetime_str, "%Y-%m-%d %H:%M:%S%.f") {
            return Ok(DateTime::from_naive_utc_and_offset(naive_dt, Utc));
        }

        // 4. 尝试 ISO 格式但没有时区: "YYYY-MM-DDTHH:MM:SS"
        if let Ok(naive_dt) = NaiveDateTime::parse_from_str(datetime_str, "%Y-%m-%dT%H:%M:%S") {
            return Ok(DateTime::from_naive_utc_and_offset(naive_dt, Utc));
        }

        // 5. 尝试 ISO 格式带毫秒但没有时区: "YYYY-MM-DDTHH:MM:SS.fff"
        if let Ok(naive_dt) = NaiveDateTime::parse_from_str(datetime_str, "%Y-%m-%dT%H:%M:%S%.f") {
            return Ok(DateTime::from_naive_utc_and_offset(naive_dt, Utc));
        }

        Err(format!("Unable to parse datetime: '{}'", datetime_str).into())
    }

    /// 将数据库行转换为WorkflowExecutionEnvironment对象
    fn row_to_environment(&self, row: &Row) -> rusqlite::Result<WorkflowExecutionEnvironment> {
        let id: Option<i64> = Some(row.get("id")?);
        let name: String = row.get("name")?;

        // 解析基本字段
        let description: Option<String> = row.get("description")?;
        let base_url: String = row.get("base_url")?;
        let api_key: Option<String> = row.get("api_key")?;

        // 解析环境类型
        let type_str: String = row.get("type")?;
        let environment_type: EnvironmentType = match serde_json::from_str(&type_str) {
            Ok(env_type) => env_type,
            Err(_) => {
                // 如果JSON解析失败，尝试直接解析字符串
                match type_str.trim_matches('"') {
                    "local_comfyui" => EnvironmentType::LocalComfyui,
                    _ => {
                        error!("Unknown environment type: {}, defaulting to LocalComfyui", type_str);
                        EnvironmentType::LocalComfyui // 默认值
                    }
                }
            }
        };

        // 解析JSON字段
        let connection_config_json = self.parse_optional_json_field(row, "connection_config_json")?;

        let supported_workflow_types_str: String = row.get("supported_workflow_types")?;
        let supported_workflow_types: Vec<String> = match serde_json::from_str(&supported_workflow_types_str) {
            Ok(types) => types,
            Err(e) => {
                error!("Failed to parse supported_workflow_types: '{}', error: {}", supported_workflow_types_str, e);
                // 返回默认值
                vec!["image_generation".to_string()]
            }
        };

        let max_concurrent_jobs: i32 = row.get("max_concurrent_jobs")?;
        let priority: i32 = row.get("priority")?;
        let is_active: bool = row.get("is_active")?;
        let is_available: bool = row.get("is_available")?;

        // 解析健康检查时间 - 使用多格式时间解析
        let last_health_check: Option<DateTime<Utc>> = match row.get::<_, Option<String>>("last_health_check")? {
            Some(time_str) => {
                match self.parse_datetime(&time_str) {
                    Ok(dt) => Some(dt),
                    Err(e) => {
                        error!("Failed to parse last_health_check: '{}', error: {}", time_str, e);
                        None
                    }
                }
            },
            None => None
        };

        // 解析健康状态
        let health_status_str: String = row.get("health_status")?;
        let health_status: HealthStatus = match serde_json::from_str(&health_status_str) {
            Ok(status) => status,
            Err(_) => {
                // 如果JSON解析失败，尝试直接解析字符串
                match health_status_str.trim_matches('"') {
                    "healthy" => HealthStatus::Healthy,
                    "unhealthy" => HealthStatus::Unhealthy,
                    "unknown" => HealthStatus::Unknown,
                    _ => {
                        error!("Unknown health status: {}", health_status_str);
                        HealthStatus::Unknown // 默认值
                    }
                }
            }
        };

        let average_response_time_ms: Option<i32> = row.get("average_response_time_ms")?;
        let success_rate: f64 = row.get("success_rate")?;
        let total_executions: i32 = row.get("total_executions")?;
        let failed_executions: i32 = row.get("failed_executions")?;
        let max_memory_mb: Option<i32> = row.get("max_memory_mb")?;
        let max_execution_time_seconds: Option<i32> = row.get("max_execution_time_seconds")?;

        // 解析metadata_json - 使用容错解析器
        let metadata_json = self.parse_optional_json_field(row, "metadata_json")?;

        // 解析tags字段
        let tags: Option<Vec<String>> = match row.get::<_, Option<String>>("tags")? {
            Some(tags_str) => {
                if tags_str.trim().is_empty() {
                    None
                } else {
                    match serde_json::from_str(&tags_str) {
                        Ok(tags_vec) => Some(tags_vec),
                        Err(e) => {
                            error!("Failed to parse tags JSON: '{}', error: {}", tags_str, e);
                            None
                        }
                    }
                }
            },
            None => None
        };

        // 解析时间戳 - 使用多格式时间解析
        let created_at_str: String = row.get("created_at")?;
        let created_at = self.parse_datetime(&created_at_str)
            .map_err(|e| {
                error!("解析 created_at 时间戳失败: '{}', error: {}", created_at_str, e);
                rusqlite::Error::FromSqlConversionFailure(
                    0, rusqlite::types::Type::Text, e
                )
            })?;

        let updated_at_str: String = row.get("updated_at")?;
        let updated_at = self.parse_datetime(&updated_at_str)
            .map_err(|e| {
                error!("解析 updated_at 时间戳失败: '{}', error: {}", updated_at_str, e);
                rusqlite::Error::FromSqlConversionFailure(
                    0, rusqlite::types::Type::Text, e
                )
            })?;

        Ok(WorkflowExecutionEnvironment {
            id,
            name,
            environment_type,
            description,
            base_url,
            api_key,
            connection_config_json,
            supported_workflow_types,
            max_concurrent_jobs,
            priority,
            is_active,
            is_available,
            last_health_check,
            health_status,
            average_response_time_ms,
            success_rate,
            total_executions,
            failed_executions,
            max_memory_mb,
            max_execution_time_seconds,
            metadata_json,
            tags,
            created_at,
            updated_at,
        })
    }
}
