use crate::models::message::{Message, MessageType};
use chrono::{DateTime, Utc};
use sqlx::{PgPool, Result, Row};
use uuid::Uuid;

pub struct MessageRepository {
    pool: PgPool,
}

impl MessageRepository {
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    pub fn pool(&self) -> &PgPool {
        &self.pool
    }

    pub async fn create(
        &self,
        conversation_id: Uuid,
        sender_id: Uuid,
        content: &str,
    ) -> Result<Message> {
        // Aligné avec le schéma PostgreSQL complet (migrations 001 et 002)
        let row = sqlx::query(
            r#"
            INSERT INTO messages (
                conversation_id, sender_id, content, message_type, 
                is_pinned, is_deleted, is_edited, status,
                created_at, updated_at
            )
            VALUES ($1, $2, $3, 'text', false, false, false, 'sent', NOW(), NOW())
            RETURNING 
                id,
                conversation_id,
                sender_id,
                content,
                message_type,
                parent_message_id,
                reply_to_id,
                is_pinned,
                is_edited,
                is_deleted,
                edited_at,
                deleted_at,
                status,
                metadata,
                created_at,
                updated_at
            "#,
        )
        .bind(conversation_id)
        .bind(sender_id)
        .bind(content)
        .fetch_one(&self.pool)
        .await?;

        let message = Message {
            id: row.get("id"),
            conversation_id: row.get("conversation_id"),
            sender_id: row.get("sender_id"),
            content: row.get("content"),
            message_type: MessageType::try_from(row.get::<String, _>("message_type"))
                .unwrap_or(MessageType::Text),
            parent_message_id: row.get("parent_message_id"),
            reply_to_id: row.get("reply_to_id"),
            is_pinned: row.get("is_pinned"),
            is_edited: row.get("is_edited"),
            is_deleted: row.get("is_deleted"),
            edited_at: row.get("edited_at"),
            deleted_at: row.get("deleted_at"),
            status: row.get("status"),
            metadata: row.get("metadata"),
            created_at: row.get("created_at"),
            updated_at: row.get("updated_at"),
        };

        Ok(message)
    }

    pub async fn get_conversation_messages(
        &self,
        conversation_id: Uuid,
        limit: i64,
    ) -> Result<Vec<Message>> {
        // Aligné avec le schéma PostgreSQL complet
        let rows = sqlx::query(
            r#"
            SELECT 
                id,
                conversation_id,
                sender_id,
                content,
                message_type,
                parent_message_id,
                reply_to_id,
                is_pinned,
                is_edited,
                is_deleted,
                edited_at,
                deleted_at,
                status,
                metadata,
                created_at,
                updated_at
            FROM messages
            WHERE conversation_id = $1 AND is_deleted = false
            ORDER BY created_at DESC
            LIMIT $2
            "#,
        )
        .bind(conversation_id)
        .bind(limit)
        .fetch_all(&self.pool)
        .await?;

        let messages: Result<Vec<Message>, sqlx::Error> = rows
            .into_iter()
            .map(|row| {
                Ok(Message {
                    id: row.get("id"),
                    conversation_id: row.get("conversation_id"),
                    sender_id: row.get("sender_id"),
                    content: row.get("content"),
                    message_type: MessageType::try_from(row.get::<String, _>("message_type"))
                        .unwrap_or(MessageType::Text),
                    parent_message_id: row.get("parent_message_id"),
                    reply_to_id: row.get("reply_to_id"),
                    is_pinned: row.get("is_pinned"),
                    is_edited: row.get("is_edited"),
                    is_deleted: row.get("is_deleted"),
                    edited_at: row.get("edited_at"),
                    deleted_at: row.get("deleted_at"),
                    status: row.get("status"),
                    metadata: row.get("metadata"),
                    created_at: row.get("created_at"),
                    updated_at: row.get("updated_at"),
                })
            })
            .collect();

        messages
    }

    // Alias pour compatibilité avec code existant
    pub async fn get_room_messages(
        &self,
        conversation_id: Uuid,
        limit: i64,
    ) -> Result<Vec<Message>> {
        self.get_conversation_messages(conversation_id, limit).await
    }

    pub async fn get_by_id(&self, id: Uuid) -> Result<Option<Message>> {
        let row = sqlx::query(
            r#"
            SELECT 
                id,
                conversation_id,
                sender_id,
                content,
                message_type,
                parent_message_id,
                reply_to_id,
                is_pinned,
                is_edited,
                is_deleted,
                edited_at,
                deleted_at,
                status,
                metadata,
                created_at,
                updated_at
            FROM messages
            WHERE id = $1 AND is_deleted = false
            "#,
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await?;

        if let Some(row) = row {
            Ok(Some(Message {
                id: row.get("id"),
                conversation_id: row.get("conversation_id"),
                sender_id: row.get("sender_id"),
                content: row.get("content"),
                message_type: MessageType::try_from(row.get::<String, _>("message_type"))
                    .unwrap_or(MessageType::Text),
                parent_message_id: row.get("parent_message_id"),
                reply_to_id: row.get("reply_to_id"),
                is_pinned: row.get("is_pinned"),
                is_edited: row.get("is_edited"),
                is_deleted: row.get("is_deleted"),
                edited_at: row.get("edited_at"),
                deleted_at: row.get("deleted_at"),
                status: row.get("status"),
                metadata: row.get("metadata"),
                created_at: row.get("created_at"),
                updated_at: row.get("updated_at"),
            }))
        } else {
            Ok(None)
        }
    }

    pub async fn update(&self, id: Uuid, new_content: &str) -> Result<Message> {
        // Mettre à jour le message avec le nouveau contenu
        let row = sqlx::query(
            r#"
            UPDATE messages
            SET 
                content = $1,
                is_edited = true,
                edited_at = NOW(),
                updated_at = NOW()
            WHERE id = $2 AND is_deleted = false
            RETURNING 
                id,
                conversation_id,
                sender_id,
                content,
                message_type,
                parent_message_id,
                reply_to_id,
                is_pinned,
                is_edited,
                is_deleted,
                edited_at,
                deleted_at,
                status,
                metadata,
                created_at,
                updated_at
            "#,
        )
        .bind(new_content)
        .bind(id)
        .fetch_optional(&self.pool)
        .await?;

        let row = row.ok_or_else(|| sqlx::Error::RowNotFound)?;

        Ok(Message {
            id: row.get("id"),
            conversation_id: row.get("conversation_id"),
            sender_id: row.get("sender_id"),
            content: row.get("content"),
            message_type: MessageType::try_from(row.get::<String, _>("message_type"))
                .unwrap_or(MessageType::Text),
            parent_message_id: row.get("parent_message_id"),
            reply_to_id: row.get("reply_to_id"),
            is_pinned: row.get("is_pinned"),
            is_edited: row.get("is_edited"),
            is_deleted: row.get("is_deleted"),
            edited_at: row.get("edited_at"),
            deleted_at: row.get("deleted_at"),
            status: row.get("status"),
            metadata: row.get("metadata"),
            created_at: row.get("created_at"),
            updated_at: row.get("updated_at"),
        })
    }

    pub async fn delete(&self, id: Uuid) -> Result<()> {
        // Soft delete : mettre à jour is_deleted et deleted_at
        sqlx::query(
            r#"
            UPDATE messages 
            SET 
                is_deleted = true, 
                deleted_at = NOW(),
                updated_at = NOW() 
            WHERE id = $1
            "#,
        )
        .bind(id)
        .execute(&self.pool)
        .await?;

        Ok(())
    }

    /// Récupère un message même s'il est supprimé (pour les admins)
    pub async fn get_by_id_including_deleted(&self, id: Uuid) -> Result<Option<Message>> {
        let row = sqlx::query(
            r#"
            SELECT 
                id,
                conversation_id,
                sender_id,
                content,
                message_type,
                parent_message_id,
                reply_to_id,
                is_pinned,
                is_edited,
                is_deleted,
                edited_at,
                deleted_at,
                status,
                metadata,
                created_at,
                updated_at
            FROM messages
            WHERE id = $1
            "#,
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await?;

        if let Some(row) = row {
            Ok(Some(Message {
                id: row.get("id"),
                conversation_id: row.get("conversation_id"),
                sender_id: row.get("sender_id"),
                content: row.get("content"),
                message_type: MessageType::try_from(row.get::<String, _>("message_type"))
                    .unwrap_or(MessageType::Text),
                parent_message_id: row.get("parent_message_id"),
                reply_to_id: row.get("reply_to_id"),
                is_pinned: row.get("is_pinned"),
                is_edited: row.get("is_edited"),
                is_deleted: row.get("is_deleted"),
                edited_at: row.get("edited_at"),
                deleted_at: row.get("deleted_at"),
                status: row.get("status"),
                metadata: row.get("metadata"),
                created_at: row.get("created_at"),
                updated_at: row.get("updated_at"),
            }))
        } else {
            Ok(None)
        }
    }

    /// Helper pour mapper une row SQL en Message
    fn row_to_message(&self, row: &sqlx::postgres::PgRow) -> Result<Message> {
        Ok(Message {
            id: row.get("id"),
            conversation_id: row.get("conversation_id"),
            sender_id: row.get("sender_id"),
            content: row.get("content"),
            message_type: MessageType::try_from(row.get::<String, _>("message_type"))
                .unwrap_or(MessageType::Text),
            parent_message_id: row.get("parent_message_id"),
            reply_to_id: row.get("reply_to_id"),
            is_pinned: row.get("is_pinned"),
            is_edited: row.get("is_edited"),
            is_deleted: row.get("is_deleted"),
            edited_at: row.get("edited_at"),
            deleted_at: row.get("deleted_at"),
            status: row.get("status"),
            metadata: row.get("metadata"),
            created_at: row.get("created_at"),
            updated_at: row.get("updated_at"),
        })
    }

    /// Récupère l'historique avec pagination par cursors (before/after)
    ///
    /// - `before`: Récupère les messages avant ce timestamp (tri DESC)
    /// - `after`: Récupère les messages après ce timestamp (tri ASC)
    /// - Les résultats sont toujours retournés en ordre ASC (du plus ancien au plus récent)
    pub async fn fetch_history(
        &self,
        conversation_id: Uuid,
        before: Option<DateTime<Utc>>,
        after: Option<DateTime<Utc>>,
        limit: usize,
        include_deleted: bool,
    ) -> Result<(Vec<Message>, bool, bool)> {
        let limit = limit.min(100); // Limiter à 100 messages max
        let limit_i64 = limit as i64;

        // Construire la requête selon before/after
        let (rows, needs_reverse) = match (before, after) {
            (Some(before_ts), None) => {
                // Récupérer les messages avant before_ts (plus anciens, tri DESC)
                let deleted_filter = if include_deleted {
                    ""
                } else {
                    " AND is_deleted = false"
                };
                let query = format!(
                    r#"
                    SELECT 
                        id, conversation_id, sender_id, content, message_type,
                        parent_message_id, reply_to_id, is_pinned, is_edited, is_deleted,
                        edited_at, deleted_at, status, metadata, created_at, updated_at
                    FROM messages
                    WHERE conversation_id = $1 AND created_at < $2{}
                    ORDER BY created_at DESC
                    LIMIT $3
                    "#,
                    deleted_filter
                );
                let rows = sqlx::query(&query)
                    .bind(conversation_id)
                    .bind(before_ts)
                    .bind(limit_i64)
                    .fetch_all(&self.pool)
                    .await?;
                (rows, true) // Besoin de reverse car tri DESC
            }
            (None, Some(after_ts)) => {
                // Récupérer les messages après after_ts (plus récents, tri ASC)
                let deleted_filter = if include_deleted {
                    ""
                } else {
                    " AND is_deleted = false"
                };
                let query = format!(
                    r#"
                    SELECT 
                        id, conversation_id, sender_id, content, message_type,
                        parent_message_id, reply_to_id, is_pinned, is_edited, is_deleted,
                        edited_at, deleted_at, status, metadata, created_at, updated_at
                    FROM messages
                    WHERE conversation_id = $1 AND created_at > $2{}
                    ORDER BY created_at ASC
                    LIMIT $3
                    "#,
                    deleted_filter
                );
                let rows = sqlx::query(&query)
                    .bind(conversation_id)
                    .bind(after_ts)
                    .bind(limit_i64)
                    .fetch_all(&self.pool)
                    .await?;
                (rows, false) // Pas besoin de reverse car tri ASC
            }
            (Some(before_ts), Some(after_ts)) => {
                // Récupérer les messages entre after_ts et before_ts (tri ASC)
                let deleted_filter = if include_deleted {
                    ""
                } else {
                    " AND is_deleted = false"
                };
                let query = format!(
                    r#"
                    SELECT 
                        id, conversation_id, sender_id, content, message_type,
                        parent_message_id, reply_to_id, is_pinned, is_edited, is_deleted,
                        edited_at, deleted_at, status, metadata, created_at, updated_at
                    FROM messages
                    WHERE conversation_id = $1 AND created_at > $2 AND created_at < $3{}
                    ORDER BY created_at ASC
                    LIMIT $4
                    "#,
                    deleted_filter
                );
                let rows = sqlx::query(&query)
                    .bind(conversation_id)
                    .bind(after_ts)
                    .bind(before_ts)
                    .bind(limit_i64)
                    .fetch_all(&self.pool)
                    .await?;
                (rows, false) // Pas besoin de reverse car tri ASC
            }
            (None, None) => {
                // Récupérer les messages les plus récents (tri DESC)
                let deleted_filter = if include_deleted {
                    ""
                } else {
                    " AND is_deleted = false"
                };
                let query = format!(
                    r#"
                    SELECT 
                        id, conversation_id, sender_id, content, message_type,
                        parent_message_id, reply_to_id, is_pinned, is_edited, is_deleted,
                        edited_at, deleted_at, status, metadata, created_at, updated_at
                    FROM messages
                    WHERE conversation_id = $1{}
                    ORDER BY created_at DESC
                    LIMIT $2
                    "#,
                    deleted_filter
                );
                let rows = sqlx::query(&query)
                    .bind(conversation_id)
                    .bind(limit_i64)
                    .fetch_all(&self.pool)
                    .await?;
                (rows, true) // Besoin de reverse car tri DESC
            }
        };

        // Mapper les rows en messages
        let mut messages: Vec<Message> = rows
            .iter()
            .map(|row| self.row_to_message(row))
            .collect::<Result<Vec<_>>>()?;

        // Toujours retourner en ordre ASC (du plus ancien au plus récent)
        if needs_reverse {
            messages.reverse();
        }

        // Vérifier s'il y a plus de messages avant/après
        let has_more_before = if let Some(first_msg) = messages.first() {
            let deleted_filter = if include_deleted {
                ""
            } else {
                " AND is_deleted = false"
            };
            let count_query = format!(
                "SELECT COUNT(*) FROM messages WHERE conversation_id = $1 AND created_at < $2{}",
                deleted_filter
            );
            let count: i64 = sqlx::query_scalar(&count_query)
                .bind(conversation_id)
                .bind(first_msg.created_at)
                .fetch_one(&self.pool)
                .await?;
            count > 0
        } else {
            false
        };

        let has_more_after = if let Some(last_msg) = messages.last() {
            let deleted_filter = if include_deleted {
                ""
            } else {
                " AND is_deleted = false"
            };
            let count_query = format!(
                "SELECT COUNT(*) FROM messages WHERE conversation_id = $1 AND created_at > $2{}",
                deleted_filter
            );
            let count: i64 = sqlx::query_scalar(&count_query)
                .bind(conversation_id)
                .bind(last_msg.created_at)
                .fetch_one(&self.pool)
                .await?;
            count > 0
        } else {
            false
        };

        Ok((messages, has_more_before, has_more_after))
    }

    /// Recherche de messages par texte (recherche ILIKE avec index trigram)
    pub async fn search_messages(
        &self,
        conversation_id: Uuid,
        query: &str,
        limit: usize,
        offset: usize,
        include_deleted: bool,
    ) -> Result<(Vec<Message>, i64)> {
        let limit = limit.min(100); // Limiter à 100 messages max
        let limit_i64 = limit as i64;
        let offset_i64 = offset as i64;

        // Requête de recherche avec ILIKE (utilise l'index trigram)
        let search_pattern = format!("%{}%", query);

        let rows = sqlx::query(
            r#"
            SELECT 
                id,
                conversation_id,
                sender_id,
                content,
                message_type,
                parent_message_id,
                reply_to_id,
                is_pinned,
                is_edited,
                is_deleted,
                edited_at,
                deleted_at,
                status,
                metadata,
                created_at,
                updated_at
            FROM messages
            WHERE conversation_id = $1
            AND content ILIKE $2
            AND ($3 = true OR is_deleted = false)
            ORDER BY created_at DESC
            LIMIT $4 OFFSET $5
            "#,
        )
        .bind(conversation_id)
        .bind(&search_pattern)
        .bind(include_deleted)
        .bind(limit_i64)
        .bind(offset_i64)
        .fetch_all(&self.pool)
        .await?;

        // Mapper les rows en messages
        let messages: Vec<Message> = rows
            .iter()
            .map(|row| self.row_to_message(row))
            .collect::<Result<Vec<_>>>()?;

        // Compter le total de résultats
        let total: i64 = sqlx::query_scalar(
            r#"
            SELECT COUNT(*) FROM messages
            WHERE conversation_id = $1
            AND content ILIKE $2
            AND ($3 = true OR is_deleted = false)
            "#,
        )
        .bind(conversation_id)
        .bind(&search_pattern)
        .bind(include_deleted)
        .fetch_one(&self.pool)
        .await?;

        Ok((messages, total))
    }

    /// Récupère tous les messages depuis un timestamp (pour sync offline)
    ///
    /// Inclut :
    /// - Messages créés depuis `since`
    /// - Messages édités depuis `since` (même si créés avant)
    /// - Messages supprimés depuis `since` (même si créés avant)
    pub async fn fetch_since(
        &self,
        conversation_id: Uuid,
        since: DateTime<Utc>,
    ) -> Result<Vec<Message>> {
        // Récupérer tous les messages créés ou modifiés depuis since
        let rows = sqlx::query(
            r#"
            SELECT 
                id,
                conversation_id,
                sender_id,
                content,
                message_type,
                parent_message_id,
                reply_to_id,
                is_pinned,
                is_edited,
                is_deleted,
                edited_at,
                deleted_at,
                status,
                metadata,
                created_at,
                updated_at
            FROM messages
            WHERE conversation_id = $1
            AND (
                created_at > $2
                OR updated_at > $2
            )
            ORDER BY created_at ASC
            "#,
        )
        .bind(conversation_id)
        .bind(since)
        .fetch_all(&self.pool)
        .await?;

        // Mapper les rows en messages
        let messages: Vec<Message> = rows
            .iter()
            .map(|row| self.row_to_message(row))
            .collect::<Result<Vec<_>>>()?;

        Ok(messages)
    }
}
