iwcore/database/
queries.rs

1//! SQL query operations for database access
2//!
3//! This module provides low-level query functions for database operations.
4//! For business-level operations, use the Wallet API.
5
6use rusqlite::{Connection, params};
7use chrono::{DateTime, Utc};
8use crate::error::Result;
9
10/// Timestamp format used in database
11pub const TIMESTAMP_FORMAT: &str = "%Y-%m-%d %H:%M:%S";
12
13/// Format a DateTime for database storage
14pub fn format_timestamp(dt: &DateTime<Utc>) -> String {
15    dt.format(TIMESTAMP_FORMAT).to_string()
16}
17
18/// Parse a timestamp from database
19pub fn parse_timestamp(s: &str) -> Option<DateTime<Utc>> {
20    chrono::NaiveDateTime::parse_from_str(s, TIMESTAMP_FORMAT)
21        .ok()
22        .map(|ndt| DateTime::from_naive_utc_and_offset(ndt, Utc))
23}
24
25/// Get current timestamp formatted for database
26pub fn now_timestamp() -> String {
27    format_timestamp(&Utc::now())
28}
29
30// ============================================================================
31// Properties queries
32// ============================================================================
33
34/// Get database ID from properties
35pub fn get_database_id(conn: &Connection) -> Result<Option<String>> {
36    let result = conn.query_row(
37        "SELECT database_id FROM nswallet_properties LIMIT 1",
38        [],
39        |row| row.get(0),
40    );
41    Ok(result.ok())
42}
43
44/// Check if properties table has any rows
45pub fn has_properties(conn: &Connection) -> Result<bool> {
46    let count: i64 = conn.query_row(
47        "SELECT COUNT(*) FROM nswallet_properties",
48        [],
49        |row| row.get(0),
50    )?;
51    Ok(count > 0)
52}
53
54/// Get all properties from the database
55pub fn get_properties(conn: &Connection) -> Result<Option<RawProperties>> {
56    let result = conn.query_row(
57        "SELECT database_id, lang, version, email, sync_timestamp, update_timestamp
58         FROM nswallet_properties LIMIT 1",
59        [],
60        |row| {
61            Ok(RawProperties {
62                database_id: row.get(0)?,
63                lang: row.get(1)?,
64                version: row.get(2)?,
65                email: row.get(3)?,
66                sync_timestamp: row.get(4)?,
67                update_timestamp: row.get(5)?,
68            })
69        },
70    );
71    Ok(result.ok())
72}
73
74/// Set properties (insert new row)
75pub fn set_properties(
76    conn: &Connection,
77    database_id: &str,
78    lang: &str,
79    version: &str,
80    encryption_count: u32,
81) -> Result<()> {
82    conn.execute(
83        "INSERT INTO nswallet_properties (database_id, lang, version, email, sync_timestamp, update_timestamp)
84         VALUES (?, ?, ?, ?, ?, ?)",
85        params![database_id, lang, version, encryption_count.to_string(), now_timestamp(), now_timestamp()],
86    )?;
87    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
88    Ok(())
89}
90
91/// Update database version
92pub fn set_db_version(conn: &Connection, version: &str) -> Result<()> {
93    conn.execute(
94        "UPDATE nswallet_properties SET version = ?, update_timestamp = ?",
95        params![version, now_timestamp()],
96    )?;
97    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
98    Ok(())
99}
100
101// ============================================================================
102// Items queries
103// ============================================================================
104
105/// Get all items from database (encrypted)
106pub fn get_all_items_raw(conn: &Connection) -> Result<Vec<RawItem>> {
107    let mut stmt = conn.prepare(
108        "SELECT item_id, parent_id, name, icon, folder, create_timestamp, change_timestamp, deleted
109         FROM nswallet_items WHERE deleted = 0"
110    )?;
111
112    let items = stmt.query_map([], |row| {
113        Ok(RawItem {
114            item_id: row.get(0)?,
115            parent_id: row.get(1)?,
116            name_encrypted: row.get(2)?,
117            icon: row.get(3)?,
118            folder: row.get::<_, i32>(4)? != 0,
119            create_timestamp: row.get(5)?,
120            change_timestamp: row.get(6)?,
121            deleted: row.get::<_, i32>(7)? != 0,
122        })
123    })?;
124
125    items.collect::<std::result::Result<Vec<_>, _>>().map_err(Into::into)
126}
127
128/// Get root item (encrypted name)
129pub fn get_root_item_raw(conn: &Connection) -> Result<Option<Vec<u8>>> {
130    let result = conn.query_row(
131        "SELECT name FROM nswallet_items WHERE item_id = '__ROOT__'",
132        [],
133        |row| row.get(0),
134    );
135    Ok(result.ok())
136}
137
138/// Check if root item exists
139pub fn has_root_item(conn: &Connection) -> Result<bool> {
140    let count: i64 = conn.query_row(
141        "SELECT COUNT(*) FROM nswallet_items WHERE item_id = '__ROOT__'",
142        [],
143        |row| row.get(0),
144    )?;
145    Ok(count > 0)
146}
147
148/// Create a new item
149pub fn create_item(
150    conn: &Connection,
151    item_id: &str,
152    parent_id: &str,
153    name_encrypted: &[u8],
154    icon: &str,
155    folder: bool,
156) -> Result<()> {
157    let now = now_timestamp();
158    conn.execute(
159        "INSERT INTO nswallet_items (item_id, parent_id, name, icon, folder, create_timestamp, change_timestamp, deleted)
160         VALUES (?, ?, ?, ?, ?, ?, ?, 0)",
161        params![item_id, parent_id, name_encrypted, icon, folder as i32, now, now],
162    )?;
163    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
164    Ok(())
165}
166
167/// Update item name (encrypted)
168pub fn update_item_name(conn: &Connection, item_id: &str, name_encrypted: &[u8]) -> Result<()> {
169    conn.execute(
170        "UPDATE nswallet_items SET name = ?, change_timestamp = ? WHERE item_id = ?",
171        params![name_encrypted, now_timestamp(), item_id],
172    )?;
173    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
174    Ok(())
175}
176
177/// Update item icon
178pub fn update_item_icon(conn: &Connection, item_id: &str, icon: &str) -> Result<()> {
179    conn.execute(
180        "UPDATE nswallet_items SET icon = ?, change_timestamp = ? WHERE item_id = ?",
181        params![icon, now_timestamp(), item_id],
182    )?;
183    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
184    Ok(())
185}
186
187/// Update item parent (move item)
188pub fn update_item_parent(conn: &Connection, item_id: &str, parent_id: &str) -> Result<()> {
189    conn.execute(
190        "UPDATE nswallet_items SET parent_id = ?, change_timestamp = ? WHERE item_id = ?",
191        params![parent_id, now_timestamp(), item_id],
192    )?;
193    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
194    Ok(())
195}
196
197/// Soft delete an item and cascade to its fields
198pub fn delete_item(conn: &Connection, item_id: &str) -> Result<()> {
199    let now = now_timestamp();
200    conn.execute(
201        "UPDATE nswallet_items SET deleted = 1, change_timestamp = ? WHERE item_id = ?",
202        params![now, item_id],
203    )?;
204    conn.execute(
205        "UPDATE nswallet_fields SET deleted = 1, change_timestamp = ? WHERE item_id = ? AND deleted = 0",
206        params![now, item_id],
207    )?;
208    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
209    Ok(())
210}
211
212/// Get all soft-deleted items from database (encrypted)
213pub fn get_deleted_items_raw(conn: &Connection) -> Result<Vec<RawItem>> {
214    let mut stmt = conn.prepare(
215        "SELECT item_id, parent_id, name, COALESCE(icon, ''), folder, create_timestamp, change_timestamp, deleted
216         FROM nswallet_items WHERE deleted = 1"
217    )?;
218
219    let items = stmt.query_map([], |row| {
220        Ok(RawItem {
221            item_id: row.get(0)?,
222            parent_id: row.get(1)?,
223            name_encrypted: row.get(2)?,
224            icon: row.get(3)?,
225            folder: row.get::<_, i32>(4)? != 0,
226            create_timestamp: row.get(5)?,
227            change_timestamp: row.get(6)?,
228            deleted: row.get::<_, i32>(7)? != 0,
229        })
230    })?;
231
232    items.collect::<std::result::Result<Vec<_>, _>>().map_err(Into::into)
233}
234
235/// Undelete an item (set deleted = 0, move to root)
236pub fn undelete_item(conn: &Connection, item_id: &str) -> Result<()> {
237    let rows = conn.execute(
238        "UPDATE nswallet_items SET deleted = 0, parent_id = '__ROOT__', change_timestamp = ? WHERE item_id = ? AND deleted = 1",
239        params![now_timestamp(), item_id],
240    )?;
241    if rows == 0 {
242        return Err(crate::error::WalletError::ItemNotFound(item_id.to_string()));
243    }
244    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
245    Ok(())
246}
247
248/// Soft-delete all descendants of a folder using recursive CTE, including their fields
249pub fn delete_item_descendants(conn: &Connection, item_id: &str) -> Result<()> {
250    let now = now_timestamp();
251    conn.execute(
252        "WITH RECURSIVE descendants(id) AS (
253            SELECT item_id FROM nswallet_items WHERE parent_id = ?1 AND deleted = 0
254            UNION ALL
255            SELECT i.item_id FROM nswallet_items i JOIN descendants d ON i.parent_id = d.id WHERE i.deleted = 0
256        )
257        UPDATE nswallet_items SET deleted = 1, change_timestamp = ?2 WHERE item_id IN (SELECT id FROM descendants)",
258        params![item_id, now],
259    )?;
260    conn.execute(
261        "WITH RECURSIVE descendants(id) AS (
262            SELECT item_id FROM nswallet_items WHERE parent_id = ?1
263            UNION ALL
264            SELECT i.item_id FROM nswallet_items i JOIN descendants d ON i.parent_id = d.id
265        )
266        UPDATE nswallet_fields SET deleted = 1, change_timestamp = ?2 WHERE item_id IN (SELECT id FROM descendants) AND deleted = 0",
267        params![item_id, now],
268    )?;
269    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
270    Ok(())
271}
272
273/// Update item name without timestamp (for password change)
274pub fn update_item_name_only(conn: &Connection, item_id: &str, name_encrypted: &[u8]) -> Result<()> {
275    conn.execute(
276        "UPDATE nswallet_items SET name = ? WHERE item_id = ?",
277        params![name_encrypted, item_id],
278    )?;
279    Ok(())
280}
281
282// ============================================================================
283// Fields queries
284// ============================================================================
285
286/// Get all fields from database (encrypted)
287pub fn get_all_fields_raw(conn: &Connection) -> Result<Vec<RawField>> {
288    let mut stmt = conn.prepare(
289        "SELECT item_id, field_id, type, value, change_timestamp, deleted, sort_weight
290         FROM nswallet_fields WHERE deleted = 0"
291    )?;
292
293    let fields = stmt.query_map([], |row| {
294        Ok(RawField {
295            item_id: row.get(0)?,
296            field_id: row.get(1)?,
297            field_type: row.get(2)?,
298            value_encrypted: row.get(3)?,
299            change_timestamp: row.get(4)?,
300            deleted: row.get::<_, i32>(5)? != 0,
301            sort_weight: row.get(6)?,
302        })
303    })?;
304
305    fields.collect::<std::result::Result<Vec<_>, _>>().map_err(Into::into)
306}
307
308/// Create a new field
309pub fn create_field(
310    conn: &Connection,
311    item_id: &str,
312    field_id: &str,
313    field_type: &str,
314    value_encrypted: &[u8],
315    sort_weight: i32,
316) -> Result<()> {
317    conn.execute(
318        "INSERT INTO nswallet_fields (item_id, field_id, type, value, change_timestamp, deleted, sort_weight)
319         VALUES (?, ?, ?, ?, ?, 0, ?)",
320        params![item_id, field_id, field_type, value_encrypted, now_timestamp(), sort_weight],
321    )?;
322    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
323    Ok(())
324}
325
326/// Update field value (encrypted)
327pub fn update_field(
328    conn: &Connection,
329    field_id: &str,
330    value_encrypted: &[u8],
331    sort_weight: Option<i32>,
332) -> Result<()> {
333    if let Some(weight) = sort_weight {
334        conn.execute(
335            "UPDATE nswallet_fields SET value = ?, sort_weight = ?, change_timestamp = ? WHERE field_id = ?",
336            params![value_encrypted, weight, now_timestamp(), field_id],
337        )?;
338    } else {
339        conn.execute(
340            "UPDATE nswallet_fields SET value = ?, change_timestamp = ? WHERE field_id = ?",
341            params![value_encrypted, now_timestamp(), field_id],
342        )?;
343    }
344    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
345    Ok(())
346}
347
348/// Soft delete a field
349pub fn delete_field(conn: &Connection, item_id: &str, field_id: &str) -> Result<()> {
350    conn.execute(
351        "UPDATE nswallet_fields SET deleted = 1, change_timestamp = ? WHERE item_id = ? AND field_id = ?",
352        params![now_timestamp(), item_id, field_id],
353    )?;
354    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
355    Ok(())
356}
357
358/// Update field value only (for password change)
359pub fn update_field_value_only(conn: &Connection, item_id: &str, field_id: &str, value_encrypted: &[u8]) -> Result<()> {
360    conn.execute(
361        "UPDATE nswallet_fields SET value = ? WHERE item_id = ? AND field_id = ?",
362        params![value_encrypted, item_id, field_id],
363    )?;
364    Ok(())
365}
366
367/// Get all soft-deleted fields from database (encrypted)
368pub fn get_deleted_fields_raw(conn: &Connection) -> Result<Vec<RawField>> {
369    let mut stmt = conn.prepare(
370        "SELECT item_id, field_id, type, value, change_timestamp, deleted, sort_weight
371         FROM nswallet_fields WHERE deleted = 1"
372    )?;
373
374    let fields = stmt.query_map([], |row| {
375        Ok(RawField {
376            item_id: row.get(0)?,
377            field_id: row.get(1)?,
378            field_type: row.get(2)?,
379            value_encrypted: row.get(3)?,
380            change_timestamp: row.get(4)?,
381            deleted: row.get::<_, i32>(5)? != 0,
382            sort_weight: row.get(6)?,
383        })
384    })?;
385
386    fields.collect::<std::result::Result<Vec<_>, _>>().map_err(Into::into)
387}
388
389/// Undelete a field (set deleted = 0)
390pub fn undelete_field(conn: &Connection, item_id: &str, field_id: &str) -> Result<()> {
391    let rows = conn.execute(
392        "UPDATE nswallet_fields SET deleted = 0, change_timestamp = ? WHERE item_id = ? AND field_id = ? AND deleted = 1",
393        params![now_timestamp(), item_id, field_id],
394    )?;
395    if rows == 0 {
396        return Err(crate::error::WalletError::FieldNotFound(field_id.to_string()));
397    }
398    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
399    Ok(())
400}
401
402/// Permanently purge all soft-deleted records.
403/// Returns (purged_items_count, purged_fields_count).
404pub fn purge_deleted(conn: &Connection) -> Result<(u32, u32)> {
405    // Count before deleting
406    let items_count: u32 = conn.query_row(
407        "SELECT COUNT(*) FROM nswallet_items WHERE deleted = 1",
408        [],
409        |row| row.get(0),
410    )?;
411
412    // Count deleted fields + orphaned fields (fields belonging to deleted items)
413    let fields_count: u32 = conn.query_row(
414        "SELECT COUNT(*) FROM nswallet_fields WHERE deleted = 1 OR item_id IN (SELECT item_id FROM nswallet_items WHERE deleted = 1)",
415        [],
416        |row| row.get(0),
417    )?;
418
419    // Delete orphaned fields first (fields belonging to deleted items)
420    conn.execute(
421        "DELETE FROM nswallet_fields WHERE item_id IN (SELECT item_id FROM nswallet_items WHERE deleted = 1)",
422        [],
423    )?;
424
425    // Delete soft-deleted fields
426    conn.execute(
427        "DELETE FROM nswallet_fields WHERE deleted = 1",
428        [],
429    )?;
430
431    // Delete soft-deleted items
432    conn.execute(
433        "DELETE FROM nswallet_items WHERE deleted = 1",
434        [],
435    )?;
436
437    // Delete soft-deleted labels
438    conn.execute(
439        "DELETE FROM nswallet_labels WHERE deleted = 1",
440        [],
441    )?;
442
443    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
444
445    Ok((items_count, fields_count))
446}
447
448/// Database statistics
449#[derive(Debug, Clone)]
450pub struct DatabaseStats {
451    /// Active items (excluding root)
452    pub total_items: u32,
453    /// Active folders
454    pub total_folders: u32,
455    /// Active fields
456    pub total_fields: u32,
457    /// All labels (system + custom)
458    pub total_labels: u32,
459    /// User-created labels only
460    pub custom_labels: u32,
461    /// Soft-deleted items
462    pub deleted_items: u32,
463    /// Soft-deleted fields
464    pub deleted_fields: u32,
465    /// Database file size in bytes
466    pub file_size_bytes: u64,
467}
468
469/// Get database statistics (counts of items, fields, labels, deleted records)
470pub fn get_database_stats(conn: &Connection) -> Result<DatabaseStats> {
471    let total_items: u32 = conn.query_row(
472        "SELECT COUNT(*) FROM nswallet_items WHERE deleted = 0 AND item_id != '__ROOT__' AND folder = 0",
473        [],
474        |row| row.get(0),
475    )?;
476    let total_folders: u32 = conn.query_row(
477        "SELECT COUNT(*) FROM nswallet_items WHERE deleted = 0 AND item_id != '__ROOT__' AND folder = 1",
478        [],
479        |row| row.get(0),
480    )?;
481    let total_fields: u32 = conn.query_row(
482        "SELECT COUNT(*) FROM nswallet_fields WHERE deleted = 0",
483        [],
484        |row| row.get(0),
485    )?;
486    let total_labels: u32 = conn.query_row(
487        "SELECT COUNT(*) FROM nswallet_labels WHERE deleted = 0",
488        [],
489        |row| row.get(0),
490    )?;
491    let custom_labels: u32 = conn.query_row(
492        "SELECT COUNT(*) FROM nswallet_labels WHERE deleted = 0 AND system = 0",
493        [],
494        |row| row.get(0),
495    )?;
496    let deleted_items: u32 = conn.query_row(
497        "SELECT COUNT(*) FROM nswallet_items WHERE deleted = 1",
498        [],
499        |row| row.get(0),
500    )?;
501    let deleted_fields: u32 = conn.query_row(
502        "SELECT COUNT(*) FROM nswallet_fields WHERE deleted = 1",
503        [],
504        |row| row.get(0),
505    )?;
506
507    Ok(DatabaseStats {
508        total_items,
509        total_folders,
510        total_fields,
511        total_labels,
512        custom_labels,
513        deleted_items,
514        deleted_fields,
515        file_size_bytes: 0, // Caller sets this from file metadata
516    })
517}
518
519/// Get a single active field's raw data by field_id
520pub fn get_field_raw_by_id(conn: &Connection, field_id: &str) -> Result<Option<RawField>> {
521    let result = conn.query_row(
522        "SELECT item_id, field_id, type, value, change_timestamp, deleted, sort_weight
523         FROM nswallet_fields WHERE field_id = ? AND deleted = 0",
524        params![field_id],
525        |row| {
526            Ok(RawField {
527                item_id: row.get(0)?,
528                field_id: row.get(1)?,
529                field_type: row.get(2)?,
530                value_encrypted: row.get(3)?,
531                change_timestamp: row.get(4)?,
532                deleted: row.get::<_, i32>(5)? != 0,
533                sort_weight: row.get(6)?,
534            })
535        },
536    );
537    Ok(result.ok())
538}
539
540/// Get the OLDP field_id for a given item
541pub fn get_oldp_field_id(conn: &Connection, item_id: &str) -> Result<Option<String>> {
542    let result = conn.query_row(
543        "SELECT field_id FROM nswallet_fields WHERE item_id = ? AND type = 'OLDP' AND deleted = 0",
544        params![item_id],
545        |row| row.get(0),
546    );
547    Ok(result.ok())
548}
549
550/// Get max sort weight for an item's fields
551pub fn get_max_field_weight(conn: &Connection, item_id: &str) -> Result<i32> {
552    let result: Option<i32> = conn.query_row(
553        "SELECT MAX(sort_weight) FROM nswallet_fields WHERE item_id = ? AND deleted = 0",
554        params![item_id],
555        |row| row.get(0),
556    ).ok().flatten();
557    Ok(result.unwrap_or(0))
558}
559
560// ============================================================================
561// Labels queries
562// ============================================================================
563
564/// Get all labels from database (with usage count)
565pub fn get_all_labels(conn: &Connection) -> Result<Vec<RawLabel>> {
566    let mut stmt = conn.prepare(
567        "SELECT l.field_type, l.label_name, l.value_type, l.icon, l.system, l.change_timestamp, l.deleted,
568                COALESCE((SELECT COUNT(*) FROM nswallet_fields f WHERE f.type = l.field_type AND f.deleted = 0), 0) as usage
569         FROM nswallet_labels l WHERE l.deleted = 0"
570    )?;
571
572    let labels = stmt.query_map([], |row| {
573        Ok(RawLabel {
574            field_type: row.get(0)?,
575            label_name: row.get(1)?,
576            value_type: row.get(2)?,
577            icon: row.get(3)?,
578            system: row.get::<_, i32>(4)? != 0,
579            change_timestamp: row.get(5)?,
580            deleted: row.get::<_, i32>(6)? != 0,
581            usage: row.get(7)?,
582        })
583    })?;
584
585    labels.collect::<std::result::Result<Vec<_>, _>>().map_err(Into::into)
586}
587
588/// Create a new label
589pub fn create_label(
590    conn: &Connection,
591    field_type: &str,
592    label_name: &str,
593    value_type: &str,
594    icon: &str,
595    system: bool,
596) -> Result<bool> {
597    let result = conn.execute(
598        "INSERT OR IGNORE INTO nswallet_labels (field_type, label_name, value_type, icon, system, change_timestamp, deleted)
599         VALUES (?, ?, ?, ?, ?, ?, 0)",
600        params![field_type, label_name, value_type, icon, system as i32, now_timestamp()],
601    )?;
602    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
603    Ok(result > 0)
604}
605
606/// Update label name
607pub fn update_label_name(conn: &Connection, field_type: &str, label_name: &str) -> Result<()> {
608    conn.execute(
609        "UPDATE nswallet_labels SET label_name = ?, change_timestamp = ? WHERE field_type = ?",
610        params![label_name, now_timestamp(), field_type],
611    )?;
612    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
613    Ok(())
614}
615
616/// Update label icon
617pub fn update_label_icon(conn: &Connection, field_type: &str, icon: &str) -> Result<()> {
618    conn.execute(
619        "UPDATE nswallet_labels SET icon = ?, change_timestamp = ? WHERE field_type = ?",
620        params![icon, now_timestamp(), field_type],
621    )?;
622    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
623    Ok(())
624}
625
626/// Soft delete a label (returns count of fields using it)
627pub fn delete_label(conn: &Connection, field_type: &str) -> Result<i32> {
628    // Count fields using this label
629    let count: i32 = conn.query_row(
630        "SELECT COUNT(*) FROM nswallet_fields WHERE type = ? AND deleted = 0",
631        params![field_type],
632        |row| row.get(0),
633    )?;
634
635    if count == 0 {
636        conn.execute(
637            "UPDATE nswallet_labels SET deleted = 1, change_timestamp = ? WHERE field_type = ?",
638            params![now_timestamp(), field_type],
639        )?;
640        conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
641    }
642
643    Ok(count)
644}
645
646/// Permanently delete a label
647pub fn remove_label_for_real(conn: &Connection, field_type: &str) -> Result<bool> {
648    let result = conn.execute(
649        "DELETE FROM nswallet_labels WHERE field_type = ?",
650        params![field_type],
651    )?;
652    conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
653    Ok(result > 0)
654}
655
656// ============================================================================
657// Raw data structures (before decryption)
658// ============================================================================
659
660/// Raw properties data from database
661#[derive(Debug, Clone)]
662pub struct RawProperties {
663    /// Unique database identifier
664    pub database_id: String,
665    /// Database language code (e.g. "en", "de")
666    pub lang: String,
667    /// Database schema version
668    pub version: String,
669    /// Encryption iteration count (stored in the legacy `email` column)
670    pub email: String,
671    /// Last cloud sync timestamp
672    pub sync_timestamp: Option<String>,
673    /// Last local update timestamp
674    pub update_timestamp: Option<String>,
675}
676
677/// Raw item data from database (before decryption)
678#[derive(Debug, Clone)]
679pub struct RawItem {
680    /// Unique item identifier
681    pub item_id: String,
682    /// Parent item/folder ID (`None` for root-level items)
683    pub parent_id: Option<String>,
684    /// AES-256 encrypted item name
685    pub name_encrypted: Vec<u8>,
686    /// Icon identifier
687    pub icon: String,
688    /// Whether this item is a folder
689    pub folder: bool,
690    /// Creation timestamp
691    pub create_timestamp: Option<String>,
692    /// Last modification timestamp
693    pub change_timestamp: Option<String>,
694    /// Whether this item is soft-deleted
695    pub deleted: bool,
696}
697
698/// Raw field data from database (before decryption)
699#[derive(Debug, Clone)]
700pub struct RawField {
701    /// Parent item ID
702    pub item_id: String,
703    /// Unique field identifier
704    pub field_id: String,
705    /// Field type code (e.g. "MAIL", "PASS", "NOTE")
706    pub field_type: String,
707    /// AES-256 encrypted field value
708    pub value_encrypted: Vec<u8>,
709    /// Last modification timestamp
710    pub change_timestamp: Option<String>,
711    /// Whether this field is soft-deleted
712    pub deleted: bool,
713    /// Display ordering weight
714    pub sort_weight: Option<i32>,
715}
716
717/// Raw label data from database
718#[derive(Debug, Clone)]
719pub struct RawLabel {
720    /// Label type code (e.g. "MAIL", "PASS")
721    pub field_type: String,
722    /// Human-readable label name
723    pub label_name: String,
724    /// Value type hint (e.g. "text", "password", "email")
725    pub value_type: String,
726    /// Icon identifier
727    pub icon: String,
728    /// Whether this is a built-in system label
729    pub system: bool,
730    /// Last modification timestamp
731    pub change_timestamp: Option<String>,
732    /// Whether this label is soft-deleted
733    pub deleted: bool,
734    /// Number of fields using this label
735    pub usage: i32,
736}
737
738#[cfg(test)]
739mod tests {
740    use super::*;
741    use chrono::{TimeZone, Datelike, Timelike};
742
743    #[test]
744    fn test_format_timestamp() {
745        let dt = Utc.with_ymd_and_hms(2023, 12, 15, 10, 30, 45).unwrap();
746        assert_eq!(format_timestamp(&dt), "2023-12-15 10:30:45");
747    }
748
749    #[test]
750    fn test_parse_timestamp() {
751        let ts = parse_timestamp("2023-12-15 10:30:45").unwrap();
752        assert_eq!(ts.year(), 2023);
753        assert_eq!(ts.month(), 12);
754        assert_eq!(ts.day(), 15);
755        assert_eq!(ts.hour(), 10);
756        assert_eq!(ts.minute(), 30);
757        assert_eq!(ts.second(), 45);
758    }
759
760    #[test]
761    fn test_parse_timestamp_invalid() {
762        assert!(parse_timestamp("invalid").is_none());
763        assert!(parse_timestamp("2023-13-01 00:00:00").is_none());
764    }
765
766    #[test]
767    fn test_now_timestamp() {
768        let ts = now_timestamp();
769        // Should be in format YYYY-MM-DD HH:MM:SS
770        assert_eq!(ts.len(), 19);
771        assert!(ts.contains("-"));
772        assert!(ts.contains(":"));
773    }
774}
iwcore/database/queries.rs

iwcore/database/
queries.rs
