use anyhow::Result;
use rusqlite::{Row, params};
use std::sync::Arc;
use chrono::DateTime;
use crate::data::models::outfit_photo_generation::{
    OutfitPhotoGeneration, GenerationStatus
};
use crate::infrastructure::database::Database;

/// 穿搭照片生成仓库
/// 遵循 Tauri 开发规范的数据访问层设计
#[derive(Clone)]
pub struct OutfitPhotoGenerationRepository {
    database: Arc<Database>,
}

impl OutfitPhotoGenerationRepository {
    /// 创建新的穿搭照片生成仓库实例
    pub fn new(database: Arc<Database>) -> Result<Self> {
        Ok(Self { database })
    }

    /// 创建穿搭照片生成记录
    pub fn create(&self, generation: &OutfitPhotoGeneration) -> Result<()> {
        // 使用最佳连接进行写操作
        let conn_handle = self.database.get_best_connection()?;

        conn_handle.execute(
            r#"
            INSERT INTO outfit_photo_generations (
                id, project_id, model_id, product_image_path, product_image_url,
                prompt, negative_prompt, status, workflow_id, comfyui_prompt_id,
                result_image_paths, result_image_urls, error_message,
                started_at, completed_at, generation_time_ms, created_at, updated_at
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18)
            "#,
            params![
                &generation.id,
                &generation.project_id,
                &generation.model_id,
                &generation.product_image_path,
                &generation.product_image_url,
                &generation.prompt,
                &generation.negative_prompt,
                serde_json::to_string(&generation.status)?,
                &generation.workflow_id,
                &generation.comfyui_prompt_id,
                serde_json::to_string(&generation.result_image_paths)?,
                serde_json::to_string(&generation.result_image_urls)?,
                &generation.error_message,
                generation.started_at.timestamp(),
                generation.completed_at.map(|t| t.timestamp()),
                generation.generation_time_ms.map(|t| t as i64),
                generation.created_at.timestamp(),
                generation.updated_at.timestamp(),
            ],
        )?;

        Ok(())
    }

    /// 根据ID获取穿搭照片生成记录
    pub fn get_by_id(&self, id: &str) -> Result<Option<OutfitPhotoGeneration>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            r#"
            SELECT id, project_id, model_id, product_image_path, product_image_url,
                   prompt, negative_prompt, status, workflow_id, comfyui_prompt_id,
                   result_image_paths, result_image_urls, error_message,
                   started_at, completed_at, generation_time_ms, created_at, updated_at
            FROM outfit_photo_generations WHERE id = ?1
            "#
        )?;

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

        for generation in generation_iter {
            return Ok(Some(generation?));
        }

        Ok(None)
    }

    /// 根据 ComfyUI prompt ID 获取穿搭照片生成记录
    pub fn get_by_comfyui_prompt_id(&self, prompt_id: &str) -> Result<Option<OutfitPhotoGeneration>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            r#"
            SELECT id, project_id, model_id, product_image_path, product_image_url,
                   prompt, negative_prompt, status, workflow_id, comfyui_prompt_id,
                   result_image_paths, result_image_urls, error_message,
                   started_at, completed_at, generation_time_ms, created_at, updated_at
            FROM outfit_photo_generations WHERE comfyui_prompt_id = ?1
            "#
        )?;

        let generation_iter = stmt.query_map([prompt_id], |row| {
            self.row_to_generation(row)
        })?;

        for generation in generation_iter {
            return Ok(Some(generation?));
        }

        Ok(None)
    }

    /// 根据项目ID获取穿搭照片生成记录
    pub fn get_by_project_id(&self, project_id: &str) -> Result<Vec<OutfitPhotoGeneration>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            r#"
            SELECT id, project_id, model_id, product_image_path, product_image_url,
                   prompt, negative_prompt, status, workflow_id, comfyui_prompt_id,
                   result_image_paths, result_image_urls, error_message,
                   started_at, completed_at, generation_time_ms, created_at, updated_at
            FROM outfit_photo_generations WHERE project_id = ?1 ORDER BY created_at DESC
            "#
        )?;

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

        let mut generations = Vec::new();
        for generation in generation_iter {
            generations.push(generation?);
        }

        Ok(generations)
    }

    /// 根据模特ID获取穿搭照片生成记录
    pub fn get_by_model_id(&self, model_id: &str) -> Result<Vec<OutfitPhotoGeneration>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            r#"
            SELECT id, project_id, model_id, product_image_path, product_image_url,
                   prompt, negative_prompt, status, workflow_id, comfyui_prompt_id,
                   result_image_paths, result_image_urls, error_message,
                   started_at, completed_at, generation_time_ms, created_at, updated_at
            FROM outfit_photo_generations WHERE model_id = ?1 ORDER BY created_at DESC
            "#
        )?;

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

        let mut generations = Vec::new();
        for generation in generation_iter {
            generations.push(generation?);
        }

        Ok(generations)
    }

    /// 根据状态获取穿搭照片生成记录
    pub fn get_by_status(&self, status: &GenerationStatus) -> Result<Vec<OutfitPhotoGeneration>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let status_json = serde_json::to_string(status)?;

        let mut stmt = conn_handle.prepare(
            r#"
            SELECT id, project_id, model_id, product_image_path, product_image_url,
                   prompt, negative_prompt, status, workflow_id, comfyui_prompt_id,
                   result_image_paths, result_image_urls, error_message,
                   started_at, completed_at, generation_time_ms, created_at, updated_at
            FROM outfit_photo_generations WHERE status = ?1 ORDER BY created_at DESC
            "#
        )?;

        let generation_iter = stmt.query_map([status_json], |row| {
            self.row_to_generation(row)
        })?;

        let mut generations = Vec::new();
        for generation in generation_iter {
            generations.push(generation?);
        }

        Ok(generations)
    }

    /// 获取所有穿搭照片生成记录
    pub fn get_all(&self) -> Result<Vec<OutfitPhotoGeneration>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            r#"
            SELECT id, project_id, model_id, product_image_path, product_image_url,
                   prompt, negative_prompt, status, workflow_id, comfyui_prompt_id,
                   result_image_paths, result_image_urls, error_message,
                   started_at, completed_at, generation_time_ms, created_at, updated_at
            FROM outfit_photo_generations ORDER BY created_at DESC
            "#
        )?;

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

        let mut generations = Vec::new();
        for generation in generation_iter {
            generations.push(generation?);
        }

        Ok(generations)
    }

    /// 更新穿搭照片生成记录
    pub fn update(&self, generation: &OutfitPhotoGeneration) -> Result<()> {
        // 使用最佳连接进行写操作
        let conn_handle = self.database.get_best_connection()?;

        conn_handle.execute(
            r#"
            UPDATE outfit_photo_generations SET
                product_image_url = ?2, status = ?3, workflow_id = ?4, comfyui_prompt_id = ?5,
                result_image_paths = ?6, result_image_urls = ?7, error_message = ?8,
                completed_at = ?9, generation_time_ms = ?10, updated_at = ?11
            WHERE id = ?1
            "#,
            params![
                &generation.id,
                &generation.product_image_url,
                serde_json::to_string(&generation.status)?,
                &generation.workflow_id,
                &generation.comfyui_prompt_id,
                serde_json::to_string(&generation.result_image_paths)?,
                serde_json::to_string(&generation.result_image_urls)?,
                &generation.error_message,
                generation.completed_at.map(|t| t.timestamp()),
                generation.generation_time_ms.map(|t| t as i64),
                generation.updated_at.timestamp(),
            ],
        )?;

        Ok(())
    }

    /// 删除穿搭照片生成记录
    pub fn delete(&self, id: &str) -> Result<()> {
        // 使用最佳连接进行写操作
        let conn_handle = self.database.get_best_connection()?;

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

        Ok(())
    }

    /// 根据项目ID删除所有相关记录
    pub fn delete_by_project_id(&self, project_id: &str) -> Result<()> {
        // 使用最佳连接进行写操作
        let conn_handle = self.database.get_best_connection()?;

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

        Ok(())
    }

    /// 获取项目的生成统计信息
    pub fn get_project_stats(&self, project_id: &str) -> Result<(u32, u32, u32, u32)> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            r#"
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = '"Pending"' THEN 1 ELSE 0 END) as pending,
                SUM(CASE WHEN status = '"Processing"' THEN 1 ELSE 0 END) as processing,
                SUM(CASE WHEN status = '"Completed"' THEN 1 ELSE 0 END) as completed,
                SUM(CASE WHEN status = '"Failed"' THEN 1 ELSE 0 END) as failed
            FROM outfit_photo_generations WHERE project_id = ?1
            "#
        )?;

        let result = stmt.query_row([project_id], |row| {
            Ok((
                row.get::<_, i64>(0)? as u32,  // total
                row.get::<_, i64>(1)? as u32,  // pending
                row.get::<_, i64>(2)? as u32,  // processing
                row.get::<_, i64>(3)? as u32,  // completed
            ))
        })?;

        Ok(result)
    }

    /// 将数据库行转换为OutfitPhotoGeneration对象
    fn row_to_generation(&self, row: &Row) -> rusqlite::Result<OutfitPhotoGeneration> {
        let started_at_timestamp: i64 = row.get("started_at")?;
        let created_at_timestamp: i64 = row.get("created_at")?;
        let updated_at_timestamp: i64 = row.get("updated_at")?;

        let completed_at_timestamp: Option<i64> = row.get("completed_at")?;
        let generation_time_ms: Option<i64> = row.get("generation_time_ms")?;

        let status_json: String = row.get("status")?;
        let status: GenerationStatus = serde_json::from_str(&status_json)
            .map_err(|e| rusqlite::Error::FromSqlConversionFailure(
                0, rusqlite::types::Type::Text, Box::new(e)
            ))?;

        let result_image_paths_json: String = row.get("result_image_paths")?;
        let result_image_paths: Vec<String> = serde_json::from_str(&result_image_paths_json)
            .map_err(|e| rusqlite::Error::FromSqlConversionFailure(
                0, rusqlite::types::Type::Text, Box::new(e)
            ))?;

        let result_image_urls_json: String = row.get("result_image_urls")?;
        let result_image_urls: Vec<String> = serde_json::from_str(&result_image_urls_json)
            .map_err(|e| rusqlite::Error::FromSqlConversionFailure(
                0, rusqlite::types::Type::Text, Box::new(e)
            ))?;

        Ok(OutfitPhotoGeneration {
            id: row.get("id")?,
            project_id: row.get("project_id")?,
            model_id: row.get("model_id")?,
            product_image_path: row.get("product_image_path")?,
            product_image_url: row.get("product_image_url")?,
            prompt: row.get("prompt")?,
            negative_prompt: row.get("negative_prompt")?,
            status,
            workflow_id: row.get("workflow_id")?,
            comfyui_prompt_id: row.get("comfyui_prompt_id")?,
            result_image_paths,
            result_image_urls,
            error_message: row.get("error_message")?,
            started_at: DateTime::from_timestamp(started_at_timestamp, 0)
                .ok_or_else(|| rusqlite::Error::InvalidColumnType(0, "started_at".to_string(), rusqlite::types::Type::Integer))?,
            completed_at: completed_at_timestamp
                .map(|ts| DateTime::from_timestamp(ts, 0))
                .flatten(),
            generation_time_ms: generation_time_ms.map(|ms| ms as u64),
            created_at: DateTime::from_timestamp(created_at_timestamp, 0)
                .ok_or_else(|| rusqlite::Error::InvalidColumnType(0, "created_at".to_string(), rusqlite::types::Type::Integer))?,
            updated_at: DateTime::from_timestamp(updated_at_timestamp, 0)
                .ok_or_else(|| rusqlite::Error::InvalidColumnType(0, "updated_at".to_string(), rusqlite::types::Type::Integer))?,
        })
    }

    /// 获取分页的穿搭照片生成记录
    pub fn get_paginated(&self, limit: u32, offset: u32) -> Result<Vec<OutfitPhotoGeneration>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            r#"
            SELECT id, project_id, model_id, product_image_path, product_image_url,
                   prompt, negative_prompt, status, workflow_id, comfyui_prompt_id,
                   result_image_paths, result_image_urls, error_message,
                   started_at, completed_at, generation_time_ms, created_at, updated_at
            FROM outfit_photo_generations
            ORDER BY created_at DESC
            LIMIT ?1 OFFSET ?2
            "#
        )?;

        let generation_iter = stmt.query_map([limit, offset], |row| {
            self.row_to_generation(row)
        })?;

        let mut generations = Vec::new();
        for generation in generation_iter {
            generations.push(generation?);
        }

        Ok(generations)
    }

    /// 获取项目的分页穿搭照片生成记录
    pub fn get_by_project_id_paginated(&self, project_id: &str, limit: u32, offset: u32) -> Result<Vec<OutfitPhotoGeneration>> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let mut stmt = conn_handle.prepare(
            r#"
            SELECT id, project_id, model_id, product_image_path, product_image_url,
                   prompt, negative_prompt, status, workflow_id, comfyui_prompt_id,
                   result_image_paths, result_image_urls, error_message,
                   started_at, completed_at, generation_time_ms, created_at, updated_at
            FROM outfit_photo_generations
            WHERE project_id = ?1
            ORDER BY created_at DESC
            LIMIT ?2 OFFSET ?3
            "#
        )?;

        let generation_iter = stmt.query_map([project_id, &limit.to_string(), &offset.to_string()], |row| {
            self.row_to_generation(row)
        })?;

        let mut generations = Vec::new();
        for generation in generation_iter {
            generations.push(generation?);
        }

        Ok(generations)
    }

    /// 获取总记录数
    pub fn count(&self) -> Result<u32> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

        let count: i64 = conn_handle.query_row(
            "SELECT COUNT(*) FROM outfit_photo_generations",
            [],
            |row| row.get(0),
        )?;

        Ok(count as u32)
    }

    /// 获取项目的总记录数
    pub fn count_by_project_id(&self, project_id: &str) -> Result<u32> {
        // 优先使用只读连接，提高并发性能
        let conn_handle = self.database.get_best_read_connection()?;

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

        Ok(count as u32)
    }
}
