Skip to main content

furumi_library/
lib.rs

1//! The local music library: a SQLite database holding artists, releases,
2//! tracks (with paths to local audio files), playlists, likes and play
3//! history. This module is the only place that talks SQL; it returns the
4//! same data shapes the furumusic API used to serve, so the views did not
5//! have to change their model of the world.
6//!
7//! All methods take `&self` and lock the connection internally, so an
8//! `Arc<Library>` can be shared across blocking tasks.
9
10pub mod import;
11pub mod models;
12
13use std::collections::{HashMap, HashSet};
14use std::io::Write as _;
15use std::path::{Path, PathBuf};
16use std::sync::Mutex;
17use std::sync::atomic::{AtomicI64, Ordering};
18use std::time::Duration;
19
20use anyhow::{Context as _, Result};
21use rusqlite::{Connection, OptionalExtension as _, params};
22
23pub use models::{
24    ArtistCard, ArtistDetail, ArtistRef, ArtistsPage, Availability, FederatedTrack, LibraryFilters,
25    LibrarySourceMode, PlaylistCard, PlaylistDetail, ReleaseCard, ReleaseDetail, ReleaseEdit,
26    SearchResults, TrackEdit, TrackItem,
27};
28
29/// The virtual "Liked tracks" playlist id, kept from the server API.
30pub const LIKES_PLAYLIST_ID: i64 = -1;
31const NETWORK_ARTIST_CACHE_TTL_MS: i64 = 7 * 24 * 60 * 60 * 1000;
32static NEXT_PENDING_TRACK_ID: AtomicI64 = AtomicI64::new(-1);
33
34type FederatedLikeState = (
35    String,
36    String,
37    String,
38    String,
39    Option<i32>,
40    Option<i64>,
41    Option<String>,
42    Option<i32>,
43    Option<i32>,
44    Option<i64>,
45);
46
47#[derive(Debug, Clone, PartialEq, Eq)]
48pub struct ListenHistoryEntry {
49    pub listen_id: String,
50    pub content_id: String,
51    pub title: String,
52    pub artist: String,
53    pub origin_device_id: String,
54    pub started_at_ms: i64,
55}
56
57const SCHEMA: &str = "
58CREATE TABLE IF NOT EXISTS artists (
59    id          INTEGER PRIMARY KEY,
60    name        TEXT NOT NULL UNIQUE COLLATE NOCASE,
61    image_path  TEXT
62);
63CREATE TABLE IF NOT EXISTS releases (
64    id            INTEGER PRIMARY KEY,
65    title         TEXT NOT NULL,
66    release_type  TEXT NOT NULL DEFAULT 'album',
67    year          INTEGER,
68    cover_path    TEXT
69);
70CREATE TABLE IF NOT EXISTS release_artists (
71    release_id  INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
72    artist_id   INTEGER NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
73    position    INTEGER NOT NULL DEFAULT 0,
74    PRIMARY KEY (release_id, artist_id)
75);
76CREATE TABLE IF NOT EXISTS tracks (
77    id                 INTEGER PRIMARY KEY,
78    title              TEXT NOT NULL,
79    track_number       INTEGER,
80    disc_number        INTEGER,
81    duration_seconds   REAL NOT NULL DEFAULT 0,
82    release_id         INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
83    file_path          TEXT NOT NULL UNIQUE,
84    content_id         TEXT,
85    audio_format       TEXT,
86    audio_bitrate      INTEGER,
87    audio_sample_rate  INTEGER,
88    audio_bit_depth    INTEGER,
89    file_size_bytes    INTEGER,
90    created_at         TEXT NOT NULL DEFAULT (datetime('now'))
91);
92CREATE TABLE IF NOT EXISTS track_artists (
93    track_id   INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
94    artist_id  INTEGER NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
95    role       TEXT NOT NULL DEFAULT 'main',
96    position   INTEGER NOT NULL DEFAULT 0,
97    PRIMARY KEY (track_id, artist_id, role)
98);
99CREATE TABLE IF NOT EXISTS playlists (
100    id           INTEGER PRIMARY KEY,
101    sync_id      TEXT UNIQUE,
102    title        TEXT NOT NULL,
103    description  TEXT,
104    created_at   TEXT NOT NULL DEFAULT (datetime('now'))
105);
106CREATE TABLE IF NOT EXISTS playlist_tracks (
107    playlist_id  INTEGER NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
108    track_id     INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
109    position     INTEGER NOT NULL,
110    PRIMARY KEY (playlist_id, track_id)
111);
112CREATE TABLE IF NOT EXISTS likes (
113    track_id  INTEGER PRIMARY KEY REFERENCES tracks(id) ON DELETE CASCADE,
114    liked_at  TEXT NOT NULL DEFAULT (datetime('now')),
115    liked_hlc_ms INTEGER
116);
117CREATE TABLE IF NOT EXISTS fed_likes (
118    item_id          TEXT PRIMARY KEY,
119    owner            TEXT NOT NULL,
120    title            TEXT NOT NULL,
121    artist_names     TEXT NOT NULL DEFAULT '',
122    featured_artist_names TEXT NOT NULL DEFAULT '',
123    year             INTEGER,
124    duration_seconds REAL,
125    content_id       TEXT,
126    release_title    TEXT,
127    track_number     INTEGER,
128    disc_number      INTEGER,
129    liked_at         TEXT NOT NULL DEFAULT (datetime('now')),
130    liked_hlc_ms     INTEGER
131);
132CREATE TABLE IF NOT EXISTS fed_playlist_tracks (
133    playlist_sync_id TEXT NOT NULL,
134    item_id          TEXT NOT NULL,
135    owner            TEXT NOT NULL,
136    title            TEXT NOT NULL,
137    artist_names     TEXT NOT NULL DEFAULT '',
138    featured_artist_names TEXT NOT NULL DEFAULT '',
139    year             INTEGER,
140    duration_seconds REAL,
141    content_id       TEXT NOT NULL,
142    release_title    TEXT,
143    track_number     INTEGER,
144    disc_number      INTEGER,
145    position         INTEGER NOT NULL DEFAULT 0,
146    added_at         TEXT NOT NULL DEFAULT (datetime('now')),
147    PRIMARY KEY (playlist_sync_id, content_id)
148);
149CREATE TABLE IF NOT EXISTS history (
150    id                INTEGER PRIMARY KEY,
151    track_id          INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
152    started_at        INTEGER,
153    listened_seconds  INTEGER NOT NULL DEFAULT 0,
154    completed         INTEGER NOT NULL DEFAULT 0,
155    played_at         TEXT NOT NULL DEFAULT (datetime('now'))
156);
157CREATE TABLE IF NOT EXISTS listen_events (
158    listen_id          TEXT PRIMARY KEY,
159    content_id         TEXT NOT NULL,
160    local_track_id     INTEGER REFERENCES tracks(id) ON DELETE SET NULL,
161    origin_device_id   TEXT NOT NULL,
162    started_at_ms      INTEGER NOT NULL,
163    listened_ms        INTEGER NOT NULL,
164    track_duration_ms  INTEGER,
165    ended_reason       TEXT NOT NULL,
166    qualified          INTEGER NOT NULL,
167    metadata_json      TEXT NOT NULL,
168    created_at         TEXT NOT NULL DEFAULT (datetime('now'))
169);
170CREATE INDEX IF NOT EXISTS idx_tracks_release ON tracks(release_id);
171CREATE INDEX IF NOT EXISTS idx_track_artists_artist ON track_artists(artist_id);
172CREATE INDEX IF NOT EXISTS idx_release_artists_artist ON release_artists(artist_id);
173CREATE INDEX IF NOT EXISTS idx_history_track ON history(track_id);
174CREATE INDEX IF NOT EXISTS idx_listen_events_content
175    ON listen_events(content_id, started_at_ms DESC);
176CREATE INDEX IF NOT EXISTS idx_listen_events_local_track
177    ON listen_events(local_track_id, qualified);
178CREATE TRIGGER IF NOT EXISTS reconcile_listen_events_after_track_insert
179AFTER INSERT ON tracks
180WHEN NEW.content_id IS NOT NULL
181BEGIN
182    UPDATE listen_events
183       SET local_track_id = NEW.id
184     WHERE content_id = NEW.content_id;
185END;
186CREATE TRIGGER IF NOT EXISTS reconcile_listen_events_after_track_content_id
187AFTER UPDATE OF content_id ON tracks
188WHEN NEW.content_id IS NOT NULL
189BEGIN
190    UPDATE listen_events
191       SET local_track_id = NEW.id
192     WHERE content_id = NEW.content_id;
193END;
194CREATE INDEX IF NOT EXISTS idx_playlist_tracks_playlist ON playlist_tracks(playlist_id);
195CREATE INDEX IF NOT EXISTS idx_fed_playlist_tracks_playlist
196    ON fed_playlist_tracks(playlist_sync_id, position);
197CREATE TABLE IF NOT EXISTS network_artist_cache (
198    source_id      TEXT NOT NULL,
199    source_kind    TEXT NOT NULL,
200    artist_key     TEXT NOT NULL,
201    name           TEXT NOT NULL,
202    image_path     TEXT,
203    remote_image_hint TEXT,
204    release_count  INTEGER NOT NULL DEFAULT 0,
205    track_count    INTEGER NOT NULL DEFAULT 0,
206    seen_at_ms     INTEGER NOT NULL,
207    PRIMARY KEY (source_id, artist_key)
208);
209CREATE INDEX IF NOT EXISTS idx_network_artist_cache_kind
210    ON network_artist_cache(source_kind, seen_at_ms);
211CREATE INDEX IF NOT EXISTS idx_network_artist_cache_artist
212    ON network_artist_cache(artist_key);
213CREATE TABLE IF NOT EXISTS similarity_profiles (
214    profile_id       TEXT PRIMARY KEY,
215    model_id         TEXT NOT NULL,
216    model_version    TEXT NOT NULL,
217    model_sha256     TEXT NOT NULL,
218    preprocessing    TEXT NOT NULL,
219    dimensions       INTEGER NOT NULL,
220    created_at_ms    INTEGER NOT NULL
221);
222CREATE TABLE IF NOT EXISTS track_embeddings (
223    track_id          INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
224    profile_id        TEXT NOT NULL REFERENCES similarity_profiles(profile_id) ON DELETE CASCADE,
225    dimensions        INTEGER NOT NULL,
226    vector            BLOB NOT NULL,
227    routing_signature BLOB,
228    source_content_id TEXT,
229    computed_at_ms    INTEGER NOT NULL,
230    PRIMARY KEY (track_id, profile_id)
231);
232CREATE INDEX IF NOT EXISTS idx_track_embeddings_profile
233    ON track_embeddings(profile_id, track_id);
234";
235
236/// The SELECT column list every TrackItem row is built from; artist lists
237/// are attached in a second pass.
238const TRACK_COLUMNS: &str = "
239    t.id, t.title, t.track_number, t.disc_number, t.duration_seconds,
240    t.release_id, r.title, r.year, r.cover_path,
241    t.file_path, t.audio_format, t.audio_bitrate, t.audio_sample_rate,
242    t.audio_bit_depth, t.file_size_bytes,
243    t.content_id,
244    ((SELECT COUNT(*) FROM history h WHERE h.track_id = t.id AND h.completed = 1)
245     + (SELECT COUNT(*) FROM listen_events le
246        WHERE le.local_track_id = t.id AND le.qualified = 1))
247";
248
249/// Plain rows handed to the federation for publishing (see
250/// `federation_export`).
251#[derive(Debug)]
252pub struct FederationExport {
253    pub artists: Vec<(i64, String)>,
254    pub releases: Vec<ExportRelease>,
255    pub tracks: Vec<ExportTrack>,
256}
257
258#[derive(Debug, Clone, Default, PartialEq, Eq)]
259pub struct ContentIdBackfillStats {
260    pub checked: usize,
261    pub normalized: usize,
262    pub hashed: usize,
263    pub failed: usize,
264}
265
266impl ContentIdBackfillStats {
267    pub fn updated(&self) -> usize {
268        self.normalized + self.hashed
269    }
270}
271
272#[derive(Debug)]
273pub struct ExportRelease {
274    pub id: i64,
275    pub title: String,
276    pub year: Option<i32>,
277    pub release_type: String,
278    pub artist_names: Vec<String>,
279}
280
281#[derive(Debug)]
282pub struct ExportTrack {
283    pub id: i64,
284    pub title: String,
285    pub year: Option<i32>,
286    pub duration_seconds: f64,
287    pub artist_names: Vec<String>,
288    pub featured_artist_names: Vec<String>,
289    pub release_title: String,
290    pub release_type: String,
291    pub track_number: Option<i32>,
292    pub disc_number: Option<i32>,
293    pub content_id: Option<String>,
294}
295
296#[derive(Debug, Clone)]
297pub struct NetworkArtistPreview {
298    pub artist_key: String,
299    pub name: String,
300    pub image_path: Option<String>,
301    pub release_count: i64,
302    pub track_count: i64,
303}
304
305#[derive(Debug, Clone)]
306pub struct NetworkArtistImageRequest {
307    pub source_id: String,
308    pub artist_key: String,
309    pub name: String,
310}
311
312/// Minimal durable-track row used by the background embedding pipeline.
313#[derive(Debug, Clone)]
314pub struct SimilarityTrack {
315    pub id: i64,
316    pub title: String,
317    pub file_path: String,
318    pub content_id: Option<String>,
319    pub duration_seconds: f64,
320}
321
322/// One validated vector loaded from SQLite for the in-memory exact index.
323#[derive(Debug, Clone)]
324pub struct StoredEmbedding {
325    pub track_id: i64,
326    pub vector: Vec<f32>,
327    pub artist_key: String,
328}
329
330#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
331pub struct SimilarityStorageStats {
332    pub total_tracks: usize,
333    pub embedded_tracks: usize,
334    pub stored_vectors: usize,
335    pub stored_bytes: u64,
336}
337
338pub struct Library {
339    conn: Mutex<Connection>,
340    db_path: PathBuf,
341    /// Directory where extracted embedded covers are stored.
342    covers_dir: PathBuf,
343}
344
345#[derive(Debug, Clone, Default, PartialEq, Eq)]
346pub struct LocalLibraryStats {
347    pub artist_count: i64,
348    pub release_count: i64,
349    pub track_count: i64,
350    pub audio_bytes: u64,
351    pub tracks_without_size: i64,
352    pub cover_bytes: u64,
353    pub database_bytes: u64,
354}
355
356#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
357pub struct MusicRelocationStats {
358    pub tracks: usize,
359    pub images: usize,
360}
361
362impl LocalLibraryStats {
363    pub fn total_bytes(&self) -> u64 {
364        self.audio_bytes
365            .saturating_add(self.cover_bytes)
366            .saturating_add(self.database_bytes)
367    }
368}
369
370/// Default database location: `<data dir>/furumi/library.db`.
371pub fn default_db_path() -> Result<PathBuf> {
372    let dirs = directories::ProjectDirs::from("", "", "furumi")
373        .context("cannot determine the Furumi data directory")?;
374    Ok(dirs.data_dir().join("library.db"))
375}
376
377impl Library {
378    pub fn open(db_path: &Path) -> Result<Self> {
379        if let Some(parent) = db_path.parent() {
380            std::fs::create_dir_all(parent)
381                .with_context(|| format!("creating {}", parent.display()))?;
382        }
383        let conn = Connection::open(db_path)
384            .with_context(|| format!("opening database {}", db_path.display()))?;
385        conn.busy_timeout(Duration::from_secs(5))?;
386        conn.pragma_update(None, "journal_mode", "WAL")?;
387        conn.pragma_update(None, "foreign_keys", "ON")?;
388        register_norm_function(&conn)?;
389        conn.execute_batch(SCHEMA).context("applying schema")?;
390        ensure_schema_migrations(&conn)?;
391        let covers_dir = db_path
392            .parent()
393            .map(|dir| dir.join("covers"))
394            .unwrap_or_else(|| PathBuf::from("covers"));
395        Ok(Self {
396            conn: Mutex::new(conn),
397            db_path: db_path.to_path_buf(),
398            covers_dir,
399        })
400    }
401
402    pub fn covers_dir(&self) -> &Path {
403        &self.covers_dir
404    }
405
406    /// Verifies that `path` can actually be used for durable downloads. The
407    /// returned path is absolute/canonical, so the persisted setting does not
408    /// later depend on Furumi's working directory.
409    pub fn validate_music_directory(path: &Path) -> Result<PathBuf> {
410        anyhow::ensure!(!path.as_os_str().is_empty(), "music directory is empty");
411        std::fs::create_dir_all(path)
412            .with_context(|| format!("creating music directory {}", path.display()))?;
413        let path = std::fs::canonicalize(path)
414            .with_context(|| format!("resolving music directory {}", path.display()))?;
415        anyhow::ensure!(path.is_dir(), "{} is not a directory", path.display());
416
417        let unique = std::time::SystemTime::now()
418            .duration_since(std::time::UNIX_EPOCH)
419            .map(|duration| duration.as_nanos())
420            .unwrap_or(0);
421        let probe = path.join(format!(
422            ".furumi-write-test-{}-{unique}",
423            std::process::id()
424        ));
425        let result = (|| -> Result<()> {
426            let mut file = std::fs::OpenOptions::new()
427                .write(true)
428                .create_new(true)
429                .open(&probe)
430                .with_context(|| format!("no write access to {}", path.display()))?;
431            file.write_all(b"furumi")?;
432            file.sync_all()?;
433            Ok(())
434        })();
435        let _ = std::fs::remove_file(&probe);
436        result?;
437        Ok(path)
438    }
439
440    /// Copies Furumi-managed audio and its managed artwork into a portable
441    /// `Artist/Release` tree, atomically switches SQLite paths, then removes
442    /// the old copies. User-imported audio outside `old_root` is untouched.
443    pub fn relocate_managed_music(
444        &self,
445        old_root: &Path,
446        new_root: &Path,
447    ) -> Result<MusicRelocationStats> {
448        let new_root = Self::validate_music_directory(new_root)?;
449        let old_root = std::fs::canonicalize(old_root)
450            .with_context(|| format!("resolving old music directory {}", old_root.display()))?;
451        anyhow::ensure!(
452            old_root != new_root,
453            "the new music directory is the current directory"
454        );
455        anyhow::ensure!(
456            !old_root.starts_with(&new_root) && !new_root.starts_with(&old_root),
457            "choose a directory outside the current music directory"
458        );
459
460        #[derive(Debug)]
461        struct Row {
462            track_id: i64,
463            track_path: String,
464            release_id: i64,
465            release_title: String,
466            cover_path: Option<String>,
467            artist_id: Option<i64>,
468            artist_name: String,
469            image_path: Option<String>,
470        }
471
472        let rows = {
473            let conn = self.lock();
474            let mut statement = conn.prepare(
475                "SELECT t.id, t.file_path, r.id, r.title, r.cover_path,
476                        a.id, COALESCE(a.name, 'Unknown Artist'), a.image_path
477                 FROM tracks t
478                 JOIN releases r ON r.id = t.release_id
479                 LEFT JOIN track_artists ta
480                   ON ta.track_id = t.id AND ta.role = 'main' AND ta.position = 0
481                 LEFT JOIN artists a ON a.id = ta.artist_id
482                 ORDER BY t.id",
483            )?;
484            statement
485                .query_map([], |row| {
486                    Ok(Row {
487                        track_id: row.get(0)?,
488                        track_path: row.get(1)?,
489                        release_id: row.get(2)?,
490                        release_title: row.get(3)?,
491                        cover_path: row.get(4)?,
492                        artist_id: row.get(5)?,
493                        artist_name: row.get(6)?,
494                        image_path: row.get(7)?,
495                    })
496                })?
497                .collect::<rusqlite::Result<Vec<_>>>()?
498        };
499
500        #[derive(Debug, Clone)]
501        struct CopyOp {
502            source: PathBuf,
503            destination: PathBuf,
504        }
505        let covers_root =
506            std::fs::canonicalize(&self.covers_dir).unwrap_or_else(|_| self.covers_dir.clone());
507        let managed_art =
508            |path: &Path| path.starts_with(&old_root) || path.starts_with(&covers_root);
509        let mut reserved = HashSet::<PathBuf>::new();
510        let mut copies = Vec::<CopyOp>::new();
511        let mut track_updates = Vec::<(i64, String, String)>::new();
512        let mut release_updates = HashMap::<i64, (String, String)>::new();
513        let mut artist_updates = HashMap::<i64, (String, String)>::new();
514
515        for row in rows {
516            let source = PathBuf::from(&row.track_path);
517            let source = std::fs::canonicalize(&source).unwrap_or(source);
518            if !source.is_file() || !source.starts_with(&old_root) {
519                continue;
520            }
521            let artist_dir = new_root.join(storage_name(&row.artist_name, "Unknown Artist"));
522            let release_dir = artist_dir.join(storage_name(&row.release_title, "Unknown Release"));
523            let filename = source
524                .file_name()
525                .filter(|name| !name.is_empty())
526                .unwrap_or_else(|| std::ffi::OsStr::new("track"));
527            let destination =
528                unique_destination(release_dir.join(filename), row.track_id, &mut reserved);
529            copies.push(CopyOp {
530                source: source.clone(),
531                destination: destination.clone(),
532            });
533            track_updates.push((
534                row.track_id,
535                row.track_path,
536                destination.to_string_lossy().into_owned(),
537            ));
538
539            if let Some(cover) = row.cover_path {
540                let cover_source = PathBuf::from(&cover);
541                let cover_source = std::fs::canonicalize(&cover_source).unwrap_or(cover_source);
542                if cover_source.is_file()
543                    && managed_art(&cover_source)
544                    && !release_updates.contains_key(&row.release_id)
545                {
546                    let extension = cover_source.extension().unwrap_or_default();
547                    let mut destination = release_dir.join("cover");
548                    destination.set_extension(extension);
549                    let destination =
550                        unique_destination(destination, row.release_id, &mut reserved);
551                    copies.push(CopyOp {
552                        source: cover_source,
553                        destination: destination.clone(),
554                    });
555                    release_updates.insert(
556                        row.release_id,
557                        (cover, destination.to_string_lossy().into_owned()),
558                    );
559                }
560            }
561
562            if let (Some(artist_id), Some(image)) = (row.artist_id, row.image_path) {
563                let image_source = PathBuf::from(&image);
564                let image_source = std::fs::canonicalize(&image_source).unwrap_or(image_source);
565                if image_source.is_file()
566                    && managed_art(&image_source)
567                    && !artist_updates.contains_key(&artist_id)
568                {
569                    let extension = image_source.extension().unwrap_or_default();
570                    let mut destination = artist_dir.join("artist");
571                    destination.set_extension(extension);
572                    let destination = unique_destination(destination, artist_id, &mut reserved);
573                    copies.push(CopyOp {
574                        source: image_source,
575                        destination: destination.clone(),
576                    });
577                    artist_updates.insert(
578                        artist_id,
579                        (image, destination.to_string_lossy().into_owned()),
580                    );
581                }
582            }
583        }
584
585        let mut created = Vec::<PathBuf>::new();
586        let copy_result = (|| -> Result<()> {
587            for op in &copies {
588                let parent = op
589                    .destination
590                    .parent()
591                    .context("music destination has no parent")?;
592                std::fs::create_dir_all(parent)?;
593                copy_file_exclusive(&op.source, &op.destination)?;
594                created.push(op.destination.clone());
595            }
596            Ok(())
597        })();
598        if let Err(error) = copy_result {
599            for path in created.iter().rev() {
600                let _ = std::fs::remove_file(path);
601            }
602            return Err(error.context("copying the existing music library"));
603        }
604
605        let database_result = (|| -> Result<()> {
606            let mut conn = self.lock();
607            let transaction = conn.transaction()?;
608            for (id, old, new) in &track_updates {
609                anyhow::ensure!(
610                    transaction.execute(
611                        "UPDATE tracks SET file_path = ?3 WHERE id = ?1 AND file_path = ?2",
612                        params![id, old, new],
613                    )? == 1,
614                    "track {id} changed while the music library was moving"
615                );
616            }
617            for (id, (old, new)) in &release_updates {
618                anyhow::ensure!(
619                    transaction.execute(
620                        "UPDATE releases SET cover_path = ?3 WHERE id = ?1 AND cover_path = ?2",
621                        params![id, old, new],
622                    )? == 1,
623                    "release {id} changed while the music library was moving"
624                );
625            }
626            for (id, (old, new)) in &artist_updates {
627                anyhow::ensure!(
628                    transaction.execute(
629                        "UPDATE artists SET image_path = ?3 WHERE id = ?1 AND image_path = ?2",
630                        params![id, old, new],
631                    )? == 1,
632                    "artist {id} changed while the music library was moving"
633                );
634            }
635            transaction.commit()?;
636            Ok(())
637        })();
638        if let Err(error) = database_result {
639            for path in created.iter().rev() {
640                let _ = std::fs::remove_file(path);
641            }
642            return Err(error.context("updating music paths in the library"));
643        }
644
645        let mut removed = HashSet::new();
646        for op in &copies {
647            if removed.insert(op.source.clone())
648                && let Err(error) = std::fs::remove_file(&op.source)
649            {
650                // The committed destination is authoritative. A failed old
651                // delete only leaves a recoverable duplicate.
652                tracing::warn!(path = %op.source.display(), %error, "old music copy was not removed");
653            }
654        }
655        remove_empty_directories(&old_root);
656
657        Ok(MusicRelocationStats {
658            tracks: track_updates.len(),
659            images: release_updates.len() + artist_updates.len(),
660        })
661    }
662
663    pub fn local_stats(&self) -> Result<LocalLibraryStats> {
664        let (artist_count, release_count, track_count, audio_bytes, tracks_without_size) = {
665            let conn = self.lock();
666            conn.query_row(
667                "SELECT
668                    (SELECT COUNT(*) FROM artists),
669                    (SELECT COUNT(*) FROM releases),
670                    (SELECT COUNT(*) FROM tracks),
671                    (SELECT COALESCE(SUM(CASE
672                        WHEN file_size_bytes IS NOT NULL AND file_size_bytes >= 0
673                        THEN file_size_bytes ELSE 0 END), 0) FROM tracks),
674                    (SELECT COUNT(*) FROM tracks WHERE file_size_bytes IS NULL)
675                ",
676                [],
677                |row| {
678                    Ok((
679                        row.get::<_, i64>(0)?,
680                        row.get::<_, i64>(1)?,
681                        row.get::<_, i64>(2)?,
682                        row.get::<_, i64>(3)?,
683                        row.get::<_, i64>(4)?,
684                    ))
685                },
686            )?
687        };
688        Ok(LocalLibraryStats {
689            artist_count,
690            release_count,
691            track_count,
692            audio_bytes: audio_bytes.max(0) as u64,
693            tracks_without_size,
694            cover_bytes: directory_size(&self.covers_dir),
695            database_bytes: sqlite_database_size(&self.db_path),
696        })
697    }
698
699    /// Make `content_id` a local-library invariant.
700    ///
701    /// Old databases can have NULL/invalid ids because the column was added
702    /// after import already existed. This scans rows cheaply, hashes only the
703    /// tracks that actually need an id, and never holds the SQLite lock while
704    /// reading audio files from disk.
705    pub fn backfill_missing_content_ids(&self) -> Result<ContentIdBackfillStats> {
706        let rows = {
707            let conn = self.lock();
708            let mut statement =
709                conn.prepare("SELECT id, content_id, file_path FROM tracks ORDER BY id")?;
710            statement
711                .query_map([], |row| {
712                    Ok((
713                        row.get::<_, i64>(0)?,
714                        row.get::<_, Option<String>>(1)?,
715                        row.get::<_, String>(2)?,
716                    ))
717                })?
718                .collect::<rusqlite::Result<Vec<_>>>()?
719        };
720
721        let mut stats = ContentIdBackfillStats {
722            checked: rows.len(),
723            ..ContentIdBackfillStats::default()
724        };
725        let mut updates = Vec::new();
726        for (track_id, raw_content_id, file_path) in rows {
727            if let Some(normalized) = raw_content_id
728                .as_deref()
729                .and_then(music_dht::normalize_content_id)
730            {
731                if raw_content_id.as_deref() != Some(normalized.as_str()) {
732                    updates.push((track_id, normalized));
733                    stats.normalized += 1;
734                }
735            } else if let Some(content_id) = audio_content_id(&file_path) {
736                updates.push((track_id, content_id));
737                stats.hashed += 1;
738            } else {
739                stats.failed += 1;
740                tracing::warn!(
741                    track_id,
742                    path = %file_path,
743                    "content id backfill skipped an unreadable track"
744                );
745            }
746
747            if updates.len() >= 64 {
748                self.write_content_id_updates(&mut updates)?;
749            }
750        }
751        self.write_content_id_updates(&mut updates)?;
752        Ok(stats)
753    }
754
755    fn write_content_id_updates(&self, updates: &mut Vec<(i64, String)>) -> Result<()> {
756        if updates.is_empty() {
757            return Ok(());
758        }
759        let mut conn = self.lock();
760        let tx = conn.transaction()?;
761        for (track_id, content_id) in updates.drain(..) {
762            tx.execute(
763                "UPDATE tracks SET content_id = ?2 WHERE id = ?1",
764                params![track_id, content_id],
765            )?;
766        }
767        tx.commit()?;
768        Ok(())
769    }
770
771    fn lock(&self) -> std::sync::MutexGuard<'_, Connection> {
772        self.conn.lock().unwrap_or_else(|poisoned| {
773            // A panic mid-query leaves the connection usable; keep going.
774            poisoned.into_inner()
775        })
776    }
777
778    // -----------------------------------------------------------------
779    // Reads (same shapes the API used to return)
780    // -----------------------------------------------------------------
781
782    pub fn artists(&self, page: i64, limit: i64, filters: LibraryFilters) -> Result<ArtistsPage> {
783        if !filters.source_mode.includes_network() {
784            return self.local_artists(page, limit, filters.hide_featured_only);
785        }
786        self.merged_artists(page, limit, filters)
787    }
788
789    fn local_artists(
790        &self,
791        page: i64,
792        limit: i64,
793        hide_featured_only: bool,
794    ) -> Result<ArtistsPage> {
795        let conn = self.lock();
796        let hide_featured_only = i64::from(hide_featured_only);
797        let total: i64 = conn.query_row(
798            "SELECT COUNT(*)
799             FROM artists a
800             WHERE ?1 = 0
801                OR EXISTS (
802                    SELECT 1
803                    FROM release_artists ra
804                    WHERE ra.artist_id = a.id
805                )",
806            [hide_featured_only],
807            |row| row.get(0),
808        )?;
809        let offset = (page.max(1) - 1) * limit;
810        let mut statement = conn.prepare(
811            "SELECT a.id, a.name, a.image_path,
812                (SELECT COUNT(DISTINCT ra.release_id)
813                 FROM release_artists ra
814                 WHERE ra.artist_id = a.id) AS release_count,
815                (SELECT COUNT(DISTINCT ta.track_id)
816                 FROM track_artists ta
817                 WHERE ta.artist_id = a.id) AS track_count
818             FROM artists a
819             WHERE ?1 = 0
820                OR EXISTS (
821                    SELECT 1
822                    FROM release_artists ra
823                    WHERE ra.artist_id = a.id
824                )
825             ORDER BY release_count DESC, track_count DESC, a.name COLLATE NOCASE
826             LIMIT ?2 OFFSET ?3",
827        )?;
828        let items = statement
829            .query_map(params![hide_featured_only, limit, offset], |row| {
830                Ok(ArtistCard {
831                    id: row.get(0)?,
832                    name: row.get(1)?,
833                    image_path: row.get(2)?,
834                    release_count: row.get(3)?,
835                    track_count: row.get(4)?,
836                    availability: Availability::Local,
837                })
838            })?
839            .collect::<rusqlite::Result<Vec<_>>>()?;
840        let has_more = offset + (items.len() as i64) < total;
841        Ok(ArtistsPage {
842            has_more,
843            items,
844            total,
845            page: page.max(1),
846        })
847    }
848
849    fn local_artist_cards(&self, hide_featured_only: bool) -> Result<Vec<ArtistCard>> {
850        let conn = self.lock();
851        let hide_featured_only = i64::from(hide_featured_only);
852        let mut statement = conn.prepare(
853            "SELECT a.id, a.name, a.image_path,
854                (SELECT COUNT(DISTINCT ra.release_id)
855                 FROM release_artists ra
856                 WHERE ra.artist_id = a.id) AS release_count,
857                (SELECT COUNT(DISTINCT ta.track_id)
858                 FROM track_artists ta
859                 WHERE ta.artist_id = a.id) AS track_count
860             FROM artists a
861             WHERE ?1 = 0
862                OR EXISTS (
863                    SELECT 1
864                    FROM release_artists ra
865                    WHERE ra.artist_id = a.id
866                )",
867        )?;
868        statement
869            .query_map(params![hide_featured_only], |row| {
870                Ok(ArtistCard {
871                    id: row.get(0)?,
872                    name: row.get(1)?,
873                    image_path: row.get(2)?,
874                    release_count: row.get(3)?,
875                    track_count: row.get(4)?,
876                    availability: Availability::Local,
877                })
878            })?
879            .collect::<rusqlite::Result<Vec<_>>>()
880            .map_err(Into::into)
881    }
882
883    fn merged_artists(
884        &self,
885        page: i64,
886        limit: i64,
887        filters: LibraryFilters,
888    ) -> Result<ArtistsPage> {
889        let mut by_key: HashMap<String, ArtistCard> = HashMap::new();
890        for artist in self.local_artist_cards(filters.hide_featured_only)? {
891            let key = music_dht::normalize_name(&artist.name);
892            if !key.is_empty() {
893                by_key.insert(key, artist);
894            }
895        }
896
897        let conn = self.lock();
898        let cutoff = now_ms_i64().saturating_sub(NETWORK_ARTIST_CACHE_TTL_MS);
899        let source_predicate = if filters.source_mode.includes_global_peers() {
900            "seen_at_ms >= ?1"
901        } else {
902            "seen_at_ms >= ?1 AND source_kind = 'personal'"
903        };
904        let release_predicate = if filters.hide_featured_only {
905            " AND release_count > 0"
906        } else {
907            ""
908        };
909        let sql = format!(
910            "SELECT artist_key,
911                    COALESCE(NULLIF(MIN(name), ''), artist_key) AS name,
912                    MAX(image_path) AS image_path,
913                    MAX(release_count) AS release_count,
914                    MAX(track_count) AS track_count
915             FROM network_artist_cache
916             WHERE {source_predicate}{release_predicate}
917             GROUP BY artist_key"
918        );
919        let mut statement = conn.prepare(&sql)?;
920        let rows = statement.query_map([cutoff], |row| {
921            Ok((
922                row.get::<_, String>(0)?,
923                row.get::<_, String>(1)?,
924                row.get::<_, Option<String>>(2)?,
925                row.get::<_, i64>(3)?,
926                row.get::<_, i64>(4)?,
927            ))
928        })?;
929        for row in rows {
930            let (artist_key, name, image_path, release_count, track_count) = row?;
931            if artist_key.trim().is_empty() || track_count <= 0 {
932                continue;
933            }
934            match by_key.get_mut(&artist_key) {
935                Some(local) => {
936                    if release_count > local.release_count || track_count > local.track_count {
937                        local.availability = Availability::Mixed;
938                    }
939                    local.release_count = local.release_count.max(release_count);
940                    local.track_count = local.track_count.max(track_count);
941                    if local.image_path.is_none() {
942                        local.image_path = image_path;
943                    }
944                }
945                None => {
946                    by_key.insert(
947                        artist_key.clone(),
948                        ArtistCard {
949                            id: remote_artist_id(&artist_key),
950                            name,
951                            image_path,
952                            release_count,
953                            track_count,
954                            availability: Availability::Remote,
955                        },
956                    );
957                }
958            }
959        }
960
961        let mut items: Vec<ArtistCard> = by_key.into_values().collect();
962        items.sort_by(|left, right| {
963            right
964                .release_count
965                .cmp(&left.release_count)
966                .then_with(|| right.track_count.cmp(&left.track_count))
967                .then_with(|| left.name.to_lowercase().cmp(&right.name.to_lowercase()))
968        });
969        let total = items.len() as i64;
970        let offset = (page.max(1) - 1) * limit;
971        let page_items = items
972            .into_iter()
973            .skip(offset.max(0) as usize)
974            .take(limit.max(0) as usize)
975            .collect::<Vec<_>>();
976        let has_more = offset + (page_items.len() as i64) < total;
977        Ok(ArtistsPage {
978            items: page_items,
979            total,
980            page: page.max(1),
981            has_more,
982        })
983    }
984
985    pub fn artist_preview_slice(
986        &self,
987        offset: usize,
988        limit: usize,
989    ) -> Result<(Vec<NetworkArtistPreview>, Option<String>)> {
990        let conn = self.lock();
991        let mut statement = conn.prepare(
992            "SELECT a.name, a.image_path,
993                (SELECT COUNT(DISTINCT ra.release_id)
994                 FROM release_artists ra
995                 WHERE ra.artist_id = a.id) AS release_count,
996                (SELECT COUNT(DISTINCT ta.track_id)
997                 FROM track_artists ta
998                 WHERE ta.artist_id = a.id) AS track_count
999             FROM artists a
1000             WHERE EXISTS (
1001                 SELECT 1 FROM track_artists ta WHERE ta.artist_id = a.id
1002             )
1003             ORDER BY release_count DESC, track_count DESC, a.name COLLATE NOCASE
1004             LIMIT ?1 OFFSET ?2",
1005        )?;
1006        let items = statement
1007            .query_map(params![limit as i64 + 1, offset as i64], |row| {
1008                let name: String = row.get(0)?;
1009                Ok(NetworkArtistPreview {
1010                    artist_key: music_dht::normalize_name(&name),
1011                    name,
1012                    image_path: row.get(1)?,
1013                    release_count: row.get(2)?,
1014                    track_count: row.get(3)?,
1015                })
1016            })?
1017            .collect::<rusqlite::Result<Vec<_>>>()?;
1018        let has_more = items.len() > limit;
1019        let mut items: Vec<_> = items
1020            .into_iter()
1021            .take(limit)
1022            .filter(|artist| !artist.artist_key.is_empty() && artist.track_count > 0)
1023            .collect();
1024        let next = has_more.then(|| (offset + items.len()).to_string());
1025        Ok((std::mem::take(&mut items), next))
1026    }
1027
1028    pub fn replace_network_artist_cache(
1029        &self,
1030        source_id: &str,
1031        source_kind: &str,
1032        artists: &[NetworkArtistPreview],
1033        _replace_source: bool,
1034    ) -> Result<usize> {
1035        let source_id = source_id.trim();
1036        let source_kind = source_kind.trim();
1037        if source_id.is_empty() || source_kind.is_empty() {
1038            return Ok(0);
1039        }
1040        let now = now_ms_i64();
1041        let mut conn = self.lock();
1042        let tx = conn.transaction()?;
1043        let mut inserted = 0usize;
1044        {
1045            let mut stmt = tx.prepare(
1046                "INSERT INTO network_artist_cache
1047                    (source_id, source_kind, artist_key, name, image_path, remote_image_hint,
1048                     release_count, track_count, seen_at_ms)
1049                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
1050                 ON CONFLICT(source_id, artist_key) DO UPDATE SET
1051                    source_kind = excluded.source_kind,
1052                    name = excluded.name,
1053                    image_path = COALESCE(network_artist_cache.image_path, excluded.image_path),
1054                    remote_image_hint = excluded.remote_image_hint,
1055                    release_count = excluded.release_count,
1056                    track_count = excluded.track_count,
1057                    seen_at_ms = excluded.seen_at_ms",
1058            )?;
1059            for artist in artists {
1060                let artist_key = if artist.artist_key.trim().is_empty() {
1061                    music_dht::normalize_name(&artist.name)
1062                } else {
1063                    artist.artist_key.clone()
1064                };
1065                if artist_key.is_empty() || artist.track_count <= 0 {
1066                    continue;
1067                }
1068                stmt.execute(params![
1069                    source_id,
1070                    source_kind,
1071                    artist_key,
1072                    artist.name.trim(),
1073                    Option::<&str>::None,
1074                    artist.image_path.as_deref(),
1075                    artist.release_count.max(0),
1076                    artist.track_count.max(0),
1077                    now,
1078                ])?;
1079                inserted += 1;
1080            }
1081        }
1082        tx.commit()?;
1083        Ok(inserted)
1084    }
1085
1086    pub fn network_artist_image_requests(
1087        &self,
1088        filters: LibraryFilters,
1089        artist_names: &[String],
1090        limit: usize,
1091    ) -> Result<Vec<NetworkArtistImageRequest>> {
1092        if !filters.source_mode.includes_network() || artist_names.is_empty() || limit == 0 {
1093            return Ok(Vec::new());
1094        }
1095        let conn = self.lock();
1096        let cutoff = now_ms_i64().saturating_sub(NETWORK_ARTIST_CACHE_TTL_MS);
1097        let source_predicate = if filters.source_mode.includes_global_peers() {
1098            "seen_at_ms >= ?2"
1099        } else {
1100            "seen_at_ms >= ?2 AND source_kind = 'personal'"
1101        };
1102        let sql = format!(
1103            "SELECT source_id, artist_key, name
1104             FROM network_artist_cache
1105             WHERE artist_key = ?1
1106               AND {source_predicate}
1107               AND image_path IS NULL
1108               AND remote_image_hint IS NOT NULL
1109               AND remote_image_hint <> ''
1110             ORDER BY CASE source_kind WHEN 'personal' THEN 0 ELSE 1 END,
1111                      seen_at_ms DESC
1112             LIMIT 1"
1113        );
1114        let mut statement = conn.prepare(&sql)?;
1115        let mut seen_keys = HashSet::new();
1116        let mut requests = Vec::new();
1117        for name in artist_names {
1118            let artist_key = music_dht::normalize_name(name);
1119            if artist_key.is_empty() || !seen_keys.insert(artist_key.clone()) {
1120                continue;
1121            }
1122            let row = statement
1123                .query_row(params![artist_key, cutoff], |row| {
1124                    Ok(NetworkArtistImageRequest {
1125                        source_id: row.get(0)?,
1126                        artist_key: row.get(1)?,
1127                        name: row.get(2)?,
1128                    })
1129                })
1130                .optional()?;
1131            if let Some(request) = row {
1132                requests.push(request);
1133                if requests.len() >= limit {
1134                    break;
1135                }
1136            }
1137        }
1138        Ok(requests)
1139    }
1140
1141    pub fn set_network_artist_image(
1142        &self,
1143        source_id: &str,
1144        artist_key: &str,
1145        image_path: &str,
1146    ) -> Result<bool> {
1147        let changed = self.lock().execute(
1148            "UPDATE network_artist_cache
1149             SET image_path = ?3
1150             WHERE source_id = ?1 AND artist_key = ?2",
1151            params![source_id, artist_key, image_path],
1152        )?;
1153        Ok(changed > 0)
1154    }
1155
1156    pub fn artist(&self, id: i64) -> Result<ArtistDetail> {
1157        let conn = self.lock();
1158        let (name, image_path): (String, Option<String>) = conn
1159            .query_row(
1160                "SELECT name, image_path FROM artists WHERE id = ?1",
1161                [id],
1162                |row| Ok((row.get(0)?, row.get(1)?)),
1163            )
1164            .context("artist not found")?;
1165        let total_track_count: i64 = conn.query_row(
1166            "SELECT COUNT(*) FROM track_artists WHERE artist_id = ?1 AND role = 'main'",
1167            [id],
1168            |row| row.get(0),
1169        )?;
1170        let total_play_count: i64 = conn.query_row(
1171            "SELECT
1172                (SELECT COUNT(*) FROM history h
1173                 WHERE h.completed = 1 AND h.track_id IN
1174                    (SELECT track_id FROM track_artists WHERE artist_id = ?1))
1175                +
1176                (SELECT COUNT(*) FROM listen_events le
1177                 WHERE le.qualified = 1 AND le.local_track_id IN
1178                    (SELECT track_id FROM track_artists WHERE artist_id = ?1))",
1179            [id],
1180            |row| row.get(0),
1181        )?;
1182        let top_tracks = query_tracks(
1183            &conn,
1184            &format!(
1185                "SELECT {TRACK_COLUMNS} FROM tracks t
1186                 JOIN releases r ON r.id = t.release_id
1187                 JOIN track_artists ta ON ta.track_id = t.id
1188                 WHERE ta.artist_id = ?1 AND ta.role = 'main'
1189                 ORDER BY 17 DESC, t.title COLLATE NOCASE
1190                 LIMIT 10"
1191            ),
1192            params![id],
1193        )?;
1194        let featured_tracks = query_tracks(
1195            &conn,
1196            &format!(
1197                "SELECT {TRACK_COLUMNS} FROM tracks t
1198                 JOIN releases r ON r.id = t.release_id
1199                 JOIN track_artists ta ON ta.track_id = t.id
1200                 WHERE ta.artist_id = ?1 AND ta.role = 'featured'
1201                 ORDER BY t.title COLLATE NOCASE"
1202            ),
1203            params![id],
1204        )?;
1205        let mut statement = conn.prepare(
1206            "SELECT r.id, r.title, r.release_type, r.year, r.cover_path,
1207                (SELECT COUNT(*) FROM tracks t WHERE t.release_id = r.id)
1208             FROM releases r
1209             JOIN release_artists ra ON ra.release_id = r.id
1210             WHERE ra.artist_id = ?1
1211             ORDER BY r.year IS NULL, r.year DESC, r.title COLLATE NOCASE",
1212        )?;
1213        let releases = statement
1214            .query_map([id], release_card_from_row)?
1215            .collect::<rusqlite::Result<Vec<_>>>()?;
1216        Ok(ArtistDetail {
1217            id,
1218            name,
1219            image_path,
1220            total_track_count,
1221            total_play_count,
1222            top_tracks,
1223            releases,
1224            featured_tracks,
1225        })
1226    }
1227
1228    /// Returns every local release in stable library order.
1229    pub fn releases(&self) -> Result<Vec<ReleaseCard>> {
1230        let conn = self.lock();
1231        let mut statement = conn.prepare(
1232            "SELECT r.id, r.title, r.release_type, r.year, r.cover_path,
1233                    (SELECT COUNT(*) FROM tracks t WHERE t.release_id = r.id)
1234             FROM releases r
1235             ORDER BY r.year IS NULL, r.year DESC, r.title COLLATE NOCASE, r.id",
1236        )?;
1237        Ok(statement
1238            .query_map([], release_card_from_row)?
1239            .collect::<rusqlite::Result<Vec<_>>>()?)
1240    }
1241
1242    pub fn release(&self, id: i64) -> Result<ReleaseDetail> {
1243        let conn = self.lock();
1244        let (title, release_type, year, cover_path) = conn
1245            .query_row(
1246                "SELECT title, release_type, year, cover_path FROM releases WHERE id = ?1",
1247                [id],
1248                |row| {
1249                    Ok((
1250                        row.get::<_, String>(0)?,
1251                        row.get::<_, String>(1)?,
1252                        row.get::<_, Option<i32>>(2)?,
1253                        row.get::<_, Option<String>>(3)?,
1254                    ))
1255                },
1256            )
1257            .context("release not found")?;
1258        let mut statement = conn.prepare(
1259            "SELECT a.id, a.name FROM artists a
1260             JOIN release_artists ra ON ra.artist_id = a.id
1261             WHERE ra.release_id = ?1 ORDER BY ra.position",
1262        )?;
1263        let artists = statement
1264            .query_map([id], |row| {
1265                Ok(ArtistRef {
1266                    id: row.get(0)?,
1267                    name: row.get(1)?,
1268                })
1269            })?
1270            .collect::<rusqlite::Result<Vec<_>>>()?;
1271        let tracks = query_tracks(
1272            &conn,
1273            &format!(
1274                "SELECT {TRACK_COLUMNS} FROM tracks t
1275                 JOIN releases r ON r.id = t.release_id
1276                 WHERE t.release_id = ?1
1277                 ORDER BY t.disc_number IS NULL, t.disc_number,
1278                          t.track_number IS NULL, t.track_number,
1279                          t.title COLLATE NOCASE"
1280            ),
1281            params![id],
1282        )?;
1283        Ok(ReleaseDetail {
1284            id,
1285            title,
1286            release_type,
1287            year,
1288            cover_path,
1289            artists,
1290            tracks,
1291        })
1292    }
1293
1294    pub fn search(&self, query: &str, limit: i64) -> Result<SearchResults> {
1295        let query = query.trim();
1296        if query.is_empty() {
1297            return Ok(SearchResults::default());
1298        }
1299        // instr over norm() folds case for every script, unlike LIKE/NOCASE
1300        // which only handle ASCII.
1301        let pattern = music_dht::normalize_name(query);
1302        if pattern.is_empty() {
1303            // Punctuation-only queries (e.g. "%") normalize to nothing.
1304            return Ok(SearchResults::default());
1305        }
1306        let conn = self.lock();
1307        let mut statement = conn.prepare(
1308            "SELECT a.id, a.name, a.image_path,
1309                (SELECT COUNT(DISTINCT ra.release_id)
1310                 FROM release_artists ra
1311                 WHERE ra.artist_id = a.id),
1312                (SELECT COUNT(DISTINCT ta.track_id)
1313                 FROM track_artists ta
1314                 WHERE ta.artist_id = a.id)
1315             FROM artists a WHERE instr(norm(a.name), ?1) > 0
1316             ORDER BY a.name COLLATE NOCASE LIMIT ?2",
1317        )?;
1318        let artists = statement
1319            .query_map(params![pattern, limit], |row| {
1320                Ok(ArtistCard {
1321                    id: row.get(0)?,
1322                    name: row.get(1)?,
1323                    image_path: row.get(2)?,
1324                    release_count: row.get(3)?,
1325                    track_count: row.get(4)?,
1326                    availability: Availability::Local,
1327                })
1328            })?
1329            .collect::<rusqlite::Result<Vec<_>>>()?;
1330        let mut statement = conn.prepare(
1331            "SELECT r.id, r.title, r.release_type, r.year, r.cover_path,
1332                (SELECT COUNT(*) FROM tracks t WHERE t.release_id = r.id)
1333             FROM releases r WHERE instr(norm(r.title), ?1) > 0
1334             ORDER BY r.title COLLATE NOCASE LIMIT ?2",
1335        )?;
1336        let releases = statement
1337            .query_map(params![pattern, limit], release_card_from_row)?
1338            .collect::<rusqlite::Result<Vec<_>>>()?;
1339        let tracks = query_tracks(
1340            &conn,
1341            &format!(
1342                "SELECT {TRACK_COLUMNS} FROM tracks t
1343                 JOIN releases r ON r.id = t.release_id
1344                 WHERE instr(norm(t.title), ?1) > 0
1345                 ORDER BY t.title COLLATE NOCASE LIMIT ?2"
1346            ),
1347            params![pattern, limit],
1348        )?;
1349        let mut results = SearchResults {
1350            artists,
1351            releases,
1352            tracks,
1353        };
1354        rank_search_results(&mut results, &pattern);
1355        Ok(results)
1356    }
1357
1358    /// Everything the federation publishes into the DHT: plain rows, so the
1359    /// federation module needs no SQL of its own.
1360    pub fn federation_export(&self) -> Result<FederationExport> {
1361        let conn = self.lock();
1362        let mut statement = conn.prepare("SELECT id, name FROM artists")?;
1363        let artists = statement
1364            .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
1365            .collect::<rusqlite::Result<Vec<(i64, String)>>>()?;
1366
1367        let mut statement = conn.prepare(
1368            "SELECT ra.release_id, a.name FROM release_artists ra
1369             JOIN artists a ON a.id = ra.artist_id
1370             ORDER BY ra.release_id, ra.position",
1371        )?;
1372        let mut release_artists: std::collections::HashMap<i64, Vec<String>> = Default::default();
1373        for row in statement.query_map([], |row| {
1374            Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
1375        })? {
1376            let (id, name) = row?;
1377            release_artists.entry(id).or_default().push(name);
1378        }
1379        let mut statement = conn.prepare("SELECT id, title, year, release_type FROM releases")?;
1380        let releases = statement
1381            .query_map([], |row| {
1382                Ok(ExportRelease {
1383                    id: row.get(0)?,
1384                    title: row.get(1)?,
1385                    year: row.get(2)?,
1386                    release_type: row.get(3)?,
1387                    artist_names: Vec::new(),
1388                })
1389            })?
1390            .collect::<rusqlite::Result<Vec<_>>>()?
1391            .into_iter()
1392            .map(|mut release| {
1393                release.artist_names = release_artists.remove(&release.id).unwrap_or_default();
1394                release
1395            })
1396            .collect();
1397
1398        let mut statement = conn.prepare(
1399            "SELECT ta.track_id, a.name, ta.role FROM track_artists ta
1400             JOIN artists a ON a.id = ta.artist_id
1401             WHERE ta.role IN ('main', 'featured')
1402             ORDER BY ta.track_id,
1403                      CASE ta.role WHEN 'main' THEN 0 ELSE 1 END,
1404                      ta.position",
1405        )?;
1406        let mut track_artists: std::collections::HashMap<i64, Vec<String>> = Default::default();
1407        let mut featured_artists: std::collections::HashMap<i64, Vec<String>> = Default::default();
1408        for row in statement.query_map([], |row| {
1409            Ok((
1410                row.get::<_, i64>(0)?,
1411                row.get::<_, String>(1)?,
1412                row.get::<_, String>(2)?,
1413            ))
1414        })? {
1415            let (id, name, role) = row?;
1416            if role == "featured" {
1417                featured_artists.entry(id).or_default().push(name);
1418            } else {
1419                track_artists.entry(id).or_default().push(name);
1420            }
1421        }
1422        let mut statement = conn.prepare(
1423            "SELECT t.id, t.title, r.year, t.duration_seconds,
1424                    r.title, r.release_type, t.track_number, t.disc_number,
1425                    t.content_id, t.file_path
1426             FROM tracks t JOIN releases r ON r.id = t.release_id",
1427        )?;
1428        let track_rows = statement
1429            .query_map([], |row| {
1430                Ok((
1431                    ExportTrack {
1432                        id: row.get(0)?,
1433                        title: row.get(1)?,
1434                        year: row.get(2)?,
1435                        duration_seconds: row.get(3)?,
1436                        artist_names: Vec::new(),
1437                        featured_artist_names: Vec::new(),
1438                        release_title: row.get(4)?,
1439                        release_type: row.get(5)?,
1440                        track_number: row.get(6)?,
1441                        disc_number: row.get(7)?,
1442                        content_id: None,
1443                    },
1444                    row.get::<_, Option<String>>(8)?,
1445                    row.get::<_, String>(9)?,
1446                ))
1447            })?
1448            .collect::<rusqlite::Result<Vec<_>>>()?;
1449        drop(statement);
1450        let mut tracks = Vec::with_capacity(track_rows.len());
1451        for (mut track, raw_content_id, file_path) in track_rows {
1452            let mut content_id = raw_content_id
1453                .as_deref()
1454                .and_then(music_dht::normalize_content_id);
1455            if content_id.is_none() {
1456                content_id = audio_content_id(&file_path);
1457            }
1458            if content_id != raw_content_id {
1459                conn.execute(
1460                    "UPDATE tracks SET content_id = ?2 WHERE id = ?1",
1461                    params![track.id, content_id.as_deref()],
1462                )?;
1463            }
1464            track.artist_names = track_artists.remove(&track.id).unwrap_or_default();
1465            track.featured_artist_names = featured_artists.remove(&track.id).unwrap_or_default();
1466            track.content_id = content_id;
1467            tracks.push(track);
1468        }
1469
1470        Ok(FederationExport {
1471            artists,
1472            releases,
1473            tracks,
1474        })
1475    }
1476
1477    pub fn tracks_by_ids(&self, ids: &[i64]) -> Result<Vec<TrackItem>> {
1478        let conn = self.lock();
1479        let mut tracks = Vec::with_capacity(ids.len());
1480        for &id in ids {
1481            let mut found = query_tracks(
1482                &conn,
1483                &format!(
1484                    "SELECT {TRACK_COLUMNS} FROM tracks t
1485                     JOIN releases r ON r.id = t.release_id
1486                     WHERE t.id = ?1"
1487                ),
1488                params![id],
1489            )?;
1490            tracks.append(&mut found);
1491        }
1492        Ok(tracks)
1493    }
1494
1495    pub fn track_by_content_id(&self, content_id: &str) -> Result<Option<TrackItem>> {
1496        let Some(content_id) = music_dht::normalize_content_id(content_id) else {
1497            return Ok(None);
1498        };
1499        let conn = self.lock();
1500        let mut tracks = query_tracks(
1501            &conn,
1502            &format!(
1503                "SELECT {TRACK_COLUMNS} FROM tracks t
1504                 JOIN releases r ON r.id = t.release_id
1505                 WHERE t.content_id = ?1
1506                 LIMIT 1"
1507            ),
1508            params![content_id],
1509        )?;
1510        Ok(tracks.pop())
1511    }
1512
1513    // -----------------------------------------------------------------
1514    // Similarity embeddings. SQLite is the canonical store; callers build
1515    // replaceable in-memory indexes from these rows.
1516    // -----------------------------------------------------------------
1517
1518    pub fn ensure_similarity_profile(
1519        &self,
1520        profile_id: &str,
1521        model_id: &str,
1522        model_version: &str,
1523        model_sha256: &str,
1524        preprocessing: &str,
1525        dimensions: usize,
1526    ) -> Result<()> {
1527        let conn = self.lock();
1528        conn.execute(
1529            "INSERT INTO similarity_profiles (
1530                 profile_id, model_id, model_version, model_sha256,
1531                 preprocessing, dimensions, created_at_ms
1532             ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
1533             ON CONFLICT(profile_id) DO NOTHING",
1534            params![
1535                profile_id,
1536                model_id,
1537                model_version,
1538                model_sha256,
1539                preprocessing,
1540                dimensions as i64,
1541                now_ms_i64(),
1542            ],
1543        )?;
1544        Ok(())
1545    }
1546
1547    pub fn pending_similarity_tracks(&self, profile_id: &str) -> Result<Vec<SimilarityTrack>> {
1548        let conn = self.lock();
1549        let mut statement = conn.prepare(
1550            "SELECT t.id, t.title, t.file_path, t.content_id, t.duration_seconds
1551             FROM tracks t
1552             WHERE NOT EXISTS (
1553                 SELECT 1 FROM track_embeddings e
1554                 WHERE e.track_id = t.id
1555                   AND e.profile_id = ?1
1556                   AND e.source_content_id IS t.content_id
1557             )
1558             ORDER BY t.id",
1559        )?;
1560        Ok(statement
1561            .query_map([profile_id], |row| {
1562                Ok(SimilarityTrack {
1563                    id: row.get(0)?,
1564                    title: row.get(1)?,
1565                    file_path: row.get(2)?,
1566                    content_id: row.get(3)?,
1567                    duration_seconds: row.get(4)?,
1568                })
1569            })?
1570            .collect::<rusqlite::Result<Vec<_>>>()?)
1571    }
1572
1573    pub fn store_similarity_embedding(
1574        &self,
1575        track: &SimilarityTrack,
1576        profile_id: &str,
1577        vector: &[f32],
1578    ) -> Result<()> {
1579        anyhow::ensure!(!vector.is_empty(), "embedding vector is empty");
1580        anyhow::ensure!(
1581            vector.iter().all(|value| value.is_finite()),
1582            "embedding contains a non-finite value"
1583        );
1584        let bytes = embedding_to_bytes(vector);
1585        let routing_signature = music_dht::similarity_lsh::routing_signature(vector)?;
1586        let conn = self.lock();
1587        conn.execute(
1588            "INSERT INTO track_embeddings (
1589                 track_id, profile_id, dimensions, vector, routing_signature,
1590                 source_content_id, computed_at_ms
1591             ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
1592             ON CONFLICT(track_id, profile_id) DO UPDATE SET
1593                 dimensions = excluded.dimensions,
1594                 vector = excluded.vector,
1595                 routing_signature = excluded.routing_signature,
1596                 source_content_id = excluded.source_content_id,
1597                 computed_at_ms = excluded.computed_at_ms",
1598            params![
1599                track.id,
1600                profile_id,
1601                vector.len() as i64,
1602                bytes,
1603                routing_signature.as_slice(),
1604                track.content_id.as_deref(),
1605                now_ms_i64(),
1606            ],
1607        )?;
1608        Ok(())
1609    }
1610
1611    pub fn similarity_embedding(
1612        &self,
1613        track_id: i64,
1614        profile_id: &str,
1615    ) -> Result<Option<Vec<f32>>> {
1616        let conn = self.lock();
1617        let row = conn
1618            .query_row(
1619                "SELECT e.dimensions, e.vector
1620                 FROM track_embeddings e
1621                 JOIN tracks t ON t.id = e.track_id
1622                 WHERE e.track_id = ?1
1623                   AND e.profile_id = ?2
1624                   AND e.source_content_id IS t.content_id",
1625                params![track_id, profile_id],
1626                |row| Ok((row.get::<_, i64>(0)?, row.get::<_, Vec<u8>>(1)?)),
1627            )
1628            .optional()?;
1629        row.map(|(dimensions, bytes)| embedding_from_bytes(dimensions, &bytes))
1630            .transpose()
1631    }
1632
1633    pub fn load_similarity_index(&self, profile_id: &str) -> Result<Vec<StoredEmbedding>> {
1634        let conn = self.lock();
1635        let mut statement = conn.prepare(
1636            "SELECT e.track_id, e.dimensions, e.vector,
1637                    COALESCE((
1638                        SELECT norm(a.name)
1639                        FROM track_artists ta
1640                        JOIN artists a ON a.id = ta.artist_id
1641                        WHERE ta.track_id = e.track_id AND ta.role = 'main'
1642                        ORDER BY ta.position LIMIT 1
1643                    ), '')
1644             FROM track_embeddings e
1645             JOIN tracks t ON t.id = e.track_id
1646             WHERE e.profile_id = ?1
1647               AND e.source_content_id IS t.content_id
1648             ORDER BY e.track_id",
1649        )?;
1650        let rows = statement.query_map([profile_id], |row| {
1651            Ok((
1652                row.get::<_, i64>(0)?,
1653                row.get::<_, i64>(1)?,
1654                row.get::<_, Vec<u8>>(2)?,
1655                row.get::<_, String>(3)?,
1656            ))
1657        })?;
1658        let mut embeddings = Vec::new();
1659        for row in rows {
1660            let (track_id, dimensions, bytes, artist_key) = row?;
1661            embeddings.push(StoredEmbedding {
1662                track_id,
1663                vector: embedding_from_bytes(dimensions, &bytes)?,
1664                artist_key,
1665            });
1666        }
1667        Ok(embeddings)
1668    }
1669
1670    /// Loads the compact DHT-routing signatures for every current local
1671    /// embedding. Rows created before similarity routing existed are
1672    /// backfilled in place from their durable vectors.
1673    pub fn similarity_routing_signatures(&self, profile_id: &str) -> Result<Vec<[u8; 32]>> {
1674        let mut conn = self.lock();
1675        let transaction = conn.transaction()?;
1676        let missing = {
1677            let mut statement = transaction.prepare(
1678                "SELECT e.track_id, e.dimensions, e.vector
1679             FROM track_embeddings e
1680             JOIN tracks t ON t.id = e.track_id
1681             WHERE e.profile_id = ?1
1682               AND e.source_content_id IS t.content_id
1683               AND (e.routing_signature IS NULL OR length(e.routing_signature) != 32)
1684             ORDER BY e.track_id",
1685            )?;
1686            statement
1687                .query_map([profile_id], |row| {
1688                    Ok((
1689                        row.get::<_, i64>(0)?,
1690                        row.get::<_, i64>(1)?,
1691                        row.get::<_, Vec<u8>>(2)?,
1692                    ))
1693                })?
1694                .collect::<rusqlite::Result<Vec<_>>>()?
1695        };
1696        for (track_id, dimensions, vector_bytes) in missing {
1697            let vector = embedding_from_bytes(dimensions, &vector_bytes)?;
1698            let signature = music_dht::similarity_lsh::routing_signature(&vector)?;
1699            transaction.execute(
1700                "UPDATE track_embeddings
1701                 SET routing_signature = ?3
1702                 WHERE track_id = ?1 AND profile_id = ?2",
1703                params![track_id, profile_id, signature.as_slice()],
1704            )?;
1705        }
1706        transaction.commit()?;
1707
1708        let mut statement = conn.prepare(
1709            "SELECT e.routing_signature
1710             FROM track_embeddings e
1711             JOIN tracks t ON t.id = e.track_id
1712             WHERE e.profile_id = ?1
1713               AND e.source_content_id IS t.content_id
1714             ORDER BY e.track_id",
1715        )?;
1716        let stored = statement
1717            .query_map([profile_id], |row| row.get::<_, Vec<u8>>(0))?
1718            .collect::<rusqlite::Result<Vec<_>>>()?;
1719        let signatures = stored
1720            .into_iter()
1721            .map(|signature| {
1722                <[u8; 32]>::try_from(signature)
1723                    .map_err(|_| anyhow::anyhow!("invalid similarity routing signature length"))
1724            })
1725            .collect::<Result<Vec<_>>>()?;
1726        Ok(signatures)
1727    }
1728
1729    pub fn similarity_storage_stats(&self, profile_id: &str) -> Result<SimilarityStorageStats> {
1730        let conn = self.lock();
1731        let total_tracks = conn.query_row("SELECT COUNT(*) FROM tracks", [], |row| {
1732            row.get::<_, i64>(0)
1733        })?;
1734        let embedded_tracks = conn.query_row(
1735            "SELECT COUNT(*)
1736             FROM track_embeddings e JOIN tracks t ON t.id = e.track_id
1737             WHERE e.profile_id = ?1 AND e.source_content_id IS t.content_id",
1738            [profile_id],
1739            |row| row.get::<_, i64>(0),
1740        )?;
1741        let (stored_vectors, stored_bytes) = conn.query_row(
1742            "SELECT COUNT(*), COALESCE(SUM(length(vector)), 0) FROM track_embeddings",
1743            [],
1744            |row| Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?)),
1745        )?;
1746        Ok(SimilarityStorageStats {
1747            total_tracks: total_tracks.max(0) as usize,
1748            embedded_tracks: embedded_tracks.max(0) as usize,
1749            stored_vectors: stored_vectors.max(0) as usize,
1750            stored_bytes: stored_bytes.max(0) as u64,
1751        })
1752    }
1753
1754    pub fn clear_similarity_embeddings(&self) -> Result<()> {
1755        let conn = self.lock();
1756        conn.execute("DELETE FROM track_embeddings", [])?;
1757        conn.execute("DELETE FROM similarity_profiles", [])?;
1758        Ok(())
1759    }
1760
1761    // -----------------------------------------------------------------
1762    // Playlists & likes
1763    // -----------------------------------------------------------------
1764
1765    pub fn playlists(&self) -> Result<Vec<PlaylistCard>> {
1766        let conn = self.lock();
1767        let liked: i64 = conn.query_row(
1768            "SELECT (SELECT COUNT(*) FROM likes) + (SELECT COUNT(*) FROM fed_likes)",
1769            [],
1770            |row| row.get(0),
1771        )?;
1772        let mut list = vec![PlaylistCard {
1773            id: LIKES_PLAYLIST_ID,
1774            title: "Liked tracks".to_string(),
1775            track_count: liked,
1776            kind: "likes".to_string(),
1777        }];
1778        let mut statement = conn.prepare(
1779            "SELECT p.id, p.title,
1780                (SELECT COUNT(*) FROM playlist_tracks pt WHERE pt.playlist_id = p.id)
1781                +
1782                (SELECT COUNT(*) FROM fed_playlist_tracks f
1783                 WHERE f.playlist_sync_id = p.sync_id
1784                   AND NOT EXISTS (
1785                    SELECT 1 FROM playlist_tracks pt
1786                    JOIN tracks t ON t.id = pt.track_id
1787                    WHERE pt.playlist_id = p.id
1788                      AND t.content_id = f.content_id
1789                   ))
1790             FROM playlists p ORDER BY p.title COLLATE NOCASE",
1791        )?;
1792        let rows = statement
1793            .query_map([], |row| {
1794                Ok(PlaylistCard {
1795                    id: row.get(0)?,
1796                    title: row.get(1)?,
1797                    track_count: row.get(2)?,
1798                    kind: "normal".to_string(),
1799                })
1800            })?
1801            .collect::<rusqlite::Result<Vec<_>>>()?;
1802        list.extend(rows);
1803        Ok(list)
1804    }
1805
1806    pub fn playlist(&self, id: i64) -> Result<PlaylistDetail> {
1807        let conn = self.lock();
1808        if id == LIKES_PLAYLIST_ID {
1809            let local_tracks = query_tracks(
1810                &conn,
1811                &format!(
1812                    "SELECT {TRACK_COLUMNS} FROM tracks t
1813                     JOIN releases r ON r.id = t.release_id
1814                     JOIN likes k ON k.track_id = t.id"
1815                ),
1816                params![],
1817            )?;
1818            let mut liked_at_by_track = HashMap::new();
1819            let mut liked_stmt = conn.prepare(
1820                "SELECT track_id,
1821                        COALESCE(liked_hlc_ms,
1822                                 CAST(strftime('%s', liked_at) AS INTEGER) * 1000,
1823                                 0)
1824                 FROM likes",
1825            )?;
1826            let liked_rows = liked_stmt
1827                .query_map([], |row| Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?)))?;
1828            for row in liked_rows {
1829                let (track_id, liked_at) = row?;
1830                liked_at_by_track.insert(track_id, liked_at);
1831            }
1832            drop(liked_stmt);
1833
1834            let mut entries: Vec<(i64, String, TrackItem)> = local_tracks
1835                .into_iter()
1836                .map(|track| {
1837                    let liked_at = liked_at_by_track
1838                        .get(&track.id)
1839                        .copied()
1840                        .unwrap_or_default();
1841                    let stable_key = track
1842                        .content_id
1843                        .clone()
1844                        .unwrap_or_else(|| format!("local:{:020}", track.id));
1845                    (liked_at, stable_key, track)
1846                })
1847                .collect();
1848
1849            let mut fed_stmt = conn.prepare(
1850                "SELECT COALESCE(liked_hlc_ms,
1851                                 CAST(strftime('%s', liked_at) AS INTEGER) * 1000,
1852                                 0),
1853                        item_id, owner, title, artist_names, featured_artist_names,
1854                        year, duration_seconds, content_id, release_title, track_number, disc_number
1855                 FROM fed_likes",
1856            )?;
1857            let fed_rows = fed_stmt.query_map([], |row| {
1858                Ok((row.get::<_, i64>(0)?, fed_track_from_offset_row(row, 1)?))
1859            })?;
1860            for row in fed_rows {
1861                let (liked_at, fed) = row?;
1862                let stable_key = fed
1863                    .content_id
1864                    .clone()
1865                    .unwrap_or_else(|| format!("fed:{}", fed.item_id));
1866                entries.push((liked_at, stable_key, pending_track(&fed)));
1867            }
1868            entries.sort_by(|left, right| right.0.cmp(&left.0).then_with(|| left.1.cmp(&right.1)));
1869            return Ok(PlaylistDetail {
1870                id,
1871                title: "Liked tracks".to_string(),
1872                description: None,
1873                tracks: entries.into_iter().map(|(_, _, track)| track).collect(),
1874            });
1875        }
1876        let (title, description, sync_id): (String, Option<String>, Option<String>) = conn
1877            .query_row(
1878                "SELECT title, description, sync_id FROM playlists WHERE id = ?1",
1879                [id],
1880                |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)),
1881            )
1882            .context("playlist not found")?;
1883        let local_tracks = query_tracks(
1884            &conn,
1885            &format!(
1886                "SELECT {TRACK_COLUMNS} FROM tracks t
1887                 JOIN releases r ON r.id = t.release_id
1888                 JOIN playlist_tracks pt ON pt.track_id = t.id
1889                 WHERE pt.playlist_id = ?1
1890                 ORDER BY pt.position"
1891            ),
1892            params![id],
1893        )?;
1894        let mut positions = HashMap::new();
1895        let mut position_stmt =
1896            conn.prepare("SELECT track_id, position FROM playlist_tracks WHERE playlist_id = ?1")?;
1897        let position_rows = position_stmt.query_map([id], |row| {
1898            Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?))
1899        })?;
1900        for row in position_rows {
1901            let (track_id, position) = row?;
1902            positions.insert(track_id, position);
1903        }
1904        drop(position_stmt);
1905        let mut entries: Vec<(i64, TrackItem)> = local_tracks
1906            .into_iter()
1907            .enumerate()
1908            .map(|(index, track)| {
1909                let position = positions.get(&track.id).copied().unwrap_or(index as i64);
1910                (position, track)
1911            })
1912            .collect();
1913        if let Some(sync_id) = sync_id.as_deref() {
1914            for (position, fed) in fed_playlist_tracks(&conn, id, sync_id)? {
1915                entries.push((position, pending_track(&fed)));
1916            }
1917        }
1918        entries.sort_by(|(left_pos, left_track), (right_pos, right_track)| {
1919            left_pos
1920                .cmp(right_pos)
1921                .then_with(|| left_track.title.cmp(&right_track.title))
1922        });
1923        Ok(PlaylistDetail {
1924            id,
1925            title,
1926            description,
1927            tracks: entries.into_iter().map(|(_, track)| track).collect(),
1928        })
1929    }
1930
1931    pub fn create_playlist(&self, title: &str) -> Result<PlaylistCard> {
1932        let conn = self.lock();
1933        conn.execute("INSERT INTO playlists (title) VALUES (?1)", [title])?;
1934        let id = conn.last_insert_rowid();
1935        let sync_id = make_playlist_sync_id(id, title);
1936        conn.execute(
1937            "UPDATE playlists SET sync_id = ?2 WHERE id = ?1",
1938            params![id, sync_id],
1939        )?;
1940        Ok(PlaylistCard {
1941            id,
1942            title: title.to_string(),
1943            track_count: 0,
1944            kind: "normal".to_string(),
1945        })
1946    }
1947
1948    pub fn update_playlist(&self, id: i64, title: &str, description: Option<&str>) -> Result<()> {
1949        let conn = self.lock();
1950        conn.execute(
1951            "UPDATE playlists SET title = ?2, description = ?3 WHERE id = ?1",
1952            params![id, title, description],
1953        )?;
1954        Ok(())
1955    }
1956
1957    pub fn delete_playlist(&self, id: i64) -> Result<()> {
1958        let conn = self.lock();
1959        let sync_id: Option<String> = conn
1960            .query_row("SELECT sync_id FROM playlists WHERE id = ?1", [id], |row| {
1961                row.get::<_, Option<String>>(0)
1962            })
1963            .optional()?
1964            .flatten();
1965        if let Some(sync_id) = sync_id {
1966            conn.execute(
1967                "DELETE FROM fed_playlist_tracks WHERE playlist_sync_id = ?1",
1968                [sync_id],
1969            )?;
1970        }
1971        conn.execute("DELETE FROM playlists WHERE id = ?1", [id])?;
1972        Ok(())
1973    }
1974
1975    pub fn delete_playlist_by_sync_id(&self, sync_id: &str) -> Result<()> {
1976        let conn = self.lock();
1977        conn.execute(
1978            "DELETE FROM fed_playlist_tracks WHERE playlist_sync_id = ?1",
1979            [sync_id],
1980        )?;
1981        conn.execute("DELETE FROM playlists WHERE sync_id = ?1", [sync_id])?;
1982        Ok(())
1983    }
1984
1985    pub fn playlist_sync_id(&self, id: i64) -> Result<Option<String>> {
1986        let conn = self.lock();
1987        Ok(conn
1988            .query_row("SELECT sync_id FROM playlists WHERE id = ?1", [id], |row| {
1989                row.get(0)
1990            })
1991            .optional()?)
1992    }
1993
1994    pub fn ensure_playlist_sync_id(&self, id: i64) -> Result<String> {
1995        let conn = self.lock();
1996        let existing: Option<String> = conn
1997            .query_row("SELECT sync_id FROM playlists WHERE id = ?1", [id], |row| {
1998                row.get(0)
1999            })
2000            .optional()?
2001            .flatten();
2002        if let Some(sync_id) = existing {
2003            return Ok(sync_id);
2004        }
2005        let title: String =
2006            conn.query_row("SELECT title FROM playlists WHERE id = ?1", [id], |row| {
2007                row.get(0)
2008            })?;
2009        let sync_id = make_playlist_sync_id(id, &title);
2010        conn.execute(
2011            "UPDATE playlists SET sync_id = ?2 WHERE id = ?1",
2012            params![id, sync_id],
2013        )?;
2014        Ok(sync_id)
2015    }
2016
2017    pub fn upsert_synced_playlist(&self, sync_id: &str, title: &str) -> Result<i64> {
2018        let conn = self.lock();
2019        if let Some(id) = conn
2020            .query_row(
2021                "SELECT id FROM playlists WHERE sync_id = ?1",
2022                [sync_id],
2023                |row| row.get(0),
2024            )
2025            .optional()?
2026        {
2027            conn.execute(
2028                "UPDATE playlists SET title = ?2 WHERE id = ?1",
2029                params![id, title],
2030            )?;
2031            return Ok(id);
2032        }
2033        conn.execute(
2034            "INSERT INTO playlists (sync_id, title) VALUES (?1, ?2)",
2035            params![sync_id, title],
2036        )?;
2037        Ok(conn.last_insert_rowid())
2038    }
2039
2040    pub fn add_tracks_to_playlist(&self, playlist_id: i64, track_ids: &[i64]) -> Result<()> {
2041        let mut conn = self.lock();
2042        let tx = conn.transaction()?;
2043        let mut next: i64 = tx.query_row(
2044            "SELECT COALESCE(MAX(position), -1) + 1 FROM playlist_tracks WHERE playlist_id = ?1",
2045            [playlist_id],
2046            |row| row.get(0),
2047        )?;
2048        for &track_id in track_ids {
2049            let inserted = tx.execute(
2050                "INSERT OR IGNORE INTO playlist_tracks (playlist_id, track_id, position)
2051                 VALUES (?1, ?2, ?3)",
2052                params![playlist_id, track_id, next],
2053            )?;
2054            if inserted > 0 {
2055                next += 1;
2056            }
2057        }
2058        tx.commit()?;
2059        Ok(())
2060    }
2061
2062    pub fn add_fed_tracks_to_playlist(
2063        &self,
2064        playlist_id: i64,
2065        tracks: &[FederatedTrack],
2066    ) -> Result<()> {
2067        if tracks.is_empty() {
2068            return Ok(());
2069        }
2070        let mut conn = self.lock();
2071        let tx = conn.transaction()?;
2072        let playlist_sync_id: Option<String> = tx
2073            .query_row(
2074                "SELECT sync_id FROM playlists WHERE id = ?1",
2075                [playlist_id],
2076                |row| row.get::<_, Option<String>>(0),
2077            )
2078            .optional()?
2079            .flatten();
2080        let playlist_sync_id = match playlist_sync_id {
2081            Some(sync_id) => sync_id,
2082            None => {
2083                let title: String = tx.query_row(
2084                    "SELECT title FROM playlists WHERE id = ?1",
2085                    [playlist_id],
2086                    |row| row.get(0),
2087                )?;
2088                let sync_id = make_playlist_sync_id(playlist_id, &title);
2089                tx.execute(
2090                    "UPDATE playlists SET sync_id = ?2 WHERE id = ?1",
2091                    params![playlist_id, sync_id],
2092                )?;
2093                sync_id
2094            }
2095        };
2096        let local_max: i64 = tx.query_row(
2097            "SELECT COALESCE(MAX(position), -1) FROM playlist_tracks WHERE playlist_id = ?1",
2098            [playlist_id],
2099            |row| row.get(0),
2100        )?;
2101        let fed_max: i64 = tx.query_row(
2102            "SELECT COALESCE(MAX(position), -1) FROM fed_playlist_tracks WHERE playlist_sync_id = ?1",
2103            [&playlist_sync_id],
2104            |row| row.get(0),
2105        )?;
2106        let mut next = local_max.max(fed_max) + 1;
2107        for fed in tracks {
2108            let Some(content_id) = fed
2109                .content_id
2110                .as_deref()
2111                .and_then(music_dht::normalize_content_id)
2112            else {
2113                continue;
2114            };
2115            let existing_position: Option<i64> = tx
2116                .query_row(
2117                    "SELECT position FROM fed_playlist_tracks
2118                     WHERE playlist_sync_id = ?1 AND content_id = ?2",
2119                    params![playlist_sync_id, content_id],
2120                    |row| row.get(0),
2121                )
2122                .optional()?;
2123            let position = match existing_position {
2124                Some(position) => position,
2125                None => {
2126                    let position = next;
2127                    next += 1;
2128                    position
2129                }
2130            };
2131            tx.execute(
2132                "INSERT INTO fed_playlist_tracks
2133                    (playlist_sync_id, item_id, owner, title, artist_names,
2134                     featured_artist_names, year, duration_seconds, content_id,
2135                     release_title, track_number, disc_number, position)
2136                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)
2137                 ON CONFLICT(playlist_sync_id, content_id) DO UPDATE SET
2138                    item_id = excluded.item_id,
2139                    owner = excluded.owner,
2140                    title = excluded.title,
2141                    artist_names = excluded.artist_names,
2142                    featured_artist_names = excluded.featured_artist_names,
2143                    year = excluded.year,
2144                    duration_seconds = excluded.duration_seconds,
2145                    release_title = excluded.release_title,
2146                    track_number = excluded.track_number,
2147                    disc_number = excluded.disc_number,
2148                    position = excluded.position",
2149                params![
2150                    playlist_sync_id,
2151                    fed.item_id,
2152                    fed.owner,
2153                    fed.title,
2154                    fed.artist_names.join("; "),
2155                    fed.featured_artist_names.join("; "),
2156                    fed.year,
2157                    fed.duration_seconds.map(|d| d as f64),
2158                    content_id,
2159                    fed.release_title,
2160                    fed.track_number,
2161                    fed.disc_number,
2162                    position,
2163                ],
2164            )?;
2165        }
2166        tx.commit()?;
2167        Ok(())
2168    }
2169
2170    pub fn remove_tracks_from_playlist(&self, playlist_id: i64, track_ids: &[i64]) -> Result<()> {
2171        let conn = self.lock();
2172        for &track_id in track_ids {
2173            conn.execute(
2174                "DELETE FROM playlist_tracks WHERE playlist_id = ?1 AND track_id = ?2",
2175                params![playlist_id, track_id],
2176            )?;
2177        }
2178        Ok(())
2179    }
2180
2181    pub fn remove_content_ids_from_playlist(
2182        &self,
2183        playlist_id: i64,
2184        content_ids: &[String],
2185    ) -> Result<()> {
2186        let conn = self.lock();
2187        let playlist_sync_id: Option<String> = conn
2188            .query_row(
2189                "SELECT sync_id FROM playlists WHERE id = ?1",
2190                [playlist_id],
2191                |row| row.get::<_, Option<String>>(0),
2192            )
2193            .optional()?
2194            .flatten();
2195        for content_id in content_ids {
2196            let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2197                continue;
2198            };
2199            conn.execute(
2200                "DELETE FROM playlist_tracks
2201                 WHERE playlist_id = ?1
2202                   AND track_id IN (
2203                    SELECT id FROM tracks WHERE content_id = ?2
2204                   )",
2205                params![playlist_id, content_id],
2206            )?;
2207            if let Some(sync_id) = playlist_sync_id.as_deref() {
2208                conn.execute(
2209                    "DELETE FROM fed_playlist_tracks
2210                     WHERE playlist_sync_id = ?1 AND content_id = ?2",
2211                    params![sync_id, content_id],
2212                )?;
2213            }
2214        }
2215        Ok(())
2216    }
2217
2218    pub fn track_content_id_by_id(&self, track_id: i64) -> Result<Option<String>> {
2219        let conn = self.lock();
2220        let content_id: Option<String> = conn
2221            .query_row(
2222                "SELECT content_id FROM tracks WHERE id = ?1",
2223                [track_id],
2224                |row| row.get::<_, Option<String>>(0),
2225            )
2226            .optional()?
2227            .flatten()
2228            .and_then(|value| music_dht::normalize_content_id(&value));
2229        Ok(content_id)
2230    }
2231
2232    pub fn playlist_track_content_positions(
2233        &self,
2234        playlist_id: i64,
2235        track_ids: &[i64],
2236    ) -> Result<Vec<(String, i64)>> {
2237        let conn = self.lock();
2238        let mut out = Vec::new();
2239        for &track_id in track_ids {
2240            let row: Option<(Option<String>, i64)> = conn
2241                .query_row(
2242                    "SELECT t.content_id, pt.position
2243                     FROM tracks t
2244                     JOIN playlist_tracks pt ON pt.track_id = t.id
2245                     WHERE t.id = ?1 AND pt.playlist_id = ?2",
2246                    params![track_id, playlist_id],
2247                    |row| Ok((row.get(0)?, row.get(1)?)),
2248                )
2249                .optional()?;
2250            let Some((content_id, position)) = row else {
2251                continue;
2252            };
2253            if let Some(content_id) = content_id
2254                .as_deref()
2255                .and_then(music_dht::normalize_content_id)
2256            {
2257                out.push((content_id, position));
2258            }
2259        }
2260        Ok(out)
2261    }
2262
2263    pub fn playlist_content_position(
2264        &self,
2265        playlist_id: i64,
2266        content_id: &str,
2267    ) -> Result<Option<i64>> {
2268        let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2269            return Ok(None);
2270        };
2271        let conn = self.lock();
2272        let local_position = conn
2273            .query_row(
2274                "SELECT pt.position
2275                 FROM playlist_tracks pt
2276                 JOIN tracks t ON t.id = pt.track_id
2277                 WHERE pt.playlist_id = ?1 AND t.content_id = ?2
2278                 LIMIT 1",
2279                params![playlist_id, content_id],
2280                |row| row.get(0),
2281            )
2282            .optional()?;
2283        if local_position.is_some() {
2284            return Ok(local_position);
2285        }
2286        let sync_id: Option<String> = conn
2287            .query_row(
2288                "SELECT sync_id FROM playlists WHERE id = ?1",
2289                [playlist_id],
2290                |row| row.get::<_, Option<String>>(0),
2291            )
2292            .optional()?
2293            .flatten();
2294        let Some(sync_id) = sync_id else {
2295            return Ok(None);
2296        };
2297        Ok(conn
2298            .query_row(
2299                "SELECT position
2300                 FROM fed_playlist_tracks
2301                 WHERE playlist_sync_id = ?1 AND content_id = ?2
2302                 LIMIT 1",
2303                params![sync_id, content_id],
2304                |row| row.get(0),
2305            )
2306            .optional()?)
2307    }
2308
2309    pub fn track_id_by_content_id(&self, content_id: &str) -> Result<Option<i64>> {
2310        let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2311            return Ok(None);
2312        };
2313        let conn = self.lock();
2314        Ok(conn
2315            .query_row(
2316                "SELECT id FROM tracks WHERE content_id = ?1 LIMIT 1",
2317                [content_id],
2318                |row| row.get(0),
2319            )
2320            .optional()?)
2321    }
2322
2323    pub fn liked_content_ids(&self) -> Result<Vec<String>> {
2324        let conn = self.lock();
2325        let mut statement = conn.prepare(
2326            "SELECT DISTINCT t.content_id
2327             FROM likes k
2328             JOIN tracks t ON t.id = k.track_id
2329             WHERE t.content_id IS NOT NULL",
2330        )?;
2331        let rows = statement
2332            .query_map([], |row| row.get::<_, String>(0))?
2333            .collect::<rusqlite::Result<Vec<_>>>()?;
2334        Ok(rows
2335            .into_iter()
2336            .filter_map(|content_id| music_dht::normalize_content_id(&content_id))
2337            .collect())
2338    }
2339
2340    pub fn local_content_ids(&self) -> Result<Vec<String>> {
2341        let conn = self.lock();
2342        let mut statement = conn.prepare(
2343            "SELECT DISTINCT content_id
2344             FROM tracks
2345             WHERE content_id IS NOT NULL",
2346        )?;
2347        let rows = statement
2348            .query_map([], |row| row.get::<_, String>(0))?
2349            .collect::<rusqlite::Result<Vec<_>>>()?;
2350        Ok(rows
2351            .into_iter()
2352            .filter_map(|content_id| music_dht::normalize_content_id(&content_id))
2353            .collect())
2354    }
2355
2356    /// Returns the new liked state for this content id.
2357    pub fn toggle_like_by_content_id(&self, content_id: &str) -> Result<bool> {
2358        let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2359            anyhow::bail!("invalid content id");
2360        };
2361        let mut conn = self.lock();
2362        let tx = conn.transaction()?;
2363        let removed_local = tx.execute(
2364            "DELETE FROM likes
2365             WHERE track_id IN (
2366                SELECT id FROM tracks WHERE content_id = ?1
2367             )",
2368            [&content_id],
2369        )?;
2370        let removed_fed =
2371            tx.execute("DELETE FROM fed_likes WHERE content_id = ?1", [&content_id])?;
2372        if removed_local + removed_fed > 0 {
2373            tx.commit()?;
2374            return Ok(false);
2375        }
2376        let track_id: i64 = tx
2377            .query_row(
2378                "SELECT id FROM tracks WHERE content_id = ?1 ORDER BY id LIMIT 1",
2379                [&content_id],
2380                |row| row.get(0),
2381            )
2382            .optional()?
2383            .with_context(|| format!("no local track with content id {content_id}"))?;
2384        tx.execute("INSERT INTO likes (track_id) VALUES (?1)", [track_id])?;
2385        tx.commit()?;
2386        Ok(true)
2387    }
2388
2389    pub fn set_synced_like(&self, track_id: i64, liked: bool, liked_hlc_ms: i64) -> Result<bool> {
2390        let conn = self.lock();
2391        let changed = if liked {
2392            conn.execute(
2393                "INSERT INTO likes (track_id, liked_at, liked_hlc_ms)
2394                 VALUES (?1, datetime(?2 / 1000, 'unixepoch'), ?2)
2395                 ON CONFLICT(track_id) DO UPDATE SET
2396                    liked_at = excluded.liked_at,
2397                    liked_hlc_ms = excluded.liked_hlc_ms",
2398                params![track_id, liked_hlc_ms],
2399            )?
2400        } else {
2401            conn.execute("DELETE FROM likes WHERE track_id = ?1", [track_id])?
2402        };
2403        Ok(changed > 0)
2404    }
2405
2406    pub fn upsert_fed_playlist_track(
2407        &self,
2408        playlist_sync_id: &str,
2409        fed: &FederatedTrack,
2410        position: i64,
2411    ) -> Result<bool> {
2412        let Some(content_id) = fed
2413            .content_id
2414            .as_deref()
2415            .and_then(music_dht::normalize_content_id)
2416        else {
2417            return Ok(false);
2418        };
2419        let conn = self.lock();
2420        Ok(conn.execute(
2421            "INSERT INTO fed_playlist_tracks
2422                (playlist_sync_id, item_id, owner, title, artist_names,
2423                 featured_artist_names, year, duration_seconds, content_id,
2424                 release_title, track_number, disc_number, position)
2425             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)
2426             ON CONFLICT(playlist_sync_id, content_id) DO UPDATE SET
2427                item_id = excluded.item_id,
2428                owner = excluded.owner,
2429                title = excluded.title,
2430                artist_names = excluded.artist_names,
2431                featured_artist_names = excluded.featured_artist_names,
2432                year = excluded.year,
2433                duration_seconds = excluded.duration_seconds,
2434                release_title = excluded.release_title,
2435                track_number = excluded.track_number,
2436                disc_number = excluded.disc_number,
2437                position = excluded.position",
2438            params![
2439                playlist_sync_id,
2440                fed.item_id,
2441                fed.owner,
2442                fed.title,
2443                fed.artist_names.join("; "),
2444                fed.featured_artist_names.join("; "),
2445                fed.year,
2446                fed.duration_seconds.map(|d| d as f64),
2447                content_id,
2448                fed.release_title,
2449                fed.track_number,
2450                fed.disc_number,
2451                position,
2452            ],
2453        )? > 0)
2454    }
2455
2456    pub fn fed_playlist_track_by_content_id(
2457        &self,
2458        playlist_sync_id: &str,
2459        content_id: &str,
2460    ) -> Result<Option<FederatedTrack>> {
2461        let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2462            return Ok(None);
2463        };
2464        let conn = self.lock();
2465        conn.query_row(
2466            "SELECT item_id, owner, title, artist_names, featured_artist_names,
2467                    year, duration_seconds, content_id, release_title, track_number, disc_number
2468             FROM fed_playlist_tracks
2469             WHERE playlist_sync_id = ?1 AND content_id = ?2
2470             LIMIT 1",
2471            params![playlist_sync_id, content_id],
2472            fed_track_from_row,
2473        )
2474        .optional()
2475        .map_err(Into::into)
2476    }
2477
2478    pub fn has_playlist_content_reference(
2479        &self,
2480        playlist_sync_id: &str,
2481        content_id: &str,
2482    ) -> Result<bool> {
2483        if self.track_id_by_content_id(content_id)?.is_some() {
2484            return Ok(true);
2485        }
2486        Ok(self
2487            .fed_playlist_track_by_content_id(playlist_sync_id, content_id)?
2488            .is_some())
2489    }
2490
2491    pub fn add_content_id_to_synced_playlist(
2492        &self,
2493        playlist_sync_id: &str,
2494        content_id: &str,
2495    ) -> Result<bool> {
2496        let Some(track_id) = self.track_id_by_content_id(content_id)? else {
2497            return Ok(false);
2498        };
2499        let conn = self.lock();
2500        let playlist_id: Option<i64> = conn
2501            .query_row(
2502                "SELECT id FROM playlists WHERE sync_id = ?1",
2503                [playlist_sync_id],
2504                |row| row.get::<_, i64>(0),
2505            )
2506            .optional()?;
2507        let Some(playlist_id) = playlist_id else {
2508            return Ok(false);
2509        };
2510        let next: i64 = conn.query_row(
2511            "SELECT COALESCE(MAX(position), -1) + 1 FROM playlist_tracks WHERE playlist_id = ?1",
2512            [playlist_id],
2513            |row| row.get(0),
2514        )?;
2515        conn.execute(
2516            "INSERT OR IGNORE INTO playlist_tracks (playlist_id, track_id, position)
2517             VALUES (?1, ?2, ?3)",
2518            params![playlist_id, track_id, next],
2519        )?;
2520        Ok(true)
2521    }
2522
2523    pub fn remove_content_id_from_synced_playlist(
2524        &self,
2525        playlist_sync_id: &str,
2526        content_id: &str,
2527    ) -> Result<()> {
2528        let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2529            return Ok(());
2530        };
2531        let conn = self.lock();
2532        let playlist_id: Option<i64> = conn
2533            .query_row(
2534                "SELECT id FROM playlists WHERE sync_id = ?1",
2535                [playlist_sync_id],
2536                |row| row.get::<_, i64>(0),
2537            )
2538            .optional()?;
2539        if let Some(playlist_id) = playlist_id {
2540            let track_id: Option<i64> = conn
2541                .query_row(
2542                    "SELECT id FROM tracks WHERE content_id = ?1 LIMIT 1",
2543                    [&content_id],
2544                    |row| row.get(0),
2545                )
2546                .optional()?;
2547            if let Some(track_id) = track_id {
2548                conn.execute(
2549                    "DELETE FROM playlist_tracks WHERE playlist_id = ?1 AND track_id = ?2",
2550                    params![playlist_id, track_id],
2551                )?;
2552            }
2553        }
2554        conn.execute(
2555            "DELETE FROM fed_playlist_tracks
2556             WHERE playlist_sync_id = ?1 AND content_id = ?2",
2557            params![playlist_sync_id, content_id],
2558        )?;
2559        Ok(())
2560    }
2561
2562    /// Item ids and content ids of every liked federated track (for the ♥ markers).
2563    pub fn fed_like_ids(&self) -> Result<Vec<String>> {
2564        let conn = self.lock();
2565        let mut statement = conn.prepare("SELECT item_id, content_id FROM fed_likes")?;
2566        let rows = statement
2567            .query_map([], |row| {
2568                Ok((row.get::<_, String>(0)?, row.get::<_, Option<String>>(1)?))
2569            })?
2570            .collect::<rusqlite::Result<Vec<_>>>()?;
2571        let mut keys = Vec::new();
2572        for (item_id, content_id) in rows {
2573            keys.push(item_id);
2574            if let Some(content_id) = content_id
2575                .as_deref()
2576                .and_then(music_dht::normalize_content_id)
2577            {
2578                keys.push(content_id);
2579            }
2580        }
2581        Ok(keys)
2582    }
2583
2584    /// Toggles a like on a federated track; returns the resulting state.
2585    pub fn toggle_fed_like(&self, fed: &FederatedTrack) -> Result<bool> {
2586        let content_id = fed
2587            .content_id
2588            .as_deref()
2589            .and_then(music_dht::normalize_content_id);
2590        let mut conn = self.lock();
2591        let tx = conn.transaction()?;
2592        let removed = match content_id.as_deref() {
2593            Some(content_id) => {
2594                let removed_fed = tx.execute(
2595                    "DELETE FROM fed_likes WHERE item_id = ?1 OR content_id = ?2",
2596                    params![fed.item_id, content_id],
2597                )?;
2598                let removed_local = tx.execute(
2599                    "DELETE FROM likes
2600                     WHERE track_id IN (
2601                        SELECT id FROM tracks WHERE content_id = ?1
2602                     )",
2603                    [content_id],
2604                )?;
2605                removed_fed + removed_local
2606            }
2607            None => tx.execute("DELETE FROM fed_likes WHERE item_id = ?1", [&fed.item_id])?,
2608        };
2609        if removed > 0 {
2610            tx.commit()?;
2611            return Ok(false);
2612        }
2613        if let Some(content_id) = content_id.as_deref() {
2614            tx.execute(
2615                "DELETE FROM fed_likes WHERE content_id = ?1 AND item_id != ?2",
2616                params![content_id, fed.item_id],
2617            )?;
2618        }
2619        tx.execute(
2620            "INSERT INTO fed_likes (item_id, owner, title, artist_names,
2621                featured_artist_names, year, duration_seconds, content_id,
2622                release_title, track_number, disc_number)
2623             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)",
2624            params![
2625                fed.item_id,
2626                fed.owner,
2627                fed.title,
2628                fed.artist_names.join("; "),
2629                fed.featured_artist_names.join("; "),
2630                fed.year,
2631                fed.duration_seconds.map(|d| d as f64),
2632                content_id,
2633                fed.release_title,
2634                fed.track_number,
2635                fed.disc_number,
2636            ],
2637        )?;
2638        tx.commit()?;
2639        Ok(true)
2640    }
2641
2642    pub fn fed_like_by_content_id(&self, content_id: &str) -> Result<Option<FederatedTrack>> {
2643        let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2644            return Ok(None);
2645        };
2646        let conn = self.lock();
2647        let mut statement = conn.prepare(
2648            "SELECT item_id, owner, title, artist_names, featured_artist_names,
2649                    year, duration_seconds, content_id, release_title, track_number, disc_number
2650             FROM fed_likes
2651             WHERE content_id = ?1
2652             ORDER BY COALESCE(liked_hlc_ms,
2653                               CAST(strftime('%s', liked_at) AS INTEGER) * 1000,
2654                               0) DESC
2655             LIMIT 1",
2656        )?;
2657        let track = statement
2658            .query_row([content_id], |row| {
2659                let artists: String = row.get(3)?;
2660                Ok(FederatedTrack {
2661                    item_id: row.get(0)?,
2662                    owner: row.get(1)?,
2663                    own: false,
2664                    title: row.get(2)?,
2665                    artist_names: artists
2666                        .split("; ")
2667                        .filter(|name| !name.is_empty())
2668                        .map(str::to_string)
2669                        .collect(),
2670                    featured_artist_names: row
2671                        .get::<_, String>(4)?
2672                        .split("; ")
2673                        .filter(|name| !name.is_empty())
2674                        .map(str::to_string)
2675                        .collect(),
2676                    year: row.get(5)?,
2677                    duration_seconds: row.get::<_, Option<f64>>(6)?.map(|d| d.round() as i64),
2678                    content_id: row.get(7)?,
2679                    release_title: row.get(8)?,
2680                    track_number: row.get(9)?,
2681                    disc_number: row.get(10)?,
2682                })
2683            })
2684            .optional()?;
2685        Ok(track)
2686    }
2687
2688    pub fn upsert_synced_fed_like(&self, fed: &FederatedTrack, liked_hlc_ms: i64) -> Result<bool> {
2689        let Some(content_id) = fed
2690            .content_id
2691            .as_deref()
2692            .and_then(music_dht::normalize_content_id)
2693        else {
2694            return Ok(false);
2695        };
2696        let conn = self.lock();
2697        let duplicate_rows = conn.execute(
2698            "DELETE FROM fed_likes WHERE content_id = ?1 AND item_id != ?2",
2699            params![content_id, fed.item_id],
2700        )?;
2701        let existing: Option<FederatedLikeState> = conn
2702            .query_row(
2703                "SELECT owner, title, artist_names, featured_artist_names,
2704                        year, duration_seconds, release_title, track_number, disc_number,
2705                        liked_hlc_ms
2706                 FROM fed_likes
2707                 WHERE item_id = ?1",
2708                [&fed.item_id],
2709                |row| {
2710                    Ok((
2711                        row.get(0)?,
2712                        row.get(1)?,
2713                        row.get(2)?,
2714                        row.get(3)?,
2715                        row.get(4)?,
2716                        row.get::<_, Option<f64>>(5)?.map(|d| d.round() as i64),
2717                        row.get(6)?,
2718                        row.get(7)?,
2719                        row.get(8)?,
2720                        row.get(9)?,
2721                    ))
2722                },
2723            )
2724            .optional()?;
2725        let incoming = (
2726            fed.owner.clone(),
2727            fed.title.clone(),
2728            fed.artist_names.join("; "),
2729            fed.featured_artist_names.join("; "),
2730            fed.year,
2731            fed.duration_seconds,
2732            fed.release_title.clone(),
2733            fed.track_number,
2734            fed.disc_number,
2735            Some(liked_hlc_ms),
2736        );
2737        if duplicate_rows == 0 && existing.as_ref() == Some(&incoming) {
2738            return Ok(false);
2739        }
2740        conn.execute(
2741            "INSERT INTO fed_likes (item_id, owner, title, artist_names,
2742                featured_artist_names, year, duration_seconds, content_id,
2743                release_title, track_number, disc_number, liked_at, liked_hlc_ms)
2744             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11,
2745                     datetime(?12 / 1000, 'unixepoch'), ?12)
2746             ON CONFLICT(item_id) DO UPDATE SET
2747                owner = excluded.owner,
2748                title = excluded.title,
2749                artist_names = excluded.artist_names,
2750                featured_artist_names = excluded.featured_artist_names,
2751                year = excluded.year,
2752                duration_seconds = excluded.duration_seconds,
2753                content_id = excluded.content_id,
2754                release_title = excluded.release_title,
2755                track_number = excluded.track_number,
2756                disc_number = excluded.disc_number,
2757                liked_at = excluded.liked_at,
2758                liked_hlc_ms = excluded.liked_hlc_ms",
2759            params![
2760                fed.item_id,
2761                fed.owner,
2762                fed.title,
2763                fed.artist_names.join("; "),
2764                fed.featured_artist_names.join("; "),
2765                fed.year,
2766                fed.duration_seconds.map(|d| d as f64),
2767                content_id,
2768                fed.release_title,
2769                fed.track_number,
2770                fed.disc_number,
2771                liked_hlc_ms,
2772            ],
2773        )?;
2774        Ok(true)
2775    }
2776
2777    pub fn remove_fed_like_by_content_id(&self, content_id: &str) -> Result<bool> {
2778        let Some(content_id) = music_dht::normalize_content_id(content_id) else {
2779            return Ok(false);
2780        };
2781        let conn = self.lock();
2782        Ok(conn.execute("DELETE FROM fed_likes WHERE content_id = ?1", [content_id])? > 0)
2783    }
2784
2785    /// Moves a federated like onto a freshly imported local track. Returns
2786    /// whether a transfer happened.
2787    pub fn transfer_fed_like(&self, item_id: &str, track_id: i64) -> Result<bool> {
2788        let conn = self.lock();
2789        let liked: Option<(String, Option<i64>)> = conn
2790            .query_row(
2791                "SELECT liked_at, liked_hlc_ms FROM fed_likes WHERE item_id = ?1",
2792                [item_id],
2793                |row| Ok((row.get(0)?, row.get(1)?)),
2794            )
2795            .optional()?;
2796        let removed = conn.execute("DELETE FROM fed_likes WHERE item_id = ?1", [item_id])?;
2797        if removed == 0 {
2798            return Ok(false);
2799        }
2800        if let Some((liked_at, liked_hlc_ms)) = liked {
2801            conn.execute(
2802                "INSERT INTO likes (track_id, liked_at, liked_hlc_ms)
2803                 VALUES (?1, ?2, ?3)
2804                 ON CONFLICT(track_id) DO UPDATE SET
2805                    liked_at = excluded.liked_at,
2806                    liked_hlc_ms = excluded.liked_hlc_ms",
2807                params![track_id, liked_at, liked_hlc_ms],
2808            )?;
2809        } else {
2810            conn.execute(
2811                "INSERT OR IGNORE INTO likes (track_id) VALUES (?1)",
2812                [track_id],
2813            )?;
2814        }
2815        Ok(true)
2816    }
2817
2818    /// Idempotently materialize a portable trusted-device listen event.
2819    pub fn apply_listen_event(
2820        &self,
2821        event: &music_dht::device_sync::ListenEvent,
2822        origin_device_id: &str,
2823    ) -> Result<bool> {
2824        if !event.should_record() || origin_device_id.trim().is_empty() {
2825            return Ok(false);
2826        }
2827        let content_id = music_dht::normalize_content_id(&event.content_id)
2828            .context("invalid listen content id")?;
2829        let conn = self.lock();
2830        let local_track_id: Option<i64> = conn
2831            .query_row(
2832                "SELECT id FROM tracks WHERE content_id = ?1 LIMIT 1",
2833                [&content_id],
2834                |row| row.get(0),
2835            )
2836            .optional()?;
2837        let inserted = conn.execute(
2838            "INSERT OR IGNORE INTO listen_events
2839                (listen_id, content_id, local_track_id, origin_device_id,
2840                 started_at_ms, listened_ms, track_duration_ms, ended_reason,
2841                 qualified, metadata_json)
2842             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)",
2843            params![
2844                event.listen_id,
2845                content_id,
2846                local_track_id,
2847                origin_device_id,
2848                event.started_at_ms,
2849                event.listened_ms,
2850                event.track_duration_ms,
2851                serde_json::to_string(&event.ended_reason)?,
2852                i64::from(event.qualifies_as_play()),
2853                serde_json::to_string(&event.track)?,
2854            ],
2855        )?;
2856        Ok(inserted > 0)
2857    }
2858
2859    /// Most recent qualified listens, including tracks that are not present
2860    /// in this device's local library.
2861    pub fn listen_history(&self, limit: usize) -> Result<Vec<ListenHistoryEntry>> {
2862        let conn = self.lock();
2863        let mut stmt = conn.prepare(
2864            "SELECT listen_id, content_id, origin_device_id, started_at_ms, metadata_json
2865             FROM listen_events
2866             WHERE qualified = 1
2867             ORDER BY started_at_ms DESC, listen_id DESC
2868             LIMIT ?1",
2869        )?;
2870        let rows = stmt
2871            .query_map([limit.min(i64::MAX as usize) as i64], |row| {
2872                Ok((
2873                    row.get::<_, String>(0)?,
2874                    row.get::<_, String>(1)?,
2875                    row.get::<_, String>(2)?,
2876                    row.get::<_, i64>(3)?,
2877                    row.get::<_, String>(4)?,
2878                ))
2879            })?
2880            .collect::<rusqlite::Result<Vec<_>>>()?;
2881        rows.into_iter()
2882            .map(
2883                |(listen_id, content_id, origin_device_id, started_at_ms, metadata_json)| {
2884                    let metadata: music_dht::device_sync::ListenTrackMetadata =
2885                        serde_json::from_str(&metadata_json)
2886                            .context("invalid listen history metadata")?;
2887                    let mut artists = metadata.artist_names;
2888                    artists.extend(metadata.featured_artist_names);
2889                    Ok(ListenHistoryEntry {
2890                        listen_id,
2891                        content_id,
2892                        title: metadata.title,
2893                        artist: artists.join(", "),
2894                        origin_device_id,
2895                        started_at_ms,
2896                    })
2897                },
2898            )
2899            .collect()
2900    }
2901
2902    // -----------------------------------------------------------------
2903    // Editing & deleting
2904    // -----------------------------------------------------------------
2905
2906    pub fn update_track(&self, id: i64, edit: &TrackEdit) -> Result<()> {
2907        let mut conn = self.lock();
2908        let tx = conn.transaction()?;
2909        tx.execute(
2910            "UPDATE tracks SET title = ?2, track_number = ?3, disc_number = ?4 WHERE id = ?1",
2911            params![id, edit.title, edit.track_number, edit.disc_number],
2912        )?;
2913        // The cover is a release attribute; editing it from a track updates
2914        // the release cover (what every view shows for this track).
2915        tx.execute(
2916            "UPDATE releases SET cover_path = ?2
2917             WHERE id = (SELECT release_id FROM tracks WHERE id = ?1)",
2918            params![id, edit.cover_path],
2919        )?;
2920        tx.execute("DELETE FROM track_artists WHERE track_id = ?1", [id])?;
2921        link_track_artists(&tx, id, &edit.artists, "main")?;
2922        link_track_artists(&tx, id, &edit.featured_artists, "featured")?;
2923        tx.commit()?;
2924        Ok(())
2925    }
2926
2927    pub fn update_release(&self, id: i64, edit: &ReleaseEdit) -> Result<()> {
2928        let mut conn = self.lock();
2929        let tx = conn.transaction()?;
2930        tx.execute(
2931            "UPDATE releases SET title = ?2, release_type = ?3, year = ?4 WHERE id = ?1",
2932            params![id, edit.title, edit.release_type, edit.year],
2933        )?;
2934        // An empty artist list means "keep the current artists".
2935        if !edit.artists.is_empty() {
2936            tx.execute("DELETE FROM release_artists WHERE release_id = ?1", [id])?;
2937            for (position, name) in edit.artists.iter().enumerate() {
2938                let artist_id = find_or_create_artist(&tx, name)?;
2939                tx.execute(
2940                    "INSERT OR IGNORE INTO release_artists (release_id, artist_id, position)
2941                     VALUES (?1, ?2, ?3)",
2942                    params![id, artist_id, position as i64],
2943                )?;
2944            }
2945        }
2946        tx.commit()?;
2947        Ok(())
2948    }
2949
2950    pub fn update_artist(&self, id: i64, name: &str, image_path: Option<&str>) -> Result<()> {
2951        let conn = self.lock();
2952        let taken: Option<i64> = conn
2953            .query_row(
2954                "SELECT id FROM artists WHERE name = ?1 COLLATE NOCASE AND id != ?2",
2955                params![name, id],
2956                |row| row.get(0),
2957            )
2958            .optional()?;
2959        if taken.is_some() {
2960            anyhow::bail!("an artist named \"{name}\" already exists");
2961        }
2962        conn.execute(
2963            "UPDATE artists SET name = ?2, image_path = ?3 WHERE id = ?1",
2964            params![id, name, image_path],
2965        )?;
2966        Ok(())
2967    }
2968
2969    /// Artist id by display name (case-insensitive), for catalog requests.
2970    pub fn artist_id_by_name(&self, name: &str) -> Result<Option<i64>> {
2971        let conn = self.lock();
2972        Ok(conn
2973            .query_row(
2974                "SELECT id FROM artists WHERE norm(name) = norm(?1) LIMIT 1",
2975                [name],
2976                |row| row.get(0),
2977            )
2978            .optional()?)
2979    }
2980
2981    /// Cover path of an artist's release, matched by names (for the peer
2982    /// catalog image protocol).
2983    pub fn release_cover_by_names(&self, artist: &str, release: &str) -> Result<Option<String>> {
2984        let conn = self.lock();
2985        Ok(conn
2986            .query_row(
2987                "SELECT r.cover_path FROM releases r
2988                 JOIN release_artists ra ON ra.release_id = r.id
2989                 JOIN artists a ON a.id = ra.artist_id
2990                 WHERE norm(a.name) = norm(?1) AND norm(r.title) = norm(?2)
2991                 LIMIT 1",
2992                params![artist, release],
2993                |row| row.get(0),
2994            )
2995            .optional()?
2996            .flatten())
2997    }
2998
2999    /// Image of one artist, for the federation metadata exchange.
3000    pub fn artist_image(&self, artist_id: i64) -> Result<Option<String>> {
3001        let conn = self.lock();
3002        Ok(conn
3003            .query_row(
3004                "SELECT image_path FROM artists WHERE id = ?1",
3005                [artist_id],
3006                |row| row.get(0),
3007            )
3008            .optional()?
3009            .flatten())
3010    }
3011
3012    /// `true` when the artist exists and has no image yet.
3013    pub fn artist_image_missing(&self, name: &str) -> Result<bool> {
3014        let conn = self.lock();
3015        let missing: Option<bool> = conn
3016            .query_row(
3017                "SELECT image_path IS NULL FROM artists WHERE norm(name) = norm(?1)",
3018                [name],
3019                |row| row.get(0),
3020            )
3021            .optional()?;
3022        Ok(missing.unwrap_or(false))
3023    }
3024
3025    /// Sets an artist's image (matched by name) unless one is already set.
3026    /// Returns whether the image was applied.
3027    pub fn set_artist_image_if_missing(&self, name: &str, image_path: &str) -> Result<bool> {
3028        let conn = self.lock();
3029        let changed = conn.execute(
3030            "UPDATE artists SET image_path = ?2
3031             WHERE norm(name) = norm(?1) AND image_path IS NULL",
3032            params![name, image_path],
3033        )?;
3034        Ok(changed > 0)
3035    }
3036
3037    pub fn delete_track(&self, id: i64) -> Result<()> {
3038        let mut conn = self.lock();
3039        let tx = conn.transaction()?;
3040        tx.execute("DELETE FROM tracks WHERE id = ?1", [id])?;
3041        cleanup_empty_releases(&tx)?;
3042        tx.commit()?;
3043        Ok(())
3044    }
3045
3046    pub fn delete_release(&self, id: i64) -> Result<()> {
3047        let conn = self.lock();
3048        conn.execute("DELETE FROM releases WHERE id = ?1", [id])?;
3049        Ok(())
3050    }
3051
3052    /// Delete an artist together with everything that only existed because
3053    /// of them: tracks left without a main artist and releases left without
3054    /// tracks. Collaborations with other artists survive.
3055    pub fn delete_artist(&self, id: i64) -> Result<()> {
3056        let mut conn = self.lock();
3057        let tx = conn.transaction()?;
3058        tx.execute("DELETE FROM artists WHERE id = ?1", [id])?;
3059        tx.execute(
3060            "DELETE FROM tracks WHERE id NOT IN
3061                (SELECT track_id FROM track_artists WHERE role = 'main')",
3062            [],
3063        )?;
3064        cleanup_empty_releases(&tx)?;
3065        tx.commit()?;
3066        Ok(())
3067    }
3068}
3069
3070/// Produces a filesystem-portable component for managed music paths.
3071pub fn storage_name(value: &str, fallback: &str) -> String {
3072    let cleaned: String = value
3073        .chars()
3074        .map(|character| match character {
3075            '/' | '\\' | ':' | '*' | '?' | '"' | '<' | '>' | '|' => '_',
3076            character if character.is_control() => '_',
3077            character => character,
3078        })
3079        .collect();
3080    let cleaned = cleaned.trim().trim_matches('.');
3081    let mut shortened: String = cleaned.chars().take(120).collect();
3082    if shortened.is_empty() {
3083        return fallback.to_string();
3084    }
3085    let windows_base = shortened
3086        .split('.')
3087        .next()
3088        .unwrap_or_default()
3089        .to_ascii_uppercase();
3090    let windows_reserved = matches!(windows_base.as_str(), "CON" | "PRN" | "AUX" | "NUL")
3091        || windows_base
3092            .strip_prefix("COM")
3093            .or_else(|| windows_base.strip_prefix("LPT"))
3094            .is_some_and(|number| number.len() == 1 && matches!(number.as_bytes()[0], b'1'..=b'9'));
3095    if windows_reserved {
3096        shortened.insert(0, '_');
3097    }
3098    shortened
3099}
3100
3101fn unique_destination(requested: PathBuf, id: i64, reserved: &mut HashSet<PathBuf>) -> PathBuf {
3102    if !requested.exists() && reserved.insert(requested.clone()) {
3103        return requested;
3104    }
3105    let stem = requested
3106        .file_stem()
3107        .unwrap_or_else(|| std::ffi::OsStr::new("file"))
3108        .to_string_lossy();
3109    let extension = requested.extension().map(|value| value.to_os_string());
3110    let mut candidate = requested.with_file_name(format!("{stem}-{id}"));
3111    if let Some(extension) = extension {
3112        candidate.set_extension(extension);
3113    }
3114    let mut suffix = 2usize;
3115    while candidate.exists() || !reserved.insert(candidate.clone()) {
3116        candidate = requested.with_file_name(format!("{stem}-{id}-{suffix}"));
3117        if let Some(extension) = requested.extension() {
3118            candidate.set_extension(extension);
3119        }
3120        suffix += 1;
3121    }
3122    candidate
3123}
3124
3125fn copy_file_exclusive(source: &Path, destination: &Path) -> Result<()> {
3126    let mut input =
3127        std::fs::File::open(source).with_context(|| format!("opening {}", source.display()))?;
3128    let mut output = std::fs::OpenOptions::new()
3129        .write(true)
3130        .create_new(true)
3131        .open(destination)
3132        .with_context(|| format!("creating {}", destination.display()))?;
3133    let result = std::io::copy(&mut input, &mut output)
3134        .with_context(|| format!("copying {}", source.display()))
3135        .and_then(|_| {
3136            output
3137                .sync_all()
3138                .with_context(|| format!("syncing {}", destination.display()))
3139        });
3140    if let Err(error) = result {
3141        drop(output);
3142        let _ = std::fs::remove_file(destination);
3143        return Err(error);
3144    }
3145    Ok(())
3146}
3147
3148fn remove_empty_directories(root: &Path) {
3149    let Ok(entries) = std::fs::read_dir(root) else {
3150        return;
3151    };
3152    for entry in entries.flatten() {
3153        let path = entry.path();
3154        if path.is_dir() {
3155            remove_empty_directories(&path);
3156        }
3157    }
3158    let _ = std::fs::remove_dir(root);
3159}
3160
3161fn cleanup_empty_releases(tx: &rusqlite::Transaction) -> rusqlite::Result<()> {
3162    tx.execute(
3163        "DELETE FROM releases WHERE id NOT IN (SELECT release_id FROM tracks)",
3164        [],
3165    )?;
3166    Ok(())
3167}
3168
3169fn link_track_artists(
3170    tx: &rusqlite::Transaction,
3171    track_id: i64,
3172    names: &[String],
3173    role: &str,
3174) -> Result<()> {
3175    for (position, name) in names.iter().enumerate() {
3176        let artist_id = find_or_create_artist(tx, name)?;
3177        tx.execute(
3178            "INSERT OR IGNORE INTO track_artists (track_id, artist_id, role, position)
3179             VALUES (?1, ?2, ?3, ?4)",
3180            params![track_id, artist_id, role, position as i64],
3181        )?;
3182    }
3183    Ok(())
3184}
3185
3186pub(crate) fn find_or_create_artist(conn: &Connection, name: &str) -> Result<i64> {
3187    let name = name.trim();
3188    anyhow::ensure!(!name.is_empty(), "artist name is empty");
3189    if let Some(id) = conn
3190        .query_row(
3191            "SELECT id FROM artists WHERE norm(name) = norm(?1) LIMIT 1",
3192            [name],
3193            |row| row.get(0),
3194        )
3195        .optional()?
3196    {
3197        return Ok(id);
3198    }
3199    conn.execute("INSERT INTO artists (name) VALUES (?1)", [name])?;
3200    Ok(conn.last_insert_rowid())
3201}
3202
3203fn release_card_from_row(row: &rusqlite::Row) -> rusqlite::Result<ReleaseCard> {
3204    Ok(ReleaseCard {
3205        id: row.get(0)?,
3206        title: row.get(1)?,
3207        release_type: row.get(2)?,
3208        year: row.get(3)?,
3209        cover_path: row.get(4)?,
3210        track_count: row.get(5)?,
3211        availability: Availability::Local,
3212    })
3213}
3214
3215fn rank_search_results(results: &mut SearchResults, normalized_query: &str) {
3216    results
3217        .artists
3218        .sort_by_key(|artist| exact_match_rank(&artist.name, normalized_query));
3219    results
3220        .releases
3221        .sort_by_key(|release| exact_match_rank(&release.title, normalized_query));
3222    results
3223        .tracks
3224        .sort_by_key(|track| track_match_rank(track, normalized_query));
3225}
3226
3227fn exact_match_rank(value: &str, normalized_query: &str) -> u8 {
3228    if music_dht::normalize_name(value) == normalized_query {
3229        0
3230    } else {
3231        1
3232    }
3233}
3234
3235fn track_match_rank(track: &TrackItem, normalized_query: &str) -> u8 {
3236    if music_dht::normalize_name(&track.title) == normalized_query {
3237        return 0;
3238    }
3239    if music_dht::normalize_name(&track.release_title) == normalized_query {
3240        return 1;
3241    }
3242    if track
3243        .artists
3244        .iter()
3245        .chain(track.featured_artists.iter())
3246        .any(|artist| music_dht::normalize_name(&artist.name) == normalized_query)
3247    {
3248        return 2;
3249    }
3250    3
3251}
3252
3253/// Run a track query built on TRACK_COLUMNS and attach artist lists.
3254fn query_tracks(
3255    conn: &Connection,
3256    sql: &str,
3257    params: &[&dyn rusqlite::types::ToSql],
3258) -> Result<Vec<TrackItem>> {
3259    let mut statement = conn.prepare(sql)?;
3260    let mut tracks = statement
3261        .query_map(params, |row| {
3262            Ok(TrackItem {
3263                id: row.get(0)?,
3264                title: row.get(1)?,
3265                track_number: row.get(2)?,
3266                disc_number: row.get(3)?,
3267                duration_seconds: row.get(4)?,
3268                release_id: row.get(5)?,
3269                release_title: row.get(6)?,
3270                release_year: row.get(7)?,
3271                cover_path: row.get(8)?,
3272                file_path: row.get(9)?,
3273                audio_format: row.get(10)?,
3274                audio_bitrate: row.get(11)?,
3275                audio_sample_rate: row.get(12)?,
3276                audio_bit_depth: row.get(13)?,
3277                file_size_bytes: row.get(14)?,
3278                content_id: row
3279                    .get::<_, Option<String>>(15)?
3280                    .as_deref()
3281                    .and_then(music_dht::normalize_content_id),
3282                play_count: row.get(16)?,
3283                artists: Vec::new(),
3284                featured_artists: Vec::new(),
3285                fed: None,
3286            })
3287        })?
3288        .collect::<rusqlite::Result<Vec<_>>>()?;
3289    let mut artist_statement = conn.prepare(
3290        "SELECT a.id, a.name, ta.role FROM artists a
3291         JOIN track_artists ta ON ta.artist_id = a.id
3292         WHERE ta.track_id = ?1
3293         ORDER BY ta.position",
3294    )?;
3295    for track in &mut tracks {
3296        let rows = artist_statement.query_map([track.id], |row| {
3297            Ok((
3298                ArtistRef {
3299                    id: row.get(0)?,
3300                    name: row.get(1)?,
3301                },
3302                row.get::<_, String>(2)?,
3303            ))
3304        })?;
3305        for row in rows {
3306            let (artist, role) = row?;
3307            if role == "featured" {
3308                track.featured_artists.push(artist);
3309            } else {
3310                track.artists.push(artist);
3311            }
3312        }
3313    }
3314    Ok(tracks)
3315}
3316
3317fn pending_track(fed: &FederatedTrack) -> TrackItem {
3318    let refs = |names: &[String]| {
3319        names
3320            .iter()
3321            .map(|name| ArtistRef {
3322                id: -1,
3323                name: name.clone(),
3324            })
3325            .collect()
3326    };
3327    TrackItem {
3328        id: NEXT_PENDING_TRACK_ID.fetch_sub(1, Ordering::Relaxed),
3329        title: fed.title.clone(),
3330        track_number: fed.track_number,
3331        disc_number: fed.disc_number,
3332        duration_seconds: fed.duration_seconds.unwrap_or(0) as f64,
3333        artists: refs(&fed.artist_names),
3334        featured_artists: refs(&fed.featured_artist_names),
3335        release_id: -1,
3336        release_title: fed
3337            .release_title
3338            .clone()
3339            .unwrap_or_else(|| format!("federation · {}", fed.owner_short())),
3340        release_year: fed.year,
3341        file_path: String::new(),
3342        content_id: fed.content_id.clone(),
3343        cover_path: None,
3344        audio_format: None,
3345        audio_bitrate: None,
3346        audio_sample_rate: None,
3347        audio_bit_depth: None,
3348        file_size_bytes: None,
3349        play_count: 0,
3350        fed: Some(fed.clone()),
3351    }
3352}
3353
3354fn fed_playlist_tracks(
3355    conn: &Connection,
3356    playlist_id: i64,
3357    playlist_sync_id: &str,
3358) -> Result<Vec<(i64, FederatedTrack)>> {
3359    let mut statement = conn.prepare(
3360        "SELECT position, item_id, owner, title, artist_names, featured_artist_names,
3361                year, duration_seconds, content_id, release_title, track_number, disc_number
3362         FROM fed_playlist_tracks f
3363         WHERE f.playlist_sync_id = ?1
3364           AND NOT EXISTS (
3365            SELECT 1 FROM playlist_tracks pt
3366            JOIN tracks t ON t.id = pt.track_id
3367            WHERE pt.playlist_id = ?2
3368              AND t.content_id = f.content_id
3369           )
3370         ORDER BY position, title COLLATE NOCASE",
3371    )?;
3372    let rows = statement.query_map(params![playlist_sync_id, playlist_id], |row| {
3373        Ok((row.get::<_, i64>(0)?, fed_track_from_offset_row(row, 1)?))
3374    })?;
3375    Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
3376}
3377
3378fn fed_track_from_row(row: &rusqlite::Row<'_>) -> rusqlite::Result<FederatedTrack> {
3379    fed_track_from_offset_row(row, 0)
3380}
3381
3382fn fed_track_from_offset_row(
3383    row: &rusqlite::Row<'_>,
3384    offset: usize,
3385) -> rusqlite::Result<FederatedTrack> {
3386    Ok(FederatedTrack {
3387        item_id: row.get(offset)?,
3388        owner: row.get(offset + 1)?,
3389        own: false,
3390        title: row.get(offset + 2)?,
3391        artist_names: split_joined_names(row.get(offset + 3)?),
3392        featured_artist_names: split_joined_names(row.get(offset + 4)?),
3393        year: row.get(offset + 5)?,
3394        duration_seconds: row
3395            .get::<_, Option<f64>>(offset + 6)?
3396            .map(|d| d.round() as i64),
3397        content_id: row.get(offset + 7)?,
3398        release_title: row.get(offset + 8)?,
3399        track_number: row.get(offset + 9)?,
3400        disc_number: row.get(offset + 10)?,
3401    })
3402}
3403
3404fn split_joined_names(names: String) -> Vec<String> {
3405    names
3406        .split("; ")
3407        .filter(|name| !name.is_empty())
3408        .map(str::to_string)
3409        .collect()
3410}
3411
3412/// Escape LIKE wildcards in user input; queries use `ESCAPE '\'`.
3413/// Registers `norm(text)` — Unicode-aware case folding and normalization
3414/// (NFKC, lowercase, punctuation stripped). SQLite's own LIKE/NOCASE only
3415/// fold ASCII, so Cyrillic and other non-Latin names were unsearchable
3416/// without it. Shares the exact algorithm with the federation DHT.
3417fn register_norm_function(conn: &Connection) -> Result<()> {
3418    use rusqlite::functions::FunctionFlags;
3419    conn.create_scalar_function(
3420        "norm",
3421        1,
3422        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
3423        |ctx| {
3424            let value: String = ctx.get(0)?;
3425            Ok(music_dht::normalize_name(&value))
3426        },
3427    )?;
3428    Ok(())
3429}
3430
3431/// Computes the stable Furumi content identifier for a local audio file.
3432pub fn audio_content_id(path: &str) -> Option<String> {
3433    let mut file = std::fs::File::open(path).ok()?;
3434    let mut hasher = blake3::Hasher::new();
3435    std::io::copy(&mut file, &mut hasher).ok()?;
3436    Some(format!("b3:{}", hasher.finalize().to_hex()))
3437}
3438
3439fn directory_size(path: &Path) -> u64 {
3440    let Ok(metadata) = std::fs::metadata(path) else {
3441        return 0;
3442    };
3443    if metadata.is_file() {
3444        return metadata.len();
3445    }
3446    let Ok(entries) = std::fs::read_dir(path) else {
3447        return 0;
3448    };
3449    entries
3450        .filter_map(|entry| entry.ok())
3451        .map(|entry| directory_size(&entry.path()))
3452        .sum()
3453}
3454
3455fn file_size(path: &Path) -> u64 {
3456    std::fs::metadata(path)
3457        .map(|metadata| metadata.len())
3458        .unwrap_or(0)
3459}
3460
3461fn sqlite_database_size(path: &Path) -> u64 {
3462    file_size(path)
3463        .saturating_add(file_size(&path_with_suffix(path, "-wal")))
3464        .saturating_add(file_size(&path_with_suffix(path, "-shm")))
3465}
3466
3467fn path_with_suffix(path: &Path, suffix: &str) -> PathBuf {
3468    let mut value = path.as_os_str().to_os_string();
3469    value.push(suffix);
3470    PathBuf::from(value)
3471}
3472
3473fn now_ms_i64() -> i64 {
3474    std::time::SystemTime::now()
3475        .duration_since(std::time::UNIX_EPOCH)
3476        .map(|duration| duration.as_millis() as i64)
3477        .unwrap_or(0)
3478}
3479
3480fn embedding_to_bytes(vector: &[f32]) -> Vec<u8> {
3481    let mut bytes = Vec::with_capacity(std::mem::size_of_val(vector));
3482    for value in vector {
3483        bytes.extend_from_slice(&value.to_le_bytes());
3484    }
3485    bytes
3486}
3487
3488fn embedding_from_bytes(dimensions: i64, bytes: &[u8]) -> Result<Vec<f32>> {
3489    anyhow::ensure!(dimensions > 0, "stored embedding has invalid dimensions");
3490    let dimensions = dimensions as usize;
3491    anyhow::ensure!(
3492        bytes.len() == dimensions * std::mem::size_of::<f32>(),
3493        "stored embedding byte length does not match its dimensions"
3494    );
3495    let vector: Vec<f32> = bytes
3496        .chunks_exact(4)
3497        .map(|chunk| f32::from_le_bytes([chunk[0], chunk[1], chunk[2], chunk[3]]))
3498        .collect();
3499    anyhow::ensure!(
3500        vector.iter().all(|value| value.is_finite()),
3501        "stored embedding contains a non-finite value"
3502    );
3503    Ok(vector)
3504}
3505
3506fn remote_artist_id(artist_key: &str) -> i64 {
3507    let hash = blake3::hash(artist_key.as_bytes());
3508    let mut bytes = [0u8; 8];
3509    bytes.copy_from_slice(&hash.as_bytes()[..8]);
3510    -(i64::from_be_bytes(bytes) & i64::MAX).max(1)
3511}
3512
3513fn ensure_schema_migrations(conn: &Connection) -> Result<()> {
3514    let fed_like_columns = table_columns(conn, "fed_likes")?;
3515    if !fed_like_columns
3516        .iter()
3517        .any(|column| column == "featured_artist_names")
3518    {
3519        conn.execute(
3520            "ALTER TABLE fed_likes
3521             ADD COLUMN featured_artist_names TEXT NOT NULL DEFAULT ''",
3522            [],
3523        )?;
3524    }
3525    if !fed_like_columns.iter().any(|column| column == "content_id") {
3526        conn.execute("ALTER TABLE fed_likes ADD COLUMN content_id TEXT", [])?;
3527    }
3528    if !fed_like_columns
3529        .iter()
3530        .any(|column| column == "liked_hlc_ms")
3531    {
3532        conn.execute("ALTER TABLE fed_likes ADD COLUMN liked_hlc_ms INTEGER", [])?;
3533    }
3534    let like_columns = table_columns(conn, "likes")?;
3535    if !like_columns.iter().any(|column| column == "liked_hlc_ms") {
3536        conn.execute("ALTER TABLE likes ADD COLUMN liked_hlc_ms INTEGER", [])?;
3537    }
3538    let track_columns = table_columns(conn, "tracks")?;
3539    if !track_columns.iter().any(|column| column == "content_id") {
3540        conn.execute("ALTER TABLE tracks ADD COLUMN content_id TEXT", [])?;
3541    }
3542    let playlist_columns = table_columns(conn, "playlists")?;
3543    if !playlist_columns.iter().any(|column| column == "sync_id") {
3544        conn.execute("ALTER TABLE playlists ADD COLUMN sync_id TEXT", [])?;
3545        conn.execute(
3546            "CREATE UNIQUE INDEX IF NOT EXISTS idx_playlists_sync_id
3547             ON playlists(sync_id)",
3548            [],
3549        )?;
3550    }
3551    let network_artist_columns = table_columns(conn, "network_artist_cache")?;
3552    if !network_artist_columns
3553        .iter()
3554        .any(|column| column == "remote_image_hint")
3555    {
3556        conn.execute(
3557            "ALTER TABLE network_artist_cache ADD COLUMN remote_image_hint TEXT",
3558            [],
3559        )?;
3560    }
3561    let embedding_columns = table_columns(conn, "track_embeddings")?;
3562    if !embedding_columns
3563        .iter()
3564        .any(|column| column == "routing_signature")
3565    {
3566        conn.execute(
3567            "ALTER TABLE track_embeddings ADD COLUMN routing_signature BLOB",
3568            [],
3569        )?;
3570    }
3571    let mut rows = conn.prepare("SELECT id, title FROM playlists WHERE sync_id IS NULL")?;
3572    let missing = rows
3573        .query_map([], |row| {
3574            Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
3575        })?
3576        .collect::<rusqlite::Result<Vec<_>>>()?;
3577    drop(rows);
3578    for (id, title) in missing {
3579        conn.execute(
3580            "UPDATE playlists SET sync_id = ?2 WHERE id = ?1",
3581            params![id, make_playlist_sync_id(id, &title)],
3582        )?;
3583    }
3584    Ok(())
3585}
3586
3587fn table_columns(conn: &Connection, table: &str) -> Result<Vec<String>> {
3588    let mut statement = conn.prepare(&format!("PRAGMA table_info({table})"))?;
3589    Ok(statement
3590        .query_map([], |row| row.get::<_, String>(1))?
3591        .collect::<rusqlite::Result<Vec<_>>>()?)
3592}
3593
3594fn make_playlist_sync_id(id: i64, title: &str) -> String {
3595    let now = std::time::SystemTime::now()
3596        .duration_since(std::time::UNIX_EPOCH)
3597        .map(|d| d.as_nanos())
3598        .unwrap_or(0);
3599    let seed = format!("playlist:{id}:{title}:{now}:{}", std::process::id());
3600    format!("pl_{}", &blake3::hash(seed.as_bytes()).to_hex()[..24])
3601}
3602
3603#[cfg(test)]
3604#[path = "tests.rs"]
3605mod tests;