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.helpers import create_safe_string
13from music_assistant_models.media_items import (
14    Artist,
15    Audiobook,
16    AudiobookSummary,
17    ItemMapping,
18    ItemMappingSummary,
19    MediaCollection,
20    ProviderMapping,
21    UniqueList,
22)
23
24from music_assistant.constants import (
25    DB_TABLE_AUDIOBOOK_ARTISTS,
26    DB_TABLE_AUDIOBOOKS,
27    DB_TABLE_PLAYLOG,
28)
29from music_assistant.controllers.webserver.helpers.auth_middleware import get_current_user
30from music_assistant.helpers.compare import (
31    compare_audiobook,
32    compare_media_item,
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        if not overwrite and update.metadata.collections is not None:
481            # always update collections to prevent stale empty ones
482            metadata.collections = update.metadata.collections
483        cur_item.external_ids.update(update.external_ids)
484        name = update.name if overwrite else cur_item.name
485        sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name
486        # only serialize str narrators/ authors to db
487        _update_authors = [author for author in update.authors if isinstance(author, str)]
488        _update_narrators = [narrator for narrator in update.narrators if isinstance(narrator, str)]
489        await self.mass.music.database.update(
490            self.db_table,
491            {"item_id": db_id},
492            {
493                "name": name,
494                "sort_name": sort_name,
495                "version": update.version if overwrite else cur_item.version or update.version,
496                "metadata": serialize_to_json(metadata),
497                "publisher": cur_item.publisher or update.publisher,
498                "authors": serialize_to_json(
499                    _update_authors if overwrite else cur_item.authors or _update_authors
500                ),
501                "narrators": serialize_to_json(
502                    _update_narrators if overwrite else cur_item.narrators or _update_narrators
503                ),
504                "duration": update.duration if overwrite else cur_item.duration or update.duration,
505                "search_name": create_safe_string(name, True, True),
506                "search_sort_name": create_safe_string(sort_name or "", True, True),
507                "timestamp_added": int(update.date_added.timestamp())
508                if update.date_added
509                else UNSET,
510            },
511        )
512        # update/set external id lookup table
513        await self.set_external_ids(
514            db_id, update.external_ids if overwrite else cur_item.external_ids
515        )
516        # update/set provider_mappings table
517        provider_mappings = (
518            update.provider_mappings
519            if overwrite
520            else {*update.provider_mappings, *cur_item.provider_mappings}
521        )
522        await self.set_provider_mappings(db_id, provider_mappings, overwrite)
523        self.logger.debug("updated %s in database: (id %s)", update.name, db_id)
524        await self._set_playlog(db_id, update)
525        await self._set_artist_mappings(update, db_id)
526
527    async def _set_playlog(self, db_id: int, media_item: Audiobook) -> None:
528        """Update/set the playlog table for the given audiobook db item_id."""
529        # Get user(s)
530        user: User | None = None
531        if session_user := get_current_user():
532            # this is the active session user that triggered the action
533            user = session_user
534        elif provider_user := await self.mass.music._get_user_for_provider(
535            media_item.provider_mappings
536        ):
537            # based on configured provider filter we can try to find a user
538            user = provider_user
539        if user:
540            user_ids = [user.user_id]
541        else:
542            # NOTE: if no user was found, we will alter the playlog for all users
543            user_ids = [user.user_id for user in await self.mass.webserver.auth.list_users()]
544
545        # cleanup provider specific entries for this item
546        # we always prefer the library playlog entry
547        for prov_mapping in media_item.provider_mappings:
548            for user_id in user_ids:
549                await self.mass.music.database.delete(
550                    DB_TABLE_PLAYLOG,
551                    {
552                        "media_type": self.media_type.value,
553                        "item_id": prov_mapping.item_id,
554                        "provider": prov_mapping.provider_instance,
555                        "userid": user_id,
556                    },
557                )
558        if media_item.fully_played is None and media_item.resume_position_ms is None:
559            return
560
561        for user_id in user_ids:
562            cur_entry = await self.mass.music.database.get_row(
563                DB_TABLE_PLAYLOG,
564                {
565                    "media_type": self.media_type.value,
566                    "item_id": db_id,
567                    "provider": "library",
568                    "userid": user_id,
569                },
570            )
571            seconds_played = int((media_item.resume_position_ms or 0) / 1000)
572            # abort if nothing changed
573            if (
574                cur_entry
575                and parse_optional_bool(cur_entry["fully_played"]) == media_item.fully_played
576                and abs((cur_entry["seconds_played"] or 0) - seconds_played) <= 2
577            ):
578                return
579
580            await self.mass.music.database.insert(
581                DB_TABLE_PLAYLOG,
582                {
583                    "item_id": db_id,
584                    "provider": "library",
585                    "media_type": media_item.media_type.value,
586                    "name": media_item.name,
587                    "image": serialize_to_json(media_item.image.to_dict())
588                    if media_item.image
589                    else None,
590                    "fully_played": media_item.fully_played,
591                    "seconds_played": seconds_played,
592                    "timestamp": utc_timestamp(),
593                    "userid": user_id,
594                },
595                allow_replace=True,
596            )
597
598    async def _authors_narrators(self, column: str) -> UniqueList[str]:
599        """Return all available authors."""
600        assert self.mass.music.database is not None  # for type checking
601        rows = await self.mass.music.database.get_rows_from_query(
602            query=f"SELECT DISTINCT {column} FROM {DB_TABLE_AUDIOBOOKS}"
603        )
604        result: set[str] = set()
605        for row in rows:
606            result.update(json_loads(row[column]))
607        return UniqueList(sorted(result))
608
609    def _sync_details_query_parts(self) -> tuple[str, str, dict[str, Any]]:
610        """Return extra (columns, joins, params) for the audiobooks sync-details query."""
611        # the sync loop needs the (str vs Artist) type of the stored authors/narrators
612        # plus the user-scoped resume state to detect changes on the provider side
613        params: dict[str, Any] = {}
614        # mirror base_query: scope the playlog lookup to the session user (if any) and
615        # pick at most one row (the most recent) so the join can never fan out
616        playlog_user_clause = ""
617        if session_user := get_current_user():
618            playlog_user_clause = "AND p2.userid = :playlog_userid "
619            params["playlog_userid"] = session_user.user_id
620        extra_columns = f"""
621            , EXISTS (
622                SELECT 1 FROM {DB_TABLE_AUDIOBOOK_ARTISTS}
623                JOIN artists ON artists.item_id = audiobook_artists.artist_id
624                WHERE audiobook_artists.audiobook_id = audiobooks.item_id
625                AND artists.artist_type = '{ArtistType.AUTHOR.value}'
626            ) AS has_author_artists
627            , EXISTS (
628                SELECT 1 FROM {DB_TABLE_AUDIOBOOK_ARTISTS}
629                JOIN artists ON artists.item_id = audiobook_artists.artist_id
630                WHERE audiobook_artists.audiobook_id = audiobooks.item_id
631                AND artists.artist_type = '{ArtistType.NARRATOR.value}'
632            ) AS has_narrator_artists
633            , json_type(audiobooks.authors, '$[0]') AS first_author_type
634            , json_type(audiobooks.narrators, '$[0]') AS first_narrator_type
635            , playlog.fully_played AS fully_played
636            , playlog.seconds_played * 1000 AS resume_position_ms
637        """
638        extra_joins = (
639            f"LEFT JOIN {DB_TABLE_PLAYLOG} ON playlog.id = ("
640            f"SELECT p2.id FROM {DB_TABLE_PLAYLOG} p2 "
641            "WHERE p2.item_id = CAST(audiobooks.item_id AS TEXT) "
642            "AND p2.media_type = 'audiobook' "
643            f"{playlog_user_clause}ORDER BY p2.timestamp DESC LIMIT 1)"
644        )
645        return extra_columns, extra_joins, params
646
647    def _parse_sync_details_row(self, db_row: Mapping[str, Any]) -> AudiobookSyncDetails:
648        """Parse a raw sync-details db row into an AudiobookSyncDetails object."""
649        # authors/narrators hydrate as str only when there are no linked Artist records
650        # and the stored JSON column holds plain strings (mirrors _parse_db_row)
651        resume_position_ms = db_row["resume_position_ms"]
652        return AudiobookSyncDetails(
653            item_id=db_row["item_id"],
654            favorite=bool(db_row["favorite"]),
655            date_added=datetime.fromtimestamp(db_row["timestamp_added"], tz=UTC),
656            provider_mappings=self._parse_sync_details_mappings(db_row),
657            author_is_str=not db_row["has_author_artists"]
658            and db_row["first_author_type"] == "text",
659            narrator_is_str=not db_row["has_narrator_artists"]
660            and db_row["first_narrator_type"] == "text",
661            fully_played=parse_optional_bool(db_row["fully_played"]),
662            resume_position_ms=int(resume_position_ms) if resume_position_ms is not None else None,
663        )
664
665    def _parse_summary_row(self, db_row: Mapping[str, Any]) -> AudiobookSummary:
666        """Parse a raw summary db row into an AudiobookSummary object."""
667        item = cast("AudiobookSummary", super()._parse_summary_row(db_row))
668        item.version = db_row["version"] or ""
669        item.publisher = db_row["publisher"]
670        item.duration = db_row["duration"] or 0
671        item.fully_played = parse_optional_bool(db_row["fully_played"])
672        item.resume_position_ms = db_row["resume_position_ms"]
673        # authors/narrators: prefer the linked artist records (as slim mappings),
674        # fall back to the plain string values stored on the audiobook itself
675        authors: list[ItemMappingSummary] = []
676        narrators: list[ItemMappingSummary] = []
677        if raw_audiobook_artists := db_row["audiobook_artists"]:
678            for artist in json_loads(raw_audiobook_artists):
679                mapping = ItemMappingSummary(
680                    media_type=MediaType.ARTIST,
681                    item_id=str(artist["item_id"]),
682                    provider="library",
683                    name=artist["name"],
684                    sort_name=artist["sort_name"],
685                )
686                if artist["artist_type"] == ArtistType.AUTHOR.value:
687                    authors.append(mapping)
688                elif artist["artist_type"] == ArtistType.NARRATOR.value:
689                    narrators.append(mapping)
690        item.authors = UniqueList(authors or json_loads(db_row["authors"] or "[]"))
691        item.narrators = UniqueList(narrators or json_loads(db_row["narrators"] or "[]"))
692        return item
693