use rusqlite::{Result, Row};
use std::sync::Arc;
use chrono::{DateTime, Utc};

use crate::infrastructure::database::Database;

use crate::data::models::template_matching_result::{
    TemplateMatchingResult, MatchingSegmentResult, MatchingFailedSegmentResult,
    MatchingResultStatus, CreateTemplateMatchingResultRequest,
    TemplateMatchingResultQueryOptions,
};

/// 模板匹配结果数据库操作仓储
/// 遵循 Tauri 开发规范的数据访问层设计原则
pub struct TemplateMatchingResultRepository {
    database: Arc<Database>,
}

impl TemplateMatchingResultRepository {
    /// 创建新的模板匹配结果仓储实例
    pub fn new(database: Arc<Database>) -> Self {
        Self { database }
    }

    /// 创建模板匹配结果
    pub fn create(&self, request: CreateTemplateMatchingResultRequest) -> Result<TemplateMatchingResult> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        
        let result = TemplateMatchingResult::new(
            uuid::Uuid::new_v4().to_string(),
            request.project_id,
            request.template_id,
            request.binding_id,
            request.result_name,
        );

        let mut final_result = result;
        if let Some(desc) = request.description {
            final_result.set_description(desc);
        }

        conn.execute(
            "INSERT INTO template_matching_results (
                id, project_id, template_id, binding_id, result_name, description,
                total_segments, matched_segments, failed_segments, success_rate,
                used_materials, used_models, matching_duration_ms, quality_score,
                status, metadata, export_count, is_exported, last_exported_at,
                created_at, updated_at, is_active
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22)",
            rusqlite::params![
                &final_result.id,
                &final_result.project_id,
                &final_result.template_id,
                &final_result.binding_id,
                &final_result.result_name,
                &final_result.description,
                &final_result.total_segments,
                &final_result.matched_segments,
                &final_result.failed_segments,
                &final_result.success_rate,
                &final_result.used_materials,
                &final_result.used_models,
                &final_result.matching_duration_ms,
                &final_result.quality_score,
                &serde_json::to_string(&final_result.status).unwrap(),
                &final_result.metadata,
                &final_result.export_count,
                &(final_result.is_exported as i32),
                &final_result.last_exported_at.map(|dt| dt.to_rfc3339()),
                &final_result.created_at.to_rfc3339(),
                &final_result.updated_at.to_rfc3339(),
                &(final_result.is_active as i32),
            ],
        )?;

        Ok(final_result)
    }

    /// 根据ID获取模板匹配结果
    pub fn get_by_id(&self, id: &str) -> Result<Option<TemplateMatchingResult>> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        
        let mut stmt = conn.prepare(
            "SELECT id, project_id, template_id, binding_id, result_name, description,
                    total_segments, matched_segments, failed_segments, success_rate,
                    used_materials, used_models, matching_duration_ms, quality_score,
                    status, metadata, export_count, is_exported, last_exported_at,
                    created_at, updated_at, is_active
             FROM template_matching_results WHERE id = ?1"
        )?;

        let result_iter = stmt.query_map([id], |row| {
            self.row_to_matching_result(row)
        })?;

        for result in result_iter {
            return Ok(Some(result?));
        }

        Ok(None)
    }

    /// 更新模板匹配结果
    pub fn update(&self, result: &TemplateMatchingResult) -> Result<()> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        
        conn.execute(
            "UPDATE template_matching_results SET
                result_name = ?1, description = ?2, total_segments = ?3,
                matched_segments = ?4, failed_segments = ?5, success_rate = ?6,
                used_materials = ?7, used_models = ?8, matching_duration_ms = ?9,
                quality_score = ?10, status = ?11, metadata = ?12, export_count = ?13,
                is_exported = ?14, last_exported_at = ?15, updated_at = ?16
             WHERE id = ?17",
            rusqlite::params![
                &result.result_name,
                &result.description,
                &result.total_segments.to_string(),
                &result.matched_segments.to_string(),
                &result.failed_segments.to_string(),
                &result.success_rate.to_string(),
                &result.used_materials.to_string(),
                &result.used_models.to_string(),
                &result.matching_duration_ms.to_string(),
                &result.quality_score.map(|s| s.to_string()),
                &serde_json::to_string(&result.status).unwrap(),
                &result.metadata,
                &result.export_count.to_string(),
                &(result.is_exported as i32),
                &result.last_exported_at.map(|dt| dt.to_rfc3339()),
                &result.updated_at.to_rfc3339(),
                &result.id,
            ],
        )?;

        Ok(())
    }

    /// 删除模板匹配结果
    pub fn delete(&self, id: &str) -> Result<bool> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        
        let rows_affected = conn.execute(
            "DELETE FROM template_matching_results WHERE id = ?1",
            [id],
        )?;

        Ok(rows_affected > 0)
    }

    /// 软删除模板匹配结果
    pub fn soft_delete(&self, id: &str) -> Result<bool> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        
        let rows_affected = conn.execute(
            "UPDATE template_matching_results SET is_active = 0, updated_at = ?1 WHERE id = ?2",
            [&Utc::now().to_rfc3339(), id],
        )?;

        Ok(rows_affected > 0)
    }

    /// 查询模板匹配结果列表
    pub fn list(&self, options: TemplateMatchingResultQueryOptions) -> Result<Vec<TemplateMatchingResult>> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        
        let mut query = "SELECT id, project_id, template_id, binding_id, result_name, description,
                                total_segments, matched_segments, failed_segments, success_rate,
                                used_materials, used_models, matching_duration_ms, quality_score,
                                status, metadata, export_count, is_exported, last_exported_at,
                                created_at, updated_at, is_active
                         FROM template_matching_results WHERE is_active = 1".to_string();
        let mut params: Vec<String> = Vec::new();

        // 添加查询条件
        if let Some(project_id) = &options.project_id {
            query.push_str(" AND project_id = ?");
            params.push(project_id.clone());
        }

        if let Some(template_id) = &options.template_id {
            query.push_str(" AND template_id = ?");
            params.push(template_id.clone());
        }

        if let Some(binding_id) = &options.binding_id {
            query.push_str(" AND binding_id = ?");
            params.push(binding_id.clone());
        }

        if let Some(status) = &options.status {
            query.push_str(" AND status = ?");
            params.push(serde_json::to_string(status).unwrap());
        }

        if let Some(keyword) = &options.search_keyword {
            query.push_str(" AND (result_name LIKE ? OR description LIKE ?)");
            let search_pattern = format!("%{}%", keyword);
            params.push(search_pattern.clone());
            params.push(search_pattern);
        }

        // 添加排序
        let sort_by = options.sort_by.as_deref().unwrap_or("created_at");
        let sort_order = options.sort_order.as_deref().unwrap_or("desc");
        query.push_str(&format!(" ORDER BY {} {}", sort_by, sort_order));

        // 添加分页
        if let Some(limit) = options.limit {
            query.push_str(" LIMIT ?");
            params.push(limit.to_string());
            
            if let Some(offset) = options.offset {
                query.push_str(" OFFSET ?");
                params.push(offset.to_string());
            }
        }

        let mut stmt = conn.prepare(&query)?;
        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p as &dyn rusqlite::ToSql).collect();
        
        let result_iter = stmt.query_map(param_refs.as_slice(), |row| {
            self.row_to_matching_result(row)
        })?;

        let mut results = Vec::new();
        for result in result_iter {
            results.push(result?);
        }

        Ok(results)
    }

    /// 根据项目ID获取匹配结果列表
    pub fn get_by_project_id(&self, project_id: &str) -> Result<Vec<TemplateMatchingResult>> {
        let options = TemplateMatchingResultQueryOptions {
            project_id: Some(project_id.to_string()),
            ..Default::default()
        };
        self.list(options)
    }

    /// 根据模板ID获取匹配结果列表
    pub fn get_by_template_id(&self, template_id: &str) -> Result<Vec<TemplateMatchingResult>> {
        let options = TemplateMatchingResultQueryOptions {
            template_id: Some(template_id.to_string()),
            ..Default::default()
        };
        self.list(options)
    }

    /// 根据绑定ID获取匹配结果列表
    pub fn get_by_binding_id(&self, binding_id: &str) -> Result<Vec<TemplateMatchingResult>> {
        let options = TemplateMatchingResultQueryOptions {
            binding_id: Some(binding_id.to_string()),
            ..Default::default()
        };
        self.list(options)
    }

    // ===== 匹配片段结果管理 =====

    /// 创建匹配片段结果
    pub fn create_segment_result(&self, segment_result: &MatchingSegmentResult) -> Result<()> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        conn.execute(
            "INSERT INTO matching_segment_results (
                id, matching_result_id, track_segment_id, track_segment_name,
                material_segment_id, material_id, material_name, model_id, model_name,
                match_score, match_reason, segment_duration, start_time, end_time,
                properties, created_at, updated_at
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
            rusqlite::params![
                &segment_result.id,
                &segment_result.matching_result_id,
                &segment_result.track_segment_id,
                &segment_result.track_segment_name,
                &segment_result.material_segment_id,
                &segment_result.material_id,
                &segment_result.material_name,
                &segment_result.model_id,
                &segment_result.model_name,
                &segment_result.match_score,
                &segment_result.match_reason,
                &segment_result.segment_duration,
                &segment_result.start_time,
                &segment_result.end_time,
                &segment_result.properties,
                &segment_result.created_at.to_rfc3339(),
                &segment_result.updated_at.to_rfc3339(),
            ],
        )?;

        Ok(())
    }

    /// 批量创建匹配片段结果
    pub fn create_segment_results(&self, segment_results: &[MatchingSegmentResult]) -> Result<()> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        let tx = conn.unchecked_transaction()?;

        for segment_result in segment_results {
            tx.execute(
                "INSERT INTO matching_segment_results (
                    id, matching_result_id, track_segment_id, track_segment_name,
                    material_segment_id, material_id, material_name, model_id, model_name,
                    match_score, match_reason, segment_duration, start_time, end_time,
                    properties, created_at, updated_at
                ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                rusqlite::params![
                    &segment_result.id,
                    &segment_result.matching_result_id,
                    &segment_result.track_segment_id,
                    &segment_result.track_segment_name,
                    &segment_result.material_segment_id,
                    &segment_result.material_id,
                    &segment_result.material_name,
                    &segment_result.model_id,
                    &segment_result.model_name,
                    &segment_result.match_score,
                    &segment_result.match_reason,
                    &segment_result.segment_duration,
                    &segment_result.start_time,
                    &segment_result.end_time,
                    &segment_result.properties,
                    &segment_result.created_at.to_rfc3339(),
                    &segment_result.updated_at.to_rfc3339(),
                ],
            )?;
        }

        tx.commit()?;
        Ok(())
    }

    /// 获取匹配结果的所有成功片段
    pub fn get_segment_results_by_matching_result_id(&self, matching_result_id: &str) -> Result<Vec<MatchingSegmentResult>> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        let mut stmt = conn.prepare(
            "SELECT id, matching_result_id, track_segment_id, track_segment_name,
                    material_segment_id, material_id, material_name, model_id, model_name,
                    match_score, match_reason, segment_duration, start_time, end_time,
                    properties, created_at, updated_at
             FROM matching_segment_results WHERE matching_result_id = ?1
             ORDER BY start_time ASC"
        )?;

        let segment_iter = stmt.query_map([matching_result_id], |row| {
            self.row_to_segment_result(row)
        })?;

        let mut segments = Vec::new();
        for segment in segment_iter {
            segments.push(segment?);
        }

        Ok(segments)
    }

    // ===== 匹配失败片段结果管理 =====

    /// 创建匹配失败片段结果
    pub fn create_failed_segment_result(&self, failed_segment: &MatchingFailedSegmentResult) -> Result<()> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        conn.execute(
            "INSERT INTO matching_failed_segment_results (
                id, matching_result_id, track_segment_id, track_segment_name,
                matching_rule_type, matching_rule_data, failure_reason, failure_details,
                segment_duration, start_time, end_time, created_at, updated_at
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)",
            rusqlite::params![
                &failed_segment.id,
                &failed_segment.matching_result_id,
                &failed_segment.track_segment_id,
                &failed_segment.track_segment_name,
                &failed_segment.matching_rule_type,
                &failed_segment.matching_rule_data,
                &failed_segment.failure_reason,
                &failed_segment.failure_details,
                &failed_segment.segment_duration,
                &failed_segment.start_time,
                &failed_segment.end_time,
                &failed_segment.created_at.to_rfc3339(),
                &failed_segment.updated_at.to_rfc3339(),
            ],
        )?;

        Ok(())
    }

    /// 批量创建匹配失败片段结果
    pub fn create_failed_segment_results(&self, failed_segments: &[MatchingFailedSegmentResult]) -> Result<()> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;

        let tx = conn.unchecked_transaction()?;

        for failed_segment in failed_segments {
            tx.execute(
                "INSERT INTO matching_failed_segment_results (
                    id, matching_result_id, track_segment_id, track_segment_name,
                    matching_rule_type, matching_rule_data, failure_reason, failure_details,
                    segment_duration, start_time, end_time, created_at, updated_at
                ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)",
                rusqlite::params![
                    &failed_segment.id,
                    &failed_segment.matching_result_id,
                    &failed_segment.track_segment_id,
                    &failed_segment.track_segment_name,
                    &failed_segment.matching_rule_type,
                    &failed_segment.matching_rule_data,
                    &failed_segment.failure_reason,
                    &failed_segment.failure_details,
                    &failed_segment.segment_duration,
                    &failed_segment.start_time,
                    &failed_segment.end_time,
                    &failed_segment.created_at.to_rfc3339(),
                    &failed_segment.updated_at.to_rfc3339(),
                ],
            )?;
        }

        tx.commit()?;
        Ok(())
    }

    /// 获取匹配结果的所有失败片段
    pub fn get_failed_segment_results_by_matching_result_id(&self, matching_result_id: &str) -> Result<Vec<MatchingFailedSegmentResult>> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;

        let mut stmt = conn.prepare(
            "SELECT id, matching_result_id, track_segment_id, track_segment_name,
                    matching_rule_type, matching_rule_data, failure_reason, failure_details,
                    segment_duration, start_time, end_time, created_at, updated_at
             FROM matching_failed_segment_results WHERE matching_result_id = ?1
             ORDER BY start_time ASC"
        )?;

        let failed_segment_iter = stmt.query_map([matching_result_id], |row| {
            self.row_to_failed_segment_result(row)
        })?;

        let mut failed_segments = Vec::new();
        for failed_segment in failed_segment_iter {
            failed_segments.push(failed_segment?);
        }

        Ok(failed_segments)
    }

    /// 将数据库行转换为模板匹配结果实体
    fn row_to_matching_result(&self, row: &Row) -> Result<TemplateMatchingResult> {
        let status_str: String = row.get("status")?;
        let status: MatchingResultStatus = serde_json::from_str(&status_str)
            .unwrap_or(MatchingResultStatus::Success);

        let quality_score: Option<f64> = row.get("quality_score")?;

        let created_at_str: String = row.get("created_at")?;
        let updated_at_str: String = row.get("updated_at")?;
        let created_at = DateTime::parse_from_rfc3339(&created_at_str)
            .unwrap_or_else(|_| Utc::now().into())
            .with_timezone(&Utc);
        let updated_at = DateTime::parse_from_rfc3339(&updated_at_str)
            .unwrap_or_else(|_| Utc::now().into())
            .with_timezone(&Utc);

        // 正确处理 is_active 字段，支持多种数据类型
        let is_active = match row.get::<_, rusqlite::types::Value>("is_active")? {
            rusqlite::types::Value::Integer(i) => i != 0,
            rusqlite::types::Value::Text(s) => s == "1" || s.to_lowercase() == "true",
            rusqlite::types::Value::Real(f) => f != 0.0,
            _ => true, // 默认为 true
        };

        // 处理 is_exported 字段
        let is_exported = match row.get::<_, rusqlite::types::Value>("is_exported") {
            Ok(rusqlite::types::Value::Integer(i)) => i != 0,
            Ok(rusqlite::types::Value::Text(s)) => s == "1" || s.to_lowercase() == "true",
            Ok(rusqlite::types::Value::Real(f)) => f != 0.0,
            _ => false, // 默认为 false
        };

        // 处理 last_exported_at 字段
        let last_exported_at = match row.get::<_, Option<String>>("last_exported_at") {
            Ok(Some(date_str)) => DateTime::parse_from_rfc3339(&date_str)
                .ok()
                .map(|dt| dt.with_timezone(&Utc)),
            _ => None,
        };

        Ok(TemplateMatchingResult {
            id: row.get("id")?,
            project_id: row.get("project_id")?,
            template_id: row.get("template_id")?,
            binding_id: row.get("binding_id")?,
            result_name: row.get("result_name")?,
            description: row.get("description")?,
            total_segments: row.get("total_segments")?,
            matched_segments: row.get("matched_segments")?,
            failed_segments: row.get("failed_segments")?,
            success_rate: row.get("success_rate")?,
            used_materials: row.get("used_materials")?,
            used_models: row.get("used_models")?,
            matching_duration_ms: row.get("matching_duration_ms")?,
            quality_score,
            status,
            metadata: row.get("metadata")?,
            export_count: row.get("export_count").unwrap_or(0),
            is_exported,
            last_exported_at,
            created_at,
            updated_at,
            is_active,
        })
    }

    /// 将数据库行转换为匹配片段结果实体
    fn row_to_segment_result(&self, row: &Row) -> Result<MatchingSegmentResult> {
        let created_at_str: String = row.get("created_at")?;
        let updated_at_str: String = row.get("updated_at")?;
        let created_at = DateTime::parse_from_rfc3339(&created_at_str)
            .unwrap_or_else(|_| Utc::now().into())
            .with_timezone(&Utc);
        let updated_at = DateTime::parse_from_rfc3339(&updated_at_str)
            .unwrap_or_else(|_| Utc::now().into())
            .with_timezone(&Utc);

        Ok(MatchingSegmentResult {
            id: row.get("id")?,
            matching_result_id: row.get("matching_result_id")?,
            track_segment_id: row.get("track_segment_id")?,
            track_segment_name: row.get("track_segment_name")?,
            material_segment_id: row.get("material_segment_id")?,
            material_id: row.get("material_id")?,
            material_name: row.get("material_name")?,
            model_id: row.get("model_id")?,
            model_name: row.get("model_name")?,
            match_score: row.get("match_score")?,
            match_reason: row.get("match_reason")?,
            segment_duration: row.get("segment_duration")?,
            start_time: row.get("start_time")?,
            end_time: row.get("end_time")?,
            properties: row.get("properties")?,
            created_at,
            updated_at,
        })
    }

    /// 将数据库行转换为匹配失败片段结果实体
    fn row_to_failed_segment_result(&self, row: &Row) -> Result<MatchingFailedSegmentResult> {
        let created_at_str: String = row.get("created_at")?;
        let updated_at_str: String = row.get("updated_at")?;
        let created_at = DateTime::parse_from_rfc3339(&created_at_str)
            .unwrap_or_else(|_| Utc::now().into())
            .with_timezone(&Utc);
        let updated_at = DateTime::parse_from_rfc3339(&updated_at_str)
            .unwrap_or_else(|_| Utc::now().into())
            .with_timezone(&Utc);

        Ok(MatchingFailedSegmentResult {
            id: row.get("id")?,
            matching_result_id: row.get("matching_result_id")?,
            track_segment_id: row.get("track_segment_id")?,
            track_segment_name: row.get("track_segment_name")?,
            matching_rule_type: row.get("matching_rule_type")?,
            matching_rule_data: row.get("matching_rule_data")?,
            failure_reason: row.get("failure_reason")?,
            failure_details: row.get("failure_details")?,
            segment_duration: row.get("segment_duration")?,
            start_time: row.get("start_time")?,
            end_time: row.get("end_time")?,
            created_at,
            updated_at,
        })
    }

    /// 增加导出次数并标记为已导出
    pub fn increment_export_count(&self, result_id: &str) -> Result<()> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;
        let now = Utc::now();

        conn.execute(
            "UPDATE template_matching_results
             SET export_count = export_count + 1, is_exported = 1,
                 last_exported_at = ?2, updated_at = ?3
             WHERE id = ?1",
            rusqlite::params![result_id, now.to_rfc3339(), now.to_rfc3339()],
        )?;

        Ok(())
    }

    /// 重置导出状态
    pub fn reset_export_status(&self, result_id: &str) -> Result<()> {
        if !self.database.has_pool() {
            return Err(rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some("连接池未启用，无法安全执行数据库操作".to_string())
            ));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_MISUSE),
                Some(format!("获取连接池连接失败: {}", e))
            ))?;

        conn.execute(
            "UPDATE template_matching_results
             SET is_exported = 0, last_exported_at = NULL, updated_at = ?2
             WHERE id = ?1",
            rusqlite::params![result_id, Utc::now().to_rfc3339()],
        )?;

        Ok(())
    }
}
