use sqlx::{FromRow, Row};
use utils::redis_cache::{Cache, RedisCache}; // Import RedisCache
use serde::{Serialize, Deserialize};
use std::sync::Arc;
use tokio::sync::Mutex;

#[derive(Debug, FromRow, Serialize, Deserialize)]
pub struct Character {
    pub id: i32,
    pub user_id: i32,
    pub name: String,
    pub inventory: serde_json::Value,
    pub stats: serde_json::Value,
    pub looks: serde_json::Value,
    pub position: serde_json::Value,
    pub created_at: chrono::NaiveDateTime,
    pub updated_at: chrono::NaiveDateTime,
    pub deleted_at: Option<i64>,
    pub is_active: bool,
}

pub struct CharacterRepository {
    pool: sqlx::PgPool,
    cache: Arc<Mutex<RedisCache>>, // Thread-safe RedisCache
}

impl CharacterRepository {
    pub fn new(pool: sqlx::PgPool, cache: Arc<Mutex<RedisCache>>) -> Self {
        Self { pool, cache }
    }

    pub async fn get_character_by_id(&self, character_id: i32) -> Result<Character, sqlx::Error> {
        let cache_key = format!("character:{}", character_id);

        // Try fetching from Redis cache
        if let Some(character) = self.cache.lock().await.get::<Character>(&cache_key).await.map_err(|_| sqlx::Error::RowNotFound)? {
            return Ok(character);
        }

        // Fetch from database
        let character = sqlx::query_as::<_, Character>(
            "SELECT id, user_id, name, inventory, stats, looks, position, \
                    created_at, updated_at, extract(epoch from (deleted_at - now()))::BIGINT as deleted_at, is_active \
             FROM characters WHERE id = $1 AND is_active = true",
        )
            .bind(character_id)
            .fetch_one(&self.pool)
            .await?;

        // Cache result
        self.cache.lock().await.set(&cache_key, &character, 300).await.map_err(|_| sqlx::Error::RowNotFound)?;
        Ok(character)
    }

    pub async fn create_character(&self, user_id: i32, name: &str, inventory: serde_json::Value, stats: serde_json::Value, looks: serde_json::Value, position: serde_json::Value) -> Result<i32, sqlx::Error> {
        let result = sqlx::query(
            "INSERT INTO characters (user_id, name, inventory, stats, looks, position, created_at, updated_at, is_active) \
             VALUES ($1, $2, $3, $4, $5, $6, NOW(), NOW(), true) RETURNING id",
        )
            .bind(user_id)
            .bind(name)
            .bind(inventory)
            .bind(stats)
            .bind(looks)
            .bind(position)
            .fetch_one(&self.pool)
            .await?;

        // Invalidate cache
        let cache_key = format!("character:user:{}", user_id);
        self.cache.lock().await.delete(&cache_key).await.map_err(|_| sqlx::Error::RowNotFound)?;
        Ok(result.get("id"))
    }

    pub async fn delete_character(&self, character_id: i32, delete_type: i32) -> Result<i64, sqlx::Error> {
        let mut query = "UPDATE characters SET updated_at = NOW(), deleted_at = NOW() + '24 hours' WHERE id = $1 RETURNING user_id, extract(epoch from (deleted_at - now()))::BIGINT as deleted_at"; 
        if 0 == delete_type {
            query = "UPDATE characters SET updated_at = NOW(), deleted_at = null WHERE id = $1 RETURNING user_id, 0::BIGINT as deleted_at";
        }
        let result = sqlx::query(
            query,
        )
            .bind(character_id)
            .fetch_one(&self.pool)
            .await?;

        // Invalidate cache
        let cache_key = format!("character:user:{}", result.get::<i32, &str>("user_id"));
        self.cache.lock().await.delete(&cache_key).await.map_err(|_| sqlx::Error::RowNotFound)?;
        let cache_key = format!("character:{}", character_id);
        self.cache.lock().await.delete(&cache_key).await.map_err(|_| sqlx::Error::RowNotFound)?;
        Ok(result.get::<i64, &str>("deleted_at"))
    }

    pub async fn get_characters_by_user(&self, user_id: i32) -> Result<Vec<Character>, sqlx::Error> {
        let cache_key = format!("character:user:{}", user_id);

        // Try fetching from Redis cache
        if let Some(characters) = self.cache.lock().await.get::<Vec<Character>>(&cache_key).await.map_err(|_| sqlx::Error::RowNotFound)? {
            return Ok(characters);
        }

        // Fetch from database
        let characters = sqlx::query_as::<_, Character>(
            "SELECT id, user_id, name, inventory, stats, looks, position, created_at, updated_at, extract(epoch from (deleted_at - now()))::BIGINT as deleted_at, is_active FROM characters WHERE user_id = $1 AND is_active = true",
        )
            .bind(user_id)
            .fetch_all(&self.pool)
            .await?;

        // Cache result
        self.cache.lock().await.set(&cache_key, &characters, 300).await.map_err(|_| sqlx::Error::RowNotFound)?;
        Ok(characters)
    }
}