use chrono::{DateTime, Utc};
use sqlx::{PgPool, Result, Row};
use uuid::Uuid;

/// Room représente une conversation/room de chat
/// Aligné avec la table conversations de la migration 001
#[derive(Debug, Clone)]
pub struct Room {
    pub id: Uuid,
    pub name: Option<String>,
    pub description: Option<String>,
    pub room_type: String, // conversation_type dans DB
    pub is_private: bool,
    pub creator_id: Uuid, // created_by dans DB
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

/// RoomMember représente un membre d'une room
#[derive(Debug, Clone)]
pub struct RoomMember {
    pub id: Uuid,
    pub room_id: Uuid,
    pub user_id: Uuid,
    pub role: String,
    pub is_banned: bool,
    pub is_muted: bool,
    pub last_read_at: Option<DateTime<Utc>>,
    pub joined_at: DateTime<Utc>,
}

pub struct RoomRepository {
    pool: PgPool,
}

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

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

    pub async fn create(
        &self,
        name: Option<&str>,
        description: Option<&str>,
        room_type: &str,
        is_private: bool,
        creator_id: Uuid,
    ) -> Result<Room> {
        // Aligné avec la table conversations de la migration 001
        let row = sqlx::query(
            r#"
            INSERT INTO conversations (name, description, conversation_type, is_private, created_by, created_at, updated_at)
            VALUES ($1, $2, $3, $4, $5, NOW(), NOW())
            RETURNING 
                id,
                name,
                description,
                conversation_type,
                is_private,
                created_by,
                created_at,
                updated_at
            "#
        )
        .bind(name)
        .bind(description)
        .bind(room_type)
        .bind(is_private)
        .bind(creator_id)
        .fetch_one(&self.pool)
        .await?;

        Ok(Room {
            id: row.get("id"),
            name: row.get("name"),
            description: row.get("description"),
            room_type: row.get("conversation_type"), // Mapper conversation_type vers room_type
            is_private: row.get("is_private"),
            creator_id: row.get("created_by"), // Mapper created_by vers creator_id
            created_at: row.get("created_at"),
            updated_at: row.get("updated_at"),
        })
    }

    pub async fn get_by_id(&self, room_id: Uuid) -> Result<Option<Room>> {
        // Aligné avec la table conversations de la migration 001
        let row_opt = sqlx::query(
            r#"
            SELECT 
                id,
                name,
                description,
                conversation_type,
                is_private,
                created_by,
                created_at,
                updated_at
            FROM conversations
            WHERE id = $1
            "#,
        )
        .bind(room_id)
        .fetch_optional(&self.pool)
        .await?;

        if let Some(row) = row_opt {
            Ok(Some(Room {
                id: row.get("id"),
                name: row.get("name"),
                description: row.get("description"),
                room_type: row.get("conversation_type"), // Mapper conversation_type vers room_type
                is_private: row.get("is_private"),
                creator_id: row.get("created_by"), // Mapper created_by vers creator_id
                created_at: row.get("created_at"),
                updated_at: row.get("updated_at"),
            }))
        } else {
            Ok(None)
        }
    }

    pub async fn add_member(&self, room_id: Uuid, user_id: Uuid, role: &str) -> Result<RoomMember> {
        // Aligné avec la table conversation_members de la migration 001
        // Note: conversation_members n'a pas de colonne id, utilise (conversation_id, user_id) comme PK
        sqlx::query(
            r#"
            INSERT INTO conversation_members (conversation_id, user_id, role, joined_at)
            VALUES ($1, $2, $3, NOW())
            ON CONFLICT (conversation_id, user_id) DO UPDATE
            SET role = EXCLUDED.role,
                joined_at = CASE 
                    WHEN conversation_members.joined_at IS NULL THEN NOW()
                    ELSE conversation_members.joined_at
                END
            "#,
        )
        .bind(room_id)
        .bind(user_id)
        .bind(role)
        .execute(&self.pool)
        .await?;

        // Récupérer le membre créé/mis à jour
        let row = sqlx::query(
            r#"
            SELECT 
                conversation_id,
                user_id,
                role,
                joined_at
            FROM conversation_members
            WHERE conversation_id = $1 AND user_id = $2
            "#,
        )
        .bind(room_id)
        .bind(user_id)
        .fetch_one(&self.pool)
        .await?;

        Ok(RoomMember {
            id: room_id, // Utiliser room_id comme id temporaire (pas de colonne id dans conversation_members)
            room_id: row.get("conversation_id"),
            user_id: row.get("user_id"),
            role: row.get("role"),
            is_banned: false,   // Pas de colonne is_banned dans conversation_members
            is_muted: false,    // Pas de colonne is_muted dans conversation_members
            last_read_at: None, // Pas de colonne last_read_at dans conversation_members
            joined_at: row.get("joined_at"),
        })
    }

    pub async fn remove_member(&self, room_id: Uuid, user_id: Uuid) -> Result<()> {
        // Aligné avec la table conversation_members de la migration 001
        sqlx::query("DELETE FROM conversation_members WHERE conversation_id = $1 AND user_id = $2")
            .bind(room_id)
            .bind(user_id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }

    pub async fn get_members(&self, room_id: Uuid) -> Result<Vec<RoomMember>> {
        // Aligné avec la table conversation_members de la migration 001
        let rows = sqlx::query(
            r#"
            SELECT 
                conversation_id,
                user_id,
                role,
                joined_at
            FROM conversation_members
            WHERE conversation_id = $1
            ORDER BY joined_at ASC
            "#,
        )
        .bind(room_id)
        .fetch_all(&self.pool)
        .await?;

        let members: Result<Vec<RoomMember>, sqlx::Error> = rows
            .into_iter()
            .map(|row| {
                let conv_id: Uuid = row.get("conversation_id");
                Ok(RoomMember {
                    id: conv_id, // Utiliser conversation_id comme id temporaire
                    room_id: conv_id,
                    user_id: row.get("user_id"),
                    role: row.get("role"),
                    is_banned: false, // Pas de colonne is_banned dans conversation_members
                    is_muted: false,  // Pas de colonne is_muted dans conversation_members
                    last_read_at: None, // Pas de colonne last_read_at dans conversation_members
                    joined_at: row.get("joined_at"),
                })
            })
            .collect();

        members
    }
}
