mostro_client/
db.rs

1use crate::util::get_mcli_path;
2use anyhow::Result;
3use mostro_core::prelude::*;
4use nip06::FromMnemonic;
5use nostr_sdk::prelude::*;
6use sqlx::pool::Pool;
7use sqlx::Sqlite;
8use sqlx::SqlitePool;
9use std::fs::File;
10use std::path::Path;
11
12pub async fn connect() -> Result<Pool<Sqlite>> {
13    let mcli_dir = get_mcli_path();
14    let mcli_db_path = format!("{}/mcli.db", mcli_dir);
15    let db_url = format!("sqlite://{}", mcli_db_path);
16    let pool: Pool<Sqlite>;
17    if !Path::exists(Path::new(&mcli_db_path)) {
18        if let Err(res) = File::create(&mcli_db_path) {
19            println!("Error in creating db file: {}", res);
20            return Err(res.into());
21        }
22        pool = SqlitePool::connect(&db_url).await?;
23        println!("Creating database file with orders table...");
24        sqlx::query(
25            r#"
26          CREATE TABLE IF NOT EXISTS orders (
27              id TEXT PRIMARY KEY,
28              kind TEXT NOT NULL,
29              status TEXT NOT NULL,
30              amount INTEGER NOT NULL,
31              min_amount INTEGER,
32              max_amount INTEGER,
33              fiat_code TEXT NOT NULL,
34              fiat_amount INTEGER NOT NULL,
35              payment_method TEXT NOT NULL,
36              premium INTEGER NOT NULL,
37              trade_keys TEXT,
38              counterparty_pubkey TEXT,
39              is_mine BOOLEAN,
40              buyer_invoice TEXT,
41              request_id INTEGER,
42              created_at INTEGER,
43              expires_at INTEGER
44          );
45          CREATE TABLE IF NOT EXISTS users (
46              i0_pubkey char(64) PRIMARY KEY,
47              mnemonic TEXT,
48              last_trade_index INTEGER,
49              created_at INTEGER
50          );
51          "#,
52        )
53        .execute(&pool)
54        .await?;
55
56        let mnemonic = match Mnemonic::generate(12) {
57            Ok(m) => m.to_string(),
58            Err(e) => {
59                println!("Error generating mnemonic: {}", e);
60                return Err(e.into());
61            }
62        };
63        let user = User::new(mnemonic, &pool).await?;
64        println!("User created with pubkey: {}", user.i0_pubkey);
65    } else {
66        pool = SqlitePool::connect(&db_url).await?;
67
68        // Migration: Drop buyer_token and seller_token columns if they exist
69        migrate_remove_token_columns(&pool).await?;
70    }
71
72    Ok(pool)
73}
74
75async fn migrate_remove_token_columns(pool: &SqlitePool) -> Result<()> {
76    println!("Checking for legacy token columns...");
77
78    // Check if buyer_token column exists
79    let buyer_token_exists = sqlx::query_scalar::<_, i64>(
80        "SELECT COUNT(*) FROM pragma_table_info('orders') WHERE name = 'buyer_token'",
81    )
82    .fetch_one(pool)
83    .await?;
84
85    // Check if seller_token column exists
86    let seller_token_exists = sqlx::query_scalar::<_, i64>(
87        "SELECT COUNT(*) FROM pragma_table_info('orders') WHERE name = 'seller_token'",
88    )
89    .fetch_one(pool)
90    .await?;
91
92    // Drop buyer_token column if it exists
93    if buyer_token_exists > 0 {
94        println!("Removing legacy buyer_token column...");
95        match sqlx::query("ALTER TABLE orders DROP COLUMN buyer_token")
96            .execute(pool)
97            .await
98        {
99            Ok(_) => println!("Successfully removed buyer_token column"),
100            Err(e) => {
101                println!("Warning: Could not remove buyer_token column: {}", e);
102                // Continue execution - this is not critical
103            }
104        }
105    }
106
107    // Drop seller_token column if it exists
108    if seller_token_exists > 0 {
109        println!("Removing legacy seller_token column...");
110        match sqlx::query("ALTER TABLE orders DROP COLUMN seller_token")
111            .execute(pool)
112            .await
113        {
114            Ok(_) => println!("Successfully removed seller_token column"),
115            Err(e) => {
116                println!("Warning: Could not remove seller_token column: {}", e);
117                // Continue execution - this is not critical
118            }
119        }
120    }
121
122    if buyer_token_exists == 0 && seller_token_exists == 0 {
123        println!("No legacy token columns found - database is up to date");
124    }
125
126    Ok(())
127}
128
129#[derive(Debug, Default, Clone, sqlx::FromRow)]
130pub struct User {
131    /// The user's ID is the identity pubkey
132    pub i0_pubkey: String,
133    pub mnemonic: String,
134    pub last_trade_index: Option<i64>,
135    pub created_at: i64,
136}
137
138impl User {
139    pub async fn new(mnemonic: String, pool: &SqlitePool) -> Result<Self> {
140        let mut user = User::default();
141        let account = NOSTR_REPLACEABLE_EVENT_KIND as u32;
142        let i0_keys =
143            Keys::from_mnemonic_advanced(&mnemonic, None, Some(account), Some(0), Some(0))?;
144        user.i0_pubkey = i0_keys.public_key().to_string();
145        user.created_at = chrono::Utc::now().timestamp();
146        user.mnemonic = mnemonic;
147        sqlx::query(
148            r#"
149                  INSERT INTO users (i0_pubkey, mnemonic, created_at)
150                  VALUES (?, ?, ?)
151                "#,
152        )
153        .bind(&user.i0_pubkey)
154        .bind(&user.mnemonic)
155        .bind(user.created_at)
156        .execute(pool)
157        .await?;
158
159        Ok(user)
160    }
161    // Chainable setters
162    pub fn set_mnemonic(&mut self, mnemonic: String) -> &mut Self {
163        self.mnemonic = mnemonic;
164        self
165    }
166
167    pub fn set_last_trade_index(&mut self, last_trade_index: i64) -> &mut Self {
168        self.last_trade_index = Some(last_trade_index);
169        self
170    }
171
172    // Applying changes to the database
173    pub async fn save(&self, pool: &SqlitePool) -> Result<()> {
174        sqlx::query(
175            r#"
176              UPDATE users 
177              SET mnemonic = ?, last_trade_index = ?
178              WHERE i0_pubkey = ?
179              "#,
180        )
181        .bind(&self.mnemonic)
182        .bind(self.last_trade_index)
183        .bind(&self.i0_pubkey)
184        .execute(pool)
185        .await?;
186
187        println!(
188            "User with i0 pubkey {} updated in the database.",
189            self.i0_pubkey
190        );
191
192        Ok(())
193    }
194
195    pub async fn get(pool: &SqlitePool) -> Result<User> {
196        let user = sqlx::query_as::<_, User>(
197            r#"
198            SELECT i0_pubkey, mnemonic, last_trade_index, created_at
199            FROM users
200            LIMIT 1
201            "#,
202        )
203        .fetch_one(pool)
204        .await?;
205
206        Ok(user)
207    }
208
209    pub async fn get_last_trade_index(pool: SqlitePool) -> Result<i64> {
210        let user = User::get(&pool).await?;
211        match user.last_trade_index {
212            Some(index) => Ok(index),
213            None => Ok(0),
214        }
215    }
216
217    pub async fn get_next_trade_index(pool: SqlitePool) -> Result<i64> {
218        let last_trade_index = User::get_last_trade_index(pool).await?;
219        Ok(last_trade_index + 1)
220    }
221
222    pub async fn get_identity_keys(pool: &SqlitePool) -> Result<Keys> {
223        let user = User::get(pool).await?;
224        let account = NOSTR_REPLACEABLE_EVENT_KIND as u32;
225        let keys =
226            Keys::from_mnemonic_advanced(&user.mnemonic, None, Some(account), Some(0), Some(0))?;
227
228        Ok(keys)
229    }
230
231    pub async fn get_next_trade_keys(pool: &SqlitePool) -> Result<(Keys, i64)> {
232        let trade_index = User::get_next_trade_index(pool.clone()).await?;
233        let trade_keys = User::get_trade_keys(pool, trade_index).await?;
234
235        Ok((trade_keys, trade_index))
236    }
237
238    pub async fn get_trade_keys(pool: &SqlitePool, index: i64) -> Result<Keys> {
239        if index < 0 {
240            return Err(anyhow::anyhow!("Trade index cannot be negative"));
241        }
242        let user = User::get(pool).await?;
243        let account = NOSTR_REPLACEABLE_EVENT_KIND as u32;
244        let keys = Keys::from_mnemonic_advanced(
245            &user.mnemonic,
246            None,
247            Some(account),
248            Some(0),
249            Some(index as u32),
250        )?;
251
252        Ok(keys)
253    }
254}
255
256#[derive(Debug, Default, Clone, sqlx::FromRow)]
257pub struct Order {
258    pub id: Option<String>,
259    pub kind: Option<String>,
260    pub status: Option<String>,
261    pub amount: i64,
262    pub fiat_code: String,
263    pub min_amount: Option<i64>,
264    pub max_amount: Option<i64>,
265    pub fiat_amount: i64,
266    pub payment_method: String,
267    pub premium: i64,
268    pub trade_keys: Option<String>,
269    pub counterparty_pubkey: Option<String>,
270    pub is_mine: Option<bool>,
271    pub buyer_invoice: Option<String>,
272    pub request_id: Option<i64>,
273    pub created_at: Option<i64>,
274    pub expires_at: Option<i64>,
275}
276
277impl Order {
278    pub async fn new(
279        pool: &SqlitePool,
280        order: SmallOrder,
281        trade_keys: &Keys,
282        request_id: Option<i64>,
283    ) -> Result<Self> {
284        let trade_keys_hex = trade_keys.secret_key().to_secret_hex();
285        let id = match order.id {
286            Some(id) => id.to_string(),
287            None => uuid::Uuid::new_v4().to_string(),
288        };
289        let order = Order {
290            id: Some(id),
291            kind: order.kind.as_ref().map(|k| k.to_string()),
292            status: order.status.as_ref().map(|s| s.to_string()),
293            amount: order.amount,
294            fiat_code: order.fiat_code,
295            min_amount: order.min_amount,
296            max_amount: order.max_amount,
297            fiat_amount: order.fiat_amount,
298            payment_method: order.payment_method,
299            premium: order.premium,
300            trade_keys: Some(trade_keys_hex),
301            counterparty_pubkey: None,
302            is_mine: Some(true),
303            buyer_invoice: None,
304            request_id,
305            created_at: Some(chrono::Utc::now().timestamp()),
306            expires_at: None,
307        };
308
309        sqlx::query(
310            r#"
311                  INSERT INTO orders (id, kind, status, amount, min_amount, max_amount,
312                  fiat_code, fiat_amount, payment_method, premium, trade_keys,
313                  counterparty_pubkey, is_mine, buyer_invoice, request_id, created_at, expires_at)
314                  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
315                "#,
316        )
317        .bind(&order.id)
318        .bind(&order.kind)
319        .bind(&order.status)
320        .bind(order.amount)
321        .bind(order.min_amount)
322        .bind(order.max_amount)
323        .bind(&order.fiat_code)
324        .bind(order.fiat_amount)
325        .bind(&order.payment_method)
326        .bind(order.premium)
327        .bind(&order.trade_keys)
328        .bind(&order.counterparty_pubkey)
329        .bind(order.is_mine)
330        .bind(&order.buyer_invoice)
331        .bind(order.request_id)
332        .bind(order.created_at)
333        .bind(order.expires_at)
334        .execute(pool)
335        .await?;
336
337        Ok(order)
338    }
339
340    // Setters encadenables
341    pub fn set_kind(&mut self, kind: String) -> &mut Self {
342        self.kind = Some(kind);
343        self
344    }
345
346    pub fn set_status(&mut self, status: String) -> &mut Self {
347        self.status = Some(status);
348        self
349    }
350
351    pub fn set_amount(&mut self, amount: i64) -> &mut Self {
352        self.amount = amount;
353        self
354    }
355
356    pub fn set_fiat_code(&mut self, fiat_code: String) -> &mut Self {
357        self.fiat_code = fiat_code;
358        self
359    }
360
361    pub fn set_min_amount(&mut self, min_amount: i64) -> &mut Self {
362        self.min_amount = Some(min_amount);
363        self
364    }
365
366    pub fn set_max_amount(&mut self, max_amount: i64) -> &mut Self {
367        self.max_amount = Some(max_amount);
368        self
369    }
370
371    pub fn set_fiat_amount(&mut self, fiat_amount: i64) -> &mut Self {
372        self.fiat_amount = fiat_amount;
373        self
374    }
375
376    pub fn set_payment_method(&mut self, payment_method: String) -> &mut Self {
377        self.payment_method = payment_method;
378        self
379    }
380
381    pub fn set_premium(&mut self, premium: i64) -> &mut Self {
382        self.premium = premium;
383        self
384    }
385
386    pub fn set_counterparty_pubkey(&mut self, counterparty_pubkey: String) -> &mut Self {
387        self.counterparty_pubkey = Some(counterparty_pubkey);
388        self
389    }
390
391    pub fn set_trade_keys(&mut self, trade_keys: String) -> &mut Self {
392        self.trade_keys = Some(trade_keys);
393        self
394    }
395
396    pub fn set_is_mine(&mut self, is_mine: bool) -> &mut Self {
397        self.is_mine = Some(is_mine);
398        self
399    }
400
401    // Applying changes to the database
402    pub async fn save(&self, pool: &SqlitePool) -> Result<()> {
403        // Validation if an identity document is present
404        if let Some(ref id) = self.id {
405            sqlx::query(
406                r#"
407              UPDATE orders 
408              SET kind = ?, status = ?, amount = ?, fiat_code = ?, min_amount = ?, max_amount = ?, 
409                  fiat_amount = ?, payment_method = ?, premium = ?, trade_keys = ?, counterparty_pubkey = ?,
410                  is_mine = ?, buyer_invoice = ?, expires_at = ?
411              WHERE id = ?
412              "#,
413            )
414            .bind(&self.kind)
415            .bind(&self.status)
416            .bind(self.amount)
417            .bind(&self.fiat_code)
418            .bind(self.min_amount)
419            .bind(self.max_amount)
420            .bind(self.fiat_amount)
421            .bind(&self.payment_method)
422            .bind(self.premium)
423            .bind(&self.trade_keys)
424            .bind(&self.counterparty_pubkey)
425            .bind(self.is_mine)
426            .bind(&self.buyer_invoice)
427            .bind(self.expires_at)
428            .bind(id)
429            .execute(pool)
430            .await?;
431
432            println!("Order with id {} updated in the database.", id);
433        } else {
434            return Err(anyhow::anyhow!("Order must have an ID to be updated."));
435        }
436
437        Ok(())
438    }
439
440    pub async fn save_new_id(
441        pool: &SqlitePool,
442        id: String,
443        new_id: String,
444    ) -> anyhow::Result<bool> {
445        let rows_affected = sqlx::query(
446            r#"
447          UPDATE orders
448          SET id = ?
449          WHERE id = ?
450        "#,
451        )
452        .bind(&new_id)
453        .bind(&id)
454        .execute(pool)
455        .await?
456        .rows_affected();
457
458        Ok(rows_affected > 0)
459    }
460
461    pub async fn get_by_id(pool: &SqlitePool, id: &str) -> Result<Order> {
462        let order = sqlx::query_as::<_, Order>(
463            r#"
464            SELECT * FROM orders WHERE id = ?
465            LIMIT 1
466            "#,
467        )
468        .bind(id)
469        .fetch_one(pool)
470        .await?;
471
472        if order.id.is_none() {
473            return Err(anyhow::anyhow!("Order not found"));
474        }
475
476        Ok(order)
477    }
478
479    pub async fn get_all(pool: &SqlitePool) -> Result<Vec<Order>> {
480        let orders = sqlx::query_as::<_, Order>(r#"SELECT * FROM orders"#)
481            .fetch_all(pool)
482            .await?;
483        Ok(orders)
484    }
485
486    pub async fn get_all_trade_keys(pool: &SqlitePool) -> Result<Vec<String>> {
487        let trade_keys: Vec<String> = sqlx::query_scalar::<_, Option<String>>(
488            "SELECT DISTINCT trade_keys FROM orders WHERE trade_keys IS NOT NULL",
489        )
490        .fetch_all(pool)
491        .await?
492        .into_iter()
493        .flatten()
494        .collect();
495
496        Ok(trade_keys)
497    }
498
499    pub async fn delete_by_id(pool: &SqlitePool, id: &str) -> Result<bool> {
500        let rows_affected = sqlx::query(
501            r#"
502          DELETE FROM orders
503          WHERE id = ?
504        "#,
505        )
506        .bind(id)
507        .execute(pool)
508        .await?
509        .rows_affected();
510
511        Ok(rows_affected > 0)
512    }
513}
mostro_client/db.rs

mostro_client/
db.rs
