dirty, broken commit

9 files changed, 916 insertions, 30 deletions

diff --git a/.zed/tasks.json b/.zed/tasks.json
index 70d637f..70f3bfe 100644
--- a/.zed/tasks.json
+++ b/.zed/tasks.json
@@ -15,7 +15,7 @@
   },
   {
     "label": "install",
-    "command": "./install.sh",
+    "command": "./install.sh && ls -lha $ZED_WORKTREE_ROOT/target/release/rsp | awk '{print $5}'",
     "show_command": false,
     "show_summary": false,
     "save": "all",
diff --git a/PLAN.md b/PLAN.md
new file mode 100644
index 0000000..baff505
--- /dev/null
+++ b/PLAN.md
@@ -0,0 +1,135 @@
+# Dynamic Shell Autocompletion for Artists/Albums/Tracks
+
+## Problem
+
+`rsp play <query>` and `rsp queue <query>` accept freeform strings. No tab-completion for artists, albums, or tracks from the SQLite library DB. User must type full path or query blindly.
+
+## Approach: Direct DB read (no daemon dependency)
+
+Hidden CLI subcommand `rsp complete <entity> [prefix]` that:
+1. Opens DB directly (same as `rsp scan` at cli.rs:204-205)
+2. Queries with prefix match
+3. Prints newline-separated results to stdout
+4. Returns empty on error (never crash completion)
+
+Works with or without daemon running. SQLite prefix query is ~1ms.
+
+## Steps
+
+### 1. Add completion query methods to `Database` (`src/database.rs`)
+
+```
+pub fn complete_artists(&self, prefix: &str) -> SqlResult<Vec<String>>
+pub fn complete_albums(&self, prefix: &str) -> SqlResult<Vec<String>>
+pub fn complete_tracks(&self, prefix: &str) -> SqlResult<Vec<String>>
+```
+
+SQL:
+- artists: `SELECT name FROM artists WHERE name LIKE ? ORDER BY name`
+- albums: `SELECT title FROM albums WHERE title LIKE ? ORDER BY title`
+- tracks: `SELECT title FROM tracks WHERE title LIKE ? ORDER BY title`
+
+Prefix `"a"` → `LIKE 'a%'`. Limit results (e.g. 25).
+
+### 2. Add hidden `DbComplete` subcommand (`src/cli.rs`)
+
+```
+#[clap(hide = true)]
+DbComplete {
+    entity: String,  // "artists", "albums", "tracks"
+    prefix: Option<String>,
+}
+```
+
+Dispatch in `Cli::run()`:
+```
+Commands::DbComplete { entity, prefix } => {
+    let config = Config::load()?;
+    let db = Database::open(&config)?;
+    let results = match entity.as_str() { ... };
+    for r in results { println!("{r}"); }
+}
+```
+
+Errors → silent empty (stderr) so completion never breaks.
+
+### 3. Filter `DbComplete` out of generated completions (like `Completions`)
+
+In `Completions` generation (cli.rs:238-253): filter `s.get_name() != "db-complete"`.
+
+### 4. Dynamic zsh completion function
+
+Replace `/usr/share/zsh/site-functions/_rsp` with a wrapper that:
+
+```zsh
+#compdef rsp
+# Static part (generated by clap)
+_rsp_command() { ... }  # clap-generated
+
+# Dynamic overrides
+_rsp() {
+  case $line[1] in
+    play|queue)
+      if compset -P 1; then
+        # Subcommand → complete tracks/artists/albums
+        local -a suggestions
+        suggestions=(${(f)"$(rsp complete tracks "$words[CURRENT]")"})
+        suggestions+=(${(f)"$(rsp complete artists "$words[CURRENT]")"})
+        suggestions+=(${(f)"$(rsp complete albums "$words[CURRENT]")"})
+        compadd -a suggestions
+      else
+        _rsp_command
+      fi
+      ;;
+    *)
+      _rsp_command
+      ;;
+  esac
+}
+```
+
+For `play <query>`, complete artists + albums + tracks.
+Where artists have the biggest priority, then comes albums and at the end tracks.
+Additionally, when we have autocompleted an artist, put a '/' after the name. Next autocompletions will autocomplete albums/tracks, do the same for albums.
+
+### 5. Update `Completions` generation to produce the wrapper
+
+In `cli.rs`, after `generate()` for zsh, append the dynamic wrapper code. The generated file becomes: `clap-generated-code` + `_rsp() { ... dynamic ... }`.
+
+Or simpler: write the wrapper as `completions/_rsp.dynamic` and concatenate in install.sh.
+
+### 6. Update `install.sh`
+
+Replace `rsp completions zsh | sudo tee ...` with:
+```bash
+(rsp completions zsh; echo; cat completions/_rsp) | sudo tee "$dest"
+```
+
+### 7. Update `TODO.md`
+
+Mark "[x] Dynamic autocompletion of artists, albums and tracks"
+
+### 8. Tests
+
+Add tests in `tests/database.rs`:
+- `test_complete_artists_prefix`
+- `test_complete_albums_prefix`
+- `test_complete_tracks_prefix`
+- `test_complete_empty_db`
+- `test_complete_no_match`
+
+Need a test helper that creates a temp DB with known data, runs queries, checks results.
+
+## Files changed
+
+| File | Change |
+|------|--------|
+| `src/database.rs` | +3 pub methods (complete_artists/albums/tracks) |
+| `src/cli.rs` | +DbComplete variant, +dispatch, +filter in Completions |
+| `completions/_rsp.dynamic` (new) | zsh dynamic completion wrapper |
+| `install.sh` | concatenate static + dynamic |
+| `tests/database.rs` | +completion query tests |
+
+## Future: fish/bash
+
+Same pattern. Fish uses `complete -c rsp -n '__fish_seen_subcommand_from play' -a '(rsp complete tracks (commandline -ct))'`. Bash uses `COMPREPLY=($(compgen -W "$(rsp complete tracks "$cur")" -- "$cur"))`. Start with zsh only (user's shell).
diff --git a/TODO.md b/TODO.md
index 9bc36c2..55f7d52 100644
--- a/TODO.md
+++ b/TODO.md
@@ -2,10 +2,8 @@
 
 - **Persist queue state** - Save queue to disk so it survives daemon restarts.
 
-- **Dynamic autocompletion of artists, albums and tracks** - Let shell communicate with the daemon so it returns autocompletions for shell
+- [x] **Dynamic autocompletion of artists, albums and tracks** - `rsp complete <entity> [prefix]` queries DB directly; zsh completion wrapper concatenated via install.sh
 
 - **Cache config in daemon** - Avoid repeated Config::load() calls in daemon hot paths; pass display_format through command chain instead
 
 - **Test last.fm scrobbling** - Current scrobbling is only tested for listenbrainz
-
-- **REPL mode** - like in bluetoothctl
diff --git a/completions/_rsp b/completions/_rsp
new file mode 100644
index 0000000..1ddc1fe
--- /dev/null
+++ b/completions/_rsp
@@ -0,0 +1,55 @@
+# Dynamic completion overrides for rsp play/queue arguments.
+
+# Save clap-generated function so we can delegate non-dynamic cases.
+functions[_rsp_clap]=$functions[_rsp]
+
+_rsp() {
+    case $words[2] in
+        play|queue)
+            _rsp_complete_args
+            ;;
+        *)
+            _rsp_clap "$@"
+            ;;
+    esac
+}
+
+_rsp_complete_args() {
+    setopt localoptions
+    setopt nobanghist
+    # Reconstruct full query from all argument words after subcommand.
+    # This handles spaces in names (e.g. "Master of Puppets") that get
+    # split across multiple $words entries when backslash escaping breaks.
+    local -a args=("${(@)words[3,$CURRENT]}")
+    local cur="${(j: :)args}"
+    local -a suggestions
+    local n_slashes=${#cur//[^\/]/}
+    case $n_slashes in
+        0) suggestions=(${(f)"$(rsp db-complete artists "$cur" 2>/dev/null)"}) ;;
+        1) suggestions=(${(f)"$(rsp db-complete albums "$cur" 2>/dev/null)"}) ;;
+        *) suggestions=(${(f)"$(rsp db-complete tracks "$cur" 2>/dev/null)"}) ;;
+    esac
+    # display: what the user sees in the completion menu (raw values)
+    local -a display=("${suggestions[@]}")
+    # vals: what gets inserted (with ZSH backslash escaping + ! escape)
+    local -a vals=()
+    local slash_idx=$cur[(I)/]
+    local s
+    for s in "${suggestions[@]}"; do
+        local esc="${(q)s:gs/\!/\\\!/}"
+        if (( slash_idx )); then
+            local dir_part=$cur[1,$slash_idx]
+            local edir="${(q)dir_part:gs/\!/\\\!/}"
+            vals+=("${edir}${esc}")
+        else
+            vals+=("${esc}")
+        fi
+    done
+    if (( ${#suggestions} )); then
+        if [[ ${vals[1]} == */ ]]; then
+            compadd -U -S '' -d display -- "${vals[@]}"
+        else
+            compadd -U -d display -- "${vals[@]}"
+        fi
+    fi
+}
diff --git a/install.sh b/install.sh
index 11af71d..280be8a 100755
--- a/install.sh
+++ b/install.sh
@@ -46,7 +46,7 @@ case "$shell_name" in
         ;;
     zsh)
         dest="/usr/share/zsh/site-functions/_rsp"
-        /usr/bin/rsp completions zsh | sudo tee "$dest" > /dev/null
+        (/usr/bin/rsp completions zsh; echo; cat completions/_rsp) | sudo tee "$dest" > /dev/null
         echo "  → Zsh completions: $dest"
         ;;
     fish)
diff --git a/src/cli.rs b/src/cli.rs
index f5fdd2c..5a53eeb 100644
--- a/src/cli.rs
+++ b/src/cli.rs
@@ -92,6 +92,16 @@ pub enum Commands {
         #[arg(value_name = "SHELL")]
         shell: Option<String>,
     },
+    #[clap(hide = true)]
+    /// Complete artists, albums, or tracks from database
+    DbComplete {
+        /// Entity type: artists, albums, or tracks
+        #[arg(value_name = "ENTITY")]
+        entity: String,
+        /// Optional prefix to match
+        #[arg(value_name = "PREFIX")]
+        prefix: Option<String>,
+    },
 }
 
 #[derive(Subcommand)]
@@ -235,11 +245,35 @@ impl Cli {
                     }
                 }
             }
+            Commands::DbComplete { entity, prefix } => {
+                let config = match Config::load() {
+                    Ok(c) => c,
+                    Err(_) => return Ok(()),
+                };
+                let db = match Database::open(&config) {
+                    Ok(d) => d,
+                    Err(_) => return Ok(()),
+                };
+                let prefix = prefix.as_deref().unwrap_or("");
+                let results: Vec<String> = match entity.as_str() {
+                    "artists" => db.complete_artists(prefix).unwrap_or_default(),
+                    "albums" => db.complete_albums(prefix).unwrap_or_default(),
+                    "tracks" => db.complete_tracks(prefix).unwrap_or_default(),
+                    _ => Vec::new(),
+                };
+                let suffix = match entity.as_str() {
+                    "artists" | "albums" => "/",
+                    _ => "",
+                };
+                for r in &results {
+                    println!("{r}{suffix}");
+                }
+            }
             Commands::Completions { shell } => {
                 let full = Cli::command();
                 let subcommands: Vec<Command> = full
                     .get_subcommands()
-                    .filter(|s| s.get_name() != "completions")
+                    .filter(|s| s.get_name() != "completions" && s.get_name() != "db-complete")
                     .cloned()
                     .collect();
                 let cmd_name = full.get_name().to_string();
diff --git a/src/daemon.rs b/src/daemon.rs
index c794495..d34305a 100644
--- a/src/daemon.rs
+++ b/src/daemon.rs
@@ -747,30 +747,40 @@ fn process_command(
                 } else {
                     match Config::load() {
                         Ok(config) => match crate::database::Database::open(&config) {
-                            Ok(db) => match db.search_tracks(query_str) {
-                                Ok(tracks) if !tracks.is_empty() => {
-                                    let db_track = &tracks[0];
-                                    let album_tracks = db
-                                        .get_album_tracks_after(
-                                            db_track.album_id,
-                                            db_track.track_number,
-                                            &db_track.path,
-                                        )
-                                        .unwrap_or_default();
-                                    let display = format!(
-                                        "{} - {}",
-                                        db_track.artist_name.as_deref().unwrap_or("unknown"),
-                                        db_track.title,
-                                    );
-                                    let paths: Vec<PathBuf> =
-                                        album_tracks.into_iter().map(|t| t.path).collect();
-                                    (db_track.path.clone(), paths, display)
-                                }
-                                Ok(_) => {
+                            Ok(db) => {
+                                let tracks = match db.resolve_path(query_str) {
+                                    Ok(Some(t)) => t,
+                                    Ok(None) => match db.search_tracks(query_str) {
+                                        Ok(t) => t,
+                                        Err(e) => return format!("Database error: {}\n", e),
+                                    },
+                                    Err(e) => return format!("Database error: {}\n", e),
+                                };
+                                if tracks.is_empty() {
                                     return format!("No tracks found matching '{}'\n", query_str);
                                 }
-                                Err(e) => return format!("Database error: {}\n", e),
-                            },
+                                let db_track = &tracks[0];
+                                let album_paths: Vec<PathBuf> = if tracks.len() > 1 {
+                                    // resolve_path returned full album — use remaining tracks
+                                    tracks[1..].iter().map(|t| t.path.clone()).collect()
+                                } else {
+                                    db.get_album_tracks_after(
+                                        db_track.album_id,
+                                        db_track.track_number,
+                                        &db_track.path,
+                                    )
+                                    .unwrap_or_default()
+                                    .into_iter()
+                                    .map(|t| t.path)
+                                    .collect()
+                                };
+                                let display = format!(
+                                    "{} - {}",
+                                    db_track.artist_name.as_deref().unwrap_or("unknown"),
+                                    db_track.title,
+                                );
+                                (db_track.path.clone(), album_paths, display)
+                            }
                             Err(e) => return format!("Database error: {}\n", e),
                         },
                         Err(e) => return format!("Config error: {}\n", e),
@@ -862,6 +872,8 @@ fn process_command(
             } else {
                 path_str.to_string()
             };
+            // Strip trailing slash so autocompleted Artist/Album/ resolves cleanly
+            let path_str = path_str.trim_end_matches('/').to_string();
 
             if path_str.is_empty() {
                 return "Usage: play [now|next|last] <path>\n".to_string();
@@ -1052,7 +1064,132 @@ fn process_command(
                         _ => format!("Error: No audio files found in '{}'\n", path_str),
                     }
                 } else {
-                    format!("File not found: {}\n", path_str)
+                    match Config::load() {
+                        Ok(config) => match crate::database::Database::open(&config) {
+                            Ok(db) => {
+                                // Collect tracks: try hierarchical path first, fallback to search
+                                let tracks = match db.resolve_path(&path_str) {
+                                    Ok(Some(t)) => t,
+                                    Ok(None) => {
+                                        let query = path_str
+                                            .rsplit_once('/')
+                                            .and_then(|(_, after)| {
+                                                (!after.is_empty()).then_some(after)
+                                            })
+                                            .unwrap_or(&path_str);
+                                        match db.search_tracks(query) {
+                                            Ok(t) => t,
+                                            Err(e) => return format!("Database error: {}\n", e),
+                                        }
+                                    }
+                                    Err(e) => return format!("Database error: {}\n", e),
+                                };
+
+                                if tracks.is_empty() {
+                                    return format!("No tracks found matching '{}'\n", path_str);
+                                }
+
+                                let is_album_play = tracks.len() > 1;
+                                if let Ok(mut q) = queue.lock() {
+                                    let was_empty = q.is_empty();
+                                    match position {
+                                        "now" => {
+                                            if let Ok(mut s) = scrobble_state.lock() {
+                                                try_scrobble_current(scrobbler, &mut s);
+                                            }
+                                            for t in tracks.iter().rev() {
+                                                q.insert_after_current(t.path.clone());
+                                            }
+                                            if !was_empty {
+                                                let new_pos = q.position() + 1;
+                                                q.set_position(new_pos);
+                                            }
+                                            if let Some(track) = q.track_at(q.position()) {
+                                                audio.play(track.path.clone());
+                                                send_now_playing(scrobbler, track);
+                                                if let Ok(mut s) = scrobble_state.lock() {
+                                                    s.start(track.clone());
+                                                }
+                                                let fmt = Config::load()
+                                                    .ok()
+                                                    .and_then(|c| c.display_format.clone());
+                                                notification::notify_track(track, fmt.as_deref());
+                                            }
+                                            if is_album_play {
+                                                format!(
+                                                    "Playing now: {} tracks from {}\n",
+                                                    tracks.len(),
+                                                    path_str
+                                                )
+                                            } else {
+                                                format!("Playing now: {}\n", path_str)
+                                            }
+                                        }
+                                        "next" => {
+                                            for t in tracks.iter().rev() {
+                                                q.insert_after_current(t.path.clone());
+                                            }
+                                            if is_album_play {
+                                                format!(
+                                                    "Queued next: {} tracks from {}\n",
+                                                    tracks.len(),
+                                                    path_str
+                                                )
+                                            } else {
+                                                format!("Queued next: {}\n", path_str)
+                                            }
+                                        }
+                                        _ => {
+                                            let was_playing = audio.is_playing();
+                                            for t in &tracks {
+                                                q.add_track(t.path.clone());
+                                            }
+                                            if was_empty && !was_playing {
+                                                if is_album_play {
+                                                    q.shuffle_initial();
+                                                }
+                                                if let Some(track) = q.current() {
+                                                    audio.play(track.path.clone());
+                                                    send_now_playing(scrobbler, track);
+                                                    if let Ok(mut s) = scrobble_state.lock() {
+                                                        s.start(track.clone());
+                                                    }
+                                                    let fmt = Config::load()
+                                                        .ok()
+                                                        .and_then(|c| c.display_format.clone());
+                                                    notification::notify_track(
+                                                        track,
+                                                        fmt.as_deref(),
+                                                    );
+                                                }
+                                                if is_album_play {
+                                                    format!(
+                                                        "Playing: {} tracks from {}\n",
+                                                        tracks.len(),
+                                                        path_str
+                                                    )
+                                                } else {
+                                                    format!("Playing: {}\n", path_str)
+                                                }
+                                            } else if is_album_play {
+                                                format!(
+                                                    "Queued {} tracks from: {}\n",
+                                                    tracks.len(),
+                                                    path_str
+                                                )
+                                            } else {
+                                                format!("Queued: {}\n", path_str)
+                                            }
+                                        }
+                                    }
+                                } else {
+                                    "Error accessing queue\n".to_string()
+                                }
+                            }
+                            Err(e) => format!("Database error: {}\n", e),
+                        },
+                        Err(e) => format!("Config error: {}\n", e),
+                    }
                 }
             }
         }
diff --git a/src/database.rs b/src/database.rs
index b80ad4a..00d970a 100644
--- a/src/database.rs
+++ b/src/database.rs
@@ -45,6 +45,10 @@ pub struct Database {
 
 #[allow(dead_code)]
 impl Database {
+    pub fn conn(&self) -> &Connection {
+        &self.conn
+    }
+
     pub fn open(_config: &crate::config::Config) -> SqlResult<Self> {
         let proj_dirs = ProjectDirs::from("com", "rsplayer", "rsplayer").ok_or_else(|| {
             rusqlite::Error::InvalidPath("Failed to get project directories".into())
@@ -269,6 +273,138 @@ 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_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()
+    }
+
+    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 (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()
+    }
+
+    #[allow(dead_code)]
+    pub fn open_in_memory() -> SqlResult<Database> {
+        let conn = Connection::open_in_memory()?;
+        let db = Database { conn };
+        db.init_schema()?;
+        Ok(db)
+    }
+
+    /// Find all tracks in an album by exact artist name and album title.
+    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
+             ORDER BY t.track_number",
+        )?;
+        let rows = stmt.query_map(rusqlite::params![artist, album], |row| {
+            Ok(Track {
+                id: row.get(0)?,
+                album_id: row.get(1)?,
+                title: row.get(2)?,
+                path: PathBuf::from(row.get::<_, String>(3)?),
+                duration: row.get(4)?,
+                track_number: row.get(5)?,
+                artist_name: Some(row.get(6)?),
+                album_title: Some(row.get(7)?),
+            })
+        })?;
+        rows.collect()
+    }
+
+    /// Find a single track by exact artist, exact album, and track title prefix.
+    /// Returns None if no match found.
+    pub fn find_track_by_parts(
+        &self,
+        artist: &str,
+        album: &str,
+        track_prefix: &str,
+    ) -> SqlResult<Option<Track>> {
+        let pattern = format!("{}%", track_prefix);
+        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 AND t.title LIKE ?3
+             ORDER BY t.track_number LIMIT 1",
+        )?;
+        let mut rows = stmt.query_map(rusqlite::params![artist, album, pattern], |row| {
+            Ok(Track {
+                id: row.get(0)?,
+                album_id: row.get(1)?,
+                title: row.get(2)?,
+                path: PathBuf::from(row.get::<_, String>(3)?),
+                duration: row.get(4)?,
+                track_number: row.get(5)?,
+                artist_name: Some(row.get(6)?),
+                album_title: Some(row.get(7)?),
+            })
+        })?;
+        match rows.next() {
+            Some(Ok(t)) => Ok(Some(t)),
+            Some(Err(e)) => Err(e),
+            None => Ok(None),
+        }
+    }
+
+    /// Resolve a hierarchical path like "Artist/Album" or "Artist/Album/Track"
+    /// to DB tracks. Returns None if the path isn't hierarchical (single part
+    /// or 4+ parts) — the caller should fall back to search_tracks().
+    pub fn resolve_path(&self, path: &str) -> SqlResult<Option<Vec<Track>>> {
+        let path = path.strip_suffix('/').unwrap_or(path);
+        let parts: Vec<&str> = path.splitn(3, '/').collect();
+        match parts.len() {
+            2 => Ok(Some(self.find_tracks_by_album(parts[0], parts[1])?)),
+            3 => match self.find_track_by_parts(parts[0], parts[1], parts[2])? {
+                Some(t) => Ok(Some(vec![t])),
+                None => Ok(Some(vec![])),
+            },
+            _ => Ok(None),
+        }
+    }
+
     pub fn search_tracks(&self, query: &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
diff --git a/tests/database.rs b/tests/database.rs
index 6cd870e..adbf535 100644
--- a/tests/database.rs
+++ b/tests/database.rs
@@ -5,7 +5,7 @@
  * https://codeberg.org/salmonik/RSPlayer
 */
 
-use rsp::database::{extract_metadata, is_audio_file};
+use rsp::database::{Database, extract_metadata, is_audio_file};
 use std::path::PathBuf;
 
 #[test]
@@ -90,3 +90,394 @@ fn test_extract_metadata_no_parent() {
     assert_eq!(album, "Unknown");
     assert_eq!(title, "song");
 }
+
+fn seed_test_db(db: &Database) {
+    let conn: &rusqlite::Connection = db.conn();
+    conn.execute_batch(
+        "INSERT INTO artists (id, name) VALUES (1, 'Metallica'), (2, 'Miles Davis'), (3, 'Aphex Twin');
+         INSERT INTO albums (id, artist_id, title) VALUES
+             (1, 1, 'Master of Puppets'),
+             (2, 1, 'Ride the Lightning'),
+             (3, 2, 'Kind of Blue'),
+             (4, 3, 'Selected Ambient Works');
+         INSERT INTO tracks (id, album_id, title, path, duration, track_number) VALUES
+             (1, 1, 'Battery', '/m/Metallica/Master of Puppets/01-Battery.mp3', 313, 1),
+             (2, 1, 'Master of Puppets', '/m/Metallica/Master of Puppets/02-Master of Puppets.mp3', 515, 2),
+             (3, 2, 'Fight Fire with Fire', '/m/Metallica/Ride the Lightning/01-Fight Fire with Fire.mp3', 285, 1),
+             (4, 3, 'So What', '/m/Miles Davis/Kind of Blue/01-So What.mp3', 562, 1),
+             (5, 4, 'Xtal', '/m/Aphex Twin/Selected Ambient Works/01-Xtal.mp3', 437, 1);",
+    )
+    .unwrap();
+}
+
+#[test]
+fn test_complete_artists_prefix() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_artists("M").unwrap();
+    assert_eq!(results.len(), 2);
+    assert!(results.contains(&"Metallica".to_string()));
+    assert!(results.contains(&"Miles Davis".to_string()));
+}
+
+#[test]
+fn test_complete_artists_prefix_lowercase() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_artists("m").unwrap();
+    assert!(
+        results.contains(&"Metallica".to_string()),
+        "LIKE should be case-insensitive"
+    );
+}
+
+#[test]
+fn test_complete_artists_no_match() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_artists("Z").unwrap();
+    assert!(results.is_empty());
+}
+
+#[test]
+fn test_complete_artists_empty_prefix() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_artists("").unwrap();
+    assert_eq!(results.len(), 3);
+}
+
+#[test]
+fn test_complete_albums_with_artist_prefix() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_albums("Metallica/").unwrap();
+    assert_eq!(results.len(), 2);
+    assert!(results.contains(&"Master of Puppets".to_string()));
+    assert!(results.contains(&"Ride the Lightning".to_string()));
+}
+
+#[test]
+fn test_complete_albums_with_album_prefix() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_albums("Metallica/R").unwrap();
+    assert_eq!(results.len(), 1);
+    assert_eq!(results[0], "Ride the Lightning");
+}
+
+#[test]
+fn test_complete_albums_unknown_artist() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_albums("Unknown/").unwrap();
+    assert!(results.is_empty());
+}
+
+#[test]
+fn test_complete_albums_no_slash() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_albums("Metallica").unwrap();
+    assert!(results.is_empty(), "needs trailing slash");
+}
+
+#[test]
+fn test_complete_tracks_full_path() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_tracks("Metallica/Master of Puppets/").unwrap();
+    assert_eq!(results.len(), 2);
+    assert!(results.contains(&"Battery".to_string()));
+    assert!(results.contains(&"Master of Puppets".to_string()));
+}
+
+#[test]
+fn test_complete_tracks_with_prefix() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_tracks("Metallica/Master of Puppets/B").unwrap();
+    assert_eq!(results.len(), 1);
+    assert_eq!(results[0], "Battery");
+}
+
+#[test]
+fn test_complete_tracks_unknown_album() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_tracks("Metallica/Nonexistent/").unwrap();
+    assert!(results.is_empty());
+}
+
+#[test]
+fn test_complete_tracks_too_few_parts() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let results = db.complete_tracks("Metallica").unwrap();
+    assert!(results.is_empty());
+}
+
+#[test]
+fn test_complete_empty_db() {
+    let db = Database::open_in_memory().unwrap();
+    assert!(db.complete_artists("").unwrap().is_empty());
+    assert!(db.complete_albums("x/").unwrap().is_empty());
+    assert!(db.complete_tracks("x/y/").unwrap().is_empty());
+}
+
+// --- find_tracks_by_album tests ---
+
+#[test]
+fn test_find_tracks_by_album_all() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let tracks = db
+        .find_tracks_by_album("Metallica", "Master of Puppets")
+        .unwrap();
+    assert_eq!(tracks.len(), 2);
+    assert_eq!(tracks[0].title, "Battery");
+    assert_eq!(tracks[1].title, "Master of Puppets");
+    assert_eq!(tracks[0].artist_name.as_deref(), Some("Metallica"));
+    assert_eq!(tracks[0].album_title.as_deref(), Some("Master of Puppets"));
+}
+
+#[test]
+fn test_find_tracks_by_album_single_track() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let tracks = db
+        .find_tracks_by_album("Miles Davis", "Kind of Blue")
+        .unwrap();
+    assert_eq!(tracks.len(), 1);
+    assert_eq!(tracks[0].title, "So What");
+}
+
+#[test]
+fn test_find_tracks_by_album_unknown_artist() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let tracks = db
+        .find_tracks_by_album("Nonexistent", "Master of Puppets")
+        .unwrap();
+    assert!(tracks.is_empty());
+}
+
+#[test]
+fn test_find_tracks_by_album_unknown_album() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let tracks = db.find_tracks_by_album("Metallica", "Nonexistent").unwrap();
+    assert!(tracks.is_empty());
+}
+
+#[test]
+fn test_find_tracks_by_album_case_sensitive_artist() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    // SQLite = is case-sensitive for TEXT (unless NOCASE collation)
+    let tracks = db
+        .find_tracks_by_album("metallica", "Master of Puppets")
+        .unwrap();
+    assert!(
+        tracks.is_empty(),
+        "artist name match should be case-sensitive"
+    );
+}
+
+#[test]
+fn test_find_tracks_by_album_ordered_by_track_number() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let tracks = db
+        .find_tracks_by_album("Metallica", "Master of Puppets")
+        .unwrap();
+    assert_eq!(tracks[0].track_number, Some(1));
+    assert_eq!(tracks[1].track_number, Some(2));
+}
+
+// --- find_track_by_parts tests ---
+
+#[test]
+fn test_find_track_by_parts_exact() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let track = db
+        .find_track_by_parts("Metallica", "Master of Puppets", "Battery")
+        .unwrap();
+    assert!(track.is_some());
+    let t = track.unwrap();
+    assert_eq!(t.title, "Battery");
+    assert_eq!(t.artist_name.as_deref(), Some("Metallica"));
+    assert_eq!(t.album_title.as_deref(), Some("Master of Puppets"));
+}
+
+#[test]
+fn test_find_track_by_parts_prefix() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let track = db
+        .find_track_by_parts("Metallica", "Master of Puppets", "B")
+        .unwrap();
+    assert!(track.is_some());
+    assert_eq!(track.unwrap().title, "Battery");
+}
+
+#[test]
+fn test_find_track_by_parts_no_match_track() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let track = db
+        .find_track_by_parts("Metallica", "Master of Puppets", "Z")
+        .unwrap();
+    assert!(track.is_none());
+}
+
+#[test]
+fn test_find_track_by_parts_no_match_album() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let track = db
+        .find_track_by_parts("Metallica", "Nonexistent", "B")
+        .unwrap();
+    assert!(track.is_none());
+}
+
+#[test]
+fn test_find_track_by_parts_no_match_artist() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let track = db
+        .find_track_by_parts("Nonexistent", "Master of Puppets", "B")
+        .unwrap();
+    assert!(track.is_none());
+}
+
+#[test]
+fn test_find_track_by_parts_case_sensitive() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let track = db
+        .find_track_by_parts("metallica", "Master of Puppets", "Battery")
+        .unwrap();
+    assert!(track.is_none(), "artist match should be case-sensitive");
+}
+
+// --- resolve_path tests ---
+
+#[test]
+fn test_resolve_path_two_parts_album() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let result = db.resolve_path("Metallica/Master of Puppets").unwrap();
+    assert!(result.is_some());
+    let tracks = result.unwrap();
+    assert_eq!(tracks.len(), 2);
+    assert_eq!(tracks[0].title, "Battery");
+}
+
+#[test]
+fn test_resolve_path_two_parts_trailing_slash() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let result = db.resolve_path("Metallica/Master of Puppets/").unwrap();
+    assert!(result.is_some());
+    let tracks = result.unwrap();
+    assert_eq!(tracks.len(), 2);
+}
+
+#[test]
+fn test_resolve_path_three_parts_track() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let result = db
+        .resolve_path("Metallica/Master of Puppets/Battery")
+        .unwrap();
+    assert!(result.is_some());
+    let tracks = result.unwrap();
+    assert_eq!(tracks.len(), 1);
+    assert_eq!(tracks[0].title, "Battery");
+}
+
+#[test]
+fn test_resolve_path_three_parts_track_prefix() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let result = db.resolve_path("Metallica/Master of Puppets/B").unwrap();
+    assert!(result.is_some());
+    let tracks = result.unwrap();
+    assert_eq!(tracks.len(), 1);
+    assert_eq!(tracks[0].title, "Battery");
+}
+
+#[test]
+fn test_resolve_path_single_part_returns_none() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let result = db.resolve_path("Metallica").unwrap();
+    assert!(
+        result.is_none(),
+        "single part should return None for fallback"
+    );
+}
+
+#[test]
+fn test_resolve_path_no_match_album() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let result = db.resolve_path("Metallica/Nonexistent").unwrap();
+    assert!(result.is_some());
+    assert!(result.unwrap().is_empty());
+}
+
+#[test]
+fn test_resolve_path_no_match_track() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let result = db.resolve_path("Metallica/Master of Puppets/Z").unwrap();
+    assert!(result.is_some());
+    assert!(result.unwrap().is_empty());
+}
+
+#[test]
+fn test_resolve_path_empty_db() {
+    let db = Database::open_in_memory().unwrap();
+    assert!(db.resolve_path("Artist/Album").unwrap().unwrap().is_empty());
+    assert!(
+        db.resolve_path("Artist/Album/Track")
+            .unwrap()
+            .unwrap()
+            .is_empty()
+    );
+    assert!(db.resolve_path("Artist").unwrap().is_none());
+}
+
+// --- Track conversion helper tests ---
+
+#[test]
+fn test_find_tracks_by_album_returns_full_metadata() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let tracks = db
+        .find_tracks_by_album("Metallica", "Ride the Lightning")
+        .unwrap();
+    assert_eq!(tracks.len(), 1);
+    let t = &tracks[0];
+    assert_eq!(t.artist_name.as_deref(), Some("Metallica"));
+    assert_eq!(t.album_title.as_deref(), Some("Ride the Lightning"));
+    assert_eq!(t.title, "Fight Fire with Fire");
+    assert!(t.path.to_string_lossy().contains("Ride the Lightning"));
+}
+
+#[test]
+fn test_find_track_by_parts_returns_full_metadata() {
+    let db = Database::open_in_memory().unwrap();
+    seed_test_db(&db);
+    let t = db
+        .find_track_by_parts("Aphex Twin", "Selected Ambient Works", "Xtal")
+        .unwrap()
+        .expect("track should be found");
+    assert_eq!(t.artist_name.as_deref(), Some("Aphex Twin"));
+    assert_eq!(t.album_title.as_deref(), Some("Selected Ambient Works"));
+    assert_eq!(t.title, "Xtal");
+    assert_eq!(t.track_number, Some(1));
+}