use anyhow::{Result, anyhow};
use rusqlite::{params, Connection, Row, OptionalExtension};
use std::sync::{Arc};
use chrono::{DateTime, Utc};
use crate::infrastructure::database::Database;

use crate::data::models::conversation::{
    ConversationSession, ConversationMessage, ConversationHistory,
    CreateConversationSessionRequest, AddMessageRequest, ConversationHistoryQuery,
    ConversationStats, MessageRole, MessageContent,
};

/// 会话数据访问层
/// 遵循 Tauri 开发规范的数据访问层设计模式
pub struct ConversationRepository {
    database: Arc<Database>,
}

impl ConversationRepository {
    /// 创建新的会话仓库实例
    pub fn new(database: Arc<Database>) -> Self {
        Self { database }
    }

    /// 初始化会话相关数据表（强制使用连接池）
    pub fn initialize_tables(&self) -> Result<()> {
        println!("🏊 使用连接池初始化会话表...");

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

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

        // 创建会话表
        pooled_conn.execute(
            "CREATE TABLE IF NOT EXISTS conversation_sessions (
                id TEXT PRIMARY KEY,
                title TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                is_active BOOLEAN NOT NULL DEFAULT 1,
                metadata TEXT
            )",
            [],
        )?;

        // 创建消息表
        pooled_conn.execute(
            "CREATE TABLE IF NOT EXISTS conversation_messages (
                id TEXT PRIMARY KEY,
                session_id TEXT NOT NULL,
                role TEXT NOT NULL,
                content TEXT NOT NULL,
                timestamp TEXT NOT NULL,
                metadata TEXT,
                FOREIGN KEY (session_id) REFERENCES conversation_sessions (id) ON DELETE CASCADE
            )",
            [],
        )?;

        // 创建索引以提高查询性能
        pooled_conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_messages_session_timestamp
             ON conversation_messages (session_id, timestamp)",
            [],
        )?;

        pooled_conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_sessions_active_updated
             ON conversation_sessions (is_active, updated_at)",
            [],
        )?;

        println!("✅ 会话表初始化完成");
        Ok(())
    }

    /// 创建新会话（强制使用连接池）
    pub fn create_session(&self, request: CreateConversationSessionRequest) -> Result<ConversationSession> {
        let session = ConversationSession::new(request.title);
        println!("🏊 使用连接池创建会话: {}", &session.id[..8]);

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

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

        pooled_conn.execute(
            "INSERT INTO conversation_sessions (id, title, created_at, updated_at, is_active, metadata)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
            params![
                session.id,
                session.title,
                session.created_at.to_rfc3339(),
                session.updated_at.to_rfc3339(),
                session.is_active,
                request.metadata.map(|m| serde_json::to_string(&m).unwrap_or_default())
            ],
        )?;

        println!("✅ 会话创建完成");
        Ok(session)
    }

    /// 获取会话信息
    pub fn get_session(&self, session_id: &str) -> Result<Option<ConversationSession>> {
        // 使用只读连接进行查询
        match self.database.try_get_read_connection() {
            Some(conn) => {
                self.execute_get_session(&conn, session_id)
            },
            None => {
                // 如果只读连接被占用，尝试主连接
                match self.database.try_get_connection() {
                    Some(conn) => {
                        self.execute_get_session(&conn, session_id)
                    },
                    None => {
                        println!("⚠️ 所有连接都被占用，get_session返回None");
                        Ok(None)
                    }
                }
            }
        }
    }

    /// 执行获取会话的具体逻辑
    fn execute_get_session(&self, conn: &Connection, session_id: &str) -> Result<Option<ConversationSession>> {
        let mut stmt = conn.prepare(
            "SELECT id, title, created_at, updated_at, is_active, metadata 
             FROM conversation_sessions WHERE id = ?1"
        )?;

        let session = stmt.query_row(params![session_id], |row| {
            match self.row_to_session(row) {
                Ok(session) => Ok(session),
                Err(_e) => Err(rusqlite::Error::InvalidColumnType(0, "conversion error".to_string(), rusqlite::types::Type::Text)),
            }
        }).optional()?;

        Ok(session)
    }

    /// 确保会话存在，如果不存在则创建
    pub fn ensure_session_exists(&self, session_id: &str) -> Result<()> {
        match self.get_session(session_id)? {
            Some(_) => {
                println!("✅ 会话已存在: {}", session_id);
                Ok(())
            },
            None => {
                println!("🆕 会话不存在，创建新会话: {}", session_id);
                println!("🏊 使用连接池创建会话: {}", &session_id[..8]);

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

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

                // 直接插入会话记录，使用指定的session_id
                pooled_conn.execute(
                    "INSERT OR REPLACE INTO conversation_sessions (id, title, created_at, updated_at, is_active, metadata)
                     VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
                    params![
                        session_id,
                        "RAG对话会话",
                        chrono::Utc::now().to_rfc3339(),
                        chrono::Utc::now().to_rfc3339(),
                        true,
                        None::<String>
                    ],
                )?;

                println!("✅ 新会话创建成功: {}", session_id);
                Ok(())
            }
        }
    }

    /// 添加消息到会话
    pub fn add_message(&self, request: AddMessageRequest) -> Result<ConversationMessage> {
        let message = ConversationMessage::new_mixed_message(
            request.session_id.clone(),
            request.role,
            request.content,
        );

        println!("🏊 使用连接池添加消息: {}", &message.id[..8]);

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

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

        // 插入消息
        pooled_conn.execute(
            "INSERT INTO conversation_messages (id, session_id, role, content, timestamp, metadata)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
            params![
                message.id,
                message.session_id,
                message.role.to_string(),
                serde_json::to_string(&message.content)?,
                message.timestamp.to_rfc3339(),
                request.metadata.map(|m| serde_json::to_string(&m).unwrap_or_default())
            ],
        )?;

        // 更新会话的最后更新时间
        pooled_conn.execute(
            "UPDATE conversation_sessions SET updated_at = ?1 WHERE id = ?2",
            params![Utc::now().to_rfc3339(), request.session_id],
        )?;

        println!("✅ 消息添加完成");
        Ok(message)
    }

    /// 获取会话历史
    pub fn get_conversation_history(&self, query: ConversationHistoryQuery) -> Result<ConversationHistory> {
        println!("🔍 使用只读连接获取会话历史");

        // 使用专用的只读连接，避免与写操作竞争
        match self.database.try_get_read_connection() {
            Some(conn) => {
                self.execute_history_query(&conn, query)
            },
            None => {
                println!("⚠️ 只读连接被占用，尝试主连接");
                // 如果只读连接被占用，尝试主连接（非阻塞）
                match self.database.try_get_connection() {
                    Some(conn) => {
                        println!("✅ 使用主连接获取历史");
                        self.execute_history_query(&conn, query)
                    },
                    None => {
                        println!("❌ 所有连接都被占用，返回空历史");
                        // 如果所有连接都被占用，返回空历史避免阻塞
                        let empty_session = ConversationSession {
                            id: query.session_id.clone(),
                            title: Some("临时会话".to_string()),
                            created_at: chrono::Utc::now(),
                            updated_at: chrono::Utc::now(),
                            is_active: true,
                            metadata: None,
                        };
                        Ok(ConversationHistory {
                            session: empty_session,
                            messages: Vec::new(),
                            total_count: 0,
                        })
                    }
                }
            }
        }
    }

    /// 执行历史查询的具体逻辑
    fn execute_history_query(&self, conn: &Connection, query: ConversationHistoryQuery) -> Result<ConversationHistory> {

        // 获取会话信息
        let session: ConversationSession = match self.get_session(&query.session_id)? {
            Some(s) => s,
            None => {
                // 如果会话不存在，创建一个临时会话对象并返回空消息列表
                println!("⚠️ 会话不存在: {}，返回空历史", query.session_id);
                let empty_session = ConversationSession {
                    id: query.session_id.clone(),
                    title: Some("临时会话".to_string()),
                    created_at: chrono::Utc::now(),
                    updated_at: chrono::Utc::now(),
                    is_active: true,
                    metadata: None,
                };
                return Ok(ConversationHistory {
                    session: empty_session,
                    messages: Vec::new(),
                    total_count: 0,
                });
            }
        };

        // 构建消息查询
        let mut sql = "SELECT id, session_id, role, content, timestamp, metadata 
                       FROM conversation_messages WHERE session_id = ?1".to_string();
        
        let mut params_vec = vec![query.session_id.clone()];

        if let Some(false) = query.include_system_messages {
            sql.push_str(" AND role != 'system'");
        }

        sql.push_str(" ORDER BY timestamp ASC");

        if let Some(limit) = query.limit {
            sql.push_str(" LIMIT ?");
            params_vec.push(limit.to_string());
        }

        if let Some(offset) = query.offset {
            sql.push_str(" OFFSET ?");
            params_vec.push(offset.to_string());
        }

        let mut stmt = conn.prepare(&sql)?;
        let message_rows = stmt.query_map(
            rusqlite::params_from_iter(params_vec.iter()),
            |row| match self.row_to_message(row) {
                Ok(message) => Ok(message),
                Err(_e) => Err(rusqlite::Error::InvalidColumnType(0, "conversion error".to_string(), rusqlite::types::Type::Text)),
            }
        )?;

        let mut messages = Vec::new();
        for message_result in message_rows {
            messages.push(message_result?);
        }

        // 获取总消息数
        let total_count: u32 = conn.query_row(
            "SELECT COUNT(*) FROM conversation_messages WHERE session_id = ?1",
            params![query.session_id],
            |row| row.get(0),
        )?;

        Ok(ConversationHistory {
            session,
            messages,
            total_count,
        })
    }

    /// 获取会话列表
    pub fn get_sessions(&self, limit: Option<u32>, offset: Option<u32>) -> Result<Vec<ConversationSession>> {
        // 使用只读连接进行查询
        match self.database.try_get_read_connection() {
            Some(conn) => {
                self.execute_get_sessions(&conn, limit, offset)
            },
            None => {
                println!("⚠️ 只读连接被占用，get_sessions返回空列表");
                Ok(Vec::new())
            }
        }
    }

    /// 执行获取会话列表的具体逻辑
    fn execute_get_sessions(&self, conn: &Connection, limit: Option<u32>, offset: Option<u32>) -> Result<Vec<ConversationSession>> {
        
        let mut sql = "SELECT id, title, created_at, updated_at, is_active, metadata 
                       FROM conversation_sessions 
                       WHERE is_active = 1 
                       ORDER BY updated_at DESC".to_string();

        let mut params_vec = Vec::new();

        if let Some(limit) = limit {
            sql.push_str(" LIMIT ?");
            params_vec.push(limit.to_string());
        }

        if let Some(offset) = offset {
            sql.push_str(" OFFSET ?");
            params_vec.push(offset.to_string());
        }

        let mut stmt = conn.prepare(&sql)?;
        let session_rows = stmt.query_map(
            rusqlite::params_from_iter(params_vec.iter()),
            |row| match self.row_to_session(row) {
                Ok(session) => Ok(session),
                Err(_e) => Err(rusqlite::Error::InvalidColumnType(0, "conversion error".to_string(), rusqlite::types::Type::Text)),
            }
        )?;

        let mut sessions = Vec::new();
        for session_result in session_rows {
            sessions.push(session_result?);
        }

        Ok(sessions)
    }

    /// 删除会话（软删除，强制使用连接池）
    pub fn delete_session(&self, session_id: &str) -> Result<()> {
        println!("🏊 使用连接池删除会话: {}", &session_id[..8]);

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

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

        pooled_conn.execute(
            "UPDATE conversation_sessions SET is_active = 0, updated_at = ?1 WHERE id = ?2",
            params![Utc::now().to_rfc3339(), session_id],
        )?;

        println!("✅ 会话删除完成");
        Ok(())
    }

    /// 获取会话统计信息
    pub fn get_conversation_stats(&self) -> Result<ConversationStats> {
        // 使用只读连接进行统计查询
        match self.database.try_get_read_connection() {
            Some(conn) => {
                self.execute_get_stats(&conn)
            },
            None => {
                println!("⚠️ 只读连接被占用，返回默认统计");
                Ok(ConversationStats {
                    total_sessions: 0,
                    active_sessions: 0,
                    total_messages: 0,
                    average_messages_per_session: 0.0,
                })
            }
        }
    }

    /// 执行统计查询的具体逻辑
    fn execute_get_stats(&self, conn: &Connection) -> Result<ConversationStats> {

        let total_sessions: u32 = conn.query_row(
            "SELECT COUNT(*) FROM conversation_sessions",
            [],
            |row| row.get(0),
        )?;

        let active_sessions: u32 = conn.query_row(
            "SELECT COUNT(*) FROM conversation_sessions WHERE is_active = 1",
            [],
            |row| row.get(0),
        )?;

        let total_messages: u32 = conn.query_row(
            "SELECT COUNT(*) FROM conversation_messages",
            [],
            |row| row.get(0),
        )?;

        let average_messages_per_session = if total_sessions > 0 {
            total_messages as f64 / total_sessions as f64
        } else {
            0.0
        };

        Ok(ConversationStats {
            total_sessions,
            active_sessions,
            total_messages,
            average_messages_per_session,
        })
    }

    /// 清理过期会话（强制使用连接池）
    pub fn cleanup_expired_sessions(&self, max_inactive_days: u32) -> Result<u32> {
        println!("🏊 使用连接池清理过期会话，超过{}天", max_inactive_days);

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

        let pooled_conn = self.database.acquire_from_pool()
            .map_err(|e| anyhow!("获取连接池连接失败: {}", e))?;
        let cutoff_date = Utc::now() - chrono::Duration::days(max_inactive_days as i64);

        let deleted_count = pooled_conn.execute(
            "UPDATE conversation_sessions 
             SET is_active = 0, updated_at = ?1 
             WHERE is_active = 1 AND updated_at < ?2",
            params![Utc::now().to_rfc3339(), cutoff_date.to_rfc3339()],
        )?;

        println!("✅ 过期会话清理完成，删除{}个会话", deleted_count);
        Ok(deleted_count as u32)
    }

    /// 将数据库行转换为会话对象
    fn row_to_session(&self, row: &Row) -> Result<ConversationSession> {
        let created_at_str: String = row.get(2)?;
        let updated_at_str: String = row.get(3)?;
        let metadata_str: Option<String> = row.get(5)?;

        Ok(ConversationSession {
            id: row.get(0)?,
            title: row.get(1)?,
            created_at: DateTime::parse_from_rfc3339(&created_at_str)?.with_timezone(&Utc),
            updated_at: DateTime::parse_from_rfc3339(&updated_at_str)?.with_timezone(&Utc),
            is_active: row.get(4)?,
            metadata: metadata_str.and_then(|s| serde_json::from_str(&s).ok()),
        })
    }

    /// 将数据库行转换为消息对象
    fn row_to_message(&self, row: &Row) -> Result<ConversationMessage> {
        let role_str: String = row.get(2)?;
        let content_str: String = row.get(3)?;
        let timestamp_str: String = row.get(4)?;
        let metadata_str: Option<String> = row.get(5)?;

        let role = match role_str.as_str() {
            "user" => MessageRole::User,
            "assistant" => MessageRole::Assistant,
            "system" => MessageRole::System,
            _ => return Err(anyhow::anyhow!("Invalid message role: {}", role_str)),
        };

        let content: Vec<MessageContent> = serde_json::from_str(&content_str)?;

        Ok(ConversationMessage {
            id: row.get(0)?,
            session_id: row.get(1)?,
            role,
            content,
            timestamp: DateTime::parse_from_rfc3339(&timestamp_str)?.with_timezone(&Utc),
            metadata: metadata_str.and_then(|s| serde_json::from_str(&s).ok()),
        })
    }
}
