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    /// Persists the board-flip orientation so resuming a session restores the same viewpoint.
90    pub async fn set_flip_board(&self, game_id: i64, flipped: bool) -> Result<(), Error> {
91        sqlx::query!(
92            "UPDATE games SET flip_board = ? WHERE id = ?",
93            flipped,
94            game_id
95        )
96        .execute(&self.pool)
97        .await?;
98        Ok(())
99    }
100
101    pub async fn get_flip_board(&self, game_id: i64) -> Result<bool, Error> {
102        let record = sqlx::query!("SELECT flip_board FROM games WHERE id = ?", game_id)
103            .fetch_optional(&self.pool)
104            .await?;
105        Ok(record.is_some_and(|r| r.flip_board != 0))
106    }
107
108    pub async fn delete_game(&self, game_id: i64) -> Result<(), Error> {
109        let mut tx = self.pool.begin().await?;
110
111        // Cascading Relational teardown structure cleanly wiping dependencies beforehand!
112        sqlx::query!("DELETE FROM chat_messages WHERE game_id = ?", game_id)
113            .execute(&mut *tx)
114            .await?;
115        sqlx::query!("DELETE FROM moves WHERE game_id = ?", game_id)
116            .execute(&mut *tx)
117            .await?;
118        sqlx::query!("DELETE FROM games WHERE id = ?", game_id)
119            .execute(&mut *tx)
120            .await?;
121
122        tx.commit().await?;
123        Ok(())
124    }
125
126    pub async fn log_move(
127        &self,
128        game_id: i64,
129        move_number: i64,
130        fen_snapshot: &str,
131        notation: &str,
132    ) -> Result<(), Error> {
133        sqlx::query!(
134            "INSERT INTO moves (game_id, move_number, fen_snapshot, notation) VALUES (?, ?, ?, ?)",
135            game_id,
136            move_number,
137            fen_snapshot,
138            notation
139        )
140        .execute(&self.pool)
141        .await?;
142        Ok(())
143    }
144
145    /// Natively handles the "Undo" architecture algebraically without engine reverse-math!
146    pub async fn undo_last_move(&self, game_id: i64) -> Result<Option<String>, Error> {
147        // Query the highest move_number mathematically tracked
148        let last_move = sqlx::query!(
149            "SELECT id, move_number FROM moves WHERE game_id = ? ORDER BY move_number DESC LIMIT 1",
150            game_id
151        )
152        .fetch_optional(&self.pool)
153        .await?;
154
155        if let Some(lm) = last_move {
156            // Discard the erroneous action from the physical history ledger natively!
157            sqlx::query!("DELETE FROM moves WHERE id = ?", lm.id)
158                .execute(&self.pool)
159                .await?;
160
161            // Look up what the physical state was directly prior natively by scanning the previous stack vector
162            let prev_move = sqlx::query!(
163                "SELECT fen_snapshot FROM moves WHERE game_id = ? ORDER BY move_number DESC LIMIT 1",
164                game_id
165            )
166            .fetch_optional(&self.pool)
167            .await?;
168
169            let recovered_fen = prev_move.map(|r| r.fen_snapshot).unwrap_or_else(|| {
170                // If the player undid absolutely the FIRST move of the game, reset to pristine FIDE starting structures universally!
171                "rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR w KQkq - 0 1".to_string()
172            });
173
174            // Re-mount the recovered array to the active game tracking native structurally!
175            sqlx::query!(
176                "UPDATE games SET current_fen = ?, status = 'ongoing', updated_at = CURRENT_TIMESTAMP WHERE id = ?",
177                recovered_fen, game_id
178            )
179            .execute(&self.pool)
180            .await?;
181
182            return Ok(Some(recovered_fen));
183        }
184
185        Ok(None)
186    }
187
188    /// Fetches a dynamic matrix of active database sessions mathematically JOINed alongside explicit strings!
189    pub async fn get_active_games(&self) -> Result<Vec<GameRecord>, sqlx::Error> {
190        let records = sqlx::query!(
191            r#"
192            SELECT
193                g.id, g.name, g.status,
194                pw.name as white_name,
195                pb.name as black_name
196            FROM games g
197            JOIN players pw ON g.white_player_id = pw.id
198            JOIN players pb ON g.black_player_id = pb.id
199            ORDER BY g.updated_at DESC
200            "#
201        )
202        .fetch_all(&self.pool)
203        .await?;
204
205        Ok(records
206            .into_iter()
207            .map(|rec| GameRecord {
208                id: rec.id,
209                name: rec.name,
210                status: rec.status,
211                white_player: rec.white_name,
212                black_player: rec.black_name,
213            })
214            .collect())
215    }
216
217    /// Recursively fetches the exact historical move vectors for resuming Egui Sandbox arrays natively!
218    pub async fn load_game_history(
219        &self,
220        game_id: i64,
221    ) -> Result<(String, Vec<String>, Vec<String>), sqlx::Error> {
222        // Technically, `games` does not retain `initial_fen` independently currently. We inject standard FIDE root explicitly!
223        let root_fen = "rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR w KQkq - 0 1".to_string();
224
225        let moves = sqlx::query!(
226            "SELECT fen_snapshot, notation FROM moves WHERE game_id = ? ORDER BY move_number ASC",
227            game_id
228        )
229        .fetch_all(&self.pool)
230        .await?;
231
232        let mut fen_history = Vec::new();
233        let mut algebraic_history = Vec::new();
234
235        for r in moves {
236            fen_history.push(r.fen_snapshot);
237            algebraic_history.push(r.notation);
238        }
239
240        Ok((root_fen, fen_history, algebraic_history))
241    }
242
243    pub async fn log_chat_message(
244        &self,
245        game_id: i64,
246        role: &str,
247        content: &str,
248    ) -> Result<i64, Error> {
249        let result = sqlx::query!(
250            "INSERT INTO chat_messages (game_id, role, content) VALUES (?, ?, ?)",
251            game_id,
252            role,
253            content
254        )
255        .execute(&self.pool)
256        .await?;
257        Ok(result.last_insert_rowid())
258    }
259
260    pub async fn load_chat_history(&self, game_id: i64) -> Result<Vec<(String, String)>, Error> {
261        let messages = sqlx::query!(
262            "SELECT role, content FROM chat_messages WHERE game_id = ? ORDER BY id ASC",
263            game_id
264        )
265        .fetch_all(&self.pool)
266        .await?;
267
268        let mut chat_history = Vec::new();
269        for r in messages {
270            chat_history.push((r.role, r.content));
271        }
272
273        Ok(chat_history)
274    }
275}
chaiss_core/db.rs

chaiss_core/
db.rs
