use anyhow::{Result, anyhow};
use std::sync::Arc;
use rusqlite::{params, Row};
use chrono;
use serde_json;
use tracing::{info, warn, error};

use crate::data::models::template::{
    Template, TemplateMaterial, Track, TrackSegment, CanvasConfig,
    TemplateMaterialType, TrackType, ImportStatus, UploadStatus,
    CreateTemplateRequest, SegmentMatchingRule
};
use crate::infrastructure::database::Database;

/// 模板服务
/// 遵循 Tauri 开发规范的业务逻辑设计原则
pub struct TemplateService {
    database: Arc<Database>,
}

/// 模板查询选项
#[derive(Debug, Default, serde::Serialize, serde::Deserialize)]
pub struct TemplateQueryOptions {
    pub project_id: Option<String>,
    pub import_status: Option<ImportStatus>,
    pub limit: Option<u32>,
    pub offset: Option<u32>,
    pub search_keyword: Option<String>,
}

/// 模板列表响应
#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct TemplateListResponse {
    pub templates: Vec<Template>,
    pub total: u32,
}

/// 模板关联数据统计
#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct TemplateAssociations {
    pub template_id: String,
    pub materials_count: u32,
    pub tracks_count: u32,
    pub segments_count: u32,
    pub bindings_count: u32,
}

impl TemplateService {
    /// 创建新的模板服务实例
    pub fn new(database: Arc<Database>) -> Self {
        Self { database }
    }

    /// 获取数据库连接（用于测试）
    #[cfg(test)]
    pub fn get_database(&self) -> Arc<Database> {
        Arc::clone(&self.database)
    }

    /// 创建模板
    pub async fn create_template(&self, request: CreateTemplateRequest) -> Result<String> {
        let template = Template::new(
            request.name,
            CanvasConfig {
                width: 1920,
                height: 1080,
                ratio: "16:9".to_string(),
            },
            0,
            30.0,
        );

        self.save_template(&template).await?;
        Ok(template.id)
    }

    /// 保存模板到数据库
    pub async fn save_template(&self, template: &Template) -> Result<()> {
        // 首先验证模板数据的完整性
        self.validate_template_data(template)?;
        // 🚨 强制使用连接池，避免死锁
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let pooled_conn = self.database.acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;
        
        // 启用外键约束检查
        pooled_conn.execute("PRAGMA foreign_keys = ON", [])?;

        // 检查外键约束是否真的启用了
        let foreign_keys_enabled: i64 = pooled_conn.query_row(
            "PRAGMA foreign_keys",
            [],
            |row| row.get(0)
        )?;

        info!(
            foreign_keys_enabled = %foreign_keys_enabled,
            "外键约束状态检查"
        );

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

        // 如果模板已存在，先删除相关数据以避免外键冲突
        // 按照外键依赖关系的逆序删除：track_segments -> tracks -> template_materials
        let deleted_segments = tx.execute(
            "DELETE FROM track_segments WHERE track_id IN (SELECT id FROM tracks WHERE template_id = ?1)",
            params![template.id]
        ).map_err(|e| anyhow!("删除轨道片段失败: {}", e))?;

        let deleted_tracks = tx.execute(
            "DELETE FROM tracks WHERE template_id = ?1",
            params![template.id]
        ).map_err(|e| anyhow!("删除轨道失败: {}", e))?;

        let deleted_materials = tx.execute(
            "DELETE FROM template_materials WHERE template_id = ?1",
            params![template.id]
        ).map_err(|e| anyhow!("删除模板素材失败: {}", e))?;

        if deleted_segments > 0 || deleted_tracks > 0 || deleted_materials > 0 {
            info!(
                template_id = %template.id,
                deleted_segments = %deleted_segments,
                deleted_tracks = %deleted_tracks,
                deleted_materials = %deleted_materials,
                "清理了已存在的模板关联数据"
            );
        }

        // 保存模板基本信息
        tx.execute(
            "INSERT OR REPLACE INTO templates (
                id, name, description, canvas_width, canvas_height,
                canvas_ratio, duration, fps, import_status, source_file_path,
                created_at, updated_at, is_active
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)",
            params![
                template.id,
                template.name,
                template.description,
                template.canvas_config.width,
                template.canvas_config.height,
                template.canvas_config.ratio,
                template.duration as i64,
                template.fps,
                format!("{:?}", template.import_status),
                template.source_file_path,
                template.created_at.to_rfc3339(),
                template.updated_at.to_rfc3339(),
                template.is_active
            ],
        ).map_err(|e| anyhow!("保存模板基本信息失败 (template_id: {}): {}", template.id, e))?;

        info!(
            template_id = %template.id,
            template_name = %template.name,
            "模板基本信息保存成功"
        );

        // 保存素材（必须在轨道之前保存，因为轨道片段可能引用素材）
        for (index, material) in template.materials.iter().enumerate() {
            // 验证素材的template_id是否与模板ID匹配
            if material.template_id != template.id {
                error!(
                    material_id = %material.id,
                    material_template_id = %material.template_id,
                    template_id = %template.id,
                    index = %index,
                    "素材的template_id与模板ID不匹配"
                );
                return Err(anyhow!(
                    "素材的template_id ({}) 与模板ID ({}) 不匹配 (material_id: {}, index: {})",
                    material.template_id, template.id, material.id, index
                ));
            }

            // 验证模板是否已存在于数据库中
            let template_exists = tx.query_row(
                "SELECT COUNT(*) FROM templates WHERE id = ?1",
                params![template.id],
                |row| row.get::<_, i64>(0)
            ).unwrap_or(0) > 0;

            if !template_exists {
                error!(
                    template_id = %template.id,
                    material_id = %material.id,
                    index = %index,
                    "尝试保存素材时，模板在数据库中不存在"
                );
                return Err(anyhow!(
                    "模板 {} 在数据库中不存在，无法保存素材 {} (index: {})",
                    template.id, material.id, index
                ));
            }

            info!(
                material_id = %material.id,
                template_id = %material.template_id,
                index = %index,
                name = %material.name,
                "开始保存素材"
            );

            // 在事务中再次验证模板是否存在
            let template_exists_in_tx = tx.query_row(
                "SELECT COUNT(*) FROM templates WHERE id = ?1",
                params![template.id],
                |row| row.get::<_, i64>(0)
            ).unwrap_or(0) > 0;

            info!(
                template_id = %template.id,
                template_exists_in_tx = %template_exists_in_tx,
                "事务中模板存在性检查"
            );

            // 检查外键约束设置
            let foreign_keys_status: i64 = tx.query_row(
                "PRAGMA foreign_keys",
                [],
                |row| row.get(0)
            ).unwrap_or(0);

            let defer_foreign_keys_status: i64 = tx.query_row(
                "PRAGMA defer_foreign_keys",
                [],
                |row| row.get(0)
            ).unwrap_or(0);

            info!(
                foreign_keys = %foreign_keys_status,
                defer_foreign_keys = %defer_foreign_keys_status,
                "事务中外键约束状态"
            );

            // 检查template_materials表的结构
            match tx.prepare("PRAGMA table_info(template_materials)") {
                Ok(mut stmt) => {
                    match stmt.query_map([], |row| {
                        Ok(format!("{}:{}", row.get::<_, String>(1)?, row.get::<_, String>(2)?))
                    }) {
                        Ok(rows) => {
                            let columns: Vec<String> = rows.filter_map(|r| r.ok()).collect();
                            info!(columns = ?columns, "template_materials表结构");
                        }
                        Err(e) => error!(error = %e, "获取表结构失败"),
                    }
                }
                Err(e) => error!(error = %e, "准备表结构查询失败"),
            }

            // 检查外键约束定义
            match tx.prepare("PRAGMA foreign_key_list(template_materials)") {
                Ok(mut stmt) => {
                    match stmt.query_map([], |row| {
                        Ok(format!("table:{}, from:{}, to:{}",
                            row.get::<_, String>(2)?,
                            row.get::<_, String>(3)?,
                            row.get::<_, String>(4)?))
                    }) {
                        Ok(rows) => {
                            let fks: Vec<String> = rows.filter_map(|r| r.ok()).collect();
                            info!(foreign_keys = ?fks, "template_materials外键约束");
                        }
                        Err(e) => error!(error = %e, "获取外键约束失败"),
                    }
                }
                Err(e) => error!(error = %e, "准备外键约束查询失败"),
            }

            // 先尝试INSERT，如果失败则UPDATE
            let insert_result = tx.execute(
                "INSERT INTO template_materials (
                    id, template_id, original_id, name, material_type, original_path,
                    remote_url, file_size, duration, width, height, upload_status,
                    file_exists, upload_success, metadata, created_at, updated_at
                ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17)",
                params![
                    material.id,
                    material.template_id,
                    material.original_id,
                    material.name,
                    format!("{:?}", material.material_type),
                    material.original_path,
                    material.remote_url,
                    material.file_size.map(|s| s as i64),
                    material.duration.map(|d| d as i64),
                    material.width.map(|w| w as i64),
                    material.height.map(|h| h as i64),
                    format!("{:?}", material.upload_status),
                    material.file_exists,
                    material.upload_success,
                    material.metadata,
                    material.created_at.to_rfc3339(),
                    material.updated_at.to_rfc3339()
                ],
            );

            let result = match insert_result {
                Ok(rows) => {
                    info!(
                        material_id = %material.id,
                        rows_affected = %rows,
                        "素材INSERT成功"
                    );
                    Ok(rows)
                }
                Err(rusqlite::Error::SqliteFailure(err, msg)) => {
                    error!(
                        material_id = %material.id,
                        error_code = ?err.code,
                        extended_code = ?err.extended_code,
                        message = ?msg,
                        "INSERT失败，详细错误信息"
                    );

                    // 检查是否是主键冲突或其他约束违规
                    if err.code == rusqlite::ErrorCode::ConstraintViolation {
                        info!(
                            material_id = %material.id,
                            "检测到约束违规，尝试UPDATE"
                        );

                        // 先检查记录是否存在
                        let exists = tx.query_row(
                            "SELECT COUNT(*) FROM template_materials WHERE id = ?1",
                            params![material.id],
                            |row| row.get::<_, i64>(0)
                        ).unwrap_or(0) > 0;

                        info!(
                            material_id = %material.id,
                            exists = %exists,
                            "记录存在性检查"
                        );

                        if exists {
                            // 记录存在，执行UPDATE
                            let update_result = tx.execute(
                                "UPDATE template_materials SET
                                    template_id = ?2, original_id = ?3, name = ?4, material_type = ?5,
                                    original_path = ?6, remote_url = ?7, file_size = ?8, duration = ?9,
                                    width = ?10, height = ?11, upload_status = ?12, file_exists = ?13,
                                    upload_success = ?14, metadata = ?15, updated_at = ?16
                                 WHERE id = ?1",
                                params![
                                    material.id,
                                    material.template_id,
                                    material.original_id,
                                    material.name,
                                    format!("{:?}", material.material_type),
                                    material.original_path,
                                    material.remote_url,
                                    material.file_size.map(|s| s as i64),
                                    material.duration.map(|d| d as i64),
                                    material.width.map(|w| w as i64),
                                    material.height.map(|h| h as i64),
                                    format!("{:?}", material.upload_status),
                                    material.file_exists,
                                    material.upload_success,
                                    material.metadata,
                                    material.updated_at.to_rfc3339()
                                ],
                            );

                            match update_result {
                                Ok(rows) => {
                                    info!(
                                        material_id = %material.id,
                                        rows_affected = %rows,
                                        "UPDATE成功"
                                    );
                                    Ok(rows)
                                }
                                Err(e) => {
                                    error!(
                                        material_id = %material.id,
                                        error = %e,
                                        "UPDATE失败"
                                    );
                                    Err(e)
                                }
                            }
                        } else {
                            // 记录不存在，但INSERT失败，可能是外键约束问题
                            error!(
                                material_id = %material.id,
                                "记录不存在但INSERT失败，可能是外键约束问题"
                            );
                            Err(rusqlite::Error::SqliteFailure(err, msg))
                        }
                    } else {
                        // 其他类型的错误
                        Err(rusqlite::Error::SqliteFailure(err, msg))
                    }
                }
                Err(e) => Err(e),
            };

            match result {
                Ok(rows_affected) => {
                    info!(
                        material_id = %material.id,
                        template_id = %material.template_id,
                        index = %index,
                        rows_affected = %rows_affected,
                        "素材保存成功"
                    );
                }
                Err(e) => {
                    error!(
                        material_id = %material.id,
                        template_id = %material.template_id,
                        index = %index,
                        error = %e,
                        "素材保存失败，详细错误信息"
                    );

                    // 检查具体的SQLite错误代码
                    if let rusqlite::Error::SqliteFailure(err, msg) = &e {
                        error!(
                            error_code = ?err.code,
                            extended_code = ?err.extended_code,
                            message = ?msg,
                            "SQLite错误详情"
                        );
                    }

                    return Err(anyhow!(
                        "保存模板素材失败 (material_id: {}, index: {}, template_id: {}): {}",
                        material.id, index, material.template_id, e
                    ));
                }
            }
        }

        info!(
            template_id = %template.id,
            materials_count = %template.materials.len(),
            "模板素材保存成功"
        );

        // 保存轨道
        for (track_index, track) in template.tracks.iter().enumerate() {
            // 先尝试INSERT，如果失败则UPDATE
            let insert_result = tx.execute(
                "INSERT INTO tracks (
                    id, template_id, name, track_type, track_index,
                    created_at, updated_at
                ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
                params![
                    track.id,
                    track.template_id,
                    track.name,
                    format!("{:?}", track.track_type),
                    track.track_index,
                    track.created_at.to_rfc3339(),
                    track.updated_at.to_rfc3339()
                ],
            );

            match insert_result {
                Ok(rows) => {
                    info!(
                        track_id = %track.id,
                        rows_affected = %rows,
                        "轨道INSERT成功"
                    );
                }
                Err(rusqlite::Error::SqliteFailure(err, _))
                    if err.code == rusqlite::ErrorCode::ConstraintViolation => {
                    // 如果是主键冲突，尝试UPDATE
                    info!(
                        track_id = %track.id,
                        "轨道已存在，尝试UPDATE"
                    );

                    tx.execute(
                        "UPDATE tracks SET
                            template_id = ?2, name = ?3, track_type = ?4, track_index = ?5,
                            updated_at = ?6
                         WHERE id = ?1",
                        params![
                            track.id,
                            track.template_id,
                            track.name,
                            format!("{:?}", track.track_type),
                            track.track_index,
                            track.updated_at.to_rfc3339()
                        ],
                    ).map_err(|e| anyhow!(
                        "更新轨道失败 (track_id: {}, index: {}, template_id: {}): {}",
                        track.id, track_index, track.template_id, e
                    ))?;
                }
                Err(e) => {
                    return Err(anyhow!(
                        "保存轨道失败 (track_id: {}, index: {}, template_id: {}): {}",
                        track.id, track_index, track.template_id, e
                    ));
                }
            }

            // 保存轨道片段
            for (segment_index, segment) in track.segments.iter().enumerate() {
                // 验证片段的素材引用
                if let Some(material_id) = &segment.template_material_id {
                    let material_exists = template.materials.iter()
                        .any(|m| m.id == *material_id);

                    if !material_exists {
                        warn!(
                            segment_id = %segment.id,
                            material_id = %material_id,
                            track_id = %track.id,
                            "轨道片段引用了不存在的素材ID，将设置为NULL"
                        );
                        // 不要直接返回错误，而是记录警告并继续
                    }
                }

                let matching_rule_json = serde_json::to_string(&segment.matching_rule)
                    .unwrap_or_else(|_| r#"{"FixedMaterial":{}}"#.to_string());

                // 如果素材不存在，将 template_material_id 设置为 None
                let validated_material_id = segment.template_material_id.as_ref()
                    .filter(|material_id| {
                        template.materials.iter().any(|m| m.id == **material_id)
                    });

                // 先尝试INSERT，如果失败则UPDATE
                let insert_result = tx.execute(
                    "INSERT INTO track_segments (
                        id, track_id, template_material_id, name, start_time, end_time,
                        duration, segment_index, properties, matching_rule, created_at, updated_at
                    ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)",
                    params![
                        segment.id,
                        segment.track_id,
                        validated_material_id,
                        segment.name,
                        segment.start_time as i64,
                        segment.end_time as i64,
                        segment.duration as i64,
                        segment.segment_index,
                        segment.properties,
                        matching_rule_json,
                        segment.created_at.to_rfc3339(),
                        segment.updated_at.to_rfc3339()
                    ],
                );

                match insert_result {
                    Ok(rows) => {
                        info!(
                            segment_id = %segment.id,
                            rows_affected = %rows,
                            "轨道片段INSERT成功"
                        );
                    }
                    Err(rusqlite::Error::SqliteFailure(err, _))
                        if err.code == rusqlite::ErrorCode::ConstraintViolation => {
                        // 如果是主键冲突，尝试UPDATE
                        info!(
                            segment_id = %segment.id,
                            "轨道片段已存在，尝试UPDATE"
                        );

                        tx.execute(
                            "UPDATE track_segments SET
                                track_id = ?2, template_material_id = ?3, name = ?4,
                                start_time = ?5, end_time = ?6, duration = ?7,
                                segment_index = ?8, properties = ?9, matching_rule = ?10,
                                updated_at = ?11
                             WHERE id = ?1",
                            params![
                                segment.id,
                                segment.track_id,
                                validated_material_id,
                                segment.name,
                                segment.start_time as i64,
                                segment.end_time as i64,
                                segment.duration as i64,
                                segment.segment_index,
                                segment.properties,
                                matching_rule_json,
                                segment.updated_at.to_rfc3339()
                            ],
                        ).map_err(|e| anyhow!(
                            "更新轨道片段失败 (segment_id: {}, index: {}, track_id: {}, material_id: {:?}): {}",
                            segment.id, segment_index, segment.track_id, validated_material_id, e
                        ))?;
                    }
                    Err(e) => {
                        return Err(anyhow!(
                            "保存轨道片段失败 (segment_id: {}, index: {}, track_id: {}, material_id: {:?}): {}",
                            segment.id, segment_index, segment.track_id, validated_material_id, e
                        ));
                    }
                }
            }
        }

        info!(
            template_id = %template.id,
            tracks_count = %template.tracks.len(),
            total_segments = %template.tracks.iter().map(|t| t.segments.len()).sum::<usize>(),
            "轨道和片段保存成功"
        );

        // 提交事务
        tx.commit().map_err(|e| anyhow!("提交事务失败: {}", e))?;

        info!(
            template_id = %template.id,
            template_name = %template.name,
            "模板保存完成"
        );

        Ok(())
    }

    /// 根据ID获取模板
    pub async fn get_template_by_id(&self, template_id: &str) -> Result<Option<Template>> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        // 查询模板基本信息
        let template_result = pooled_conn.query_row(
            "SELECT id, name, description, canvas_width, canvas_height,
                    canvas_ratio, duration, fps, import_status, source_file_path,
                    created_at, updated_at, is_active
             FROM templates WHERE id = ?1",
            params![template_id],
            |row| self.row_to_template_basic(row),
        );

        let mut template = match template_result {
            Ok(template) => template,
            Err(rusqlite::Error::QueryReturnedNoRows) => return Ok(None),
            Err(e) => return Err(e.into()),
        };

        // 查询素材
        let mut stmt = pooled_conn.prepare(
            "SELECT id, template_id, original_id, name, material_type, original_path,
                    remote_url, file_size, duration, width, height, upload_status,
                    file_exists, upload_success, metadata, created_at, updated_at
             FROM template_materials WHERE template_id = ?1"
        )?;

        let material_rows = stmt.query_map(params![template_id], |row| {
            self.row_to_template_material(row)
        })?;

        for material_result in material_rows {
            template.materials.push(material_result?);
        }

        // 查询轨道
        let mut stmt = pooled_conn.prepare(
            "SELECT id, template_id, name, track_type, track_index, created_at, updated_at
             FROM tracks WHERE template_id = ?1 ORDER BY track_index"
        )?;

        let track_rows = stmt.query_map(params![template_id], |row| {
            self.row_to_track_basic(row)
        })?;

        for track_result in track_rows {
            let mut track = track_result?;
            
            // 查询轨道片段
            let mut segment_stmt = pooled_conn.prepare(
                "SELECT id, track_id, template_material_id, name, start_time, end_time,
                        duration, segment_index, properties, matching_rule, created_at, updated_at
                 FROM track_segments WHERE track_id = ?1 ORDER BY segment_index"
            )?;

            let segment_rows = segment_stmt.query_map(params![track.id], |row| {
                self.row_to_track_segment(row)
            })?;

            for segment_result in segment_rows {
                track.segments.push(segment_result?);
            }

            template.tracks.push(track);
        }

        Ok(Some(template))
    }

    /// 查询模板列表
    pub async fn list_templates(&self, options: TemplateQueryOptions) -> Result<TemplateListResponse> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let pooled_conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;
        // 简化的查询方法，使用具体的参数而不是动态构建
        if let Some(project_id) = &options.project_id {
            if let Some(import_status) = &options.import_status {
                // 有项目ID和状态过滤
                let status_str = format!("{:?}", import_status);
                let total: u32 = pooled_conn.query_row(
                    "SELECT COUNT(*) FROM templates WHERE is_active = 1 AND project_id = ?1 AND import_status = ?2",
                    params![project_id, status_str],
                    |row| Ok(row.get::<_, i64>(0)? as u32)
                )?;

                let mut sql = "SELECT id, name, description, project_id, canvas_width, canvas_height,
                        canvas_ratio, duration, fps, import_status, source_file_path,
                        created_at, updated_at, is_active
                 FROM templates WHERE is_active = 1 AND project_id = ?1 AND import_status = ?2 ORDER BY created_at DESC".to_string();

                if let Some(limit) = options.limit {
                    sql.push_str(&format!(" LIMIT {}", limit));
                    if let Some(offset) = options.offset {
                        sql.push_str(&format!(" OFFSET {}", offset));
                    }
                }

                let mut stmt = pooled_conn.prepare(&sql)?;
                let template_rows = stmt.query_map(params![project_id, status_str], |row| {
                    self.row_to_template_basic(row)
                })?;

                let mut templates = Vec::new();
                for template_result in template_rows {
                    let mut template = template_result?;
                    // 填充素材和轨道数量
                    self.fill_template_counts(&pooled_conn, &mut template)?;
                    templates.push(template);
                }

                return Ok(TemplateListResponse { templates, total });
            } else {
                // 只有项目ID过滤
                let total: u32 = pooled_conn.query_row(
                    "SELECT COUNT(*) FROM templates WHERE is_active = 1 AND project_id = ?1",
                    params![project_id],
                    |row| Ok(row.get::<_, i64>(0)? as u32)
                )?;

                let mut sql = "SELECT id, name, description, project_id, canvas_width, canvas_height,
                        canvas_ratio, duration, fps, import_status, source_file_path,
                        created_at, updated_at, is_active
                 FROM templates WHERE is_active = 1 AND project_id = ?1 ORDER BY created_at DESC".to_string();

                if let Some(limit) = options.limit {
                    sql.push_str(&format!(" LIMIT {}", limit));
                    if let Some(offset) = options.offset {
                        sql.push_str(&format!(" OFFSET {}", offset));
                    }
                }

                let mut stmt = pooled_conn.prepare(&sql)?;
                let template_rows = stmt.query_map(params![project_id], |row| {
                    self.row_to_template_basic(row)
                })?;

                let mut templates = Vec::new();
                for template_result in template_rows {
                    let mut template = template_result?;
                    // 填充素材和轨道数量
                    self.fill_template_counts(&pooled_conn, &mut template)?;
                    templates.push(template);
                }

                return Ok(TemplateListResponse { templates, total });
            }
        }

        // 默认查询所有活跃模板
        let total: u32 = pooled_conn.query_row(
            "SELECT COUNT(*) FROM templates WHERE is_active = 1",
            [],
            |row| Ok(row.get::<_, i64>(0)? as u32)
        )?;

        let mut sql = "SELECT id, name, description, canvas_width, canvas_height,
                canvas_ratio, duration, fps, import_status, source_file_path,
                created_at, updated_at, is_active
         FROM templates WHERE is_active = 1 ORDER BY created_at DESC".to_string();

        if let Some(limit) = options.limit {
            sql.push_str(&format!(" LIMIT {}", limit));
            if let Some(offset) = options.offset {
                sql.push_str(&format!(" OFFSET {}", offset));
            }
        }

        let mut stmt = pooled_conn.prepare(&sql)?;
        let template_rows = stmt.query_map([], |row| {
            self.row_to_template_basic(row)
        })?;

        let mut templates = Vec::new();
        for template_result in template_rows {
            let mut template = template_result?;
            // 填充素材和轨道数量
            self.fill_template_counts(&pooled_conn, &mut template)?;
            templates.push(template);
        }

        Ok(TemplateListResponse { templates, total })
    }

    /// 更新模板
    pub async fn update_template(&self, template: &Template) -> Result<()> {
        self.save_template(template).await
    }

    /// 软删除模板（设置为非活跃状态）
    pub async fn delete_template(&self, template_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 templates SET is_active = 0, updated_at = ?1 WHERE id = ?2",
            params![chrono::Utc::now().to_rfc3339(), template_id],
        )?;

        info!(
            template_id = %template_id,
            "模板已软删除（设置为非活跃状态）"
        );

        Ok(())
    }

    /// 硬删除模板及其所有关联数据
    /// 注意：这将永久删除模板及其所有关联的素材、轨道和片段数据
    pub async fn hard_delete_template(&self, template_id: &str) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;
        // 暂时禁用外键约束检查以避免INSERT OR REPLACE的问题
        // TODO: 在解决外键约束问题后重新启用
        conn.execute("PRAGMA foreign_keys = OFF", [])?;

        info!("外键约束已禁用以避免模板保存问题");

        // 开始事务
        let tx = conn.unchecked_transaction()?;

        // 首先检查模板是否存在
        let template_exists: bool = tx.query_row(
            "SELECT EXISTS(SELECT 1 FROM templates WHERE id = ?1)",
            params![template_id],
            |row| row.get(0)
        ).map_err(|e| anyhow!("检查模板是否存在失败: {}", e))?;

        if !template_exists {
            return Err(anyhow!("模板不存在: {}", template_id));
        }

        // 统计关联数据数量（用于日志记录）
        let segments_count: i64 = tx.query_row(
            "SELECT COUNT(*) FROM track_segments WHERE track_id IN (SELECT id FROM tracks WHERE template_id = ?1)",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        let tracks_count: i64 = tx.query_row(
            "SELECT COUNT(*) FROM tracks WHERE template_id = ?1",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        let materials_count: i64 = tx.query_row(
            "SELECT COUNT(*) FROM template_materials WHERE template_id = ?1",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        // 按照外键依赖关系的逆序删除：track_segments -> tracks -> template_materials -> templates

        // 1. 删除轨道片段
        let deleted_segments = tx.execute(
            "DELETE FROM track_segments WHERE track_id IN (SELECT id FROM tracks WHERE template_id = ?1)",
            params![template_id]
        ).map_err(|e| anyhow!("删除轨道片段失败: {}", e))?;

        // 2. 删除轨道
        let deleted_tracks = tx.execute(
            "DELETE FROM tracks WHERE template_id = ?1",
            params![template_id]
        ).map_err(|e| anyhow!("删除轨道失败: {}", e))?;

        // 3. 删除模板素材
        let deleted_materials = tx.execute(
            "DELETE FROM template_materials WHERE template_id = ?1",
            params![template_id]
        ).map_err(|e| anyhow!("删除模板素材失败: {}", e))?;

        // 4. 删除项目-模板绑定关系
        let deleted_bindings = tx.execute(
            "DELETE FROM project_template_bindings WHERE template_id = ?1",
            params![template_id]
        ).map_err(|e| anyhow!("删除项目-模板绑定关系失败: {}", e))?;

        // 5. 最后删除模板本身
        let deleted_template = tx.execute(
            "DELETE FROM templates WHERE id = ?1",
            params![template_id]
        ).map_err(|e| anyhow!("删除模板失败: {}", e))?;

        // 验证删除结果
        if deleted_template == 0 {
            return Err(anyhow!("模板删除失败，可能已被其他进程删除"));
        }

        // 提交事务
        tx.commit().map_err(|e| anyhow!("提交删除事务失败: {}", e))?;

        info!(
            template_id = %template_id,
            expected_segments = %segments_count,
            deleted_segments = %deleted_segments,
            expected_tracks = %tracks_count,
            deleted_tracks = %deleted_tracks,
            expected_materials = %materials_count,
            deleted_materials = %deleted_materials,
            deleted_bindings = %deleted_bindings,
            deleted_template = %deleted_template,
            "模板及其所有关联数据已硬删除"
        );

        // 验证删除的完整性
        if deleted_segments as i64 != segments_count {
            warn!(
                template_id = %template_id,
                expected = %segments_count,
                actual = %deleted_segments,
                "删除的轨道片段数量与预期不符"
            );
        }

        if deleted_tracks as i64 != tracks_count {
            warn!(
                template_id = %template_id,
                expected = %tracks_count,
                actual = %deleted_tracks,
                "删除的轨道数量与预期不符"
            );
        }

        if deleted_materials as i64 != materials_count {
            warn!(
                template_id = %template_id,
                expected = %materials_count,
                actual = %deleted_materials,
                "删除的素材数量与预期不符"
            );
        }

        Ok(())
    }

    /// 验证模板删除后的数据清理完整性
    pub async fn verify_template_deletion(&self, template_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 template_exists: bool = conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM templates WHERE id = ?1)",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(false);

        // 检查关联的素材是否还存在
        let materials_count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM template_materials WHERE template_id = ?1",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        // 检查关联的轨道是否还存在
        let tracks_count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM tracks WHERE template_id = ?1",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        // 检查关联的轨道片段是否还存在
        let segments_count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM track_segments WHERE track_id IN (SELECT id FROM tracks WHERE template_id = ?1)",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        // 检查项目-模板绑定关系是否还存在
        let bindings_count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM project_template_bindings WHERE template_id = ?1",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        let is_clean = !template_exists && materials_count == 0 && tracks_count == 0 && segments_count == 0 && bindings_count == 0;

        if is_clean {
            info!(
                template_id = %template_id,
                "模板删除验证通过：所有关联数据已清理"
            );
        } else {
            warn!(
                template_id = %template_id,
                template_exists = %template_exists,
                materials_count = %materials_count,
                tracks_count = %tracks_count,
                segments_count = %segments_count,
                bindings_count = %bindings_count,
                "模板删除验证失败：仍有关联数据残留"
            );
        }

        Ok(is_clean)
    }

    /// 获取模板的关联数据统计信息
    pub async fn get_template_associations(&self, template_id: &str) -> Result<TemplateAssociations> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;
        let materials_count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM template_materials WHERE template_id = ?1",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        let tracks_count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM tracks WHERE template_id = ?1",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        let segments_count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM track_segments WHERE track_id IN (SELECT id FROM tracks WHERE template_id = ?1)",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        let bindings_count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM project_template_bindings WHERE template_id = ?1",
            params![template_id],
            |row| row.get(0)
        ).unwrap_or(0);

        Ok(TemplateAssociations {
            template_id: template_id.to_string(),
            materials_count: materials_count as u32,
            tracks_count: tracks_count as u32,
            segments_count: segments_count as u32,
            bindings_count: bindings_count as u32,
        })
    }

    /// 数据库行转换为模板基本信息
    fn row_to_template_basic(&self, row: &Row) -> rusqlite::Result<Template> {
        let canvas_config = CanvasConfig {
            width: row.get::<_, i64>("canvas_width")? as u32,
            height: row.get::<_, i64>("canvas_height")? as u32,
            ratio: row.get("canvas_ratio")?,
        };

        let import_status_str: String = row.get("import_status")?;
        let import_status = match import_status_str.as_str() {
            "Pending" => ImportStatus::Pending,
            "Parsing" => ImportStatus::Parsing,
            "Uploading" => ImportStatus::Uploading,
            "Processing" => ImportStatus::Processing,
            "Completed" => ImportStatus::Completed,
            "Failed" => ImportStatus::Failed,
            _ => ImportStatus::Pending,
        };

        Ok(Template {
            id: row.get("id")?,
            name: row.get("name")?,
            description: row.get("description")?,
            canvas_config,
            duration: row.get::<_, i64>("duration")? as u64,
            fps: row.get("fps")?,
            materials: Vec::new(), // 稍后填充
            tracks: Vec::new(),    // 稍后填充
            import_status,
            source_file_path: row.get("source_file_path")?,
            created_at: {
                let created_at_str: String = row.get("created_at")?;
                chrono::DateTime::parse_from_rfc3339(&created_at_str)
                    .or_else(|_| {
                        // 尝试解析SQLite格式的日期
                        chrono::NaiveDateTime::parse_from_str(&created_at_str, "%Y-%m-%d %H:%M:%S")
                            .map(|dt| dt.and_utc().fixed_offset())
                    })
                    .map_err(|e| {
                        eprintln!("❌ 解析created_at失败: '{}', 错误: {}", created_at_str, e);
                        rusqlite::Error::InvalidColumnType(0, "created_at".to_string(), rusqlite::types::Type::Text)
                    })?
                    .with_timezone(&chrono::Utc)
            },
            updated_at: {
                let updated_at_str: String = row.get("updated_at")?;
                chrono::DateTime::parse_from_rfc3339(&updated_at_str)
                    .or_else(|_| {
                        // 尝试解析SQLite格式的日期
                        chrono::NaiveDateTime::parse_from_str(&updated_at_str, "%Y-%m-%d %H:%M:%S")
                            .map(|dt| dt.and_utc().fixed_offset())
                    })
                    .map_err(|e| {
                        eprintln!("❌ 解析updated_at失败: '{}', 错误: {}", updated_at_str, e);
                        rusqlite::Error::InvalidColumnType(0, "updated_at".to_string(), rusqlite::types::Type::Text)
                    })?
                    .with_timezone(&chrono::Utc)
            },
            is_active: row.get("is_active")?,
        })
    }

    /// 数据库行转换为模板素材
    fn row_to_template_material(&self, row: &Row) -> rusqlite::Result<TemplateMaterial> {
        let material_type_str: String = row.get("material_type")?;
        let material_type = match material_type_str.as_str() {
            "Video" => TemplateMaterialType::Video,
            "Audio" => TemplateMaterialType::Audio,
            "Image" => TemplateMaterialType::Image,
            "Text" => TemplateMaterialType::Text,
            "Effect" => TemplateMaterialType::Effect,
            "Sticker" => TemplateMaterialType::Sticker,
            "Canvas" => TemplateMaterialType::Canvas,
            other => TemplateMaterialType::Other(other.to_string()),
        };

        let upload_status_str: String = row.get("upload_status")?;
        let upload_status = match upload_status_str.as_str() {
            "Pending" => UploadStatus::Pending,
            "Uploading" => UploadStatus::Uploading,
            "Completed" => UploadStatus::Completed,
            "Failed" => UploadStatus::Failed,
            "Skipped" => UploadStatus::Skipped,
            _ => UploadStatus::Pending,
        };

        Ok(TemplateMaterial {
            id: row.get("id")?,
            template_id: row.get("template_id")?,
            original_id: row.get("original_id")?,
            name: row.get("name")?,
            material_type,
            original_path: row.get("original_path")?,
            remote_url: row.get("remote_url")?,
            file_size: row.get::<_, Option<i64>>("file_size")?.map(|s| s as u64),
            duration: row.get::<_, Option<i64>>("duration")?.map(|d| d as u64),
            width: row.get::<_, Option<i64>>("width")?.map(|w| w as u32),
            height: row.get::<_, Option<i64>>("height")?.map(|h| h as u32),
            upload_status,
            file_exists: row.get::<_, Option<bool>>("file_exists")?.unwrap_or(false),
            upload_success: row.get::<_, Option<bool>>("upload_success")?.unwrap_or(false),
            metadata: row.get("metadata")?,
            created_at: {
                let created_at_str: String = row.get("created_at")?;
                chrono::DateTime::parse_from_rfc3339(&created_at_str)
                    .or_else(|_| {
                        chrono::NaiveDateTime::parse_from_str(&created_at_str, "%Y-%m-%d %H:%M:%S")
                            .map(|dt| dt.and_utc().fixed_offset())
                    })
                    .map_err(|e| {
                        eprintln!("❌ 解析素材created_at失败: '{}', 错误: {}", created_at_str, e);
                        rusqlite::Error::InvalidColumnType(0, "created_at".to_string(), rusqlite::types::Type::Text)
                    })?
                    .with_timezone(&chrono::Utc)
            },
            updated_at: {
                let updated_at_str: String = row.get("updated_at")?;
                chrono::DateTime::parse_from_rfc3339(&updated_at_str)
                    .or_else(|_| {
                        chrono::NaiveDateTime::parse_from_str(&updated_at_str, "%Y-%m-%d %H:%M:%S")
                            .map(|dt| dt.and_utc().fixed_offset())
                    })
                    .map_err(|e| {
                        eprintln!("❌ 解析素材updated_at失败: '{}', 错误: {}", updated_at_str, e);
                        rusqlite::Error::InvalidColumnType(0, "updated_at".to_string(), rusqlite::types::Type::Text)
                    })?
                    .with_timezone(&chrono::Utc)
            },
        })
    }

    /// 数据库行转换为轨道基本信息
    fn row_to_track_basic(&self, row: &Row) -> rusqlite::Result<Track> {
        let track_type_str: String = row.get("track_type")?;
        let track_type = match track_type_str.as_str() {
            "Video" => TrackType::Video,
            "Audio" => TrackType::Audio,
            "Text" => TrackType::Text,
            "Sticker" => TrackType::Sticker,
            "Effect" => TrackType::Effect,
            other => TrackType::Other(other.to_string()),
        };

        Ok(Track {
            id: row.get("id")?,
            template_id: row.get("template_id")?,
            name: row.get("name")?,
            track_type,
            track_index: row.get::<_, i64>("track_index")? as u32,
            segments: Vec::new(), // 稍后填充
            created_at: chrono::DateTime::parse_from_rfc3339(&row.get::<_, String>("created_at")?)
                .unwrap()
                .with_timezone(&chrono::Utc),
            updated_at: chrono::DateTime::parse_from_rfc3339(&row.get::<_, String>("updated_at")?)
                .unwrap()
                .with_timezone(&chrono::Utc),
        })
    }

    /// 数据库行转换为轨道片段
    fn row_to_track_segment(&self, row: &Row) -> rusqlite::Result<TrackSegment> {
        // 解析匹配规则，如果解析失败则使用默认值
        let matching_rule = row.get::<_, Option<String>>("matching_rule")?
            .and_then(|rule_str| serde_json::from_str(&rule_str).ok())
            .unwrap_or_default();

        Ok(TrackSegment {
            id: row.get("id")?,
            track_id: row.get("track_id")?,
            template_material_id: row.get("template_material_id")?,
            name: row.get("name")?,
            start_time: row.get::<_, i64>("start_time")? as u64,
            end_time: row.get::<_, i64>("end_time")? as u64,
            duration: row.get::<_, i64>("duration")? as u64,
            segment_index: row.get::<_, i64>("segment_index")? as u32,
            properties: row.get("properties")?,
            matching_rule,
            created_at: chrono::DateTime::parse_from_rfc3339(&row.get::<_, String>("created_at")?)
                .unwrap()
                .with_timezone(&chrono::Utc),
            updated_at: chrono::DateTime::parse_from_rfc3339(&row.get::<_, String>("updated_at")?)
                .unwrap()
                .with_timezone(&chrono::Utc),
        })
    }

    /// 填充模板的真实素材和轨道数据
    fn fill_template_counts(&self, conn: &rusqlite::Connection, template: &mut Template) -> Result<()> {
        // 查询真实的素材数据
        let mut stmt = conn.prepare(
            "SELECT id, template_id, original_id, name, material_type, original_path,
                    remote_url, file_size, duration, width, height, upload_status,
                    file_exists, upload_success, metadata, created_at, updated_at
             FROM template_materials WHERE template_id = ?1"
        )?;

        let material_rows = stmt.query_map(params![template.id], |row| {
            self.row_to_template_material(row)
        })?;

        for material_result in material_rows {
            template.materials.push(material_result?);
        }

        // 查询真实的轨道数据
        let mut stmt = conn.prepare(
            "SELECT id, template_id, name, track_type, track_index, created_at, updated_at
             FROM tracks WHERE template_id = ?1 ORDER BY track_index"
        )?;

        let track_rows = stmt.query_map(params![template.id], |row| {
            self.row_to_track_basic(row)
        })?;

        for track_result in track_rows {
            let mut track = track_result?;

            // 查询每个轨道的真实片段数据
            let mut segment_stmt = conn.prepare(
                "SELECT id, track_id, template_material_id, name, start_time, end_time,
                        duration, segment_index, properties, matching_rule, created_at, updated_at
                 FROM track_segments WHERE track_id = ?1 ORDER BY segment_index"
            )?;

            let segment_rows = segment_stmt.query_map(params![track.id], |row| {
                self.row_to_track_segment(row)
            })?;

            for segment_result in segment_rows {
                track.segments.push(segment_result?);
            }

            template.tracks.push(track);
        }

        Ok(())
    }

    /// 更新轨道片段的匹配规则
    pub async fn update_segment_matching_rule(
        &self,
        segment_id: &str,
        matching_rule: SegmentMatchingRule,
    ) -> Result<()> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

        let conn = self
            .database
            .acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;
        let matching_rule_json = serde_json::to_string(&matching_rule)
            .map_err(|e| anyhow!("Failed to serialize matching rule: {}", e))?;

        let updated_rows = conn.execute(
            "UPDATE track_segments SET matching_rule = ?1, updated_at = ?2 WHERE id = ?3",
            params![
                matching_rule_json,
                chrono::Utc::now().to_rfc3339(),
                segment_id
            ],
        )?;

        if updated_rows == 0 {
            return Err(anyhow!("Track segment not found: {}", segment_id));
        }

        Ok(())
    }

    /// 获取轨道片段的匹配规则
    pub async fn get_segment_matching_rule(&self, segment_id: &str) -> Result<SegmentMatchingRule> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let matching_rule_json: Option<String> = match conn.query_row(
            "SELECT matching_rule FROM track_segments WHERE id = ?1",
            params![segment_id],
            |row| row.get(0),
        ) {
            Ok(value) => Some(value),
            Err(rusqlite::Error::QueryReturnedNoRows) => None,
            Err(e) => return Err(anyhow!("Database error: {}", e)),
        };

        let matching_rule = matching_rule_json
            .and_then(|rule_str| serde_json::from_str(&rule_str).ok())
            .unwrap_or_default();

        Ok(matching_rule)
    }

    /// 根据ID获取轨道信息
    pub async fn get_track_by_id(&self, track_id: &str) -> Result<Option<Track>> {
        if !self.database.has_pool() {
            return Err(anyhow!("连接池未启用，无法安全执行数据库操作"));
        }

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

        let track_result = conn.query_row(
            "SELECT id, template_id, name, track_type, track_index, created_at, updated_at
             FROM tracks WHERE id = ?1",
            params![track_id],
            |row| self.row_to_track_basic(row),
        );

        let mut track = match track_result {
            Ok(track) => track,
            Err(rusqlite::Error::QueryReturnedNoRows) => return Ok(None),
            Err(e) => return Err(e.into()),
        };

        // 查询轨道片段
        let mut segment_stmt = conn.prepare(
            "SELECT id, track_id, template_material_id, name, start_time, end_time,
                    duration, segment_index, properties, matching_rule, created_at, updated_at
             FROM track_segments WHERE track_id = ?1 ORDER BY segment_index"
        )?;

        let segment_rows = segment_stmt.query_map(params![track.id], |row| {
            self.row_to_track_segment(row)
        })?;

        for segment_result in segment_rows {
            track.segments.push(segment_result?);
        }

        Ok(Some(track))
    }

    /// 验证模板数据的完整性和一致性
    fn validate_template_data(&self, template: &Template) -> Result<()> {
        // 验证模板基本信息
        if template.id.is_empty() {
            return Err(anyhow!("模板ID不能为空"));
        }
        if template.name.is_empty() {
            return Err(anyhow!("模板名称不能为空"));
        }

        // 验证所有素材的 template_id 匹配
        for (index, material) in template.materials.iter().enumerate() {
            if material.id.is_empty() {
                return Err(anyhow!("素材ID不能为空 (index: {})", index));
            }
            if material.template_id != template.id {
                return Err(anyhow!(
                    "素材 {} (index: {}) 的 template_id ({}) 与模板ID ({}) 不匹配",
                    material.id, index, material.template_id, template.id
                ));
            }
        }

        // 检查素材ID是否有重复
        let mut material_ids = std::collections::HashSet::new();
        for material in &template.materials {
            if !material_ids.insert(&material.id) {
                return Err(anyhow!("发现重复的素材ID: {}", material.id));
            }
        }

        // 验证所有轨道的 template_id 匹配
        for (track_index, track) in template.tracks.iter().enumerate() {
            if track.id.is_empty() {
                return Err(anyhow!("轨道ID不能为空 (index: {})", track_index));
            }
            if track.template_id != template.id {
                return Err(anyhow!(
                    "轨道 {} (index: {}) 的 template_id ({}) 与模板ID ({}) 不匹配",
                    track.id, track_index, track.template_id, template.id
                ));
            }

            // 验证轨道片段
            for (segment_index, segment) in track.segments.iter().enumerate() {
                if segment.id.is_empty() {
                    return Err(anyhow!(
                        "轨道片段ID不能为空 (track_index: {}, segment_index: {})",
                        track_index, segment_index
                    ));
                }
                if segment.track_id != track.id {
                    return Err(anyhow!(
                        "片段 {} (track_index: {}, segment_index: {}) 的 track_id ({}) 与轨道ID ({}) 不匹配",
                        segment.id, track_index, segment_index, segment.track_id, track.id
                    ));
                }

                // 验证素材引用（如果存在）
                if let Some(material_id) = &segment.template_material_id {
                    let material_exists = template.materials.iter()
                        .any(|m| m.id == *material_id);
                    if !material_exists {
                        warn!(
                            segment_id = %segment.id,
                            material_id = %material_id,
                            track_id = %track.id,
                            "片段引用了不存在的素材ID，这将在保存时被设置为NULL"
                        );
                    }
                }

                // 验证时间范围
                if segment.start_time >= segment.end_time {
                    return Err(anyhow!(
                        "片段 {} 的时间范围无效: start_time ({}) >= end_time ({})",
                        segment.id, segment.start_time, segment.end_time
                    ));
                }
                if segment.duration != segment.end_time - segment.start_time {
                    warn!(
                        segment_id = %segment.id,
                        calculated_duration = %(segment.end_time - segment.start_time),
                        stored_duration = %segment.duration,
                        "片段的duration字段与计算值不匹配"
                    );
                }
            }
        }

        // 检查轨道ID是否有重复
        let mut track_ids = std::collections::HashSet::new();
        for track in &template.tracks {
            if !track_ids.insert(&track.id) {
                return Err(anyhow!("发现重复的轨道ID: {}", track.id));
            }
        }

        // 检查片段ID是否有重复
        let mut segment_ids = std::collections::HashSet::new();
        for track in &template.tracks {
            for segment in &track.segments {
                if !segment_ids.insert(&segment.id) {
                    return Err(anyhow!("发现重复的片段ID: {}", segment.id));
                }
            }
        }

        info!(
            template_id = %template.id,
            template_name = %template.name,
            materials_count = %template.materials.len(),
            tracks_count = %template.tracks.len(),
            total_segments = %template.tracks.iter().map(|t| t.segments.len()).sum::<usize>(),
            "模板数据验证通过"
        );

        Ok(())
    }
}
