music-assistant-server

1"""Manage MediaItems of type Audiobook."""
2
3from __future__ import annotations
4
5from collections.abc import Iterable
6from datetime import UTC, datetime
7from json import loads as json_loads
8from typing import TYPE_CHECKING, Any, Literal, cast, overload
9
10from music_assistant_models.auth import Scope
11from music_assistant_models.enums import ArtistType, MediaType, ProviderFeature
12from music_assistant_models.media_items import (
13    Artist,
14    Audiobook,
15    AudiobookSummary,
16    ItemMapping,
17    ItemMappingSummary,
18    MediaCollection,
19    ProviderMapping,
20    UniqueList,
21)
22
23from music_assistant.constants import (
24    DB_TABLE_AUDIOBOOK_ARTISTS,
25    DB_TABLE_AUDIOBOOKS,
26    DB_TABLE_PLAYLOG,
27)
28from music_assistant.controllers.webserver.helpers.auth_middleware import get_current_user
29from music_assistant.helpers.compare import (
30    compare_audiobook,
31    compare_media_item,
32    create_safe_string,
33    loose_compare_strings,
34)
35from music_assistant.helpers.database import UNSET
36from music_assistant.helpers.datetime import utc_timestamp
37from music_assistant.helpers.json import serialize_to_json
38from music_assistant.helpers.util import parse_optional_bool
39from music_assistant.models.music_provider import MusicProvider
40
41from .base import AudiobookSyncDetails, MediaControllerBase
42
43if TYPE_CHECKING:
44    from collections.abc import Mapping
45
46    from music_assistant_models.auth import User
47
48    from music_assistant import MusicAssistant
49
50
51class AudiobooksController(MediaControllerBase[Audiobook]):
52    """Controller managing MediaItems of type Audiobook."""
53
54    db_table = DB_TABLE_AUDIOBOOKS
55    media_type = MediaType.AUDIOBOOK
56    item_cls = Audiobook
57    summary_item_cls = AudiobookSummary
58
59    def __init__(self, mass: MusicAssistant) -> None:
60        """Initialize class."""
61        super().__init__(mass)
62        # register (extra) api handlers
63        api_base = self.api_base
64        self.mass.register_api_command(
65            f"music/{api_base}/audiobook_versions", self.versions, required_scope=Scope.LIBRARY_READ
66        )
67
68    @property
69    def base_query(self) -> tuple[str, dict[str, Any]]:
70        """
71        Return the base SELECT query for audiobooks and its bound query params.
72
73        The playlog table is joined to hydrate per-user resume info (fully_played,
74        resume_position_ms). When a session user is present the join is scoped to that
75        user, so multi-user installs don't surface each other's resume state.
76        """
77        params: dict[str, Any] = {}
78        # scope the playlog lookup to the session user (if any) and pick at most one
79        # row (the most recent) so the join can never fan out the result set
80        playlog_user_clause = ""
81        if session_user := get_current_user():
82            playlog_user_clause = "AND p2.userid = :playlog_userid "
83            params["playlog_userid"] = session_user.user_id
84        query = f"""
85        SELECT
86            audiobooks.*,
87            {self._external_ids_query()} AS external_ids,
88            {self._provider_mappings_query()} AS provider_mappings,
89            (SELECT JSON_GROUP_ARRAY(
90                json_object(
91                 'item_id', artists.item_id,
92                 'provider', 'library',
93                 'name', artists.name,
94                 'sort_name', artists.sort_name,
95                 'media_type', 'artist',
96                 'artist_type', artists.artist_type
97            ))
98            FROM artists JOIN audiobook_artists on audiobook_artists.audiobook_id = audiobooks.item_id WHERE artists.item_id = audiobook_artists.artist_id) AS audiobook_artists,
99            playlog.fully_played AS fully_played,
100            playlog.seconds_played AS seconds_played,
101            playlog.seconds_played * 1000 as resume_position_ms
102            FROM audiobooks
103            LEFT JOIN playlog ON playlog.id = (
104                SELECT p2.id FROM playlog p2
105                WHERE p2.item_id = CAST(audiobooks.item_id AS TEXT)
106                AND p2.media_type = 'audiobook'
107                {playlog_user_clause}ORDER BY p2.timestamp DESC LIMIT 1)
108            """
109        return query, params
110
111    @property
112    def summary_query(self) -> tuple[str, dict[str, Any]]:
113        """
114        Return the slim SELECT query used for audiobook summary listings.
115
116        Joins the playlog table the same way as the base query to hydrate the
117        per-user resume info (fully_played, resume_position_ms).
118        """
119        params: dict[str, Any] = {}
120        playlog_user_clause = ""
121        if session_user := get_current_user():
122            playlog_user_clause = "AND p2.userid = :playlog_userid "
123            params["playlog_userid"] = session_user.user_id
124        artists_query = self._artist_mappings_summary_query(
125            DB_TABLE_AUDIOBOOK_ARTISTS, "audiobook_id", include_artist_type=True
126        )
127        query = f"""
128        SELECT
129            {self._summary_base_columns()},
130            audiobooks.version,
131            audiobooks.publisher,
132            audiobooks.duration,
133            audiobooks.authors,
134            audiobooks.narrators,
135            {self._provider_mappings_query()} AS provider_mappings,
136            {artists_query} AS audiobook_artists,
137            playlog.fully_played AS fully_played,
138            playlog.seconds_played * 1000 as resume_position_ms
139            FROM audiobooks
140            LEFT JOIN playlog ON playlog.id = (
141                SELECT p2.id FROM playlog p2
142                WHERE p2.item_id = CAST(audiobooks.item_id AS TEXT)
143                AND p2.media_type = 'audiobook'
144                {playlog_user_clause}ORDER BY p2.timestamp DESC LIMIT 1)
145            """
146        return query, params
147
148    if TYPE_CHECKING:
149
150        @overload
151        async def library_items(
152            self,
153            favorite: bool | None = None,
154            search: str | None = None,
155            limit: int = 500,
156            offset: int = 0,
157            order_by: str = "sort_name",
158            provider: str | list[str] | None = None,
159            genre: int | list[int] | None = None,
160            played_only: bool = False,
161            *,
162            summary: bool = True,
163            collapse_collections: Literal[False] = False,
164            **kwargs: Any,
165        ) -> list[Audiobook]: ...
166
167        @overload
168        async def library_items(
169            self,
170            favorite: bool | None = None,
171            search: str | None = None,
172            limit: int = 500,
173            offset: int = 0,
174            order_by: str = "sort_name",
175            provider: str | list[str] | None = None,
176            genre: int | list[int] | None = None,
177            played_only: bool = False,
178            *,
179            summary: bool = True,
180            collapse_collections: Literal[True],
181            **kwargs: Any,
182        ) -> list[Audiobook] | list[Audiobook | MediaCollection[Audiobook]]: ...
183
184        @overload
185        async def library_items(
186            self,
187            favorite: bool | None = None,
188            search: str | None = None,
189            limit: int = 500,
190            offset: int = 0,
191            order_by: str = "sort_name",
192            provider: str | list[str] | None = None,
193            genre: int | list[int] | None = None,
194            played_only: bool = False,
195            *,
196            summary: bool = True,
197            collapse_collections: bool,
198            **kwargs: Any,
199        ) -> list[Audiobook] | list[Audiobook | MediaCollection[Audiobook]]: ...
200
201    async def library_items(
202        self,
203        favorite: bool | None = None,
204        search: str | None = None,
205        limit: int = 500,
206        offset: int = 0,
207        order_by: str = "sort_name",
208        provider: str | list[str] | None = None,
209        genre: int | list[int] | None = None,
210        played_only: bool = False,
211        *,
212        summary: bool = True,
213        collapse_collections: bool = False,
214        **kwargs: Any,
215    ) -> list[Audiobook] | list[Audiobook | MediaCollection[Audiobook]]:
216        """
217        Get in-database audiobooks.
218
219        :param favorite: Filter by favorite status.
220        :param search: Filter by search query.
221        :param limit: Maximum number of items to return.
222        :param offset: Number of items to skip.
223        :param order_by: Order by field (e.g. 'sort_name', 'timestamp_added').
224        :param provider: Filter by provider instance ID (single string or list).
225        :param genre: Filter by genre id(s).
226        :param summary: When True (default), return slim summary items containing only the
227            fields needed for a list view. Set to False to get fully hydrated items.
228        :param collapse_collections: Collapse available collections. Items in a collection won't
229            be returned individually.
230        """
231        extra_query_params: dict[str, Any] = {}
232        extra_query_parts: list[str] = []
233        result = await self.get_library_items_by_query(
234            favorite=favorite,
235            search=search,
236            genre_ids=genre,
237            limit=limit,
238            offset=offset,
239            order_by=order_by,
240            provider_filter=self._ensure_provider_filter(provider),
241            extra_query_parts=extra_query_parts,
242            extra_query_params=extra_query_params,
243            played_only=played_only,
244            in_library_only=True,
245            summary=summary,
246            collapse_collections=collapse_collections,
247        )
248        if search and len(result) < 25 and not offset:
249            # append author items to result
250            extra_query_parts = [
251                "WHERE audiobooks.authors LIKE :search or audiobooks.narrators LIKE :search",
252            ]
253            extra_query_params["search"] = f"%{search}%"
254            return result + await self.get_library_items_by_query(
255                favorite=favorite,
256                search=None,
257                genre_ids=genre,
258                limit=limit,
259                order_by=order_by,
260                provider_filter=self._ensure_provider_filter(provider),
261                extra_query_parts=extra_query_parts,
262                extra_query_params=extra_query_params,
263                in_library_only=True,
264                summary=summary,
265                collapse_collections=collapse_collections,
266            )
267        return result
268
269    async def versions(
270        self,
271        item_id: str,
272        provider_instance_id_or_domain: str,
273    ) -> UniqueList[Audiobook]:
274        """Return all versions of an audiobook we can find on all providers."""
275        audiobook = await self.get_provider_item(item_id, provider_instance_id_or_domain)
276        search_query = audiobook.name
277        result: UniqueList[Audiobook] = UniqueList()
278        for provider_id in self.mass.music.get_unique_providers():
279            provider = self.mass.get_provider(provider_id)
280            if not isinstance(provider, MusicProvider):
281                continue
282            if not self.mass.music.library_supported(provider, MediaType.AUDIOBOOK):
283                continue
284            result.extend(
285                prov_item
286                for prov_item in await self.search(search_query, provider_id)
287                if loose_compare_strings(audiobook.name, prov_item.name)
288                # make sure that the 'base' version is NOT included
289                and not audiobook.provider_mappings.intersection(prov_item.provider_mappings)
290            )
291        return result
292
293    async def match_provider(
294        self, db_audiobook: Audiobook, provider: MusicProvider, strict: bool = True
295    ) -> list[ProviderMapping]:
296        """
297        Try to find match on (streaming) provider for the provided (database) audiobook.
298
299        This is used to link objects of different providers/qualities together.
300        """
301        self.logger.debug(
302            "Trying to match audiobook %s on provider %s",
303            db_audiobook.name,
304            provider.name,
305        )
306        matches: list[ProviderMapping] = []
307        author_name = db_audiobook.authors[0] if db_audiobook.authors else ""
308        search_str = f"{author_name} - {db_audiobook.name}" if author_name else db_audiobook.name
309        search_result = await self.search(search_str, provider.instance_id)
310        for search_result_item in search_result:
311            if not search_result_item.available:
312                continue
313            if not compare_media_item(db_audiobook, search_result_item, strict=strict):
314                continue
315            # we must fetch the full audiobook version, search results can be simplified objects
316            prov_audiobook = await self.get_provider_item(
317                search_result_item.item_id,
318                search_result_item.provider,
319                fallback=search_result_item,
320            )
321            if compare_audiobook(db_audiobook, prov_audiobook, strict=strict):
322                # 100% match
323                matches.extend(prov_audiobook.provider_mappings)
324        if not matches:
325            self.logger.debug(
326                "Could not find match for Audiobook %s on provider %s",
327                db_audiobook.name,
328                provider.name,
329            )
330        return matches
331
332    async def match_providers(self, db_audiobook: Audiobook) -> None:
333        """
334        Try to find match on all (streaming) providers for the provided (database) audiobook.
335
336        This is used to link objects of different providers/qualities together.
337        """
338        if db_audiobook.provider != "library":
339            return  # Matching only supported for database items
340
341        # try to find match on all providers
342        cur_provider_domains = {x.provider_domain for x in db_audiobook.provider_mappings}
343        for provider in self.mass.music.providers:
344            if provider.domain in cur_provider_domains:
345                continue
346            if ProviderFeature.SEARCH not in provider.supported_features:
347                continue
348            if not self.mass.music.library_supported(provider, MediaType.AUDIOBOOK):
349                continue
350            if not provider.is_streaming_provider:
351                # matching on unique providers is pointless as they push (all) their content to MA
352                continue
353            if match := await self.match_provider(db_audiobook, provider):
354                # 100% match, we update the db with the additional provider mapping(s)
355                await self.add_provider_mappings(db_audiobook.item_id, match)
356                cur_provider_domains.add(provider.domain)
357
358    async def remove_item_from_library(self, item_id: str | int, recursive: bool = True) -> None:
359        """Delete item from the library(database)."""
360        db_id = int(item_id)  # ensure integer
361        # delete entry(s) from album artists table
362        await self.mass.music.database.delete(DB_TABLE_AUDIOBOOK_ARTISTS, {"audiobook_id": db_id})
363        # delete the album itself from db
364        # this will raise if the item still has references and recursive is false
365        await super().remove_item_from_library(item_id)
366
367    async def _add_library_item(self, item: Audiobook, overwrite_existing: bool = False) -> int:
368        """Add a new record to the database."""
369        # only serialize str narrators/ authors to db
370        _authors = [author for author in item.authors if isinstance(author, str)]
371        _narrators = [narrator for narrator in item.narrators if isinstance(narrator, str)]
372        db_id = await self.mass.music.database.insert(
373            self.db_table,
374            {
375                "name": item.name,
376                "sort_name": item.sort_name,
377                "version": item.version,
378                "favorite": item.favorite,
379                "metadata": serialize_to_json(item.metadata),
380                "publisher": item.publisher,
381                "authors": serialize_to_json(_authors),
382                "narrators": serialize_to_json(_narrators),
383                "duration": item.duration,
384                "search_name": create_safe_string(item.name, True, True),
385                "search_sort_name": create_safe_string(item.sort_name or "", True, True),
386                "timestamp_added": int(item.date_added.timestamp()) if item.date_added else UNSET,
387            },
388        )
389        # update/set external id lookup table
390        await self.set_external_ids(db_id, item.external_ids)
391        # update/set provider_mappings table
392        await self.set_provider_mappings(db_id, item.provider_mappings)
393        self.logger.debug("added %s to database (id: %s)", item.name, db_id)
394        await self._set_playlog(db_id, item)
395        await self._set_artist_mappings(item, db_id)
396
397        return db_id
398
399    async def _set_artist_mappings(
400        self, item: Audiobook, db_id: int, overwrite: bool = False
401    ) -> None:
402        # update artist mappings - the sync method in the provider model raises an exception
403        # if not all entries are either of type str or Artist
404        if overwrite:
405            # on overwrite, clear the audiobook_artists table first
406            await self.mass.music.database.delete(
407                DB_TABLE_AUDIOBOOK_ARTISTS,
408                {
409                    "audiobook_id": db_id,
410                },
411            )
412        if item.authors and isinstance(item.authors[0], Artist):
413            # only for type checking
414            authors = [author for author in item.authors if isinstance(author, Artist)]
415            for author in authors:
416                # just to be sure
417                author.artist_type = ArtistType.AUTHOR
418            await self._set_audiobook_authors_narrators(db_id, authors)
419        if item.narrators and isinstance(item.narrators[0], Artist):
420            # only for type checking
421            narrators = [narrator for narrator in item.narrators if isinstance(narrator, Artist)]
422            for narrator in narrators:
423                # just to be sure
424                narrator.artist_type = ArtistType.NARRATOR
425            await self._set_audiobook_authors_narrators(db_id, narrators)
426
427    async def _set_audiobook_authors_narrators(
428        self,
429        db_id: int,
430        artists: Iterable[Artist | ItemMapping],
431        overwrite: bool = False,
432    ) -> None:
433        """Write audiobook id and author/ narrator id to DB_TABLE_AUDIOBOOK_ARTISTS."""
434        for artist in artists:
435            await self._set_audiobook_author_narrator(db_id, artist=artist, overwrite=overwrite)
436
437    async def _set_audiobook_author_narrator(
438        self, db_id: int, artist: Artist | ItemMapping, overwrite: bool = False
439    ) -> ItemMapping:
440        """Store Album Artist info."""
441        db_artist: Artist | ItemMapping | None = None
442        if artist.provider == "library":
443            db_artist = artist
444        elif existing := await self.mass.music.artists.get_library_item_by_prov_id(
445            artist.item_id, artist.provider
446        ):
447            db_artist = existing
448
449        if not db_artist or overwrite:
450            # Convert ItemMapping to Artist if needed
451            artist_to_add = (
452                self.mass.music.artists.artist_from_item_mapping(artist)
453                if isinstance(artist, ItemMapping)
454                else artist
455            )
456            db_artist = await self.mass.music.artists.add_item_to_library(
457                artist_to_add, overwrite_existing=overwrite
458            )
459        # write (or update) record in album_artists table
460        await self.mass.music.database.insert_or_replace(
461            DB_TABLE_AUDIOBOOK_ARTISTS,
462            {
463                "audiobook_id": db_id,
464                "artist_id": int(db_artist.item_id),
465            },
466        )
467        return ItemMapping.from_item(db_artist)
468
469    async def _update_library_item(
470        self, item_id: str | int, update: Audiobook, overwrite: bool = False
471    ) -> None:
472        """Update existing record in the database."""
473        db_id = int(item_id)  # ensure integer
474        cur_item = await self.get_library_item(db_id)
475        metadata = update.metadata if overwrite else cur_item.metadata.update(update.metadata)
476        if not overwrite and update.metadata.images is not None:
477            # audiobooks have no image picker, so keep the cover in sync with the
478            # provider instead of accumulating merged entries
479            metadata.images = update.metadata.images
480        cur_item.external_ids.update(update.external_ids)
481        name = update.name if overwrite else cur_item.name
482        sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name
483        # only serialize str narrators/ authors to db
484        _update_authors = [author for author in update.authors if isinstance(author, str)]
485        _update_narrators = [narrator for narrator in update.narrators if isinstance(narrator, str)]
486        await self.mass.music.database.update(
487            self.db_table,
488            {"item_id": db_id},
489            {
490                "name": name,
491                "sort_name": sort_name,
492                "version": update.version if overwrite else cur_item.version or update.version,
493                "metadata": serialize_to_json(metadata),
494                "publisher": cur_item.publisher or update.publisher,
495                "authors": serialize_to_json(
496                    _update_authors if overwrite else cur_item.authors or _update_authors
497                ),
498                "narrators": serialize_to_json(
499                    _update_narrators if overwrite else cur_item.narrators or _update_narrators
500                ),
501                "duration": update.duration if overwrite else cur_item.duration or update.duration,
502                "search_name": create_safe_string(name, True, True),
503                "search_sort_name": create_safe_string(sort_name or "", True, True),
504                "timestamp_added": int(update.date_added.timestamp())
505                if update.date_added
506                else UNSET,
507            },
508        )
509        # update/set external id lookup table
510        await self.set_external_ids(
511            db_id, update.external_ids if overwrite else cur_item.external_ids
512        )
513        # update/set provider_mappings table
514        provider_mappings = (
515            update.provider_mappings
516            if overwrite
517            else {*update.provider_mappings, *cur_item.provider_mappings}
518        )
519        await self.set_provider_mappings(db_id, provider_mappings, overwrite)
520        self.logger.debug("updated %s in database: (id %s)", update.name, db_id)
521        await self._set_playlog(db_id, update)
522        await self._set_artist_mappings(update, db_id)
523
524    async def _set_playlog(self, db_id: int, media_item: Audiobook) -> None:
525        """Update/set the playlog table for the given audiobook db item_id."""
526        # Get user(s)
527        user: User | None = None
528        if session_user := get_current_user():
529            # this is the active session user that triggered the action
530            user = session_user
531        elif provider_user := await self.mass.music._get_user_for_provider(
532            media_item.provider_mappings
533        ):
534            # based on configured provider filter we can try to find a user
535            user = provider_user
536        if user:
537            user_ids = [user.user_id]
538        else:
539            # NOTE: if no user was found, we will alter the playlog for all users
540            user_ids = [user.user_id for user in await self.mass.webserver.auth.list_users()]
541
542        # cleanup provider specific entries for this item
543        # we always prefer the library playlog entry
544        for prov_mapping in media_item.provider_mappings:
545            for user_id in user_ids:
546                await self.mass.music.database.delete(
547                    DB_TABLE_PLAYLOG,
548                    {
549                        "media_type": self.media_type.value,
550                        "item_id": prov_mapping.item_id,
551                        "provider": prov_mapping.provider_instance,
552                        "userid": user_id,
553                    },
554                )
555        if media_item.fully_played is None and media_item.resume_position_ms is None:
556            return
557
558        for user_id in user_ids:
559            cur_entry = await self.mass.music.database.get_row(
560                DB_TABLE_PLAYLOG,
561                {
562                    "media_type": self.media_type.value,
563                    "item_id": db_id,
564                    "provider": "library",
565                    "userid": user_id,
566                },
567            )
568            seconds_played = int((media_item.resume_position_ms or 0) / 1000)
569            # abort if nothing changed
570            if (
571                cur_entry
572                and parse_optional_bool(cur_entry["fully_played"]) == media_item.fully_played
573                and abs((cur_entry["seconds_played"] or 0) - seconds_played) <= 2
574            ):
575                return
576
577            await self.mass.music.database.insert(
578                DB_TABLE_PLAYLOG,
579                {
580                    "item_id": db_id,
581                    "provider": "library",
582                    "media_type": media_item.media_type.value,
583                    "name": media_item.name,
584                    "image": serialize_to_json(media_item.image.to_dict())
585                    if media_item.image
586                    else None,
587                    "fully_played": media_item.fully_played,
588                    "seconds_played": seconds_played,
589                    "timestamp": utc_timestamp(),
590                    "userid": user_id,
591                },
592                allow_replace=True,
593            )
594
595    async def _authors_narrators(self, column: str) -> UniqueList[str]:
596        """Return all available authors."""
597        assert self.mass.music.database is not None  # for type checking
598        rows = await self.mass.music.database.get_rows_from_query(
599            query=f"SELECT DISTINCT {column} FROM {DB_TABLE_AUDIOBOOKS}"
600        )
601        result: set[str] = set()
602        for row in rows:
603            result.update(json_loads(row[column]))
604        return UniqueList(sorted(result))
605
606    def _sync_details_query_parts(self) -> tuple[str, str, dict[str, Any]]:
607        """Return extra (columns, joins, params) for the audiobooks sync-details query."""
608        # the sync loop needs the (str vs Artist) type of the stored authors/narrators
609        # plus the user-scoped resume state to detect changes on the provider side
610        params: dict[str, Any] = {}
611        # mirror base_query: scope the playlog lookup to the session user (if any) and
612        # pick at most one row (the most recent) so the join can never fan out
613        playlog_user_clause = ""
614        if session_user := get_current_user():
615            playlog_user_clause = "AND p2.userid = :playlog_userid "
616            params["playlog_userid"] = session_user.user_id
617        extra_columns = f"""
618            , EXISTS (
619                SELECT 1 FROM {DB_TABLE_AUDIOBOOK_ARTISTS}
620                JOIN artists ON artists.item_id = audiobook_artists.artist_id
621                WHERE audiobook_artists.audiobook_id = audiobooks.item_id
622                AND artists.artist_type = '{ArtistType.AUTHOR.value}'
623            ) AS has_author_artists
624            , EXISTS (
625                SELECT 1 FROM {DB_TABLE_AUDIOBOOK_ARTISTS}
626                JOIN artists ON artists.item_id = audiobook_artists.artist_id
627                WHERE audiobook_artists.audiobook_id = audiobooks.item_id
628                AND artists.artist_type = '{ArtistType.NARRATOR.value}'
629            ) AS has_narrator_artists
630            , json_type(audiobooks.authors, '$[0]') AS first_author_type
631            , json_type(audiobooks.narrators, '$[0]') AS first_narrator_type
632            , playlog.fully_played AS fully_played
633            , playlog.seconds_played * 1000 AS resume_position_ms
634        """
635        extra_joins = (
636            f"LEFT JOIN {DB_TABLE_PLAYLOG} ON playlog.id = ("
637            f"SELECT p2.id FROM {DB_TABLE_PLAYLOG} p2 "
638            "WHERE p2.item_id = CAST(audiobooks.item_id AS TEXT) "
639            "AND p2.media_type = 'audiobook' "
640            f"{playlog_user_clause}ORDER BY p2.timestamp DESC LIMIT 1)"
641        )
642        return extra_columns, extra_joins, params
643
644    def _parse_sync_details_row(self, db_row: Mapping[str, Any]) -> AudiobookSyncDetails:
645        """Parse a raw sync-details db row into an AudiobookSyncDetails object."""
646        # authors/narrators hydrate as str only when there are no linked Artist records
647        # and the stored JSON column holds plain strings (mirrors _parse_db_row)
648        resume_position_ms = db_row["resume_position_ms"]
649        return AudiobookSyncDetails(
650            item_id=db_row["item_id"],
651            favorite=bool(db_row["favorite"]),
652            date_added=datetime.fromtimestamp(db_row["timestamp_added"], tz=UTC),
653            provider_mappings=self._parse_sync_details_mappings(db_row),
654            author_is_str=not db_row["has_author_artists"]
655            and db_row["first_author_type"] == "text",
656            narrator_is_str=not db_row["has_narrator_artists"]
657            and db_row["first_narrator_type"] == "text",
658            fully_played=parse_optional_bool(db_row["fully_played"]),
659            resume_position_ms=int(resume_position_ms) if resume_position_ms is not None else None,
660        )
661
662    def _parse_summary_row(self, db_row: Mapping[str, Any]) -> AudiobookSummary:
663        """Parse a raw summary db row into an AudiobookSummary object."""
664        item = cast("AudiobookSummary", super()._parse_summary_row(db_row))
665        item.version = db_row["version"] or ""
666        item.publisher = db_row["publisher"]
667        item.duration = db_row["duration"] or 0
668        item.fully_played = parse_optional_bool(db_row["fully_played"])
669        item.resume_position_ms = db_row["resume_position_ms"]
670        # authors/narrators: prefer the linked artist records (as slim mappings),
671        # fall back to the plain string values stored on the audiobook itself
672        authors: list[ItemMappingSummary] = []
673        narrators: list[ItemMappingSummary] = []
674        if raw_audiobook_artists := db_row["audiobook_artists"]:
675            for artist in json_loads(raw_audiobook_artists):
676                mapping = ItemMappingSummary(
677                    media_type=MediaType.ARTIST,
678                    item_id=str(artist["item_id"]),
679                    provider="library",
680                    name=artist["name"],
681                    sort_name=artist["sort_name"],
682                )
683                if artist["artist_type"] == ArtistType.AUTHOR.value:
684                    authors.append(mapping)
685                elif artist["artist_type"] == ArtistType.NARRATOR.value:
686                    narrators.append(mapping)
687        item.authors = UniqueList(authors or json_loads(db_row["authors"] or "[]"))
688        item.narrators = UniqueList(narrators or json_loads(db_row["narrators"] or "[]"))
689        return item
690