use anyhow::{Result, anyhow};
use rusqlite::{Row, params};
use std::sync::Arc;
use crate::data::models::material::{
    Material, MaterialSegment, MaterialType, ProcessingStatus,
    MaterialMetadata, SceneDetection, MaterialStats
};
use crate::infrastructure::database::Database;

/// 素材仓库
/// 遵循 Tauri 开发规范的数据访问层设计
#[derive(Clone)]
pub struct MaterialRepository {
    database: Arc<Database>,
}

impl MaterialRepository {
    /// 创建新的素材仓库实例
    pub fn new(database: Arc<Database>) -> Result<Self> {
        Ok(Self { database })
    }

    /// 创建素材
    pub fn create(&self, material: &Material) -> Result<()> {
        // 预处理 JSON 序列化，避免在持有锁时进行
        let metadata_json = serde_json::to_string(&material.metadata)?;
        let scene_detection_json = material.scene_detection.as_ref()
            .map(|sd| serde_json::to_string(sd))
            .transpose()?;
        let material_type_json = serde_json::to_string(&material.material_type)?;
        let processing_status_json = serde_json::to_string(&material.processing_status)?;

        self.database.with_connection(|conn| {
            // 添加调试日志
            println!("Creating material with model_id: {:?}", material.model_id);

            conn.execute(
                "INSERT INTO materials (
                    id, project_id, model_id, name, original_path, file_size, md5_hash,
                    material_type, processing_status, metadata, scene_detection,
                    created_at, updated_at, processed_at, error_message
                ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15)",
                (
                    &material.id,
                    &material.project_id,
                    &material.model_id,
                    &material.name,
                    &material.original_path,
                    material.file_size as i64,
                    &material.md5_hash,
                    &material_type_json,
                    &processing_status_json,
                    &metadata_json,
                    scene_detection_json.as_deref(),
                    material.created_at.to_rfc3339(),
                    material.updated_at.to_rfc3339(),
                    material.processed_at.map(|dt| dt.to_rfc3339()),
                    &material.error_message,
                ),
            )?;

            Ok(())
        }).map_err(|e| anyhow::anyhow!("创建素材失败: {}", e))
    }

    /// 根据ID获取素材
    /// 优化为使用连接池，支持并发访问
    pub fn get_by_id(&self, id: &str) -> Result<Option<Material>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            "SELECT id, project_id, model_id, name, original_path, file_size, md5_hash,
                    material_type, processing_status, metadata, scene_detection,
                    thumbnail_path, created_at, updated_at, processed_at, error_message
             FROM materials WHERE id = ?1"
        )?;

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

        for material in material_iter {
            return Ok(Some(material?));
        }

        Ok(None)
    }

    /// 根据项目ID获取所有素材
    /// 优化为使用连接池，支持并发访问
    pub fn get_by_project_id(&self, project_id: &str) -> Result<Vec<Material>> {
        println!("🔍 查询项目素材，project_id: {}", project_id);

        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            "SELECT id, project_id, model_id, name, original_path, file_size, md5_hash,
                    material_type, processing_status, metadata, scene_detection,
                    thumbnail_path, created_at, updated_at, processed_at, error_message
             FROM materials WHERE project_id = ?1 ORDER BY created_at DESC"
        )?;

        let material_iter = stmt.query_map([project_id], |row| {
            self.row_to_material(row)
        })?;

        let mut materials = Vec::new();
        for material in material_iter {
            let material = material?;
            materials.push(material);
        }

        println!("📁 项目 {} 共找到 {} 个素材", project_id, materials.len());
        Ok(materials)
    }

    /// 获取所有素材
    /// 优化为使用连接池，支持并发访问
    pub fn get_all(&self) -> Result<Vec<Material>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            "SELECT id, project_id, model_id, name, original_path, file_size, md5_hash,
                    material_type, processing_status, metadata, scene_detection,
                    thumbnail_path, created_at, updated_at, processed_at, error_message
             FROM materials ORDER BY created_at DESC"
        )?;

        let material_iter = stmt.query_map([], |row| {
            self.row_to_material(row)
        })?;

        let mut materials = Vec::new();
        for material in material_iter {
            materials.push(material?);
        }

        Ok(materials)
    }

    /// 根据MD5哈希检查素材是否存在
    /// 优化为使用连接池，支持并发访问
    pub fn exists_by_md5(&self, project_id: &str, md5_hash: &str) -> Result<bool> {
        // 使用只读连接进行查询
        let conn_handle = self.database.get_best_read_connection()?;

        let count: i64 = conn_handle.query_row(
            "SELECT COUNT(*) FROM materials WHERE project_id = ?1 AND md5_hash = ?2",
            [project_id, md5_hash],
            |row| row.get(0),
        )?;

        Ok(count > 0)
    }

    /// 更新素材
    /// 优化为使用连接池，支持并发访问
    pub fn update(&self, material: &Material) -> Result<()> {
        // 使用最佳连接进行写操作
        let conn_handle = self.database.get_best_connection()?;

        let metadata_json = serde_json::to_string(&material.metadata)?;
        let scene_detection_json = material.scene_detection.as_ref()
            .map(|sd| serde_json::to_string(sd))
            .transpose()?;

        conn_handle.execute(
            "UPDATE materials SET
                name = ?1, model_id = ?2, processing_status = ?3, metadata = ?4, scene_detection = ?5,
                updated_at = ?6, processed_at = ?7, error_message = ?8
             WHERE id = ?9",
            (
                &material.name,
                &material.model_id,
                serde_json::to_string(&material.processing_status)?,
                metadata_json,
                scene_detection_json,
                material.updated_at.to_rfc3339(),
                material.processed_at.map(|dt| dt.to_rfc3339()),
                &material.error_message,
                &material.id,
            ),
        )?;

        Ok(())
    }

    /// 删除素材
    /// 优化为使用连接池，支持并发访问
    pub fn delete(&self, id: &str) -> Result<()> {
        // 使用最佳连接进行写操作
        let conn_handle = self.database.get_best_connection()?;

        conn_handle.execute("DELETE FROM materials WHERE id = ?1", [id])?;

        Ok(())
    }

    /// 批量删除素材
    /// 使用事务确保数据一致性，返回成功删除的素材ID列表和失败的素材ID及错误信息（强制使用连接池）
    pub fn batch_delete(&self, material_ids: &[String]) -> Result<(Vec<String>, Vec<(String, String)>)> {
        println!("🏊 使用连接池批量删除素材，共{}个", material_ids.len());

        // 🚨 强制使用连接池，避免死锁
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let mut successful_deletes = Vec::new();
        let mut failed_deletes = Vec::new();

        // 开始事务
        let tx = pooled_conn.transaction()?;

        for material_id in material_ids {
            // 首先检查素材是否存在
            let exists: bool = tx.query_row(
                "SELECT 1 FROM materials WHERE id = ?1",
                [material_id],
                |_| Ok(true)
            ).unwrap_or(false);

            if !exists {
                failed_deletes.push((material_id.clone(), "素材不存在".to_string()));
                continue;
            }

            // 删除相关的片段数据
            match tx.execute("DELETE FROM material_segments WHERE material_id = ?1", [material_id]) {
                Ok(_) => {},
                Err(e) => {
                    failed_deletes.push((material_id.clone(), format!("删除片段失败: {}", e)));
                    continue;
                }
            }

            // 删除素材本身
            match tx.execute("DELETE FROM materials WHERE id = ?1", [material_id]) {
                Ok(rows_affected) => {
                    if rows_affected > 0 {
                        successful_deletes.push(material_id.clone());
                    } else {
                        failed_deletes.push((material_id.clone(), "删除失败，未找到记录".to_string()));
                    }
                },
                Err(e) => {
                    failed_deletes.push((material_id.clone(), format!("删除失败: {}", e)));
                }
            }
        }

        // 提交事务
        tx.commit()?;

        Ok((successful_deletes, failed_deletes))
    }

    /// 创建素材片段
    pub fn create_segment(&self, segment: &MaterialSegment) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;
        
        conn.execute(
            "INSERT INTO material_segments (
                id, material_id, segment_index, start_time, end_time,
                duration, file_path, file_size, thumbnail_path, created_at
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)",
            (
                &segment.id,
                &segment.material_id,
                segment.segment_index as i64,
                segment.start_time,
                segment.end_time,
                segment.duration,
                &segment.file_path,
                segment.file_size as i64,
                &segment.thumbnail_path,
                segment.created_at.to_rfc3339(),
            ),
        )?;

        Ok(())
    }

    /// 更新片段的缩略图路径
    pub fn update_segment_thumbnail(&self, segment_id: &str, thumbnail_path: &str) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        conn.execute(
            "UPDATE material_segments SET thumbnail_path = ?1 WHERE id = ?2",
            (thumbnail_path, segment_id),
        )?;

        Ok(())
    }

    /// 获取素材的所有片段
    pub fn get_segments(&self, material_id: &str) -> Result<Vec<MaterialSegment>> {

        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, material_id, segment_index, start_time, end_time,
                    duration, file_path, file_size, thumbnail_path,
                    usage_count, is_used, last_used_at, created_at
             FROM material_segments WHERE material_id = ?1 ORDER BY segment_index"
        )?;

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

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

        Ok(segments)
    }

    /// 获取项目素材统计信息
    pub fn get_project_stats(&self, project_id: &str) -> Result<MaterialStats> {
        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 
                COUNT(*) as total,
                SUM(CASE WHEN material_type = '\"Video\"' THEN 1 ELSE 0 END) as video_count,
                SUM(CASE WHEN material_type = '\"Audio\"' THEN 1 ELSE 0 END) as audio_count,
                SUM(CASE WHEN material_type = '\"Image\"' THEN 1 ELSE 0 END) as image_count,
                SUM(CASE WHEN material_type NOT IN ('\"Video\"', '\"Audio\"', '\"Image\"') THEN 1 ELSE 0 END) as other_count,
                SUM(file_size) as total_size
             FROM materials WHERE project_id = ?1"
        )?;

        let (total_materials, video_count, audio_count, image_count, other_count, total_size) = 
            stmt.query_row([project_id], |row| {
                Ok((
                    row.get::<_, i64>(0)? as u32,
                    row.get::<_, i64>(1)? as u32,
                    row.get::<_, i64>(2)? as u32,
                    row.get::<_, i64>(3)? as u32,
                    row.get::<_, i64>(4)? as u32,
                    row.get::<_, i64>(5)? as u64,
                ))
            })?;

        // 获取处理状态统计
        let mut status_stmt = conn.prepare(
            "SELECT processing_status, COUNT(*) FROM materials WHERE project_id = ?1 GROUP BY processing_status"
        )?;

        let status_iter = status_stmt.query_map([project_id], |row| {
            Ok((
                row.get::<_, String>(0)?,
                row.get::<_, i64>(1)? as u32,
            ))
        })?;

        let mut processing_status_counts = std::collections::HashMap::new();
        for status_result in status_iter {
            let (status, count) = status_result?;
            processing_status_counts.insert(status, count);
        }

        // TODO: 计算总时长（需要解析metadata）
        let total_duration = 0.0;

        Ok(MaterialStats {
            total_materials,
            video_count,
            audio_count,
            image_count,
            other_count,
            total_size,
            total_duration,
            processing_status_counts,
        })
    }

    /// 将数据库行转换为素材对象
    fn row_to_material(&self, row: &Row) -> rusqlite::Result<Material> {
        let metadata_str: String = row.get("metadata")?;
        let metadata: MaterialMetadata = serde_json::from_str(&metadata_str)
            .map_err(|e| rusqlite::Error::InvalidColumnType(
                row.as_ref().column_index("metadata").unwrap(),
                format!("JSON parse error: {}", e),
                rusqlite::types::Type::Text
            ))?;

        let scene_detection: Option<SceneDetection> = row.get::<_, Option<String>>("scene_detection")?
            .map(|s| serde_json::from_str(&s))
            .transpose()
            .map_err(|e| rusqlite::Error::InvalidColumnType(
                row.as_ref().column_index("scene_detection").unwrap(),
                format!("JSON parse error: {}", e),
                rusqlite::types::Type::Text
            ))?;

        let material_type_str: String = row.get("material_type")?;
        let material_type: MaterialType = serde_json::from_str(&material_type_str)
            .map_err(|e| rusqlite::Error::InvalidColumnType(
                row.as_ref().column_index("material_type").unwrap(),
                format!("JSON parse error: {}", e),
                rusqlite::types::Type::Text
            ))?;

        let processing_status_str: String = row.get("processing_status")?;
        let processing_status: ProcessingStatus = serde_json::from_str(&processing_status_str)
            .map_err(|e| rusqlite::Error::InvalidColumnType(
                row.as_ref().column_index("processing_status").unwrap(),
                format!("JSON parse error: {}", e),
                rusqlite::types::Type::Text
            ))?;

        Ok(Material {
            id: row.get("id")?,
            project_id: row.get("project_id")?,
            model_id: row.get("model_id")?,
            name: row.get("name")?,
            original_path: row.get("original_path")?,
            file_size: row.get::<_, i64>("file_size")? as u64,
            md5_hash: row.get("md5_hash")?,
            material_type,
            processing_status,
            metadata,
            scene_detection,
            segments: Vec::new(), // 需要单独查询
            thumbnail_path: row.get("thumbnail_path")?,
            created_at: {
                let created_at_str = row.get::<_, String>("created_at")?;
                // 尝试解析SQLite DATETIME格式 (YYYY-MM-DD HH:MM:SS)
                if let Ok(dt) = chrono::NaiveDateTime::parse_from_str(&created_at_str, "%Y-%m-%d %H:%M:%S") {
                    dt.and_utc()
                } else {
                    // 回退到RFC3339格式
                    chrono::DateTime::parse_from_rfc3339(&created_at_str)
                        .map_err(|e| rusqlite::Error::InvalidColumnType(
                            row.as_ref().column_index("created_at").unwrap(),
                            format!("DateTime parse error: {}", e),
                            rusqlite::types::Type::Text
                        ))?.with_timezone(&chrono::Utc)
                }
            },
            updated_at: {
                let updated_at_str = row.get::<_, String>("updated_at")?;
                // 尝试解析SQLite DATETIME格式 (YYYY-MM-DD HH:MM:SS)
                if let Ok(dt) = chrono::NaiveDateTime::parse_from_str(&updated_at_str, "%Y-%m-%d %H:%M:%S") {
                    dt.and_utc()
                } else {
                    // 回退到RFC3339格式
                    chrono::DateTime::parse_from_rfc3339(&updated_at_str)
                        .map_err(|e| rusqlite::Error::InvalidColumnType(
                            row.as_ref().column_index("updated_at").unwrap(),
                            format!("DateTime parse error: {}", e),
                            rusqlite::types::Type::Text
                        ))?.with_timezone(&chrono::Utc)
                }
            },
            processed_at: row.get::<_, Option<String>>("processed_at")?
                .map(|s| {
                    // 尝试解析SQLite DATETIME格式 (YYYY-MM-DD HH:MM:SS)
                    if let Ok(dt) = chrono::NaiveDateTime::parse_from_str(&s, "%Y-%m-%d %H:%M:%S") {
                        Ok(dt.and_utc())
                    } else {
                        // 回退到RFC3339格式
                        chrono::DateTime::parse_from_rfc3339(&s).map(|dt| dt.with_timezone(&chrono::Utc))
                    }
                })
                .transpose()
                .map_err(|e| rusqlite::Error::InvalidColumnType(
                    row.as_ref().column_index("processed_at").unwrap(),
                    format!("DateTime parse error: {}", e),
                    rusqlite::types::Type::Text
                ))?,
            error_message: row.get("error_message")?,
        })
    }

    /// 将数据库行转换为素材片段对象
    fn row_to_segment(&self, row: &Row) -> rusqlite::Result<MaterialSegment> {
        Ok(MaterialSegment {
            id: row.get("id")?,
            material_id: row.get("material_id")?,
            segment_index: row.get::<_, i64>("segment_index")? as u32,
            start_time: row.get("start_time")?,
            end_time: row.get("end_time")?,
            duration: row.get("duration")?,
            file_path: row.get("file_path")?,
            file_size: row.get::<_, i64>("file_size")? as u64,
            thumbnail_path: row.get("thumbnail_path")?,
            usage_count: row.get::<_, Option<u32>>("usage_count")?.unwrap_or(0),
            is_used: row.get::<_, Option<bool>>("is_used")?.unwrap_or(false),
            last_used_at: row.get::<_, Option<String>>("last_used_at")?
                .and_then(|s| chrono::DateTime::parse_from_rfc3339(&s).ok())
                .map(|dt| dt.with_timezone(&chrono::Utc)),
            created_at: {
                let created_at_str = row.get::<_, String>("created_at")?;
                // 尝试解析SQLite DATETIME格式 (YYYY-MM-DD HH:MM:SS)
                if let Ok(dt) = chrono::NaiveDateTime::parse_from_str(&created_at_str, "%Y-%m-%d %H:%M:%S") {
                    dt.and_utc()
                } else {
                    // 回退到RFC3339格式
                    chrono::DateTime::parse_from_rfc3339(&created_at_str)
                        .map_err(|e| rusqlite::Error::InvalidColumnType(
                            row.as_ref().column_index("created_at").unwrap(),
                            format!("DateTime parse error: {}", e),
                            rusqlite::types::Type::Text
                        ))?.with_timezone(&chrono::Utc)
                }
            },
        })
    }

    /// 关联素材到模特
    pub fn associate_material_to_model(&self, material_id: &str, model_id: &str) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        conn.execute(
            "UPDATE materials SET model_id = ?1, updated_at = ?2 WHERE id = ?3",
            (model_id, chrono::Utc::now().to_rfc3339(), material_id),
        )?;

        Ok(())
    }

    /// 取消素材与模特的关联
    pub fn disassociate_material_from_model(&self, material_id: &str) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        conn.execute(
            "UPDATE materials SET model_id = NULL, updated_at = ?1 WHERE id = ?2",
            (chrono::Utc::now().to_rfc3339(), material_id),
        )?;

        Ok(())
    }

    /// 根据模特ID获取关联的素材
    pub fn get_materials_by_model_id(&self, model_id: &str) -> Result<Vec<Material>> {
        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, project_id, model_id, name, original_path, file_size, md5_hash,
                    material_type, processing_status, metadata, scene_detection,
                    thumbnail_path, created_at, updated_at, processed_at, error_message
             FROM materials WHERE model_id = ?1 ORDER BY created_at DESC"
        )?;

        let material_iter = stmt.query_map([model_id], |row| {
            self.row_to_material(row)
        })?;

        let mut materials = Vec::new();
        for material in material_iter {
            materials.push(material?);
        }

        Ok(materials)
    }

    /// 获取未关联模特的素材
    pub fn get_unassociated_materials(&self, project_id: Option<&str>) -> Result<Vec<Material>> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let (query, params): (String, Vec<&str>) = if let Some(pid) = project_id {
            (
                "SELECT id, project_id, model_id, name, original_path, file_size, md5_hash,
                        material_type, processing_status, metadata, scene_detection,
                        thumbnail_path, created_at, updated_at, processed_at, error_message
                 FROM materials WHERE model_id IS NULL AND project_id = ?1 ORDER BY created_at DESC".to_string(),
                vec![pid]
            )
        } else {
            (
                "SELECT id, project_id, model_id, name, original_path, file_size, md5_hash,
                        material_type, processing_status, metadata, scene_detection,
                        thumbnail_path, created_at, updated_at, processed_at, error_message
                 FROM materials WHERE model_id IS NULL ORDER BY created_at DESC".to_string(),
                vec![]
            )
        };

        let mut stmt = conn.prepare(&query)?;
        let materials = if params.is_empty() {
            let material_iter = stmt.query_map([], |row| {
                self.row_to_material(row)
            })?;
            let mut materials = Vec::new();
            for material in material_iter {
                materials.push(material?);
            }
            materials
        } else {
            let material_iter = stmt.query_map([params[0]], |row| {
                self.row_to_material(row)
            })?;
            let mut materials = Vec::new();
            for material in material_iter {
                materials.push(material?);
            }
            materials
        };

        Ok(materials)
    }

    /// 根据项目ID获取项目信息
    pub async fn get_project_by_id(&self, project_id: &str) -> Result<Option<crate::data::models::project::Project>> {
        use crate::data::models::project::Project;
        use chrono::{DateTime, Utc};

        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, path, description, created_at, updated_at, is_active
             FROM projects WHERE id = ?1"
        )?;

        let result = stmt.query_row([project_id], |row| {
            let created_at_str: String = row.get(4)?;
            let updated_at_str: String = row.get(5)?;

            let created_at = DateTime::parse_from_rfc3339(&created_at_str)
                .map_err(|_| rusqlite::Error::InvalidColumnType(4, "created_at".to_string(), rusqlite::types::Type::Text))?
                .with_timezone(&Utc);
            let updated_at = DateTime::parse_from_rfc3339(&updated_at_str)
                .map_err(|_| rusqlite::Error::InvalidColumnType(5, "updated_at".to_string(), rusqlite::types::Type::Text))?
                .with_timezone(&Utc);

            Ok(Project {
                id: row.get(0)?,
                name: row.get(1)?,
                path: row.get(2)?,
                description: row.get::<_, Option<String>>(3)?.filter(|s| !s.is_empty()),
                created_at,
                updated_at,
                is_active: row.get::<_, i32>(6)? != 0,
            })
        });

        match result {
            Ok(project) => Ok(Some(project)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e.into()),
        }
    }

    /// 更新片段的文件路径
    pub async fn update_segment_file_path(&self, segment_id: &str, new_file_path: &str) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let updated_rows = conn.execute(
            "UPDATE material_segments SET file_path = ? WHERE id = ?",
            params![new_file_path, segment_id]
        )?;

        if updated_rows == 0 {
            return Err(anyhow::anyhow!("片段不存在或更新失败: {}", segment_id));
        }

        Ok(())
    }

    /// 根据片段ID获取片段信息（同步版本）
    pub fn get_segment_by_id_sync(&self, segment_id: &str) -> Result<Option<MaterialSegment>> {
        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, material_id, segment_index, start_time, end_time, duration,
                    file_path, file_size, thumbnail_path, usage_count, is_used, last_used_at, created_at
             FROM material_segments WHERE id = ?1"
        )?;

        let mut rows = stmt.query_map([segment_id], |row| {
            Ok(MaterialSegment {
                id: row.get(0)?,
                material_id: row.get(1)?,
                segment_index: row.get(2)?,
                start_time: row.get(3)?,
                end_time: row.get(4)?,
                duration: row.get(5)?,
                file_path: row.get(6)?,
                file_size: row.get(7)?,
                thumbnail_path: row.get(8)?,
                usage_count: row.get::<_, Option<u32>>(9)?.unwrap_or(0),
                is_used: row.get::<_, Option<bool>>(10)?.unwrap_or(false),
                last_used_at: row.get::<_, Option<String>>(11)?
                    .and_then(|s| chrono::DateTime::parse_from_rfc3339(&s).ok())
                    .map(|dt| dt.with_timezone(&chrono::Utc)),
                created_at: {
                    let created_at_str = row.get::<_, String>(12)?;
                    // 尝试解析SQLite DATETIME格式 (YYYY-MM-DD HH:MM:SS)
                    if let Ok(dt) = chrono::NaiveDateTime::parse_from_str(&created_at_str, "%Y-%m-%d %H:%M:%S") {
                        dt.and_utc()
                    } else {
                        // 回退到RFC3339格式
                        chrono::DateTime::parse_from_rfc3339(&created_at_str)
                            .map_err(|_| rusqlite::Error::InvalidColumnType(9, "created_at".to_string(), rusqlite::types::Type::Text))?
                            .with_timezone(&chrono::Utc)
                    }
                },
            })
        })?;

        match rows.next() {
            Some(row) => Ok(Some(row?)),
            None => Ok(None),
        }
    }

    /// 根据片段ID获取片段信息
    pub async fn get_segment_by_id(&self, segment_id: &str) -> Result<Option<MaterialSegment>> {
        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, material_id, segment_index, start_time, end_time, duration,
                    file_path, file_size, thumbnail_path, usage_count, is_used, last_used_at, created_at
             FROM material_segments WHERE id = ?1"
        )?;

        let mut rows = stmt.query_map([segment_id], |row| {
            Ok(MaterialSegment {
                id: row.get(0)?,
                material_id: row.get(1)?,
                segment_index: row.get(2)?,
                start_time: row.get(3)?,
                end_time: row.get(4)?,
                duration: row.get(5)?,
                file_path: row.get(6)?,
                file_size: row.get(7)?,
                thumbnail_path: row.get(8)?,
                usage_count: row.get::<_, Option<u32>>(9)?.unwrap_or(0),
                is_used: row.get::<_, Option<bool>>(10)?.unwrap_or(false),
                last_used_at: row.get::<_, Option<String>>(11)?
                    .and_then(|s| chrono::DateTime::parse_from_rfc3339(&s).ok())
                    .map(|dt| dt.with_timezone(&chrono::Utc)),
                created_at: {
                    let created_at_str = row.get::<_, String>(12)?;
                    // 尝试解析SQLite DATETIME格式 (YYYY-MM-DD HH:MM:SS)
                    if let Ok(dt) = chrono::NaiveDateTime::parse_from_str(&created_at_str, "%Y-%m-%d %H:%M:%S") {
                        dt.and_utc()
                    } else {
                        // 回退到RFC3339格式
                        chrono::DateTime::parse_from_rfc3339(&created_at_str)
                            .map_err(|_| rusqlite::Error::InvalidColumnType(9, "created_at".to_string(), rusqlite::types::Type::Text))?
                            .with_timezone(&chrono::Utc)
                    }
                },
            })
        })?;

        match rows.next() {
            Some(row) => Ok(Some(row?)),
            None => Ok(None),
        }
    }

    /// 批量更新素材的模特绑定
    pub fn batch_update_model_binding(&self, material_ids: &[String], model_id: Option<&str>) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        for material_id in material_ids {
            conn.execute(
                "UPDATE materials SET model_id = ?1, updated_at = ?2 WHERE id = ?3",
                (model_id, &updated_at, material_id),
            )?;
        }

        Ok(())
    }

    /// 更新素材的模特绑定
    pub fn update_model_binding(&self, material_id: &str, model_id: Option<&str>) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        conn.execute(
            "UPDATE materials SET model_id = ?1, updated_at = ?2 WHERE id = ?3",
            (model_id, chrono::Utc::now().to_rfc3339(), material_id),
        )?;

        Ok(())
    }

    /// 根据模特ID获取素材（别名方法，为了API一致性）
    pub fn get_by_model_id(&self, model_id: &str) -> Result<Vec<Material>> {
        self.get_materials_by_model_id(model_id)
    }

    /// 获取未绑定模特的素材（别名方法，为了API一致性）
    pub fn get_unbound_materials(&self) -> Result<Vec<Material>> {
        self.get_unassociated_materials(None)
    }

    /// 获取模特的素材统计信息
    pub fn get_model_statistics(&self, model_id: &str) -> Result<ModelMaterialStatistics> {
        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
                COUNT(*) as total_materials,
                SUM(CASE WHEN material_type = 'Video' THEN 1 ELSE 0 END) as video_count,
                SUM(CASE WHEN material_type = 'Audio' THEN 1 ELSE 0 END) as audio_count,
                SUM(CASE WHEN material_type = 'Image' THEN 1 ELSE 0 END) as image_count,
                SUM(file_size) as total_size
             FROM materials WHERE model_id = ?1"
        )?;

        let row = stmt.query_row([model_id], |row| {
            Ok(ModelMaterialStatistics {
                total_materials: row.get::<_, i64>("total_materials")? as u32,
                video_count: row.get::<_, i64>("video_count")? as u32,
                audio_count: row.get::<_, i64>("audio_count")? as u32,
                image_count: row.get::<_, i64>("image_count")? as u32,
                total_size: row.get::<_, i64>("total_size")? as u64,
            })
        })?;

        Ok(row)
    }

    /// 检查模特是否有关联的素材
    pub fn has_materials_for_model(&self, model_id: &str) -> Result<bool> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM materials WHERE model_id = ?1",
            [model_id],
            |row| row.get(0),
        )?;

        Ok(count > 0)
    }

    /// 获取项目中绑定了模特的素材数量
    pub fn count_bound_materials_in_project(&self, project_id: &str) -> Result<u32> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM materials WHERE project_id = ?1 AND model_id IS NOT NULL",
            [project_id],
            |row| row.get(0),
        )?;

        Ok(count as u32)
    }

    /// 获取项目中未绑定模特的素材数量
    pub fn count_unbound_materials_in_project(&self, project_id: &str) -> Result<u32> {
if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM materials WHERE project_id = ?1 AND model_id IS NULL",
            [project_id],
            |row| row.get(0),
        )?;

        Ok(count as u32)
    }

    /// 获取全局绑定了模特的素材数量
    pub fn count_global_bound_materials(&self) -> Result<u32> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM materials WHERE model_id IS NOT NULL",
            [],
            |row| row.get(0),
        )?;

        Ok(count as u32)
    }

    /// 获取全局未绑定模特的素材数量
    pub fn count_global_unbound_materials(&self) -> Result<u32> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM materials WHERE model_id IS NULL",
            [],
            |row| row.get(0),
        )?;

        Ok(count as u32)
    }

    /// 更新素材的缩略图路径
    pub fn update_material_thumbnail(&self, material_id: &str, thumbnail_path: &str) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        conn.execute(
            "UPDATE materials SET thumbnail_path = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
            [thumbnail_path, material_id],
        )?;

        Ok(())
    }
}

/// 模特素材统计信息
#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
pub struct ModelMaterialStatistics {
    pub total_materials: u32,
    pub video_count: u32,
    pub audio_count: u32,
    pub image_count: u32,
    pub total_size: u64,
}
