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

#[derive(Debug, FromRow, Serialize, Deserialize)]
pub struct Character {
    pub id: i32,
    pub user_id: String,
    pub name: String,
    pub money: i64,
    pub inventory: serde_json::Value,
    pub stats: serde_json::Value,
    pub skills: 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, userId as user_id, name, money, inventory, stats, skills, looks, position, \
                    createdAt as created_at, updatedAt as updated_at, extract(epoch from (deletedAt - now()))::BIGINT as deleted_at, isActive as is_active \
             FROM character WHERE id = $1 AND isActive = true",
        )
            .bind(character_id)
            .fetch_one(&self.pool)
            .await?;

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

    pub async fn create_character(
        &self,
        user_id: String,
        name: &str,
        inventory: serde_json::Value,
        skills: 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 character (userId, name, inventory, stats, skills, looks, position, createdAt, updatedAt, isActive) \
             VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW(), true) RETURNING id",
        )
            .bind(&user_id)
            .bind(name)
            .bind(inventory)
            .bind(stats)
            .bind(skills)
            .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 character SET updatedAt = NOW(), deletedAt = NOW() + '24 hours' WHERE id = $1 RETURNING userId, extract(epoch from (deletedAt - now()))::BIGINT as deletedAt";
        if 0 == delete_type {
            query = "UPDATE character SET updatedAt = NOW(), deletedAt = null WHERE id = $1 RETURNING userId, 0::BIGINT as deletedAt";
        }
        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, userId as user_id, name, money, inventory, stats, skills, looks, position, createdAt as created_at, updatedAt as updated_at, extract(epoch from (deletedAt - now()))::BIGINT as deleted_at, isActive as is_active FROM character WHERE userId = $1 AND isActive = true",
        )
            .bind(user_id)
            .fetch_all(&self.pool)
            .await?;

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