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