- update: major refactor of the database-service to make it easy to add newer api services

- add: character database api

database-service/src/characters.rs

@@ -0,0 +1,110 @@
+use sqlx::{FromRow, Row};
+use crate::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 level: i16,
+    pub experience: i64,
+    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<chrono::NaiveDateTime>,
+    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, level, experience, inventory, stats, looks, position, \
+                    created_at, updated_at, 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, level, experience, inventory, stats, looks, position, created_at, updated_at, is_active) \
+             VALUES ($1, $2, 1, 0, $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?;
+
+        Ok(result.get("id"))
+    }
+
+    pub async fn delete_character(&self, character_id: i32) -> Result<(), sqlx::Error> {
+        sqlx::query(
+            "UPDATE characters SET deleted_at = NOW(), is_active = false WHERE id = $1",
+        )
+            .bind(character_id)
+            .execute(&self.pool)
+            .await?;
+
+        // Invalidate cache
+        let cache_key = format!("character:{}", character_id);
+        self.cache.lock().await.delete(&cache_key).await.map_err(|_| sqlx::Error::RowNotFound)?;
+        Ok(())
+    }
+
+    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, level, experience, inventory, stats, looks, position, \
+                    created_at, updated_at, 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)
+    }
+}