correctly working auto completion for db

1 files changed, 206 insertions, 53 deletions

diff --git a/src/database.rs b/src/database.rs
index 00d970a..01bee92 100644
--- a/src/database.rs
+++ b/src/database.rs
@@ -63,7 +63,29 @@ impl Database {
         fs::create_dir_all(&data_dir).ok();
         let db_path = data_dir.join("library.db");
 
-        let conn = Connection::open(&db_path)?;
+        let debug_log = std::env::temp_dir().join("rsp-completion-debug.log");
+        macro_rules! debug_log {
+            ($($arg:tt)*) => {
+                let _ = std::fs::OpenOptions::new()
+                    .create(true)
+                    .append(true)
+                    .open(&debug_log)
+                    .and_then(|mut f| {
+                        use std::io::Write;
+                        writeln!(f, $($arg)*)
+                    });
+            };
+        }
+        debug_log!("[Database::open] db_path: {:?}", db_path);
+        debug_log!("[Database::open] db exists: {}", db_path.exists());
+
+        let conn = match Connection::open(&db_path) {
+            Ok(c) => c,
+            Err(e) => {
+                debug_log!("[Database::open] Connection::open failed: {e}");
+                return Err(e);
+            }
+        };
         let db = Database { conn };
         db.init_schema()?;
         Ok(db)
@@ -273,51 +295,174 @@ impl Database {
         self.get_album_tracks_after(album_id, track_number, &path)
     }
 
-    pub fn complete_artists(&self, prefix: &str) -> SqlResult<Vec<String>> {
-        let pattern = format!("%{}%", prefix);
-        let mut stmt = self
-            .conn
-            .prepare("SELECT name FROM artists WHERE name LIKE ?1 ORDER BY name LIMIT 25")?;
-        let rows = stmt.query_map([&pattern], |row| row.get::<_, String>(0))?;
-        rows.collect()
-    }
+    pub fn complete(&self, query: &str) -> SqlResult<Vec<String>> {
+        let mut results: Vec<String> = Vec::new();
 
-    pub fn complete_albums(&self, prefix: &str) -> SqlResult<Vec<String>> {
-        let (artist, album_prefix) = match prefix.split_once('/') {
-            Some((a, ap)) => (a, ap),
-            None => return Ok(Vec::new()),
-        };
-        let pattern = format!("{}%", album_prefix);
-        let mut stmt = self.conn.prepare(
-            "SELECT a.title FROM albums a
-             JOIN artists ar ON a.artist_id = ar.id
-             WHERE ar.name = ?1 AND a.title LIKE ?2
-             ORDER BY a.title LIMIT 25",
-        )?;
-        let rows = stmt.query_map(rusqlite::params![artist, pattern], |row| {
-            row.get::<_, String>(0)
-        })?;
-        rows.collect()
-    }
+        if query.is_empty() {
+            let mut stmt = self
+                .conn
+                .prepare("SELECT name FROM artists ORDER BY name LIMIT 25")?;
+            let rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
+            for name in rows.flatten() {
+                results.push(format!("{name}/"));
+            }
+            return Ok(results);
+        }
 
-    pub fn complete_tracks(&self, prefix: &str) -> SqlResult<Vec<String>> {
-        let parts: Vec<&str> = prefix.splitn(3, '/').collect();
-        if parts.len() < 3 {
-            return Ok(Vec::new());
+        let has_trailing_slash = query.ends_with('/');
+
+        if has_trailing_slash {
+            let prefix = query.trim_end_matches('/');
+            let segments: Vec<&str> = prefix.split('/').filter(|s| !s.is_empty()).collect();
+
+            match segments.len() {
+                1 => {
+                    let artist = segments[0];
+                    let mut stmt = self.conn.prepare(
+                        "SELECT a.title FROM albums a
+                         JOIN artists ar ON a.artist_id = ar.id
+                         WHERE ar.name LIKE ?1
+                         ORDER BY a.title LIMIT 25",
+                    )?;
+                    let rows = stmt.query_map([artist], |row| row.get::<_, String>(0))?;
+                    for album in rows.flatten() {
+                        results.push(format!("{artist}/{album}/"));
+                    }
+                }
+                2 => {
+                    let artist = segments[0];
+                    let album = segments[1];
+                    let mut stmt = self.conn.prepare(
+                        "SELECT t.title FROM tracks t
+                         JOIN albums a ON t.album_id = a.id
+                         JOIN artists ar ON a.artist_id = ar.id
+                         WHERE ar.name LIKE ?1 AND a.title LIKE ?2
+                         ORDER BY t.track_number, t.title LIMIT 25",
+                    )?;
+                    let rows = stmt.query_map(rusqlite::params![artist, album], |row| {
+                        row.get::<_, String>(0)
+                    })?;
+                    for track in rows.flatten() {
+                        results.push(format!("{artist}/{album}/{track}"));
+                    }
+                }
+                _ => {}
+            }
+        } else {
+            let segments: Vec<&str> = query.split('/').collect();
+
+            match segments.len() {
+                1 => {
+                    let prefix = segments[0];
+                    let like_pattern = format!("%{prefix}%");
+                    let prefix_pattern = format!("{prefix}%");
+
+                    let mut stmt = self.conn.prepare(
+                        "SELECT name FROM artists
+                         WHERE name LIKE ?1
+                         ORDER BY
+                           CASE WHEN name LIKE ?2 THEN 0 ELSE 1 END,
+                           name
+                         LIMIT 25",
+                    )?;
+                    let rows = stmt
+                        .query_map(rusqlite::params![&like_pattern, &prefix_pattern], |row| {
+                            row.get::<_, String>(0)
+                        })?;
+                    for name in rows.flatten() {
+                        results.push(format!("{name}/"));
+                    }
+                }
+                2 => {
+                    let artist = segments[0];
+                    let album_prefix = segments[1];
+                    if artist.is_empty() {
+                        return Ok(results);
+                    }
+
+                    let artist_pattern = format!("%{artist}%");
+                    let artist_prefix = format!("{artist}%");
+                    let album_pattern = format!("%{album_prefix}%");
+                    let album_prefix_pattern = format!("{album_prefix}%");
+
+                    let mut stmt = self.conn.prepare(
+                        "SELECT ar.name, a.title FROM albums a
+                         JOIN artists ar ON a.artist_id = ar.id
+                         WHERE ar.name LIKE ?1 AND a.title LIKE ?2
+                         ORDER BY
+                           CASE WHEN ar.name LIKE ?3 THEN 0 ELSE 1 END,
+                           CASE WHEN a.title LIKE ?4 THEN 0 ELSE 1 END,
+                           ar.name, a.title
+                         LIMIT 25",
+                    )?;
+                    let rows = stmt.query_map(
+                        rusqlite::params![
+                            &artist_pattern,
+                            &album_pattern,
+                            &artist_prefix,
+                            &album_prefix_pattern,
+                        ],
+                        |row| Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?)),
+                    )?;
+                    for (name, album) in rows.flatten() {
+                        results.push(format!("{name}/{album}/"));
+                    }
+                }
+                3 => {
+                    let artist = segments[0];
+                    let album = segments[1];
+                    let track_prefix = segments[2];
+                    if artist.is_empty() || album.is_empty() {
+                        return Ok(results);
+                    }
+
+                    let artist_pattern = format!("%{artist}%");
+                    let artist_prefix = format!("{artist}%");
+                    let album_pattern = format!("%{album}%");
+                    let album_prefix = format!("{album}%");
+                    let track_pattern = format!("%{track_prefix}%");
+                    let track_prefix_pattern = format!("{track_prefix}%");
+
+                    let mut stmt = self.conn.prepare(
+                        "SELECT ar.name, a.title, t.title FROM tracks t
+                         JOIN albums a ON t.album_id = a.id
+                         JOIN artists ar ON a.artist_id = ar.id
+                         WHERE ar.name LIKE ?1 AND a.title LIKE ?2 AND t.title LIKE ?3
+                         ORDER BY
+                           CASE WHEN ar.name LIKE ?4 THEN 0 ELSE 1 END,
+                           CASE WHEN a.title LIKE ?5 THEN 0 ELSE 1 END,
+                           CASE WHEN t.title LIKE ?6 THEN 0 ELSE 1 END,
+                           t.track_number, t.title
+                         LIMIT 25",
+                    )?;
+                    let rows = stmt.query_map(
+                        rusqlite::params![
+                            &artist_pattern,
+                            &album_pattern,
+                            &track_pattern,
+                            &artist_prefix,
+                            &album_prefix,
+                            &track_prefix_pattern,
+                        ],
+                        |row| {
+                            Ok((
+                                row.get::<_, String>(0)?,
+                                row.get::<_, String>(1)?,
+                                row.get::<_, String>(2)?,
+                            ))
+                        },
+                    )?;
+                    for (name, album_title, track) in rows.flatten() {
+                        results.push(format!("{name}/{album_title}/{track}"));
+                    }
+                }
+                _ => {}
+            }
         }
-        let (artist, album, track_prefix) = (parts[0], parts[1], parts[2]);
-        let pattern = format!("{}%", track_prefix);
-        let mut stmt = self.conn.prepare(
-            "SELECT t.title FROM tracks t
-             JOIN albums a ON t.album_id = a.id
-             JOIN artists ar ON a.artist_id = ar.id
-             WHERE ar.name = ?1 AND a.title = ?2 AND t.title LIKE ?3
-             ORDER BY t.track_number, t.title LIMIT 25",
-        )?;
-        let rows = stmt.query_map(rusqlite::params![artist, album, pattern], |row| {
-            row.get::<_, String>(0)
-        })?;
-        rows.collect()
+
+        results.truncate(25);
+
+        Ok(results)
     }
 
     #[allow(dead_code)]
@@ -328,14 +473,14 @@ impl Database {
         Ok(db)
     }
 
-    /// Find all tracks in an album by exact artist name and album title.
+    /// Find all tracks in an album by case-insensitive artist and album match.
     pub fn find_tracks_by_album(&self, artist: &str, album: &str) -> SqlResult<Vec<Track>> {
         let mut stmt = self.conn.prepare(
             "SELECT t.id, t.album_id, t.title, t.path, t.duration, t.track_number, ar.name, a.title
              FROM tracks t
              JOIN albums a ON t.album_id = a.id
              JOIN artists ar ON a.artist_id = ar.id
-             WHERE ar.name = ?1 AND a.title = ?2
+             WHERE ar.name COLLATE NOCASE = ?1 AND a.title COLLATE NOCASE = ?2
              ORDER BY t.track_number",
         )?;
         let rows = stmt.query_map(rusqlite::params![artist, album], |row| {
@@ -367,7 +512,7 @@ impl Database {
              FROM tracks t
              JOIN albums a ON t.album_id = a.id
              JOIN artists ar ON a.artist_id = ar.id
-             WHERE ar.name = ?1 AND a.title = ?2 AND t.title LIKE ?3
+             WHERE ar.name COLLATE NOCASE = ?1 AND a.title COLLATE NOCASE = ?2 AND t.title LIKE ?3
              ORDER BY t.track_number LIMIT 1",
         )?;
         let mut rows = stmt.query_map(rusqlite::params![artist, album, pattern], |row| {
@@ -462,11 +607,19 @@ pub fn extract_metadata(path: &Path) -> (String, String, String, Option<i32>, Op
         .and_then(|s| s.to_str())
         .unwrap_or("Unknown");
 
-    (
-        grandparent.to_string(),
-        parent.to_string(),
-        file_stem.to_string(),
-        None,
-        None,
-    )
+    let fs_artist = grandparent.to_string();
+    let fs_album = parent.to_string();
+    let fs_title = file_stem.to_string();
+
+    if let Some(audio_meta) = crate::queue::extract_metadata(path) {
+        (
+            audio_meta.artist.unwrap_or(fs_artist),
+            audio_meta.album.unwrap_or(fs_album),
+            audio_meta.title.unwrap_or(fs_title),
+            audio_meta.track_number,
+            audio_meta.duration_secs.map(|d| d as u64),
+        )
+    } else {
+        (fs_artist, fs_album, fs_title, None, None)
+    }
 }