chaiss_core/
db.rs

1// SQLite sqlx persistence logic
2use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};
3use sqlx::Error;
4
5#[derive(Debug, Clone)]
6pub struct GameRecord {
7    pub id: i64,
8    pub name: String,
9    pub status: String,
10    pub white_player: String,
11    pub black_player: String,
12}
13
14pub struct DbClient {
15    pool: SqlitePool,
16}
17
18impl DbClient {
19    pub async fn new(database_url: &str) -> Result<Self, Error> {
20        use sqlx::sqlite::SqliteConnectOptions;
21        use std::str::FromStr;
22
23        let options = SqliteConnectOptions::from_str(database_url)?.create_if_missing(true);
24
25        let pool = SqlitePoolOptions::new()
26            .max_connections(5)
27            .connect_with(options)
28            .await?;
29
30        // Automatically run migrations on startup natively inside Rust!
31        sqlx::migrate!("./migrations").run(&pool).await?;
32
33        Ok(Self { pool })
34    }
35
36    pub async fn create_player(&self, name: &str) -> Result<i64, Error> {
37        let result = sqlx::query!("INSERT INTO players (name) VALUES (?)", name)
38            .execute(&self.pool)
39            .await?;
40        Ok(result.last_insert_rowid())
41    }
42
43    pub async fn get_or_create_player(&self, name: &str) -> Result<i64, Error> {
44        if let Some(id) = self.get_player_by_name(name).await? {
45            return Ok(id);
46        }
47        self.create_player(name).await
48    }
49
50    pub async fn get_player_by_name(&self, name: &str) -> Result<Option<i64>, Error> {
51        let record = sqlx::query!("SELECT id FROM players WHERE name = ?", name)
52            .fetch_optional(&self.pool)
53            .await?;
54        Ok(record.and_then(|r| r.id)) // flatten the implicitly wrapped SQLite Option
55    }
56
57    pub async fn create_game(
58        &self,
59        name: &str,
60        white_id: i64,
61        black_id: i64,
62        initial_fen: &str,
63    ) -> Result<i64, Error> {
64        let status = "ongoing";
65        let result = sqlx::query!(
66            "INSERT INTO games (name, white_player_id, black_player_id, current_fen, status) VALUES (?, ?, ?, ?, ?)",
67            name, white_id, black_id, initial_fen, status
68        )
69        .execute(&self.pool)
70        .await?;
71        Ok(result.last_insert_rowid())
72    }
73
74    pub async fn update_game_state(
75        &self,
76        game_id: i64,
77        current_fen: &str,
78        status: &str,
79    ) -> Result<(), Error> {
80        sqlx::query!(
81            "UPDATE games SET current_fen = ?, status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
82            current_fen, status, game_id
83        )
84        .execute(&self.pool)
85        .await?;
86        Ok(())
87    }
88
89    pub async fn delete_game(&self, game_id: i64) -> Result<(), Error> {
90        let mut tx = self.pool.begin().await?;
91
92        // Cascading Relational teardown structure cleanly wiping dependencies beforehand!
93        sqlx::query!("DELETE FROM chat_messages WHERE game_id = ?", game_id)
94            .execute(&mut *tx)
95            .await?;
96        sqlx::query!("DELETE FROM moves WHERE game_id = ?", game_id)
97            .execute(&mut *tx)
98            .await?;
99        sqlx::query!("DELETE FROM games WHERE id = ?", game_id)
100            .execute(&mut *tx)
101            .await?;
102
103        tx.commit().await?;
104        Ok(())
105    }
106
107    pub async fn log_move(
108        &self,
109        game_id: i64,
110        move_number: i64,
111        fen_snapshot: &str,
112        notation: &str,
113    ) -> Result<(), Error> {
114        sqlx::query!(
115            "INSERT INTO moves (game_id, move_number, fen_snapshot, notation) VALUES (?, ?, ?, ?)",
116            game_id,
117            move_number,
118            fen_snapshot,
119            notation
120        )
121        .execute(&self.pool)
122        .await?;
123        Ok(())
124    }
125
126    /// Natively handles the "Undo" architecture algebraically without engine reverse-math!
127    pub async fn undo_last_move(&self, game_id: i64) -> Result<Option<String>, Error> {
128        // Query the highest move_number mathematically tracked
129        let last_move = sqlx::query!(
130            "SELECT id, move_number FROM moves WHERE game_id = ? ORDER BY move_number DESC LIMIT 1",
131            game_id
132        )
133        .fetch_optional(&self.pool)
134        .await?;
135
136        if let Some(lm) = last_move {
137            // Discard the erroneous action from the physical history ledger natively!
138            sqlx::query!("DELETE FROM moves WHERE id = ?", lm.id)
139                .execute(&self.pool)
140                .await?;
141
142            // Look up what the physical state was directly prior natively by scanning the previous stack vector
143            let prev_move = sqlx::query!(
144                "SELECT fen_snapshot FROM moves WHERE game_id = ? ORDER BY move_number DESC LIMIT 1",
145                game_id
146            )
147            .fetch_optional(&self.pool)
148            .await?;
149
150            let recovered_fen = prev_move.map(|r| r.fen_snapshot).unwrap_or_else(|| {
151                // If the player undid absolutely the FIRST move of the game, reset to pristine FIDE starting structures universally!
152                "rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR w KQkq - 0 1".to_string()
153            });
154
155            // Re-mount the recovered array to the active game tracking native structurally!
156            sqlx::query!(
157                "UPDATE games SET current_fen = ?, status = 'ongoing', updated_at = CURRENT_TIMESTAMP WHERE id = ?",
158                recovered_fen, game_id
159            )
160            .execute(&self.pool)
161            .await?;
162
163            return Ok(Some(recovered_fen));
164        }
165
166        Ok(None)
167    }
168
169    /// Fetches a dynamic matrix of active database sessions mathematically JOINed alongside explicit strings!
170    pub async fn get_active_games(&self) -> Result<Vec<GameRecord>, sqlx::Error> {
171        let records = sqlx::query!(
172            r#"
173            SELECT
174                g.id, g.name, g.status,
175                pw.name as white_name,
176                pb.name as black_name
177            FROM games g
178            JOIN players pw ON g.white_player_id = pw.id
179            JOIN players pb ON g.black_player_id = pb.id
180            ORDER BY g.updated_at DESC
181            "#
182        )
183        .fetch_all(&self.pool)
184        .await?;
185
186        Ok(records
187            .into_iter()
188            .map(|rec| GameRecord {
189                id: rec.id,
190                name: rec.name,
191                status: rec.status,
192                white_player: rec.white_name,
193                black_player: rec.black_name,
194            })
195            .collect())
196    }
197
198    /// Recursively fetches the exact historical move vectors for resuming Egui Sandbox arrays natively!
199    pub async fn load_game_history(
200        &self,
201        game_id: i64,
202    ) -> Result<(String, Vec<String>, Vec<String>), sqlx::Error> {
203        // Technically, `games` does not retain `initial_fen` independently currently. We inject standard FIDE root explicitly!
204        let root_fen = "rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR w KQkq - 0 1".to_string();
205
206        let moves = sqlx::query!(
207            "SELECT fen_snapshot, notation FROM moves WHERE game_id = ? ORDER BY move_number ASC",
208            game_id
209        )
210        .fetch_all(&self.pool)
211        .await?;
212
213        let mut fen_history = Vec::new();
214        let mut algebraic_history = Vec::new();
215
216        for r in moves {
217            fen_history.push(r.fen_snapshot);
218            algebraic_history.push(r.notation);
219        }
220
221        Ok((root_fen, fen_history, algebraic_history))
222    }
223
224    pub async fn log_chat_message(
225        &self,
226        game_id: i64,
227        role: &str,
228        content: &str,
229    ) -> Result<i64, Error> {
230        let result = sqlx::query!(
231            "INSERT INTO chat_messages (game_id, role, content) VALUES (?, ?, ?)",
232            game_id,
233            role,
234            content
235        )
236        .execute(&self.pool)
237        .await?;
238        Ok(result.last_insert_rowid())
239    }
240
241    pub async fn load_chat_history(&self, game_id: i64) -> Result<Vec<(String, String)>, Error> {
242        let messages = sqlx::query!(
243            "SELECT role, content FROM chat_messages WHERE game_id = ? ORDER BY id ASC",
244            game_id
245        )
246        .fetch_all(&self.pool)
247        .await?;
248
249        let mut chat_history = Vec::new();
250        for r in messages {
251            chat_history.push((r.role, r.content));
252        }
253
254        Ok(chat_history)
255    }
256}
chaiss_core/db.rs

chaiss_core/
db.rs
