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            reachable_via: list[str] | None = None,
165            **kwargs: Any,
166        ) -> list[Audiobook]: ...
167
168        @overload
169        async def library_items(
170            self,
171            favorite: bool | None = None,
172            search: str | None = None,
173            limit: int = 500,
174            offset: int = 0,
175            order_by: str = "sort_name",
176            provider: str | list[str] | None = None,
177            genre: int | list[int] | None = None,
178            played_only: bool = False,
179            *,
180            summary: bool = True,
181            collapse_collections: Literal[True],
182            reachable_via: list[str] | None = None,
183            **kwargs: Any,
184        ) -> list[Audiobook] | list[Audiobook | MediaCollection[Audiobook]]: ...
185
186        @overload
187        async def library_items(
188            self,
189            favorite: bool | None = None,
190            search: str | None = None,
191            limit: int = 500,
192            offset: int = 0,
193            order_by: str = "sort_name",
194            provider: str | list[str] | None = None,
195            genre: int | list[int] | None = None,
196            played_only: bool = False,
197            *,
198            summary: bool = True,
199            collapse_collections: bool,
200            reachable_via: list[str] | None = None,
201            **kwargs: Any,
202        ) -> list[Audiobook] | list[Audiobook | MediaCollection[Audiobook]]: ...
203
204    async def library_items(  # noqa: PLR0913
205        self,
206        favorite: bool | None = None,
207        search: str | None = None,
208        limit: int = 500,
209        offset: int = 0,
210        order_by: str = "sort_name",
211        provider: str | list[str] | None = None,
212        genre: int | list[int] | None = None,
213        played_only: bool = False,
214        *,
215        summary: bool = True,
216        collapse_collections: bool = False,
217        reachable_via: list[str] | None = None,
218        **kwargs: Any,
219    ) -> list[Audiobook] | list[Audiobook | MediaCollection[Audiobook]]:
220        """
221        Get in-database audiobooks.
222
223        :param favorite: Filter by favorite status.
224        :param search: Filter by search query.
225        :param limit: Maximum number of items to return.
226        :param offset: Number of items to skip.
227        :param order_by: Order by field (e.g. 'sort_name', 'timestamp_added').
228        :param provider: Filter by provider instance ID (single string or list).
229        :param genre: Filter by genre id(s).
230        :param summary: When True (default), return slim summary items containing only the
231            fields needed for a list view. Set to False to get fully hydrated items.
232        :param collapse_collections: Collapse available collections. Items in a collection won't
233            be returned individually.
234        :param reachable_via: Restrict results to items with a provider mapping reachable
235            through one of these provider instance ids (OR semantics). See
236            `MediaControllerBase.library_items` for the full semantics.
237        """
238        reachable_via = self._resolve_reachable_via(reachable_via)
239        if reachable_via is not None and not reachable_via:
240            return []
241        extra_query_params: dict[str, Any] = {}
242        extra_query_parts: list[str] = []
243        result = await self.get_library_items_by_query(
244            favorite=favorite,
245            search=search,
246            genre_ids=genre,
247            limit=limit,
248            offset=offset,
249            order_by=order_by,
250            provider_filter=self._provider_filter_considering_reachability(provider, reachable_via),
251            extra_query_parts=extra_query_parts,
252            extra_query_params=extra_query_params,
253            played_only=played_only,
254            in_library_only=True,
255            summary=summary,
256            collapse_collections=collapse_collections,
257            reachable_via=reachable_via,
258        )
259        if search and len(result) < 25 and not offset:
260            # append author items to result
261            extra_query_parts = [
262                "WHERE audiobooks.authors LIKE :search or audiobooks.narrators LIKE :search",
263            ]
264            extra_query_params["search"] = f"%{search}%"
265            return result + await self.get_library_items_by_query(
266                favorite=favorite,
267                search=None,
268                genre_ids=genre,
269                limit=limit,
270                order_by=order_by,
271                provider_filter=self._provider_filter_considering_reachability(
272                    provider, reachable_via
273                ),
274                extra_query_parts=extra_query_parts,
275                extra_query_params=extra_query_params,
276                in_library_only=True,
277                summary=summary,
278                collapse_collections=collapse_collections,
279                reachable_via=reachable_via,
280            )
281        return result
282
283    async def versions(
284        self,
285        item_id: str,
286        provider_instance_id_or_domain: str,
287    ) -> UniqueList[Audiobook]:
288        """Return all versions of an audiobook we can find on all providers."""
289        audiobook = await self.get_provider_item(item_id, provider_instance_id_or_domain)
290        search_query = audiobook.name
291        result: UniqueList[Audiobook] = UniqueList()
292        for provider_id in self.mass.music.get_unique_providers():
293            provider = self.mass.get_provider(provider_id)
294            if not isinstance(provider, MusicProvider):
295                continue
296            if not self.mass.music.library_supported(provider, MediaType.AUDIOBOOK):
297                continue
298            result.extend(
299                prov_item
300                for prov_item in await self.search(search_query, provider_id)
301                if loose_compare_strings(audiobook.name, prov_item.name)
302                # make sure that the 'base' version is NOT included
303                and not audiobook.provider_mappings.intersection(prov_item.provider_mappings)
304            )
305        return result
306
307    async def match_provider(
308        self, db_audiobook: Audiobook, provider: MusicProvider, strict: bool = True
309    ) -> list[ProviderMapping]:
310        """
311        Try to find match on (streaming) provider for the provided (database) audiobook.
312
313        This is used to link objects of different providers/qualities together.
314        """
315        self.logger.debug(
316            "Trying to match audiobook %s on provider %s",
317            db_audiobook.name,
318            provider.name,
319        )
320        matches: list[ProviderMapping] = []
321        author_name = db_audiobook.authors[0] if db_audiobook.authors else ""
322        search_str = f"{author_name} - {db_audiobook.name}" if author_name else db_audiobook.name
323        search_result = await self.search(search_str, provider.instance_id)
324        for search_result_item in search_result:
325            if not search_result_item.available:
326                continue
327            if not compare_media_item(db_audiobook, search_result_item, strict=strict):
328                continue
329            # we must fetch the full audiobook version, search results can be simplified objects
330            prov_audiobook = await self.get_provider_item(
331                search_result_item.item_id,
332                search_result_item.provider,
333                fallback=search_result_item,
334            )
335            if compare_audiobook(db_audiobook, prov_audiobook, strict=strict):
336                # 100% match
337                matches.extend(prov_audiobook.provider_mappings)
338        if not matches:
339            self.logger.debug(
340                "Could not find match for Audiobook %s on provider %s",
341                db_audiobook.name,
342                provider.name,
343            )
344        return matches
345
346    async def match_providers(self, db_audiobook: Audiobook) -> None:
347        """
348        Try to find match on all (streaming) providers for the provided (database) audiobook.
349
350        This is used to link objects of different providers/qualities together.
351        """
352        if db_audiobook.provider != "library":
353            return  # Matching only supported for database items
354
355        # try to find match on all providers
356        cur_provider_domains = {x.provider_domain for x in db_audiobook.provider_mappings}
357        for provider in self.mass.music.providers:
358            if provider.domain in cur_provider_domains:
359                continue
360            if ProviderFeature.SEARCH not in provider.supported_features:
361                continue
362            if not self.mass.music.library_supported(provider, MediaType.AUDIOBOOK):
363                continue
364            if not provider.is_streaming_provider:
365                # matching on unique providers is pointless as they push (all) their content to MA
366                continue
367            if match := await self.match_provider(db_audiobook, provider):
368                # 100% match, we update the db with the additional provider mapping(s)
369                await self.add_provider_mappings(db_audiobook.item_id, match)
370                cur_provider_domains.add(provider.domain)
371
372    async def remove_item_from_library(self, item_id: str | int, recursive: bool = True) -> None:
373        """Delete item from the library(database)."""
374        db_id = int(item_id)  # ensure integer
375        # delete entry(s) from album artists table
376        await self.mass.music.database.delete(DB_TABLE_AUDIOBOOK_ARTISTS, {"audiobook_id": db_id})
377        # delete the album itself from db
378        # this will raise if the item still has references and recursive is false
379        await super().remove_item_from_library(item_id)
380
381    async def _add_library_item(self, item: Audiobook, overwrite_existing: bool = False) -> int:
382        """Add a new record to the database."""
383        # only serialize str narrators/ authors to db
384        _authors = [author for author in item.authors if isinstance(author, str)]
385        _narrators = [narrator for narrator in item.narrators if isinstance(narrator, str)]
386        db_id = await self.mass.music.database.insert(
387            self.db_table,
388            {
389                "name": item.name,
390                "sort_name": item.sort_name,
391                "version": item.version,
392                "favorite": item.favorite,
393                "metadata": serialize_to_json(item.metadata),
394                "publisher": item.publisher,
395                "authors": serialize_to_json(_authors),
396                "narrators": serialize_to_json(_narrators),
397                "duration": item.duration,
398                "search_name": create_safe_string(item.name, True, True),
399                "search_sort_name": create_safe_string(item.sort_name or "", True, True),
400                "timestamp_added": int(item.date_added.timestamp()) if item.date_added else UNSET,
401            },
402        )
403        # update/set external id lookup table
404        await self.set_external_ids(db_id, item.external_ids)
405        # update/set provider_mappings table
406        await self.set_provider_mappings(db_id, item.provider_mappings)
407        self.logger.debug("added %s to database (id: %s)", item.name, db_id)
408        await self._set_playlog(db_id, item)
409        await self._set_artist_mappings(item, db_id)
410
411        return db_id
412
413    async def _set_artist_mappings(
414        self, item: Audiobook, db_id: int, overwrite: bool = False
415    ) -> None:
416        # update artist mappings - the sync method in the provider model raises an exception
417        # if not all entries are either of type str or Artist
418        if overwrite:
419            # on overwrite, clear the audiobook_artists table first
420            await self.mass.music.database.delete(
421                DB_TABLE_AUDIOBOOK_ARTISTS,
422                {
423                    "audiobook_id": db_id,
424                },
425            )
426        if item.authors and isinstance(item.authors[0], Artist):
427            # only for type checking
428            authors = [author for author in item.authors if isinstance(author, Artist)]
429            for author in authors:
430                # just to be sure
431                author.artist_type = ArtistType.AUTHOR
432            await self._set_audiobook_authors_narrators(db_id, authors)
433        if item.narrators and isinstance(item.narrators[0], Artist):
434            # only for type checking
435            narrators = [narrator for narrator in item.narrators if isinstance(narrator, Artist)]
436            for narrator in narrators:
437                # just to be sure
438                narrator.artist_type = ArtistType.NARRATOR
439            await self._set_audiobook_authors_narrators(db_id, narrators)
440
441    async def _set_audiobook_authors_narrators(
442        self,
443        db_id: int,
444        artists: Iterable[Artist | ItemMapping],
445        overwrite: bool = False,
446    ) -> None:
447        """Write audiobook id and author/ narrator id to DB_TABLE_AUDIOBOOK_ARTISTS."""
448        for artist in artists:
449            await self._set_audiobook_author_narrator(db_id, artist=artist, overwrite=overwrite)
450
451    async def _set_audiobook_author_narrator(
452        self, db_id: int, artist: Artist | ItemMapping, overwrite: bool = False
453    ) -> ItemMapping:
454        """Store Album Artist info."""
455        db_artist: Artist | ItemMapping | None = None
456        if artist.provider == "library":
457            db_artist = artist
458        elif existing := await self.mass.music.artists.get_library_item_by_prov_id(
459            artist.item_id, artist.provider
460        ):
461            db_artist = existing
462
463        if not db_artist or overwrite:
464            # Convert ItemMapping to Artist if needed
465            artist_to_add = (
466                self.mass.music.artists.artist_from_item_mapping(artist)
467                if isinstance(artist, ItemMapping)
468                else artist
469            )
470            db_artist = await self.mass.music.artists.add_item_to_library(
471                artist_to_add, overwrite_existing=overwrite
472            )
473        # write (or update) record in album_artists table
474        await self.mass.music.database.insert_or_replace(
475            DB_TABLE_AUDIOBOOK_ARTISTS,
476            {
477                "audiobook_id": db_id,
478                "artist_id": int(db_artist.item_id),
479            },
480        )
481        return ItemMapping.from_item(db_artist)
482
483    async def _update_library_item(
484        self, item_id: str | int, update: Audiobook, overwrite: bool = False
485    ) -> None:
486        """Update existing record in the database."""
487        db_id = int(item_id)  # ensure integer
488        cur_item = await self.get_library_item(db_id)
489        metadata = update.metadata if overwrite else cur_item.metadata.update(update.metadata)
490        if not overwrite and update.metadata.images is not None:
491            # audiobooks have no image picker, so keep the cover in sync with the
492            # provider instead of accumulating merged entries
493            metadata.images = update.metadata.images
494        if not overwrite and update.metadata.collections is not None:
495            # always update collections to prevent stale empty ones
496            metadata.collections = update.metadata.collections
497        cur_item.external_ids.update(update.external_ids)
498        name = update.name if overwrite else cur_item.name
499        sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name
500        # only serialize str narrators/ authors to db
501        _update_authors = [author for author in update.authors if isinstance(author, str)]
502        _update_narrators = [narrator for narrator in update.narrators if isinstance(narrator, str)]
503        await self.mass.music.database.update(
504            self.db_table,
505            {"item_id": db_id},
506            {
507                "name": name,
508                "sort_name": sort_name,
509                "version": update.version if overwrite else cur_item.version or update.version,
510                "metadata": serialize_to_json(metadata),
511                "publisher": cur_item.publisher or update.publisher,
512                "authors": serialize_to_json(
513                    _update_authors if overwrite else cur_item.authors or _update_authors
514                ),
515                "narrators": serialize_to_json(
516                    _update_narrators if overwrite else cur_item.narrators or _update_narrators
517                ),
518                "duration": update.duration if overwrite else cur_item.duration or update.duration,
519                "search_name": create_safe_string(name, True, True),
520                "search_sort_name": create_safe_string(sort_name or "", True, True),
521                "timestamp_added": int(update.date_added.timestamp())
522                if update.date_added
523                else UNSET,
524            },
525        )
526        # update/set external id lookup table
527        await self.set_external_ids(
528            db_id, update.external_ids if overwrite else cur_item.external_ids
529        )
530        # update/set provider_mappings table
531        provider_mappings = (
532            update.provider_mappings
533            if overwrite
534            else {*update.provider_mappings, *cur_item.provider_mappings}
535        )
536        await self.set_provider_mappings(db_id, provider_mappings, overwrite)
537        self.logger.debug("updated %s in database: (id %s)", update.name, db_id)
538        await self._set_playlog(db_id, update)
539        await self._set_artist_mappings(update, db_id)
540
541    async def _set_playlog(self, db_id: int, media_item: Audiobook) -> None:
542        """Update/set the playlog table for the given audiobook db item_id."""
543        # Get user(s)
544        user: User | None = None
545        if session_user := get_current_user():
546            # this is the active session user that triggered the action
547            user = session_user
548        elif provider_user := await self.mass.music._get_user_for_provider(
549            media_item.provider_mappings
550        ):
551            # based on configured provider filter we can try to find a user
552            user = provider_user
553        if user:
554            user_ids = [user.user_id]
555        else:
556            # NOTE: if no user was found, we will alter the playlog for all users
557            user_ids = [user.user_id for user in await self.mass.webserver.auth.list_users()]
558
559        # cleanup provider specific entries for this item
560        # we always prefer the library playlog entry
561        for prov_mapping in media_item.provider_mappings:
562            for user_id in user_ids:
563                await self.mass.music.database.delete(
564                    DB_TABLE_PLAYLOG,
565                    {
566                        "media_type": self.media_type.value,
567                        "item_id": prov_mapping.item_id,
568                        "provider": prov_mapping.provider_instance,
569                        "userid": user_id,
570                    },
571                )
572        if media_item.fully_played is None and media_item.resume_position_ms is None:
573            return
574
575        for user_id in user_ids:
576            cur_entry = await self.mass.music.database.get_row(
577                DB_TABLE_PLAYLOG,
578                {
579                    "media_type": self.media_type.value,
580                    "item_id": db_id,
581                    "provider": "library",
582                    "userid": user_id,
583                },
584            )
585            seconds_played = int((media_item.resume_position_ms or 0) / 1000)
586            # abort if nothing changed
587            if (
588                cur_entry
589                and parse_optional_bool(cur_entry["fully_played"]) == media_item.fully_played
590                and abs((cur_entry["seconds_played"] or 0) - seconds_played) <= 2
591            ):
592                return
593
594            await self.mass.music.database.insert(
595                DB_TABLE_PLAYLOG,
596                {
597                    "item_id": db_id,
598                    "provider": "library",
599                    "media_type": media_item.media_type.value,
600                    "name": media_item.name,
601                    "image": serialize_to_json(media_item.image.to_dict())
602                    if media_item.image
603                    else None,
604                    "fully_played": media_item.fully_played,
605                    "seconds_played": seconds_played,
606                    "timestamp": utc_timestamp(),
607                    "userid": user_id,
608                },
609                allow_replace=True,
610            )
611
612    async def _authors_narrators(self, column: str) -> UniqueList[str]:
613        """Return all available authors."""
614        assert self.mass.music.database is not None  # for type checking
615        rows = await self.mass.music.database.get_rows_from_query(
616            query=f"SELECT DISTINCT {column} FROM {DB_TABLE_AUDIOBOOKS}"
617        )
618        result: set[str] = set()
619        for row in rows:
620            result.update(json_loads(row[column]))
621        return UniqueList(sorted(result))
622
623    def _sync_details_query_parts(self) -> tuple[str, str, dict[str, Any]]:
624        """Return extra (columns, joins, params) for the audiobooks sync-details query."""
625        # the sync loop needs the (str vs Artist) type of the stored authors/narrators
626        # plus the user-scoped resume state to detect changes on the provider side
627        params: dict[str, Any] = {}
628        # mirror base_query: scope the playlog lookup to the session user (if any) and
629        # pick at most one row (the most recent) so the join can never fan out
630        playlog_user_clause = ""
631        if session_user := get_current_user():
632            playlog_user_clause = "AND p2.userid = :playlog_userid "
633            params["playlog_userid"] = session_user.user_id
634        extra_columns = f"""
635            , EXISTS (
636                SELECT 1 FROM {DB_TABLE_AUDIOBOOK_ARTISTS}
637                JOIN artists ON artists.item_id = audiobook_artists.artist_id
638                WHERE audiobook_artists.audiobook_id = audiobooks.item_id
639                AND artists.artist_type = '{ArtistType.AUTHOR.value}'
640            ) AS has_author_artists
641            , EXISTS (
642                SELECT 1 FROM {DB_TABLE_AUDIOBOOK_ARTISTS}
643                JOIN artists ON artists.item_id = audiobook_artists.artist_id
644                WHERE audiobook_artists.audiobook_id = audiobooks.item_id
645                AND artists.artist_type = '{ArtistType.NARRATOR.value}'
646            ) AS has_narrator_artists
647            , json_type(audiobooks.authors, '$[0]') AS first_author_type
648            , json_type(audiobooks.narrators, '$[0]') AS first_narrator_type
649            , playlog.fully_played AS fully_played
650            , playlog.seconds_played * 1000 AS resume_position_ms
651        """
652        extra_joins = (
653            f"LEFT JOIN {DB_TABLE_PLAYLOG} ON playlog.id = ("
654            f"SELECT p2.id FROM {DB_TABLE_PLAYLOG} p2 "
655            "WHERE p2.item_id = CAST(audiobooks.item_id AS TEXT) "
656            "AND p2.media_type = 'audiobook' "
657            f"{playlog_user_clause}ORDER BY p2.timestamp DESC LIMIT 1)"
658        )
659        return extra_columns, extra_joins, params
660
661    def _parse_sync_details_row(self, db_row: Mapping[str, Any]) -> AudiobookSyncDetails:
662        """Parse a raw sync-details db row into an AudiobookSyncDetails object."""
663        # authors/narrators hydrate as str only when there are no linked Artist records
664        # and the stored JSON column holds plain strings (mirrors _parse_db_row)
665        resume_position_ms = db_row["resume_position_ms"]
666        return AudiobookSyncDetails(
667            item_id=db_row["item_id"],
668            favorite=bool(db_row["favorite"]),
669            date_added=datetime.fromtimestamp(db_row["timestamp_added"], tz=UTC),
670            provider_mappings=self._parse_sync_details_mappings(db_row),
671            author_is_str=not db_row["has_author_artists"]
672            and db_row["first_author_type"] == "text",
673            narrator_is_str=not db_row["has_narrator_artists"]
674            and db_row["first_narrator_type"] == "text",
675            fully_played=parse_optional_bool(db_row["fully_played"]),
676            resume_position_ms=int(resume_position_ms) if resume_position_ms is not None else None,
677        )
678
679    def _parse_summary_row(self, db_row: Mapping[str, Any]) -> AudiobookSummary:
680        """Parse a raw summary db row into an AudiobookSummary object."""
681        item = cast("AudiobookSummary", super()._parse_summary_row(db_row))
682        item.version = db_row["version"] or ""
683        item.publisher = db_row["publisher"]
684        item.duration = db_row["duration"] or 0
685        item.fully_played = parse_optional_bool(db_row["fully_played"])
686        item.resume_position_ms = db_row["resume_position_ms"]
687        # authors/narrators: prefer the linked artist records (as slim mappings),
688        # fall back to the plain string values stored on the audiobook itself
689        authors: list[ItemMappingSummary] = []
690        narrators: list[ItemMappingSummary] = []
691        if raw_audiobook_artists := db_row["audiobook_artists"]:
692            for artist in json_loads(raw_audiobook_artists):
693                mapping = ItemMappingSummary(
694                    media_type=MediaType.ARTIST,
695                    item_id=str(artist["item_id"]),
696                    provider="library",
697                    name=artist["name"],
698                    sort_name=artist["sort_name"],
699                )
700                if artist["artist_type"] == ArtistType.AUTHOR.value:
701                    authors.append(mapping)
702                elif artist["artist_type"] == ArtistType.NARRATOR.value:
703                    narrators.append(mapping)
704        item.authors = UniqueList(authors or json_loads(db_row["authors"] or "[]"))
705        item.narrators = UniqueList(narrators or json_loads(db_row["narrators"] or "[]"))
706        return item
707