use crate::data::models::workflow_template::{
    WorkflowTemplate, WorkflowType, CreateWorkflowTemplateRequest, 
    UpdateWorkflowTemplateRequest, WorkflowTemplateFilter
};
use crate::infrastructure::database::Database;
use crate::infrastructure::tolerant_json_parser::{CachedTolerantJsonParser, ParserConfig, RecoveryStrategy};
use anyhow::{Result, anyhow};
use chrono::{DateTime, Utc};
use rusqlite::Row;
use std::sync::Arc;
use tracing::{debug, info, error};

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

impl WorkflowTemplateRepository {
    /// 创建新的工作流模板仓库实例
    pub fn new(database: Arc<Database>) -> Self {
        // 创建带缓存的容错JSON解析器，配置为适合工作流模板的解析
        let json_parser = CachedTolerantJsonParser::new(Some(ParserConfig {
            max_text_length: 10_000_000, // 10MB限制，适合大型工作流
            enable_comments: true,
            enable_unquoted_keys: true,
            enable_trailing_commas: true,
            timeout_ms: 30000, // 30秒超时
            recovery_strategies: vec![
                RecoveryStrategy::StandardJson,
                RecoveryStrategy::ManualFix,
                RecoveryStrategy::RegexExtract,
                RecoveryStrategy::PartialParse,
            ],
        }), Some(1000)).expect("Failed to create JSON parser");

        Self {
            database,
            json_parser: Arc::new(json_parser),
        }
    }

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

    /// 安全地解析可选JSON字段的辅助函数，使用容错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 {
                    // 使用容错JSON解析器
                    match self.json_parser.parse(&json_str) {
                        Ok((parsed_value, _stats)) => {
                            debug!("Successfully parsed optional {} using tolerant parser", field_name);
                            Ok(Some(parsed_value))
                        },
                        Err(e) => {
                            error!("Tolerant 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())
    }

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

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

        // 序列化JSON字段
        let comfyui_workflow_json = serde_json::to_string(&request.comfyui_workflow_json)
            .map_err(|e| anyhow!("序列化comfyui_workflow_json失败: {}", e))?;
        
        let ui_config_json = serde_json::to_string(&request.ui_config_json)
            .map_err(|e| anyhow!("序列化ui_config_json失败: {}", e))?;
        
        let execution_config_json = request.execution_config_json.as_ref()
            .map(|config| serde_json::to_string(config))
            .transpose()
            .map_err(|e| anyhow!("序列化execution_config_json失败: {}", e))?;
        
        let input_schema_json = request.input_schema_json.as_ref()
            .map(|schema| serde_json::to_string(schema))
            .transpose()
            .map_err(|e| anyhow!("序列化input_schema_json失败: {}", e))?;
        
        let output_schema_json = request.output_schema_json.as_ref()
            .map(|schema| serde_json::to_string(schema))
            .transpose()
            .map_err(|e| anyhow!("序列化output_schema_json失败: {}", e))?;
        
        let tags_json = request.tags.as_ref()
            .map(|tags| serde_json::to_string(tags))
            .transpose()
            .map_err(|e| anyhow!("序列化tags失败: {}", e))?;

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

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

        let result = conn.execute(
            "INSERT INTO workflow_templates (
                name, base_name, version, type, description,
                comfyui_workflow_json, ui_config_json, execution_config_json,
                input_schema_json, output_schema_json,
                is_active, is_published, tags, category, author,
                created_at, updated_at
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
            [
                &request.name as &dyn rusqlite::ToSql,
                &request.base_name,
                &request.version.as_str(),
                &workflow_type_str,
                &request.description,
                &comfyui_workflow_json,
                &ui_config_json,
                &execution_config_json,
                &input_schema_json,
                &output_schema_json,
                &true,  // is_active - 新创建的模板默认为活跃状态
                &false, // is_published - 新创建的模板默认为未发布状态
                &tags_json,
                &request.category,
                &request.author,
                &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<WorkflowTemplate>> {
        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, base_name, version, type, description,
                    comfyui_workflow_json, ui_config_json, execution_config_json,
                    input_schema_json, output_schema_json,
                    is_active, is_published, tags, category, author,
                    created_at, updated_at
             FROM workflow_templates WHERE id = ?1"
        ).map_err(|e| anyhow!("准备查询语句失败: {}", e))?;

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

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

        Ok(None)
    }

    /// 获取所有工作流模板（支持筛选）
    pub fn find_all(&self, filter: Option<&WorkflowTemplateFilter>) -> Result<Vec<WorkflowTemplate>> {
        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 template_iter = stmt.query_map(rusqlite::params_from_iter(params.iter()), |row| {
            self.row_to_template(row)
        }).map_err(|e| anyhow!("执行查询失败: {}", e))?;

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

        debug!("查询到 {} 个工作流模板", templates.len());
        Ok(templates)
    }

    /// 根据base_name查找工作流模板
    pub fn find_by_base_name(&self, base_name: &str) -> Result<Vec<WorkflowTemplate>> {
        let filter = WorkflowTemplateFilter {
            base_name: Some(base_name.to_string()),
            ..Default::default()
        };
        self.find_all(Some(&filter))
    }

    /// 获取指定base_name的最新版本
    pub fn get_latest_version(&self, base_name: &str) -> Result<Option<WorkflowTemplate>> {
        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, base_name, version, type, description,
                    comfyui_workflow_json, ui_config_json, execution_config_json,
                    input_schema_json, output_schema_json,
                    is_active, is_published, tags, category, author,
                    created_at, updated_at
             FROM workflow_templates 
             WHERE base_name = ?1 
             ORDER BY version DESC, created_at DESC 
             LIMIT 1"
        ).map_err(|e| anyhow!("准备查询语句失败: {}", e))?;

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

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

        Ok(None)
    }

    /// 更新工作流模板
    pub fn update(&self, id: i64, request: &UpdateWorkflowTemplateRequest) -> 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(comfyui_workflow_json) = &request.comfyui_workflow_json {
            let json_str = serde_json::to_string(comfyui_workflow_json)
                .map_err(|e| anyhow!("序列化comfyui_workflow_json失败: {}", e))?;
            set_clauses.push("comfyui_workflow_json = ?");
            params.push(Box::new(json_str));
        }

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

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

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

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

        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 let Some(category) = &request.category {
            set_clauses.push("category = ?");
            params.push(Box::new(category.clone()));
        }

        if set_clauses.is_empty() {
            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_templates SET {} WHERE id = ?",
            set_clauses.join(", ")
        );

        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))?;

        info!("成功更新工作流模板，ID: {}", id);
        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_templates WHERE id = ?1", [id])
            .map_err(|e| anyhow!("删除工作流模板失败: {}", e))?;

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

        info!("成功删除工作流模板，ID: {}", id);
        Ok(())
    }

    /// 分页查询工作流模板
    pub fn find_with_pagination(
        &self,
        filter: Option<&WorkflowTemplateFilter>,
        page: u32,
        page_size: u32
    ) -> Result<(Vec<WorkflowTemplate>, u32)> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        // 首先获取总数
        let (count_sql, count_params) = self.build_count_sql(filter);
        let mut stmt = conn.prepare(&count_sql)
            .map_err(|e| anyhow!("准备计数查询语句失败: {}", e))?;

        let total_count: u32 = stmt.query_row(rusqlite::params_from_iter(count_params.iter()), |row| {
            Ok(row.get::<_, i64>(0)? as u32)
        }).map_err(|e| anyhow!("执行计数查询失败: {}", e))?;

        // 然后获取分页数据
        let (sql, mut params) = self.build_query_sql(filter);
        let offset = page * page_size;
        let paginated_sql = format!("{} LIMIT ? OFFSET ?", sql);

        params.push(Box::new(page_size));
        params.push(Box::new(offset));

        let mut stmt = conn.prepare(&paginated_sql)
            .map_err(|e| anyhow!("准备分页查询语句失败: {}", e))?;

        let template_iter = stmt.query_map(rusqlite::params_from_iter(params.iter()), |row| self.row_to_template(row))
            .map_err(|e| anyhow!("执行分页查询失败: {}", e))?;

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

        debug!("分页查询到 {} 个工作流模板，总数: {}", templates.len(), total_count);
        Ok((templates, total_count))
    }

    /// 构建查询SQL和参数
    fn build_query_sql(&self, filter: Option<&WorkflowTemplateFilter>) -> (String, Vec<Box<dyn rusqlite::ToSql>>) {
        let mut sql = "SELECT id, name, base_name, version, type, description,
                             comfyui_workflow_json, ui_config_json, execution_config_json,
                             input_schema_json, output_schema_json,
                             is_active, is_published, tags, category, author,
                             created_at, updated_at
                      FROM workflow_templates".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(base_name) = &filter.base_name {
                where_clauses.push("base_name = ?");
                params.push(Box::new(base_name.clone()));
            }

            if let Some(workflow_type) = &filter.workflow_type {
                let type_str = serde_json::to_string(workflow_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_published) = filter.is_published {
                where_clauses.push("is_published = ?");
                params.push(Box::new(is_published));
            }

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

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

            if let Some(search_term) = &filter.search_term {
                where_clauses.push("(name LIKE ? OR description LIKE ?)");
                let search_pattern = format!("%{}%", search_term);
                params.push(Box::new(search_pattern.clone()));
                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 created_at DESC");

        (sql, params)
    }

    /// 构建计数查询SQL和参数
    fn build_count_sql(&self, filter: Option<&WorkflowTemplateFilter>) -> (String, Vec<Box<dyn rusqlite::ToSql>>) {
        let mut sql = "SELECT COUNT(*) FROM workflow_templates".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(base_name) = &filter.base_name {
                where_clauses.push("base_name = ?");
                params.push(Box::new(base_name.clone()));
            }

            if let Some(workflow_type) = &filter.workflow_type {
                let type_str = serde_json::to_string(workflow_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_published) = filter.is_published {
                where_clauses.push("is_published = ?");
                params.push(Box::new(is_published));
            }

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

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

            if let Some(search_term) = &filter.search_term {
                where_clauses.push("(name LIKE ? OR description LIKE ?)");
                let search_pattern = format!("%{}%", search_term);
                params.push(Box::new(search_pattern.clone()));
                params.push(Box::new(search_pattern));
            }
        }

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

        (sql, params)
    }

    /// 将数据库行转换为WorkflowTemplate对象
    fn row_to_template(&self, row: &Row) -> rusqlite::Result<WorkflowTemplate> {
        // 解析基本字段
        let id: Option<i64> = Some(row.get("id")?);

        let name: String = row.get::<_, Option<String>>("name")?.unwrap_or_default();
        if name.is_empty() {
            error!("Template name is empty or null");
            return Err(rusqlite::Error::InvalidColumnType(0, "name".to_string(), rusqlite::types::Type::Text));
        }

        let base_name: String = row.get::<_, Option<String>>("base_name")?.unwrap_or_default();
        let version: String = row.get::<_, Option<String>>("version")?.unwrap_or("1.0.0".to_string());
        let type_str: String = row.get::<_, Option<String>>("type")?.unwrap_or("image_generation".to_string());
        let workflow_type: WorkflowType = WorkflowType::from(type_str);
        let description: Option<String> = row.get("description")?;

        // 解析JSON字段
        let comfyui_workflow_json = self.parse_json_field(row, "comfyui_workflow_json")?;
        let ui_config_json = self.parse_json_field(row, "ui_config_json")?;
        let execution_config_json = self.parse_optional_json_field(row, "execution_config_json")?;
        let input_schema_json = self.parse_optional_json_field(row, "input_schema_json")?;
        let output_schema_json = self.parse_optional_json_field(row, "output_schema_json")?;

        // 解析布尔字段
        let is_active: bool = row.get("is_active")?;
        let is_published: bool = row.get("is_published")?;

        // 解析tags字段
        let tags: Option<Vec<String>> = match row.get::<_, Option<String>>("tags")? {
            Some(tags_str) => {
                if tags_str.trim().is_empty() {
                    None
                } else {
                    // 使用容错JSON解析器解析tags
                    match self.json_parser.parse(&tags_str) {
                        Ok((parsed_value, _stats)) => {
                            match parsed_value.as_array() {
                                Some(arr) => {
                                    let string_tags: Vec<String> = arr.iter()
                                        .map(|v| v.as_str().unwrap_or("").to_string())
                                        .filter(|s| !s.is_empty())
                                        .collect();
                                    Some(string_tags)
                                },
                                None => {
                                    error!("Tags field is not an array: {}", tags_str);
                                    None
                                }
                            }
                        },
                        Err(e) => {
                            error!("Failed to parse tags field: '{}', error: {}", tags_str, e);
                            None
                        }
                    }
                }
            },
            None => None
        };

        let category: Option<String> = row.get("category")?;
        let author: Option<String> = row.get("author")?;

        // 解析时间戳 - 处理多种时间格式
        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(WorkflowTemplate {
            id,
            name,
            base_name,
            version,
            workflow_type,
            description,
            comfyui_workflow_json,
            ui_config_json,
            execution_config_json,
            input_schema_json,
            output_schema_json,
            is_active,
            is_published,
            tags,
            category,
            author,
            created_at,
            updated_at,
        })
    }

    /// 导出工作流模板到JSON
    pub fn export_template(&self, id: i64) -> Result<String> {
        let template = self.find_by_id(id)?
            .ok_or_else(|| anyhow!("工作流模板不存在，ID: {}", id))?;

        let export_data = serde_json::json!({
            "template": template,
            "export_version": "1.0",
            "exported_at": chrono::Utc::now().to_rfc3339(),
            "exported_by": "MixVideo AI Workflow System"
        });

        let json_str = serde_json::to_string_pretty(&export_data)
            .map_err(|e| anyhow!("序列化模板失败: {}", e))?;

        info!("成功导出工作流模板，ID: {}", id);
        Ok(json_str)
    }

    /// 从JSON导入工作流模板
    pub fn import_template(&self, json_data: &str, override_name: Option<String>) -> Result<i64> {
        let import_data: serde_json::Value = serde_json::from_str(json_data)
            .map_err(|e| anyhow!("解析导入数据失败: {}", e))?;

        let template_data = import_data.get("template")
            .ok_or_else(|| anyhow!("导入数据中缺少template字段"))?;

        let mut template: WorkflowTemplate = serde_json::from_value(template_data.clone())
            .map_err(|e| anyhow!("解析模板数据失败: {}", e))?;

        // 重置ID，因为这是新导入的模板
        template.id = None;

        // 如果提供了新名称，使用新名称
        if let Some(new_name) = override_name {
            template.name = new_name;
        } else {
            // 否则在名称后添加"(导入)"后缀
            template.name = format!("{} (导入)", template.name);
        }

        // 更新时间戳
        let now = chrono::Utc::now();
        template.created_at = now;
        template.updated_at = now;

        // 创建导入请求
        let create_request = CreateWorkflowTemplateRequest {
            name: template.name,
            base_name: template.base_name,
            version: template.version,
            workflow_type: template.workflow_type,
            description: template.description,
            comfyui_workflow_json: template.comfyui_workflow_json,
            ui_config_json: template.ui_config_json,
            execution_config_json: template.execution_config_json,
            input_schema_json: template.input_schema_json,
            output_schema_json: template.output_schema_json,
            tags: template.tags,
            category: template.category,
            author: template.author,
        };

        let template_id = self.create(&create_request)?;
        info!("成功导入工作流模板，新ID: {}", template_id);
        Ok(template_id)
    }

    /// 复制工作流模板
    pub fn copy_template(&self, id: i64, new_name: String, new_base_name: Option<String>) -> Result<i64> {
        let template = self.find_by_id(id)?
            .ok_or_else(|| anyhow!("工作流模板不存在，ID: {}", id))?;

        let create_request = CreateWorkflowTemplateRequest {
            name: new_name,
            base_name: new_base_name.unwrap_or_else(|| format!("{}_copy", template.base_name)),
            version: "1.0".to_string(), // 复制的模板从1.0版本开始
            workflow_type: template.workflow_type,
            description: template.description.map(|desc| format!("{} (复制)", desc)),
            comfyui_workflow_json: template.comfyui_workflow_json,
            ui_config_json: template.ui_config_json,
            execution_config_json: template.execution_config_json,
            input_schema_json: template.input_schema_json,
            output_schema_json: template.output_schema_json,
            tags: template.tags,
            category: template.category,
            author: template.author,
        };

        let new_template_id = self.create(&create_request)?;
        info!("成功复制工作流模板，原ID: {}，新ID: {}", id, new_template_id);
        Ok(new_template_id)
    }

    /// 验证工作流模板
    pub fn validate_template(&self, template: &WorkflowTemplate) -> Result<Vec<String>> {
        let mut warnings = Vec::new();

        // 验证基本字段
        if template.name.trim().is_empty() {
            return Err(anyhow!("模板名称不能为空"));
        }

        if template.base_name.trim().is_empty() {
            return Err(anyhow!("模板基础名称不能为空"));
        }

        if template.version.trim().is_empty() {
            return Err(anyhow!("模板版本不能为空"));
        }

        // 验证JSON字段格式
        if !template.comfyui_workflow_json.is_object() {
            warnings.push("ComfyUI工作流JSON不是有效的对象格式".to_string());
        }

        if !template.ui_config_json.is_object() {
            warnings.push("UI配置JSON不是有效的对象格式".to_string());
        }

        // 验证执行配置
        if let Some(exec_config) = &template.execution_config_json {
            if let Some(timeout) = exec_config.get("timeout_seconds") {
                if let Some(timeout_val) = timeout.as_i64() {
                    if timeout_val <= 0 {
                        warnings.push("执行超时时间必须大于0".to_string());
                    }
                    if timeout_val > 3600 {
                        warnings.push("执行超时时间超过1小时，可能过长".to_string());
                    }
                }
            }
        }

        // 验证输入输出模式
        if let Some(input_schema) = &template.input_schema_json {
            if !input_schema.is_object() {
                warnings.push("输入模式JSON不是有效的对象格式".to_string());
            }
        }

        if let Some(output_schema) = &template.output_schema_json {
            if !output_schema.is_object() {
                warnings.push("输出模式JSON不是有效的对象格式".to_string());
            }
        }

        // 验证标签
        if let Some(tags) = &template.tags {
            if tags.len() > 10 {
                warnings.push("标签数量过多，建议不超过10个".to_string());
            }
            for tag in tags {
                if tag.len() > 50 {
                    warnings.push(format!("标签'{}'过长，建议不超过50个字符", tag));
                }
            }
        }

        info!("模板验证完成，发现 {} 个警告", warnings.len());
        Ok(warnings)
    }

    /// 批量导出工作流模板
    pub fn batch_export(&self, ids: &[i64]) -> Result<String> {
        let mut templates = Vec::new();

        for &id in ids {
            if let Some(template) = self.find_by_id(id)? {
                templates.push(template);
            } else {
                return Err(anyhow!("工作流模板不存在，ID: {}", id));
            }
        }

        let export_data = serde_json::json!({
            "templates": templates,
            "export_version": "1.0",
            "exported_at": chrono::Utc::now().to_rfc3339(),
            "exported_by": "MixVideo AI Workflow System",
            "count": templates.len()
        });

        let json_str = serde_json::to_string_pretty(&export_data)
            .map_err(|e| anyhow!("序列化模板失败: {}", e))?;

        info!("成功批量导出 {} 个工作流模板", templates.len());
        Ok(json_str)
    }

    /// 批量导入工作流模板
    pub fn batch_import(&self, json_data: &str) -> Result<Vec<i64>> {
        let import_data: serde_json::Value = serde_json::from_str(json_data)
            .map_err(|e| anyhow!("解析导入数据失败: {}", e))?;

        let templates_data = import_data.get("templates")
            .ok_or_else(|| anyhow!("导入数据中缺少templates字段"))?
            .as_array()
            .ok_or_else(|| anyhow!("templates字段不是数组格式"))?;

        let mut imported_ids = Vec::new();

        for (index, template_data) in templates_data.iter().enumerate() {
            match self.import_single_template(template_data, index) {
                Ok(id) => imported_ids.push(id),
                Err(e) => {
                    error!("导入第{}个模板失败: {}", index + 1, e);
                    return Err(anyhow!("导入第{}个模板失败: {}", index + 1, e));
                }
            }
        }

        info!("成功批量导入 {} 个工作流模板", imported_ids.len());
        Ok(imported_ids)
    }

    /// 导入单个模板（内部方法）
    fn import_single_template(&self, template_data: &serde_json::Value, index: usize) -> Result<i64> {
        let mut template: WorkflowTemplate = serde_json::from_value(template_data.clone())
            .map_err(|e| anyhow!("解析第{}个模板数据失败: {}", index + 1, e))?;

        // 重置ID和时间戳
        template.id = None;
        template.name = format!("{} (批量导入)", template.name);
        let now = chrono::Utc::now();
        template.created_at = now;
        template.updated_at = now;

        // 创建导入请求
        let create_request = CreateWorkflowTemplateRequest {
            name: template.name,
            base_name: template.base_name,
            version: template.version,
            workflow_type: template.workflow_type,
            description: template.description,
            comfyui_workflow_json: template.comfyui_workflow_json,
            ui_config_json: template.ui_config_json,
            execution_config_json: template.execution_config_json,
            input_schema_json: template.input_schema_json,
            output_schema_json: template.output_schema_json,
            tags: template.tags,
            category: template.category,
            author: template.author,
        };

        self.create(&create_request)
    }
}
