koan-core 0.60.10

//! Batched and SQL-filtered reads for API layers.
//!
//! Everything here exists so a caller serving many rows at once issues a bounded
//! number of statements: one query per batch of parents rather than one per
//! parent, and `WHERE`/`LIMIT` in SQLite rather than `retain()` in Rust.

use std::collections::{HashMap, HashSet};

use rusqlite::{Connection, ToSql, params, params_from_iter};

use crate::db::connection::DbError;

use super::json_list;
use super::tracks::row_to_track_row;
use super::{AlbumRow, TrackRow};

const TRACK_COLUMNS: &str = "t.id, t.album_id, t.artist_id, a.name, aa.name, al.title,
                t.disc, t.track_number, t.title, t.duration_ms, t.path,
                t.codec, t.sample_rate, t.bit_depth, t.channels, t.bitrate,
                t.genre, t.source, t.remote_id, t.cached_path";

const TRACK_JOINS: &str = "FROM tracks t
         LEFT JOIN artists a ON t.artist_id = a.id
         LEFT JOIN albums al ON t.album_id = al.id
         LEFT JOIN artists aa ON al.artist_id = aa.id";

/// A list bound as one parameter, the JSON array `json_list` makes.
const IN_LIST: &str = "(SELECT value FROM json_each(?))";

/// Wrap a user substring for `LIKE ... ESCAPE '\'`, neutralising `%` and `_`.
fn like_contains(needle: &str) -> String {
    let mut escaped = String::with_capacity(needle.len() + 2);
    escaped.push('%');
    for c in needle.chars() {
        if matches!(c, '\\' | '%' | '_') {
            escaped.push('\\');
        }
        escaped.push(c);
    }
    escaped.push('%');
    escaped
}

// ---------------------------------------------------------------------------
// Batched parent → children loads
// ---------------------------------------------------------------------------

/// Albums for many artists in one query, keyed by artist ID.
pub fn albums_for_artists(
    conn: &Connection,
    artist_ids: &[i64],
) -> Result<HashMap<i64, Vec<AlbumRow>>, DbError> {
    if artist_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let sql = format!(
        "SELECT al.id, al.title, al.artist_id, a.name, al.date,
                al.total_discs, al.total_tracks, al.codec, al.label, al.remote_id,
                al.added_at
         FROM albums al
         LEFT JOIN artists a ON al.artist_id = a.id
         WHERE al.artist_id IN {IN_LIST}
         ORDER BY al.date, al.title COLLATE LIBRARY"
    );
    let mut stmt = conn.prepare_cached(&sql)?;
    let rows = stmt.query_map([json_list(artist_ids)], |row| {
        Ok(AlbumRow {
            id: row.get(0)?,
            title: row.get(1)?,
            artist_id: row.get(2)?,
            artist_name: row.get::<_, Option<String>>(3)?.unwrap_or_default(),
            date: row.get(4)?,
            total_discs: row.get(5)?,
            total_tracks: row.get(6)?,
            codec: row.get(7)?,
            label: row.get(8)?,
            remote_id: row.get(9)?,
            added_at: row.get(10)?,
            on_device: None,
        })
    })?;

    let mut out: HashMap<i64, Vec<AlbumRow>> = HashMap::new();
    for album in rows {
        let album = album?;
        out.entry(album.artist_id).or_default().push(album);
    }
    Ok(out)
}

/// Tracks for many albums in one query, keyed by album ID.
pub fn tracks_for_albums(
    conn: &Connection,
    album_ids: &[i64],
) -> Result<HashMap<i64, Vec<TrackRow>>, DbError> {
    if album_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let sql = format!(
        "SELECT {TRACK_COLUMNS} {TRACK_JOINS} WHERE t.album_id IN {IN_LIST}
         ORDER BY t.disc, t.track_number"
    );
    let mut stmt = conn.prepare_cached(&sql)?;
    let rows = stmt.query_map([json_list(album_ids)], row_to_track_row)?;

    let mut out: HashMap<i64, Vec<TrackRow>> = HashMap::new();
    for track in rows {
        let track = track?;
        if let Some(aid) = track.album_id {
            out.entry(aid).or_default().push(track);
        }
    }
    Ok(out)
}

/// Tracks for many artists in one query, keyed by the requested artist ID.
///
/// A track matches on either its own artist or its album artist, so one row can
/// land under two keys — the map is built by re-checking each requested ID.
///
/// The album-artist half goes through a subquery on `albums` rather than
/// `al.artist_id IN (...)` on the join: an `OR` spanning two tables cannot use
/// an index, and the join form read the whole library for it.
pub fn tracks_for_artists(
    conn: &Connection,
    artist_ids: &[i64],
) -> Result<HashMap<i64, Vec<TrackRow>>, DbError> {
    if artist_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let sql = format!(
        "SELECT {TRACK_COLUMNS}, al.artist_id {TRACK_JOINS}
          WHERE t.artist_id IN {IN_LIST}
             OR t.album_id IN (SELECT id FROM albums WHERE artist_id IN {IN_LIST})
          ORDER BY al.date, al.title, t.disc, t.track_number"
    );
    let mut stmt = conn.prepare_cached(&sql)?;
    let ids = json_list(artist_ids);
    let rows = stmt.query_map([&ids, &ids], |row| {
        Ok((row_to_track_row(row)?, row.get::<_, Option<i64>>(20)?))
    })?;

    let wanted: HashSet<i64> = artist_ids.iter().copied().collect();
    let mut out: HashMap<i64, Vec<TrackRow>> = HashMap::new();
    for row in rows {
        let (track, album_artist_id) = row?;
        for key in [track.artist_id, album_artist_id].into_iter().flatten() {
            if wanted.contains(&key)
                && !out.entry(key).or_default().iter().any(|t| t.id == track.id)
            {
                out.entry(key).or_default().push(track.clone());
            }
        }
    }
    Ok(out)
}

// ---------------------------------------------------------------------------
// Aggregates — counted in SQLite rather than by materialising rows
// ---------------------------------------------------------------------------

#[derive(Debug, Clone, Copy, Default)]
pub struct AlbumStats {
    pub track_count: i64,
    pub total_duration_ms: i64,
}

/// Track count and summed duration per album, in one query.
pub fn album_stats(
    conn: &Connection,
    album_ids: &[i64],
) -> Result<HashMap<i64, AlbumStats>, DbError> {
    if album_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let sql = format!(
        "SELECT album_id, COUNT(*), COALESCE(SUM(duration_ms), 0)
         FROM tracks WHERE album_id IN {IN_LIST} GROUP BY album_id"
    );
    let mut stmt = conn.prepare_cached(&sql)?;
    let rows = stmt.query_map([json_list(album_ids)], |row| {
        Ok((
            row.get::<_, i64>(0)?,
            AlbumStats {
                track_count: row.get(1)?,
                total_duration_ms: row.get(2)?,
            },
        ))
    })?;
    rows.collect::<Result<HashMap<_, _>, _>>()
        .map_err(Into::into)
}

#[derive(Debug, Clone, Copy, Default)]
pub struct ArtistStats {
    pub album_count: i64,
    pub track_count: i64,
}

/// Album and track counts per artist, in two grouped queries rather than one
/// full row fetch per artist per field.
pub fn artist_stats(
    conn: &Connection,
    artist_ids: &[i64],
) -> Result<HashMap<i64, ArtistStats>, DbError> {
    if artist_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let ids = json_list(artist_ids);
    let mut out: HashMap<i64, ArtistStats> = artist_ids
        .iter()
        .map(|&id| (id, ArtistStats::default()))
        .collect();

    let album_sql = format!(
        "SELECT artist_id, COUNT(*) FROM albums WHERE artist_id IN {IN_LIST} GROUP BY artist_id"
    );
    let mut stmt = conn.prepare_cached(&album_sql)?;
    let rows = stmt.query_map([&ids], |row| {
        Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?))
    })?;
    for row in rows {
        let (id, count) = row?;
        out.entry(id).or_default().album_count = count;
    }

    // Mirrors `tracks_for_artist`: own artist or album artist, counted once.
    let track_sql = format!(
        "SELECT k.id, COUNT(DISTINCT t.id)
         FROM artists k
         JOIN tracks t ON t.artist_id = k.id OR t.album_id IN (
             SELECT al.id FROM albums al WHERE al.artist_id = k.id
         )
         WHERE k.id IN {IN_LIST}
         GROUP BY k.id"
    );
    let mut stmt = conn.prepare_cached(&track_sql)?;
    let rows = stmt.query_map([&ids], |row| {
        Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?))
    })?;
    for row in rows {
        let (id, count) = row?;
        out.entry(id).or_default().track_count = count;
    }

    Ok(out)
}

/// Which of the given tracks are favourited — one query instead of one per
/// track.
pub fn favourite_track_ids(
    conn: &Connection,
    user: i64,
    track_ids: &[i64],
) -> Result<HashSet<i64>, DbError> {
    if track_ids.is_empty() {
        return Ok(HashSet::new());
    }
    let sql =
        format!("SELECT track_id FROM favourites WHERE user_id = ?1 AND track_id IN {IN_LIST}");
    let mut stmt = conn.prepare_cached(&sql)?;
    let user = super::auth::resolve_user(conn, user)?;
    let rows = stmt.query_map(params![user, json_list(track_ids)], |row| row.get(0))?;
    rows.collect::<Result<HashSet<_>, _>>().map_err(Into::into)
}

/// The album each of the given tracks belongs to, keyed by track ID.
///
/// Tracks with no album are absent from the map rather than present with a
/// `None`; a caller asking which album to draw has the same answer either way.
pub fn album_ids_for_tracks(
    conn: &Connection,
    track_ids: &[i64],
) -> Result<HashMap<i64, i64>, DbError> {
    if track_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let sql =
        format!("SELECT id, album_id FROM tracks WHERE id IN {IN_LIST} AND album_id IS NOT NULL");
    let mut stmt = conn.prepare_cached(&sql)?;
    let rows = stmt.query_map([json_list(track_ids)], |row| {
        Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?))
    })?;
    rows.collect::<Result<HashMap<_, _>, _>>()
        .map_err(Into::into)
}

/// Where each track's bytes are: on the server, on this machine, or both.
///
/// One query for a whole queue. The same reading `Track` gives, so a row in the
/// queue and the same row in an album agree about what they are — asked here
/// per queue rather than per row, which is what a list of a thousand would
/// otherwise cost.
pub fn sources_for_tracks(
    conn: &Connection,
    track_ids: &[i64],
) -> Result<HashMap<i64, (bool, bool)>, DbError> {
    if track_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let sql = format!(
        "SELECT id, remote_id IS NOT NULL, COALESCE(cached_path, path) IS NOT NULL \
         FROM tracks WHERE id IN {IN_LIST}"
    );
    let mut stmt = conn.prepare_cached(&sql)?;
    let rows = stmt.query_map([json_list(track_ids)], |row| {
        Ok((
            row.get::<_, i64>(0)?,
            (row.get::<_, bool>(1)?, row.get::<_, bool>(2)?),
        ))
    })?;
    rows.collect::<Result<HashMap<_, _>, _>>()
        .map_err(Into::into)
}

// ---------------------------------------------------------------------------
// SQL-side track filtering
// ---------------------------------------------------------------------------

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum TrackOrder {
    ArtistAlbumDiscTrack,
    Title,
    Artist,
    Album,
    Duration,
    /// When last played, by `TrackFilter::played`; without it, `Title`.
    LastPlayed,
    /// The closest match to `TrackFilter::search` first, by title, artist or
    /// album (see `search::match_rank`), then as `ArtistAlbumDiscTrack`.
    /// Without a search, in the order of `TrackFilter::ids`; without either,
    /// `ArtistAlbumDiscTrack`.
    Relevance,
}

#[derive(Debug, Clone, Default)]
pub struct TrackFilter {
    pub ids: Option<Vec<i64>>,
    /// FTS5 query. Applied as a subquery so it composes with the other filters.
    /// When no track matches it, the closest fuzzy matches instead
    /// (`search::fuzzy_ids`).
    pub search: Option<String>,
    pub album_id: Option<i64>,
    pub artist_ids: Option<Vec<i64>>,
    pub title: Option<String>,
    pub artist_name: Option<String>,
    pub album_title: Option<String>,
    pub genre: Option<String>,
    pub codec: Option<String>,
    pub source: Option<String>,
    pub year_start: Option<i32>,
    pub year_end: Option<i32>,
    pub min_sample_rate: Option<i32>,
    pub min_bit_depth: Option<i32>,
    pub channels: Option<i32>,
    pub min_duration_ms: Option<i64>,
    pub max_duration_ms: Option<i64>,
    /// Only tracks this user has favourited.
    pub favourites_of: Option<i64>,
    /// Only tracks played since then.
    pub played: Option<super::history::PlayedSince>,
    /// Only tracks that can play here: downloaded, or a file in the library.
    pub on_device: bool,
}

/// Fetch a page of tracks matching `filter`.
///
/// Both the filtering and the windowing happen in SQLite: the caller never sees
/// more than `limit` rows regardless of library size.
pub fn filter_tracks(
    conn: &Connection,
    filter: &TrackFilter,
    order: TrackOrder,
    descending: bool,
    limit: u32,
    offset: u32,
) -> Result<Vec<TrackRow>, DbError> {
    if let Some(fallback) = fuzzy_fallback(conn, filter)? {
        return filter_tracks(conn, &fallback, order, descending, limit, offset);
    }
    let Some((body, mut binds)) = track_body(conn, filter, order == TrackOrder::Relevance)? else {
        return Ok(Vec::new());
    };
    let order = match order {
        TrackOrder::LastPlayed if filter.played.is_none() => TrackOrder::Title,
        order => order,
    };
    let dir = if descending { "DESC" } else { "ASC" };
    let shelved =
        format!("a.name {dir}, al.date {dir}, al.title {dir}, t.disc {dir}, t.track_number {dir}");
    let order_by = match order {
        TrackOrder::ArtistAlbumDiscTrack => shelved,
        TrackOrder::Relevance => match (&filter.search, &filter.ids) {
            (Some(query), _) => {
                let rank = super::search::match_rank_binds(query);
                for _ in 0..3 {
                    binds.extend(rank.iter().map(|b| Box::new(b.clone()) as Box<dyn ToSql>));
                }
                format!(
                    "min({}, {}, {}) {dir}, {shelved}",
                    super::search::match_rank("t.title"),
                    super::search::match_rank("a.name"),
                    super::search::match_rank("al.title"),
                )
            }
            (None, Some(_)) => format!("r.key {dir}"),
            (None, None) => shelved,
        },
        TrackOrder::Title => format!("t.title {dir}"),
        TrackOrder::Artist => {
            format!("a.name {dir}, al.date {dir}, t.disc {dir}, t.track_number {dir}")
        }
        TrackOrder::Album => format!("al.title {dir}, t.disc {dir}, t.track_number {dir}"),
        TrackOrder::Duration => format!("t.duration_ms {dir}"),
        TrackOrder::LastPlayed => format!("p.last {dir}, p.last_id {dir}"),
    };
    let sql = format!(
        "SELECT {TRACK_COLUMNS} {body}
         ORDER BY {order_by}, t.id {dir} LIMIT ? OFFSET ?"
    );
    binds.push(Box::new(limit));
    binds.push(Box::new(offset));

    let mut stmt = conn.prepare(&sql)?;
    let rows = stmt
        .query_map(params_from_iter(binds.iter()), row_to_track_row)?
        .collect::<Result<Vec<_>, _>>()?;
    Ok(rows)
}

/// How many tracks `filter_tracks` would list for `filter`, ignoring paging.
pub fn count_tracks(conn: &Connection, filter: &TrackFilter) -> Result<u64, DbError> {
    if let Some(fallback) = fuzzy_fallback(conn, filter)? {
        return count_tracks(conn, &fallback);
    }
    let Some((body, binds)) = track_body(conn, filter, false)? else {
        return Ok(0);
    };
    let n: i64 = conn.query_row(
        &format!("SELECT COUNT(*) {body}"),
        params_from_iter(binds.iter()),
        |r| r.get(0),
    )?;
    Ok(n as u64)
}

/// What `filter` lists when its search matches no track: the same filter with
/// the fuzzy matches in place of the search, best first. `None` when it has
/// no search or the search finds something.
fn fuzzy_fallback(conn: &Connection, filter: &TrackFilter) -> Result<Option<TrackFilter>, DbError> {
    let Some(query) = &filter.search else {
        return Ok(None);
    };
    let Some((body, binds)) = track_body(conn, filter, false)? else {
        return Ok(None);
    };
    let found: bool = conn.query_row(
        &format!("SELECT EXISTS (SELECT 1 {body})"),
        params_from_iter(binds.iter()),
        |r| r.get(0),
    )?;
    if found {
        return Ok(None);
    }
    let mut ids = super::search::fuzzy_ids(conn, super::search::CorpusKind::Track, query)?;
    if let Some(only) = &filter.ids {
        ids.retain(|id| only.contains(id));
    }
    Ok(Some(TrackFilter {
        search: None,
        ids: Some(ids),
        ..filter.clone()
    }))
}

/// The FROM and WHERE that `filter_tracks` and `count_tracks` share, and their
/// parameters in order. `None` when nothing can match: an empty id list.
/// `ranked` joins the ids as `r`, for `TrackOrder::Relevance` to order by.
#[allow(clippy::type_complexity)]
fn track_body(
    conn: &Connection,
    filter: &TrackFilter,
    ranked: bool,
) -> Result<Option<(String, Vec<Box<dyn ToSql>>)>, DbError> {
    let mut clauses: Vec<String> = Vec::new();
    let mut binds: Vec<Box<dyn ToSql>> = Vec::new();
    let mut joins = String::from(TRACK_JOINS);
    if let Some(played) = filter.played {
        // In the FROM, so bound before anything the WHERE binds.
        binds.push(Box::new(super::auth::resolve_user(conn, played.user)?));
        binds.push(Box::new(played.since));
        joins.push_str(
            " JOIN (SELECT track_id AS id, MAX(played_at) AS last, MAX(id) AS last_id
                      FROM play_history WHERE user_id = ? AND played_at >= ?
                     GROUP BY track_id) p ON p.id = t.id",
        );
    }

    if let Some(ids) = &filter.ids {
        if ids.is_empty() {
            return Ok(None);
        }
        if ranked && filter.search.is_none() {
            // Joined once rather than looked up per row, so the order of the
            // ids is `r.key`. In the FROM, after the joins above bind theirs.
            joins.push_str(" JOIN json_each(?) r ON r.value = t.id");
        } else {
            clauses.push(format!("t.id IN {IN_LIST}"));
        }
        binds.push(Box::new(json_list(ids)));
    }

    if let Some(query) = &filter.search {
        if query.chars().any(char::is_alphanumeric) {
            clauses.push(
                "t.id IN (SELECT rowid FROM tracks_fts WHERE tracks_fts MATCH ?)".to_string(),
            );
            binds.push(Box::new(super::search::sanitize_fts_query(query)));
        } else {
            // The index tokenizes words and drops punctuation, so a query of
            // punctuation alone ("!!!", a band) would match nothing there.
            clauses.push(
                "(t.title LIKE ? ESCAPE '\\' OR a.name LIKE ? ESCAPE '\\'
                  OR aa.name LIKE ? ESCAPE '\\' OR al.title LIKE ? ESCAPE '\\')"
                    .to_string(),
            );
            let pattern = like_contains(query.trim());
            for _ in 0..4 {
                binds.push(Box::new(pattern.clone()));
            }
        }
    }

    if filter.on_device {
        clauses.push("COALESCE(t.cached_path, t.path) IS NOT NULL".to_string());
    }

    if let Some(album_id) = filter.album_id {
        clauses.push("t.album_id = ?".to_string());
        binds.push(Box::new(album_id));
    }

    if let Some(ids) = &filter.artist_ids {
        if ids.is_empty() {
            return Ok(None);
        }
        // The album-artist half as a subquery on `albums`, as in
        // `tracks_for_artists`: an `OR` across two tables cannot use an index.
        clauses.push(format!(
            "(t.artist_id IN {IN_LIST}
              OR t.album_id IN (SELECT id FROM albums WHERE artist_id IN {IN_LIST}))"
        ));
        let ids = json_list(ids);
        binds.push(Box::new(ids.clone()));
        binds.push(Box::new(ids));
    }

    if let Some(title) = &filter.title {
        clauses.push("t.title LIKE ? ESCAPE '\\'".to_string());
        binds.push(Box::new(like_contains(title)));
    }

    if let Some(name) = &filter.artist_name {
        clauses.push("(a.name LIKE ? ESCAPE '\\' OR aa.name LIKE ? ESCAPE '\\')".to_string());
        binds.push(Box::new(like_contains(name)));
        binds.push(Box::new(like_contains(name)));
    }

    if let Some(title) = &filter.album_title {
        clauses.push("al.title LIKE ? ESCAPE '\\'".to_string());
        binds.push(Box::new(like_contains(title)));
    }

    if let Some(genre) = &filter.genre {
        clauses.push("t.genre LIKE ? ESCAPE '\\'".to_string());
        binds.push(Box::new(like_contains(genre)));
    }

    if let Some(codec) = &filter.codec {
        clauses.push("t.codec LIKE ? ESCAPE '\\'".to_string());
        binds.push(Box::new(like_contains(codec)));
    }

    if let Some(source) = &filter.source {
        clauses.push("t.source = ?".to_string());
        binds.push(Box::new(source.clone()));
    }

    if filter.year_start.is_some() || filter.year_end.is_some() {
        // A track with no parsable four-digit year is excluded.
        clauses.push("substr(al.date, 1, 4) GLOB '[0-9][0-9][0-9][0-9]'".to_string());
        if let Some(start) = filter.year_start {
            clauses.push("CAST(substr(al.date, 1, 4) AS INTEGER) >= ?".to_string());
            binds.push(Box::new(start));
        }
        if let Some(end) = filter.year_end {
            clauses.push("CAST(substr(al.date, 1, 4) AS INTEGER) <= ?".to_string());
            binds.push(Box::new(end));
        }
    }

    for (column, value) in [
        ("t.sample_rate >= ?", filter.min_sample_rate),
        ("t.bit_depth >= ?", filter.min_bit_depth),
        ("t.channels = ?", filter.channels),
    ] {
        if let Some(v) = value {
            clauses.push(column.to_string());
            binds.push(Box::new(v));
        }
    }

    for (column, value) in [
        ("t.duration_ms >= ?", filter.min_duration_ms),
        ("t.duration_ms <= ?", filter.max_duration_ms),
    ] {
        if let Some(v) = value {
            clauses.push(column.to_string());
            binds.push(Box::new(v));
        }
    }

    if let Some(user) = filter.favourites_of {
        clauses.push(format!(
            "t.id IN ({})",
            super::tracks::favourite_track_ids_sql("?")
        ));
        binds.push(Box::new(super::auth::resolve_user(conn, user)?));
    }

    let where_clause = if clauses.is_empty() {
        String::new()
    } else {
        format!("WHERE {}", clauses.join(" AND "))
    };
    Ok(Some((format!("{joins} {where_clause}"), binds)))
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db::connection::Database;
    use crate::db::queries::{add_favourite, sample_meta, upsert_track};

    fn test_db() -> Database {
        let conn = Connection::open_in_memory().unwrap();
        conn.pragma_update(None, "foreign_keys", "on").unwrap();
        crate::db::schema::create_tables(&conn).unwrap();
        Database { conn }
    }

    fn seed(db: &Database) {
        for (title, artist, album) in [
            ("Vordhosbn", "Aphex Twin", "Drukqs"),
            ("Avril 14th", "Aphex Twin", "Drukqs"),
            ("Roygbiv", "Boards of Canada", "MHTRTC"),
        ] {
            upsert_track(&db.conn, &sample_meta(title, artist, album)).unwrap();
        }
    }

    #[test]
    fn filter_pushes_limit_into_sql() {
        let db = test_db();
        seed(&db);
        let page = filter_tracks(
            &db.conn,
            &TrackFilter::default(),
            TrackOrder::Title,
            false,
            2,
            0,
        )
        .unwrap();
        assert_eq!(page.len(), 2);
        let page2 = filter_tracks(
            &db.conn,
            &TrackFilter::default(),
            TrackOrder::Title,
            false,
            2,
            2,
        )
        .unwrap();
        assert_eq!(page2.len(), 1);
    }

    #[test]
    fn filter_matches_substrings_and_escapes_wildcards() {
        let db = test_db();
        seed(&db);
        let filter = TrackFilter {
            title: Some("vril".into()),
            ..Default::default()
        };
        let hits = filter_tracks(&db.conn, &filter, TrackOrder::Title, false, 50, 0).unwrap();
        assert_eq!(hits.len(), 1);
        assert_eq!(hits[0].title, "Avril 14th");

        // A bare `%` must not match everything.
        let filter = TrackFilter {
            title: Some("%".into()),
            ..Default::default()
        };
        assert!(
            filter_tracks(&db.conn, &filter, TrackOrder::Title, false, 50, 0)
                .unwrap()
                .is_empty()
        );
    }

    #[test]
    fn filter_composes_search_with_other_predicates() {
        let db = test_db();
        seed(&db);
        let filter = TrackFilter {
            search: Some("Aphex".into()),
            title: Some("Vordhosbn".into()),
            ..Default::default()
        };
        let hits = filter_tracks(&db.conn, &filter, TrackOrder::Title, false, 50, 0).unwrap();
        assert_eq!(hits.len(), 1);
    }

    #[test]
    fn stats_are_aggregated_not_materialised() {
        let db = test_db();
        seed(&db);
        let album_id = db
            .conn
            .query_row("SELECT id FROM albums WHERE title = 'Drukqs'", [], |r| {
                r.get::<_, i64>(0)
            })
            .unwrap();
        let stats = album_stats(&db.conn, &[album_id]).unwrap();
        assert_eq!(stats[&album_id].track_count, 2);
        assert_eq!(stats[&album_id].total_duration_ms, 480_000);

        let artist_id = db
            .conn
            .query_row(
                "SELECT id FROM artists WHERE name = 'Aphex Twin'",
                [],
                |r| r.get::<_, i64>(0),
            )
            .unwrap();
        let stats = artist_stats(&db.conn, &[artist_id]).unwrap();
        assert_eq!(stats[&artist_id].album_count, 1);
        assert_eq!(stats[&artist_id].track_count, 2);
    }

    #[test]
    fn favourite_track_ids_only_returns_the_requested_tracks() {
        let db = test_db();
        seed(&db);
        let id = |path: &str| {
            crate::db::queries::track_id_by_path(&db.conn, path)
                .unwrap()
                .unwrap()
        };
        let (starred, plain) = (
            id("/music/Drukqs/Vordhosbn.flac"),
            id("/music/MHTRTC/Roygbiv.flac"),
        );
        add_favourite(&db.conn, crate::db::queries::LOCAL_USER, starred).unwrap();
        let hits = favourite_track_ids(&db.conn, crate::db::queries::LOCAL_USER, &[starred, plain])
            .unwrap();
        assert_eq!(hits, HashSet::from([starred]));
    }

    #[test]
    fn batched_children_are_keyed_by_parent() {
        let db = test_db();
        seed(&db);
        let album_ids: Vec<i64> = db
            .conn
            .prepare("SELECT id FROM albums ORDER BY id")
            .unwrap()
            .query_map([], |r| r.get(0))
            .unwrap()
            .collect::<Result<_, _>>()
            .unwrap();
        let map = tracks_for_albums(&db.conn, &album_ids).unwrap();
        assert_eq!(map.values().map(Vec::len).sum::<usize>(), 3);

        let artist_ids: Vec<i64> = db
            .conn
            .prepare("SELECT id FROM artists ORDER BY id")
            .unwrap()
            .query_map([], |r| r.get(0))
            .unwrap()
            .collect::<Result<_, _>>()
            .unwrap();
        let map = albums_for_artists(&db.conn, &artist_ids).unwrap();
        assert_eq!(map.values().map(Vec::len).sum::<usize>(), 2);
        let map = tracks_for_artists(&db.conn, &artist_ids).unwrap();
        assert_eq!(map.values().map(Vec::len).sum::<usize>(), 3);
    }

    #[test]
    fn favourites_match_a_stream_url() {
        let db = test_db();
        seed(&db);
        let mut meta = sample_meta("Streamed", "Burial", "Untrue");
        meta.path = None;
        meta.source = "remote".into();
        meta.remote_id = Some("tr-1".into());
        meta.remote_url = Some("https://music.example/rest/stream?id=tr-1".into());
        let id = upsert_track(&db.conn, &meta).unwrap();
        let user = crate::db::queries::LOCAL_USER;
        add_favourite(&db.conn, user, id).unwrap();
        let track = &tracks_by_ids_for_test(&db, &[id])[0];

        let albums = crate::db::queries::favourite_album_ids_batch(&db.conn, user).unwrap();
        assert_eq!(albums, HashSet::from([track.album_id.unwrap()]));
        let artists = crate::db::queries::favourite_artist_ids_batch(&db.conn, user).unwrap();
        assert_eq!(artists, HashSet::from([track.artist_id.unwrap()]));
        let filter = TrackFilter {
            favourites_of: Some(user),
            ..Default::default()
        };
        let rows = filter_tracks(&db.conn, &filter, TrackOrder::Title, false, 50, 0).unwrap();
        assert_eq!(rows.iter().map(|t| t.id).collect::<Vec<_>>(), [id]);
    }

    fn tracks_by_ids_for_test(db: &Database, ids: &[i64]) -> Vec<TrackRow> {
        crate::db::queries::tracks_by_ids(&db.conn, ids).unwrap()
    }

    #[test]
    fn artist_filter_matches_the_album_artist() {
        let db = test_db();
        let mut meta = sample_meta("Guest Spot", "Guest", "Host Album");
        meta.album_artist = Some("Host".into());
        let id = upsert_track(&db.conn, &meta).unwrap();
        upsert_track(&db.conn, &sample_meta("Elsewhere", "Other", "Other Album")).unwrap();
        let host = crate::db::queries::get_or_create_artist(&db.conn, "Host", None).unwrap();

        let filter = TrackFilter {
            artist_ids: Some(vec![host]),
            ..Default::default()
        };
        let rows = filter_tracks(&db.conn, &filter, TrackOrder::Title, false, 50, 0).unwrap();
        assert_eq!(rows.iter().map(|t| t.id).collect::<Vec<_>>(), [id]);
    }

    /// More ids than SQLite allows parameters in one statement.
    #[test]
    fn id_lists_are_not_limited_by_the_parameter_count() {
        let db = test_db();
        seed(&db);
        let ids: Vec<i64> = (1..=40_000).collect();
        assert_eq!(tracks_by_ids_for_test(&db, &ids).len(), 3);
        assert_eq!(album_ids_for_tracks(&db.conn, &ids).unwrap().len(), 3);
        assert_eq!(sources_for_tracks(&db.conn, &ids).unwrap().len(), 3);
        assert!(
            favourite_track_ids(&db.conn, crate::db::queries::LOCAL_USER, &ids)
                .unwrap()
                .is_empty()
        );
        let filter = TrackFilter {
            ids: Some(ids),
            ..Default::default()
        };
        assert_eq!(
            filter_tracks(&db.conn, &filter, TrackOrder::Title, false, 50, 0)
                .unwrap()
                .len(),
            3
        );
    }
}