music-assistant-server

1"""MusicController: Orchestrates all data from music providers and sync to internal database."""
2
3from __future__ import annotations
4
5import asyncio
6import logging
7import os
8import shutil
9import time
10from collections.abc import Iterable, Sequence
11from contextlib import suppress
12from copy import deepcopy
13from datetime import datetime
14from itertools import zip_longest
15from math import inf
16from typing import TYPE_CHECKING, Any, Final, cast
17
18import numpy as np
19from music_assistant_models.config_entries import ConfigEntry, ConfigValueType
20from music_assistant_models.enums import (
21    ConfigEntryType,
22    EventType,
23    MediaType,
24    ProviderFeature,
25    ProviderType,
26)
27from music_assistant_models.errors import (
28    InvalidProviderID,
29    InvalidProviderURI,
30    MediaNotFoundError,
31    MusicAssistantError,
32)
33from music_assistant_models.helpers import get_global_cache_value
34from music_assistant_models.media_items import (
35    Artist,
36    AudioFormat,
37    BrowseFolder,
38    ItemMapping,
39    MediaItemType,
40    ProviderMapping,
41    RecommendationFolder,
42    SearchResults,
43    Track,
44)
45from music_assistant_models.provider import SyncTask
46from music_assistant_models.unique_list import UniqueList
47
48from music_assistant.constants import (
49    DB_TABLE_ALBUM_ARTISTS,
50    DB_TABLE_ALBUM_TRACKS,
51    DB_TABLE_ALBUMS,
52    DB_TABLE_ARTISTS,
53    DB_TABLE_AUDIOBOOKS,
54    DB_TABLE_LOUDNESS_MEASUREMENTS,
55    DB_TABLE_PLAYLISTS,
56    DB_TABLE_PLAYLOG,
57    DB_TABLE_PODCASTS,
58    DB_TABLE_PROVIDER_MAPPINGS,
59    DB_TABLE_RADIOS,
60    DB_TABLE_SETTINGS,
61    DB_TABLE_SMART_FADES_ANALYSIS,
62    DB_TABLE_TRACK_ARTISTS,
63    DB_TABLE_TRACKS,
64    PROVIDERS_WITH_SHAREABLE_URLS,
65)
66from music_assistant.controllers.streams.smart_fades.fades import SMART_CROSSFADE_DURATION
67from music_assistant.controllers.webserver.helpers.auth_middleware import get_current_user
68from music_assistant.helpers.api import api_command
69from music_assistant.helpers.compare import compare_strings, compare_version, create_safe_string
70from music_assistant.helpers.database import UNSET, DatabaseConnection
71from music_assistant.helpers.datetime import utc_timestamp
72from music_assistant.helpers.json import json_dumps, json_loads, serialize_to_json
73from music_assistant.helpers.tags import split_artists
74from music_assistant.helpers.uri import parse_uri
75from music_assistant.helpers.util import TaskManager, parse_title_and_version
76from music_assistant.models.core_controller import CoreController
77from music_assistant.models.music_provider import MusicProvider
78from music_assistant.models.smart_fades import SmartFadesAnalysis, SmartFadesAnalysisFragment
79
80from .media.albums import AlbumsController
81from .media.artists import ArtistsController
82from .media.audiobooks import AudiobooksController
83from .media.genres import GenreController
84from .media.playlists import PlaylistController
85from .media.podcasts import PodcastsController
86from .media.radio import RadioController
87from .media.tracks import TracksController
88
89if TYPE_CHECKING:
90    from music_assistant_models.auth import User
91    from music_assistant_models.config_entries import CoreConfig
92    from music_assistant_models.media_items import Audiobook, PodcastEpisode
93
94    from music_assistant import MusicAssistant
95
96
97CONF_RESET_DB = "reset_db"
98DEFAULT_SYNC_INTERVAL = 12 * 60  # default sync interval in minutes
99CONF_SYNC_INTERVAL = "sync_interval"
100CONF_DELETED_PROVIDERS = "deleted_providers"
101DB_SCHEMA_VERSION: Final[int] = 26
102
103CACHE_CATEGORY_LAST_SYNC: Final[int] = 9
104CACHE_CATEGORY_SEARCH_RESULTS: Final[int] = 10
105LAST_PROVIDER_INSTANCE_SCAN: Final[str] = "last_provider_instance_scan"
106PROVIDER_INSTANCE_SCAN_INTERVAL: Final[int] = 30 * 24 * 60 * 60  # one month in seconds
107
108
109class MusicController(CoreController):
110    """Several helpers around the musicproviders."""
111
112    domain: str = "music"
113    config: CoreConfig
114
115    def __init__(self, mass: MusicAssistant) -> None:
116        """Initialize class."""
117        super().__init__(mass)
118        self.cache = self.mass.cache
119        self.artists = ArtistsController(self.mass)
120        self.albums = AlbumsController(self.mass)
121        self.tracks = TracksController(self.mass)
122        self.radio = RadioController(self.mass)
123        self.playlists = PlaylistController(self.mass)
124        self.audiobooks = AudiobooksController(self.mass)
125        self.podcasts = PodcastsController(self.mass)
126        self.genres = GenreController(self.mass)
127        self.in_progress_syncs: list[SyncTask] = []
128        self._database: DatabaseConnection | None = None
129        self._sync_lock = asyncio.Lock()
130        self.manifest.name = "Music controller"
131        self.manifest.description = (
132            "Music Assistant's core controller which manages all music from all providers."
133        )
134        self.manifest.icon = "archive-music"
135
136    @property
137    def database(self) -> DatabaseConnection:
138        """Return the database connection."""
139        if self._database is None:
140            raise RuntimeError("Database not initialized")
141        return self._database
142
143    async def get_config_entries(
144        self,
145        action: str | None = None,
146        values: dict[str, ConfigValueType] | None = None,
147    ) -> tuple[ConfigEntry, ...]:
148        """Return all Config Entries for this core module (if any)."""
149        entries = (
150            ConfigEntry(
151                key=CONF_RESET_DB,
152                type=ConfigEntryType.ACTION,
153                label="Reset library database",
154                description="This will issue a full reset of the library "
155                "database and trigger a full sync. Only use this option as a last resort "
156                "if you are seeing issues with the library database.",
157                category="generic",
158                advanced=True,
159            ),
160        )
161        if action == CONF_RESET_DB:
162            await self._reset_database()
163            await self.mass.cache.clear()
164            await self.start_sync()
165            entries = (
166                *entries,
167                ConfigEntry(
168                    key=CONF_RESET_DB,
169                    type=ConfigEntryType.LABEL,
170                    label="The database has been reset.",
171                ),
172            )
173        return entries
174
175    async def setup(self, config: CoreConfig) -> None:
176        """Async initialize of module."""
177        self.config = config
178        # setup library database
179        await self._setup_database()
180        # make sure to finish any removal jobs
181        for removed_provider in self.mass.config.get_raw_core_config_value(
182            self.domain, CONF_DELETED_PROVIDERS, []
183        ):
184            await self.cleanup_provider(removed_provider)
185        # schedule cleanup task for matching provider instances
186        last_scan = cast(
187            "int",
188            self.mass.config.get_raw_core_config_value(self.domain, LAST_PROVIDER_INSTANCE_SCAN, 0),
189        )
190        if time.time() - last_scan > PROVIDER_INSTANCE_SCAN_INTERVAL:
191            self.mass.call_later(60, self.correct_multi_instance_provider_mappings)
192
193    async def close(self) -> None:
194        """Cleanup on exit."""
195        if self._database:
196            await self._database.close()
197
198    async def on_provider_loaded(self, provider: MusicProvider) -> None:
199        """Handle logic when a provider is loaded."""
200        await self.schedule_provider_sync(provider.instance_id)
201
202    async def on_provider_unload(self, provider: MusicProvider) -> None:
203        """Handle logic when a provider is (about to get) unloaded."""
204        # make sure to stop any running sync tasks first
205        for sync_task in list(self.in_progress_syncs):
206            if sync_task.provider_instance == provider.instance_id:
207                if sync_task.task:
208                    sync_task.task.cancel()
209
210    @property
211    def providers(self) -> list[MusicProvider]:
212        """
213        Return all loaded/running MusicProviders (instances).
214
215        Note that this applies user provider filters (for all user types).
216        """
217        user = get_current_user()
218        user_provider_filter = user.provider_filter if user else None
219        return [
220            x
221            for x in self.mass.providers
222            if x.type == ProviderType.MUSIC
223            and (not user_provider_filter or x.instance_id in user_provider_filter)
224        ]
225
226    @api_command("music/sync")
227    async def start_sync(
228        self,
229        media_types: list[MediaType] | None = None,
230        providers: list[str] | None = None,
231    ) -> None:
232        """Start running the sync of (all or selected) musicproviders.
233
234        media_types: only sync these media types. None for all.
235        providers: only sync these provider instances. None for all.
236        """
237        if media_types is None:
238            media_types = MediaType.ALL
239        if providers is None:
240            providers = [x.instance_id for x in self.providers]
241
242        for media_type in media_types:
243            for provider in self.providers:
244                if provider.instance_id not in providers:
245                    continue
246                if not provider.library_supported(media_type):
247                    continue
248                # handle mediatype specific sync config
249                conf_key = f"library_sync_{media_type}s"
250                sync_conf = await self.mass.config.get_provider_config_value(
251                    provider.instance_id, conf_key
252                )
253                if not sync_conf:
254                    continue
255                self._start_provider_sync(provider, media_type)
256
257    @api_command("music/synctasks")
258    def get_running_sync_tasks(self) -> list[SyncTask]:
259        """Return list with providers that are currently (scheduled for) syncing."""
260        return self.in_progress_syncs
261
262    @api_command("music/search")
263    async def search(
264        self,
265        search_query: str,
266        media_types: list[MediaType] = MediaType.ALL,
267        limit: int = 25,
268        library_only: bool = False,
269    ) -> SearchResults:
270        """Perform global search for media items on all providers.
271
272        :param search_query: Search query.
273        :param media_types: A list of media_types to include.
274        :param limit: number of items to return in the search (per type).
275        """
276        # use cache to avoid repeated searches
277        search_providers = sorted(self.get_unique_providers())
278        cache_provider_key = "library" if library_only else ",".join(search_providers)
279        cache_key = f"{search_query}{'-'.join(sorted([mt.value for mt in media_types]))}-{limit}-{library_only}-{cache_provider_key}"  # noqa: E501
280        if cache := await self.mass.cache.get(
281            key=cache_key, provider=self.domain, category=CACHE_CATEGORY_SEARCH_RESULTS
282        ):
283            return cache
284        if not media_types:
285            media_types = MediaType.ALL
286        # Check if the search query is a streaming provider public shareable URL
287        try:
288            media_type, provider_instance_id_or_domain, item_id = await parse_uri(
289                search_query, validate_id=True
290            )
291        except InvalidProviderURI:
292            pass
293        except InvalidProviderID as err:
294            self.logger.warning("%s", str(err))
295            return SearchResults()
296        else:
297            # handle special case of direct shareable url search
298            if provider_instance_id_or_domain in PROVIDERS_WITH_SHAREABLE_URLS:
299                try:
300                    item = await self.get_item(
301                        media_type=media_type,
302                        item_id=item_id,
303                        provider_instance_id_or_domain=provider_instance_id_or_domain,
304                    )
305                except MusicAssistantError as err:
306                    self.logger.warning("%s", str(err))
307                    return SearchResults()
308                else:
309                    if media_type == MediaType.ARTIST:
310                        return SearchResults(artists=[item])
311                    if media_type == MediaType.ALBUM:
312                        return SearchResults(albums=[item])
313                    if media_type == MediaType.TRACK:
314                        return SearchResults(tracks=[item])
315                    if media_type == MediaType.PLAYLIST:
316                        return SearchResults(playlists=[item])
317                    if media_type == MediaType.AUDIOBOOK:
318                        return SearchResults(audiobooks=[item])
319                    if media_type == MediaType.PODCAST:
320                        return SearchResults(podcasts=[item])
321                    return SearchResults()
322        # handle normal global search by querying all providers
323        results_per_provider: list[SearchResults] = []
324        # always first search the library
325        library_results = await self.search_library(search_query, media_types, limit=limit)
326        results_per_provider.append(library_results)
327        if not library_only:
328            # create a set of all provider item ids already in library
329            # this way we can avoid returning duplicates in the search results
330            all_prov_item_ids = {
331                (item.media_type, prov_mapping.provider_domain, prov_mapping.item_id)
332                for items in (
333                    library_results.artists,
334                    library_results.albums,
335                    library_results.tracks,
336                    library_results.playlists,
337                    library_results.audiobooks,
338                    library_results.podcasts,
339                )
340                for item in items
341                for prov_mapping in item.provider_mappings
342            }
343            # include results from library + all (unique) music providers
344            results_per_provider += await asyncio.gather(
345                *[
346                    self._search_provider(
347                        search_query,
348                        provider_instance,
349                        media_types,
350                        limit=limit,
351                        skip_item_ids=all_prov_item_ids,
352                    )
353                    for provider_instance in search_providers
354                ],
355            )
356        # return result from all providers while keeping index
357        # so the result is sorted as each provider delivered
358        result = SearchResults(
359            artists=[
360                item
361                for sublist in zip_longest(*[x.artists for x in results_per_provider])
362                for item in sublist
363                if item is not None
364            ][:limit],
365            albums=[
366                item
367                for sublist in zip_longest(*[x.albums for x in results_per_provider])
368                for item in sublist
369                if item is not None
370            ][:limit],
371            tracks=[
372                item
373                for sublist in zip_longest(*[x.tracks for x in results_per_provider])
374                for item in sublist
375                if item is not None
376            ][:limit],
377            playlists=[
378                item
379                for sublist in zip_longest(*[x.playlists for x in results_per_provider])
380                for item in sublist
381                if item is not None
382            ][:limit],
383            radio=[
384                item
385                for sublist in zip_longest(*[x.radio for x in results_per_provider])
386                for item in sublist
387                if item is not None
388            ][:limit],
389            audiobooks=[
390                item
391                for sublist in zip_longest(*[x.audiobooks for x in results_per_provider])
392                for item in sublist
393                if item is not None
394            ][:limit],
395            podcasts=[
396                item
397                for sublist in zip_longest(*[x.podcasts for x in results_per_provider])
398                for item in sublist
399                if item is not None
400            ][:limit],
401        )
402
403        # the search results should already be sorted by relevance
404        # but we apply one extra round of sorting and that is to put exact name
405        # matches and library items first
406        result.artists = self._sort_search_result(search_query, result.artists)
407        result.albums = self._sort_search_result(search_query, result.albums)
408        result.tracks = self._sort_search_result(search_query, result.tracks)
409        result.playlists = self._sort_search_result(search_query, result.playlists)
410        result.radio = self._sort_search_result(search_query, result.radio)
411        result.audiobooks = self._sort_search_result(search_query, result.audiobooks)
412        result.podcasts = self._sort_search_result(search_query, result.podcasts)
413        await self.mass.cache.set(
414            key=cache_key,
415            data=result,
416            expiration=600,
417            provider=self.domain,
418            category=CACHE_CATEGORY_SEARCH_RESULTS,
419        )
420        return result
421
422    async def _search_provider(
423        self,
424        search_query: str,
425        provider_instance_id_or_domain: str,
426        media_types: list[MediaType],
427        limit: int = 10,
428        skip_item_ids: set[tuple[MediaType, str, str]] | None = None,
429    ) -> SearchResults:
430        """Perform search on given provider.
431
432        :param search_query: Search query
433        :param provider_instance_id_or_domain: instance_id or domain of the provider
434                                               to perform the search on.
435        :param media_types: A list of media_types to include.
436        :param limit: number of items to return in the search (per type).
437        """
438        prov = self.mass.get_provider(provider_instance_id_or_domain)
439        if not prov:
440            return SearchResults()
441        if ProviderFeature.SEARCH not in prov.supported_features:
442            return SearchResults()
443
444        # create safe search string
445        search_query = search_query.replace("/", " ").replace("'", "")
446        prov_search_results = await prov.search(
447            search_query,
448            media_types,
449            limit,
450        )
451        if skip_item_ids:
452            # filter out items already in skip_item_ids
453            prov_search_results.artists = [
454                item
455                for item in prov_search_results.artists
456                if (item.media_type, prov.domain, item.item_id) not in skip_item_ids
457            ]
458            prov_search_results.albums = [
459                item
460                for item in prov_search_results.albums
461                if (item.media_type, prov.domain, item.item_id) not in skip_item_ids
462            ]
463            prov_search_results.tracks = [
464                item
465                for item in prov_search_results.tracks
466                if (item.media_type, prov.domain, item.item_id) not in skip_item_ids
467            ]
468            prov_search_results.playlists = [
469                item
470                for item in prov_search_results.playlists
471                if (item.media_type, prov.domain, item.item_id) not in skip_item_ids
472            ]
473            prov_search_results.audiobooks = [
474                item
475                for item in prov_search_results.audiobooks
476                if (item.media_type, prov.domain, item.item_id) not in skip_item_ids
477            ]
478            prov_search_results.podcasts = [
479                item
480                for item in prov_search_results.podcasts
481                if (item.media_type, prov.domain, item.item_id) not in skip_item_ids
482            ]
483        return prov_search_results
484
485    async def search_library(
486        self,
487        search_query: str,
488        media_types: list[MediaType],
489        limit: int = 10,
490    ) -> SearchResults:
491        """Perform search on the library.
492
493        :param search_query: Search query
494        :param media_types: A list of media_types to include.
495        :param limit: number of items to return in the search (per type).
496        """
497        result = SearchResults()
498        for media_type in media_types:
499            ctrl = self.get_controller(media_type)
500            search_results = await ctrl.search(search_query, "library", limit=limit)
501            if search_results:
502                if media_type == MediaType.ARTIST:
503                    result.artists = search_results
504                elif media_type == MediaType.ALBUM:
505                    result.albums = search_results
506                elif media_type == MediaType.TRACK:
507                    result.tracks = search_results
508                elif media_type == MediaType.PLAYLIST:
509                    result.playlists = search_results
510                elif media_type == MediaType.RADIO:
511                    result.radio = search_results
512                elif media_type == MediaType.AUDIOBOOK:
513                    result.audiobooks = search_results
514                elif media_type == MediaType.PODCAST:
515                    result.podcasts = search_results
516        return result
517
518    @api_command("music/browse")
519    async def browse(self, path: str | None = None) -> Sequence[MediaItemType | BrowseFolder]:
520        """Browse Music providers."""
521        if not path or path == "root":
522            # root level; folder per provider
523            root_items: list[BrowseFolder] = []
524            for prov in self.providers:
525                if ProviderFeature.BROWSE not in prov.supported_features:
526                    continue
527                root_items.append(
528                    BrowseFolder(
529                        item_id="root",
530                        provider=prov.domain,
531                        path=f"{prov.instance_id}://",
532                        uri=f"{prov.instance_id}://",
533                        name=prov.name,
534                    )
535                )
536            return root_items
537
538        # provider level
539        prepend_items: list[BrowseFolder] = []
540        provider_instance, sub_path = path.split("://", 1)
541        prov = self.mass.get_provider(provider_instance)
542        # handle regular provider listing, always add back folder first
543        if not prov or not sub_path:
544            prepend_items.append(
545                BrowseFolder(item_id="root", provider="library", path="root", name="..")
546            )
547            if not prov:
548                return prepend_items
549        else:
550            back_path = f"{provider_instance}://" + "/".join(sub_path.split("/")[:-1])
551            prepend_items.append(
552                BrowseFolder(
553                    item_id="back",
554                    provider=provider_instance,
555                    path=back_path,
556                    name="..",
557                )
558            )
559        # limit -1 to account for the prepended items
560        prov_items = await prov.browse(path=path)
561        return prepend_items + prov_items
562
563    @api_command("music/recently_played_items")
564    async def recently_played(
565        self,
566        limit: int = 10,
567        media_types: list[MediaType] | None = None,
568        userid: str | None = None,
569        queue_id: str | None = None,
570        fully_played_only: bool = True,
571        user_initiated_only: bool = False,
572    ) -> list[ItemMapping]:
573        """Return a list of the last played items.
574
575        :param limit: Maximum number of items to return.
576        :param media_types: Filter by media types.
577        :param userid: Filter by specific user ID.
578        :param queue_id: Filter by specific queue ID.
579        :param fully_played_only: If True, only return fully played items.
580        :param user_initiated_only: If True, only return items initiated by the user.
581        """
582        if media_types is None:
583            media_types = MediaType.ALL
584        media_types_str = "(" + ",".join(f'"{x}"' for x in media_types) + ")"
585        available_providers = ("library", *self.get_unique_providers())
586        available_providers_str = "(" + ",".join(f'"{x}"' for x in available_providers) + ")"
587        query = (
588            f"SELECT * FROM {DB_TABLE_PLAYLOG} "
589            f"WHERE media_type in {media_types_str} "
590            f"AND provider in {available_providers_str} "
591        )
592        params: dict[str, Any] = {}
593        if fully_played_only:
594            query += "AND fully_played = 1 "
595        if user_initiated_only:
596            query += "AND user_initiated = 1 "
597        if userid:
598            query += "AND userid = :userid "
599            params["userid"] = userid
600        elif user := get_current_user():
601            query += "AND userid = :userid "
602            params["userid"] = user.user_id
603        if queue_id:
604            query += "AND queue_id = :queue_id "
605            params["queue_id"] = queue_id
606        query += "ORDER BY timestamp DESC"
607        db_rows = await self.mass.music.database.get_rows_from_query(
608            query, params=params or None, limit=limit
609        )
610        result: list[ItemMapping] = []
611        available_providers = ("library", *get_global_cache_value("available_providers", []))
612
613        # Get user provider filter if set
614        user = get_current_user()
615        user_provider_filter = user.provider_filter if user and user.provider_filter else None
616
617        for db_row in db_rows:
618            provider = db_row["provider"]
619            # Apply user provider filter
620            if user_provider_filter and provider not in user_provider_filter:
621                continue
622            result.append(
623                ItemMapping.from_dict(
624                    {
625                        "item_id": db_row["item_id"],
626                        "provider": provider,
627                        "media_type": db_row["media_type"],
628                        "name": db_row["name"],
629                        "image": json_loads(db_row["image"]) if db_row["image"] else None,
630                        "available": provider in available_providers,
631                    }
632                )
633            )
634        return result
635
636    @api_command("music/recently_added_tracks")
637    async def recently_added_tracks(self, limit: int = 10) -> list[Track]:
638        """Return a list of the last added tracks."""
639        return await self.tracks.library_items(limit=limit, order_by="timestamp_added_desc")
640
641    @api_command("music/in_progress_items")
642    async def in_progress_items(
643        self, limit: int = 10, all_users: bool = False
644    ) -> list[ItemMapping]:
645        """Return a list of the Audiobooks and PodcastEpisodes that are in progress."""
646        available_providers = ("library", *self.get_unique_providers())
647        available_providers_str = "(" + ",".join(f'"{x}"' for x in available_providers) + ")"
648        query = (
649            f"SELECT * FROM {DB_TABLE_PLAYLOG} "
650            f"WHERE media_type in ('audiobook', 'podcast_episode') AND fully_played = 0 "
651            f"AND provider in {available_providers_str} "
652            "AND seconds_played > 0 "
653        )
654        if not all_users and (user := get_current_user()):
655            query += f"AND userid = '{user.user_id}' "
656
657        query += "ORDER BY timestamp DESC"
658        db_rows = await self.mass.music.database.get_rows_from_query(query, limit=limit)
659        result: list[ItemMapping] = []
660
661        # Get user provider filter if set
662        user = get_current_user()
663        user_provider_filter = user.provider_filter if user and user.provider_filter else None
664
665        for db_row in db_rows:
666            provider = db_row["provider"]
667            # Apply user provider filter
668            if user_provider_filter and provider not in user_provider_filter:
669                continue
670            result.append(
671                ItemMapping.from_dict(
672                    {
673                        "item_id": db_row["item_id"],
674                        "provider": provider,
675                        "media_type": db_row["media_type"],
676                        "name": db_row["name"],
677                        "image": json_loads(db_row["image"]) if db_row["image"] else None,
678                        "available": provider in available_providers,
679                    }
680                )
681            )
682        return result
683
684    async def get_playlog_provider_item_ids(
685        self, provider_instance_id: str, limit: int = 0, userid: str | None = None
686    ) -> list[tuple[MediaType, str]]:
687        """Return a list of MediaType and provider_item_id of items in playlog of provider."""
688        # check if there is a provider user
689        # this method is not available in the frontend, so no need to check for session users.
690        user: User | None = None
691        if userid:
692            # userid overridden by parameter
693            user = await self.mass.webserver.auth.get_user(userid)
694        elif provider_user := await self._get_user_for_provider(provider_instance_id):
695            # based on configured provider filter we can try to find a user
696            user = provider_user
697
698        query = (
699            f"SELECT * FROM {DB_TABLE_PLAYLOG} "
700            "WHERE media_type in ('audiobook', 'podcast_episode') "
701            f"AND provider in ('library','{provider_instance_id}')"
702        )
703
704        if user:
705            # NOTE: if no user was found, we will return playlog items for all users
706            query += f" AND userid = '{user.user_id}'"
707        db_rows = await self.mass.music.database.get_rows_from_query(query, limit=limit)
708
709        result: list[tuple[MediaType, str]] = []
710        for db_row in db_rows:
711            if db_row["provider"] == "library":
712                # If the provider is library, we need to make sure that the item
713                # is part of the passed provider_instance_id.
714                # A podcast_episode cannot be in the provider_mappings
715                # so these entries must be audiobooks.
716                subquery = (
717                    f"SELECT * FROM {DB_TABLE_PROVIDER_MAPPINGS} "
718                    f"WHERE media_type = 'audiobook' AND item_id = {db_row['item_id']} "
719                    f"AND provider_instance = '{provider_instance_id}'"
720                )
721                subrow = await self.mass.music.database.get_rows_from_query(subquery)
722                if len(subrow) != 1:
723                    continue
724                result.append((MediaType.AUDIOBOOK, subrow[0]["provider_item_id"]))
725                continue
726            # non library - item id is provider_item_id
727            result.append((MediaType(db_row["media_type"]), db_row["item_id"]))
728
729        return result
730
731    @api_command("music/item_by_uri")
732    async def get_item_by_uri(self, uri: str) -> MediaItemType | BrowseFolder:
733        """Fetch MediaItem by uri."""
734        media_type, provider_instance_id_or_domain, item_id = await parse_uri(uri)
735        return await self.get_item(
736            media_type=media_type,
737            item_id=item_id,
738            provider_instance_id_or_domain=provider_instance_id_or_domain,
739        )
740
741    @api_command("music/recommendations")
742    async def recommendations(self) -> list[RecommendationFolder]:
743        """Get all recommendations."""
744        recommendation_providers = [
745            x for x in self.providers if ProviderFeature.RECOMMENDATIONS in x.supported_features
746        ]
747        results_per_provider: list[list[RecommendationFolder]] = await asyncio.gather(
748            self._get_default_recommendations(),
749            *[
750                self._get_provider_recommendations(provider_instance)
751                for provider_instance in recommendation_providers
752            ],
753        )
754        # return result from all providers while keeping index
755        # so the result is sorted as each provider delivered
756        return [item for sublist in zip_longest(*results_per_provider) for item in sublist if item]
757
758    @api_command("music/item")
759    async def get_item(
760        self,
761        media_type: MediaType,
762        item_id: str,
763        provider_instance_id_or_domain: str,
764    ) -> MediaItemType | BrowseFolder:
765        """Get single music item by id and media type."""
766        if provider_instance_id_or_domain == "database":
767            # backwards compatibility - to remove when 2.0 stable is released
768            provider_instance_id_or_domain = "library"
769        if provider_instance_id_or_domain == "builtin":
770            # handle special case of 'builtin' MusicProvider which allows us to play regular url's
771            return await self.mass.get_provider("builtin").parse_item(item_id)
772        if media_type == MediaType.PODCAST_EPISODE:
773            # special case for podcast episodes
774            return await self.podcasts.episode(item_id, provider_instance_id_or_domain)
775        if media_type == MediaType.FOLDER:
776            # special case for folders
777            return BrowseFolder(
778                item_id=item_id,
779                provider=provider_instance_id_or_domain,
780                name=item_id,
781            )
782        ctrl = self.get_controller(media_type)
783        return await ctrl.get(
784            item_id=item_id,
785            provider_instance_id_or_domain=provider_instance_id_or_domain,
786        )
787
788    @api_command("music/get_library_item")
789    async def get_library_item_by_prov_id(
790        self,
791        media_type: MediaType,
792        item_id: str,
793        provider_instance_id_or_domain: str,
794    ) -> MediaItemType | None:
795        """Get single library music item by id and media type."""
796        ctrl = self.get_controller(media_type)
797        return await ctrl.get_library_item_by_prov_id(
798            item_id=item_id,
799            provider_instance_id_or_domain=provider_instance_id_or_domain,
800        )
801
802    @api_command("music/favorites/add_item")
803    async def add_item_to_favorites(
804        self,
805        item: str | MediaItemType | ItemMapping,
806    ) -> None:
807        """Add an item to the favorites."""
808        if isinstance(item, str):
809            item = await self.get_item_by_uri(item)
810        # make sure we have a full library item
811        # a favorite must always be in the library
812        full_item = await self.get_item(
813            item.media_type,
814            item.item_id,
815            item.provider,
816        )
817        if full_item.provider != "library":
818            full_item = await self.add_item_to_library(full_item)
819        # set favorite in library db
820        ctrl = self.get_controller(item.media_type)
821        await ctrl.set_favorite(
822            full_item.item_id,
823            True,
824        )
825        # forward to provider(s) if needed
826        for prov_mapping in full_item.provider_mappings:
827            provider = self.mass.get_provider(prov_mapping.provider_instance)
828            if not provider or not provider.library_favorites_edit_supported(full_item.media_type):
829                continue
830            await provider.set_favorite(prov_mapping.item_id, full_item.media_type, True)
831
832    @api_command("music/favorites/remove_item")
833    async def remove_item_from_favorites(
834        self,
835        media_type: MediaType,
836        library_item_id: str | int,
837    ) -> None:
838        """Remove (library) item from the favorites."""
839        ctrl = self.get_controller(media_type)
840        await ctrl.set_favorite(
841            library_item_id,
842            False,
843        )
844        # forward to provider(s) if needed
845        full_item = await ctrl.get_library_item(library_item_id)
846        for prov_mapping in full_item.provider_mappings:
847            provider = self.mass.get_provider(prov_mapping.provider_instance)
848            if not provider or not provider.library_favorites_edit_supported(full_item.media_type):
849                continue
850            self.mass.create_task(provider.set_favorite(prov_mapping.item_id, media_type, False))
851
852    @api_command("music/library/remove_item")
853    async def remove_item_from_library(
854        self, media_type: MediaType, library_item_id: str | int, recursive: bool = True
855    ) -> None:
856        """
857        Remove item from the library.
858
859        Destructive! Will remove the item and all dependants.
860        """
861        ctrl = self.get_controller(media_type)
862        # remove from provider(s) library
863        full_item = await ctrl.get_library_item(library_item_id)
864        for prov_mapping in full_item.provider_mappings:
865            if not prov_mapping.in_library:
866                continue
867            provider = self.mass.get_provider(prov_mapping.provider_instance)
868            if not provider or not provider.library_edit_supported(full_item.media_type):
869                continue
870            if not provider.library_sync_back_enabled(full_item.media_type):
871                continue
872            prov_mapping.in_library = False
873            self.mass.create_task(provider.library_remove(prov_mapping.item_id, media_type))
874        # remove from library
875        await ctrl.remove_item_from_library(library_item_id, recursive)
876
877    @api_command("music/library/add_item")
878    async def add_item_to_library(
879        self, item: str | MediaItemType | ItemMapping, overwrite_existing: bool = False
880    ) -> MediaItemType:
881        """Add item (uri or mediaitem) to the library."""
882        if isinstance(item, ItemMapping):
883            # handle browse results that are returned as ItemMappings
884            item = item.uri
885        # ensure we have a full item
886        if isinstance(item, str):
887            full_item = await self.get_item_by_uri(item)
888        # For builtin provider (manual URLs), use the provided item directly
889        # to preserve custom modifications (name, images, etc.)
890        # For other providers, fetch fresh to ensure data validity
891        elif item.provider == "builtin":
892            full_item = item
893        else:
894            full_item = await self.get_item(
895                item.media_type,
896                item.item_id,
897                item.provider,
898            )
899        # add to provider(s) library first
900        for prov_mapping in full_item.provider_mappings:
901            provider = self.mass.get_provider(prov_mapping.provider_instance)
902            if not provider or not provider.library_edit_supported(full_item.media_type):
903                continue
904            if not provider.library_sync_back_enabled(full_item.media_type):
905                continue
906            prov_item = deepcopy(full_item) if full_item.provider == "library" else full_item
907            prov_item.provider = prov_mapping.provider_instance
908            prov_item.item_id = prov_mapping.item_id
909            prov_mapping.in_library = True
910            self.mass.create_task(provider.library_add(prov_item))
911        # add (or overwrite) to library
912        ctrl = self.get_controller(full_item.media_type)
913        library_item = await ctrl.add_item_to_library(full_item, overwrite_existing)
914        # perform full metadata scan
915        await self.mass.metadata.update_metadata(library_item, overwrite_existing)
916        return library_item
917
918    async def refresh_items(self, items: list[MediaItemType]) -> None:
919        """Refresh MediaItems to force retrieval of full info and matches.
920
921        Creates background tasks to process the action.
922        """
923        async with TaskManager(self.mass) as tg:
924            for media_item in items:
925                tg.create_task(self.refresh_item(media_item))
926
927    @api_command("music/refresh_item")
928    async def refresh_item(  # noqa: PLR0915
929        self,
930        media_item: str | MediaItemType,
931    ) -> MediaItemType | None:
932        """Try to refresh a mediaitem by requesting it's full object or search for substitutes."""
933        if isinstance(media_item, str):
934            # media item uri given
935            media_item = await self.get_item_by_uri(media_item)
936
937        media_type = media_item.media_type
938        ctrl = self.get_controller(media_type)
939        library_id = media_item.item_id if media_item.provider == "library" else None
940
941        available_providers = get_global_cache_value("available_providers")
942        if TYPE_CHECKING:
943            available_providers = cast("set[str]", available_providers)
944
945        # fetch the first (available) provider item
946        for prov_mapping in sorted(
947            media_item.provider_mappings, key=lambda x: x.priority, reverse=True
948        ):
949            if not self.mass.get_provider(prov_mapping.provider_instance):
950                # ignore unavailable providers
951                continue
952            with suppress(MediaNotFoundError):
953                media_item = await ctrl.get_provider_item(
954                    prov_mapping.item_id,
955                    prov_mapping.provider_instance,
956                    force_refresh=True,
957                )
958                provider = media_item.provider
959                item_id = media_item.item_id
960                break
961        else:
962            # try to find a substitute using search
963            searchresult = await self.search(media_item.name, [media_item.media_type], 20)
964            if media_item.media_type == MediaType.ARTIST:
965                result = searchresult.artists
966            elif media_item.media_type == MediaType.ALBUM:
967                result = searchresult.albums
968            elif media_item.media_type == MediaType.TRACK:
969                result = searchresult.tracks
970            elif media_item.media_type == MediaType.PLAYLIST:
971                result = searchresult.playlists
972            elif media_item.media_type == MediaType.AUDIOBOOK:
973                result = searchresult.audiobooks
974            elif media_item.media_type == MediaType.PODCAST:
975                result = searchresult.podcasts
976            else:
977                result = searchresult.radio
978            for item in result:
979                if item == media_item or item.provider == "library":
980                    continue
981                if item.available:
982                    provider = item.provider
983                    item_id = item.item_id
984                    break
985            else:
986                # raise if we didn't find a substitute
987                raise MediaNotFoundError(f"Could not find a substitute for {media_item.name}")
988        # fetch full (provider) item
989        media_item = await ctrl.get_provider_item(item_id, provider, force_refresh=True)
990        # update library item if needed (including refresh of the metadata etc.)
991        if library_id is None:
992            return media_item
993        library_item = await ctrl.update_item_in_library(library_id, media_item, overwrite=True)
994        if library_item.media_type == MediaType.ALBUM:
995            # update (local) album tracks
996            for album_track in await self.albums.tracks(
997                library_item.item_id, library_item.provider, True
998            ):
999                for prov_mapping in album_track.provider_mappings:
1000                    if not (prov := self.mass.get_provider(prov_mapping.provider_instance)):
1001                        continue
1002                    if prov.is_streaming_provider:
1003                        continue
1004                    with suppress(MediaNotFoundError):
1005                        prov_track = await prov.get_track(prov_mapping.item_id)
1006                        await self.mass.music.tracks.update_item_in_library(
1007                            album_track.item_id, prov_track
1008                        )
1009        await ctrl.match_providers(library_item)
1010        await self.mass.metadata.update_metadata(library_item, force_refresh=True)
1011        return library_item
1012
1013    async def set_loudness(
1014        self,
1015        item_id: str,
1016        provider_instance_id_or_domain: str,
1017        loudness: float,
1018        album_loudness: float | None = None,
1019        media_type: MediaType = MediaType.TRACK,
1020    ) -> None:
1021        """Store (EBU-R128) Integrated Loudness Measurement for a mediaitem in db."""
1022        if not (provider := self.mass.get_provider(provider_instance_id_or_domain)):
1023            return
1024        if loudness in (None, inf, -inf):
1025            # skip invalid values
1026            return
1027        # prefer domain for streaming providers as the catalog is the same across instances
1028        prov_key = provider.domain if provider.is_streaming_provider else provider.instance_id
1029        values = {
1030            "item_id": item_id,
1031            "media_type": media_type.value,
1032            "provider": prov_key,
1033            "loudness": loudness,
1034        }
1035        if album_loudness not in (None, inf, -inf):
1036            values["loudness_album"] = album_loudness
1037        await self.database.insert_or_replace(DB_TABLE_LOUDNESS_MEASUREMENTS, values)
1038
1039    async def set_smart_fades_analysis(
1040        self,
1041        item_id: str,
1042        provider_instance_id_or_domain: str,
1043        analysis: SmartFadesAnalysis,
1044    ) -> None:
1045        """Store Smart Fades BPM analysis for a track in db."""
1046        if not (provider := self.mass.get_provider(provider_instance_id_or_domain)):
1047            return
1048        if (
1049            analysis.duration <= 0.75 * SMART_CROSSFADE_DURATION
1050            or analysis.bpm <= 0
1051            or analysis.confidence < 0
1052        ):
1053            # skip invalid values, we skip analysis that were performed on
1054            # a short amount of audio as those are often unreliable
1055            return
1056        beats_json = await asyncio.to_thread(lambda: json_dumps(analysis.beats.tolist()))
1057        downbeats_json = await asyncio.to_thread(lambda: json_dumps(analysis.downbeats.tolist()))
1058        # prefer domain for streaming providers as the catalog is the same across instances
1059        prov_key = provider.domain if provider.is_streaming_provider else provider.instance_id
1060        values = {
1061            "fragment": analysis.fragment.value,
1062            "item_id": item_id,
1063            "provider": prov_key,
1064            "bpm": analysis.bpm,
1065            "beats": beats_json,
1066            "downbeats": downbeats_json,
1067            "confidence": analysis.confidence,
1068            "duration": analysis.duration,
1069        }
1070        await self.database.insert_or_replace(DB_TABLE_SMART_FADES_ANALYSIS, values)
1071
1072    async def get_smart_fades_analysis(
1073        self,
1074        item_id: str,
1075        provider_instance_id_or_domain: str,
1076        fragment: SmartFadesAnalysisFragment,
1077    ) -> SmartFadesAnalysis | None:
1078        """Get Smart Fades BPM analysis for a track from db."""
1079        if not (provider := self.mass.get_provider(provider_instance_id_or_domain)):
1080            return None
1081        # prefer domain for streaming providers as the catalog is the same across instances
1082        prov_key = provider.domain if provider.is_streaming_provider else provider.instance_id
1083        db_row = await self.database.get_row(
1084            DB_TABLE_SMART_FADES_ANALYSIS,
1085            {
1086                "item_id": item_id,
1087                "provider": prov_key,
1088                "fragment": fragment.value,
1089            },
1090        )
1091        if db_row and db_row["bpm"] > 0:
1092            beats = await asyncio.to_thread(lambda: np.array(json_loads(db_row["beats"])))
1093            downbeats = await asyncio.to_thread(lambda: np.array(json_loads(db_row["downbeats"])))
1094            return SmartFadesAnalysis(
1095                fragment=SmartFadesAnalysisFragment(db_row["fragment"]),
1096                bpm=float(db_row["bpm"]),
1097                beats=beats,
1098                downbeats=downbeats,
1099                confidence=float(db_row["confidence"]),
1100                duration=float(db_row["duration"]),
1101            )
1102        return None
1103
1104    async def get_loudness(
1105        self,
1106        item_id: str,
1107        provider_instance_id_or_domain: str,
1108        media_type: MediaType = MediaType.TRACK,
1109    ) -> tuple[float, float | None] | None:
1110        """Get (EBU-R128) Integrated Loudness Measurement for a mediaitem in db."""
1111        if not (provider := self.mass.get_provider(provider_instance_id_or_domain)):
1112            return None
1113        # prefer domain for streaming providers as the catalog is the same across instances
1114        prov_key = provider.domain if provider.is_streaming_provider else provider.instance_id
1115        db_row = await self.database.get_row(
1116            DB_TABLE_LOUDNESS_MEASUREMENTS,
1117            {
1118                "item_id": item_id,
1119                "media_type": media_type.value,
1120                "provider": prov_key,
1121            },
1122        )
1123        if db_row and db_row["loudness"] != inf and db_row["loudness"] != -inf:
1124            loudness = round(db_row["loudness"], 2)
1125            loudness_album = db_row["loudness_album"]
1126            loudness_album = (
1127                None if loudness_album in (None, inf, -inf) else round(loudness_album, 2)
1128            )
1129            return (loudness, loudness_album)
1130
1131        return None
1132
1133    @api_command("music/mark_played")
1134    async def mark_item_played(
1135        self,
1136        media_item: MediaItemType,
1137        fully_played: bool = True,
1138        seconds_played: int | None = None,
1139        is_playing: bool = False,
1140        userid: str | None = None,
1141        queue_id: str | None = None,
1142        user_initiated: bool = True,
1143    ) -> None:
1144        """
1145        Mark item as played in playlog.
1146
1147        :param media_item: The media item to mark as played.
1148        :param fully_played: If True, mark the item as fully played.
1149        :param seconds_played: The number of seconds played.
1150        :param is_playing: If True, the item is currently playing.
1151        :param userid: The user ID to mark the item as played for (instead of the current user).
1152        :param queue_id: The queue ID where the item was played.
1153        :param user_initiated: If True, the playback was initiated by the user (e.g. enqueued).
1154        """
1155        timestamp = utc_timestamp()
1156        if (
1157            media_item.provider.startswith("builtin")
1158            and media_item.media_type != MediaType.PLAYLIST
1159        ):
1160            # we deliberately skip builtin provider items as those are often
1161            # one-off items like TTS or some sound effect etc.
1162            return
1163
1164        params = {
1165            "item_id": media_item.item_id,
1166            "provider": media_item.provider,
1167            "media_type": media_item.media_type.value,
1168            "name": media_item.name,
1169            "image": serialize_to_json(media_item.image.to_dict()) if media_item.image else None,
1170            "fully_played": fully_played,
1171            "seconds_played": seconds_played,
1172            "timestamp": timestamp,
1173            "queue_id": queue_id,
1174            "user_initiated": user_initiated,
1175        }
1176        # try to figure out the user that triggered the action
1177        user: User | None = None
1178        if userid:
1179            # userid overridden by parameter
1180            user = await self.mass.webserver.auth.get_user(userid)
1181        elif session_user := get_current_user():
1182            # this is the active session user that triggered the action
1183            user = session_user
1184        elif provider_user := await self._get_user_for_provider(media_item.provider_mappings):
1185            # based on configured provider filter we can try to find a user
1186            user = provider_user
1187
1188        # update generic playlog table (when not playing)
1189        if not is_playing:
1190            if user:
1191                user_ids = [user.user_id]
1192            else:
1193                # NOTE: if no user was found, we will alter the playlog for all users
1194                user_ids = [user.user_id for user in await self.mass.webserver.auth.list_users()]
1195            for user_id in user_ids:
1196                params["userid"] = user_id
1197                await self.database.insert(
1198                    DB_TABLE_PLAYLOG,
1199                    params,
1200                    allow_replace=True,
1201                )
1202
1203        # forward to provider(s) to sync resume state (e.g. for audiobooks)
1204        for prov_mapping in media_item.provider_mappings:
1205            if (
1206                user
1207                and user.provider_filter
1208                and prov_mapping.provider_instance not in user.provider_filter
1209            ):
1210                continue
1211            if music_prov := self.mass.get_provider(prov_mapping.provider_instance):
1212                self.mass.create_task(
1213                    music_prov.on_played(
1214                        media_type=media_item.media_type,
1215                        prov_item_id=prov_mapping.item_id,
1216                        fully_played=fully_played,
1217                        position=seconds_played,
1218                        media_item=media_item,
1219                        is_playing=is_playing,
1220                    )
1221                )
1222
1223        # also update playcount in library table (if fully played)
1224        if not fully_played or is_playing:
1225            return
1226        if not (ctrl := self.get_controller(media_item.media_type)):
1227            # skip non media items (e.g. plugin source)
1228            return
1229        db_item = await ctrl.get_library_item_by_prov_id(media_item.item_id, media_item.provider)
1230        if db_item:
1231            await self.database.execute(
1232                f"UPDATE {ctrl.db_table} SET play_count = play_count + 1, "
1233                f"last_played = {timestamp} WHERE item_id = {db_item.item_id}"
1234            )
1235        await self.database.commit()
1236
1237    @api_command("music/mark_unplayed")
1238    async def mark_item_unplayed(
1239        self,
1240        media_item: MediaItemType,
1241        userid: str | None = None,
1242    ) -> None:
1243        """
1244        Mark item as unplayed in playlog.
1245
1246        :param media_item: The media item to mark as unplayed.
1247        :param all_users: If True, mark the item as unplayed for all users.
1248        :param userid: The user ID to mark the item as unplayed for (instead of the current user).
1249        """
1250        params = {
1251            "item_id": media_item.item_id,
1252            "provider": media_item.provider,
1253            "media_type": media_item.media_type.value,
1254        }
1255        # try to figure out the user that triggered the action
1256        user: User | None = None
1257        if userid:
1258            # userid overridden by parameter
1259            user = await self.mass.webserver.auth.get_user(userid)
1260        elif session_user := get_current_user():
1261            # this is the active session user that triggered the action
1262            user = session_user
1263        elif provider_user := await self._get_user_for_provider(media_item.provider_mappings):
1264            # based on configured provider filter we can try to find a user
1265            user = provider_user
1266
1267        if user:
1268            user_ids = [user.user_id]
1269        else:
1270            # NOTE: if no user was found, we will alter the playlog for all users
1271            user_ids = [user.user_id for user in await self.mass.webserver.auth.list_users()]
1272        for user_id in user_ids:
1273            params["userid"] = user_id
1274            await self.database.delete(DB_TABLE_PLAYLOG, params)
1275
1276        # forward to provider(s) to sync resume state (e.g. for audiobooks)
1277        for prov_mapping in media_item.provider_mappings:
1278            if (
1279                user
1280                and user.provider_filter
1281                and prov_mapping.provider_instance not in user.provider_filter
1282            ):
1283                continue
1284            if music_prov := self.mass.get_provider(prov_mapping.provider_instance):
1285                self.mass.create_task(
1286                    music_prov.on_played(
1287                        media_type=media_item.media_type,
1288                        prov_item_id=prov_mapping.item_id,
1289                        fully_played=False,
1290                        position=0,
1291                        media_item=media_item,
1292                    )
1293                )
1294        # also update playcount in library table
1295        ctrl = self.get_controller(media_item.media_type)
1296        db_item = await ctrl.get_library_item_by_prov_id(media_item.item_id, media_item.provider)
1297        if db_item:
1298            await self.database.execute(
1299                f"UPDATE {ctrl.db_table} SET play_count = play_count - 1, "
1300                f"last_played = 0 WHERE item_id = {db_item.item_id}"
1301            )
1302            await self.database.commit()
1303
1304    @api_command("music/track_by_name")
1305    async def get_track_by_name(
1306        self,
1307        track_name: str,
1308        artist_name: str | None = None,
1309        album_name: str | None = None,
1310        track_version: str | None = None,
1311    ) -> Track | None:
1312        """Get a track by its name, optionally with artist and album."""
1313        if track_version is None:
1314            track_name, version = parse_title_and_version(track_name)
1315        search_query = f"{artist_name} - {track_name}" if artist_name else track_name
1316        search_result = await self.mass.music.search(
1317            search_query=search_query,
1318            media_types=[MediaType.TRACK],
1319        )
1320        for allow_item_mapping in (False, True):
1321            for search_track in search_result.tracks:
1322                is_track = isinstance(search_track, Track)
1323                if not allow_item_mapping and not is_track:
1324                    continue
1325                if not compare_strings(track_name, search_track.name):
1326                    continue
1327                if not compare_version(version, search_track.version):
1328                    continue
1329                # check optional artist(s)
1330                if artist_name and is_track:
1331                    for artist in search_track.artists:
1332                        if compare_strings(artist_name, artist.name, False):
1333                            break
1334                    else:
1335                        # no artist match found: abort
1336                        continue
1337                # check optional album
1338                if (
1339                    album_name
1340                    and is_track
1341                    and not compare_strings(album_name, search_track.album.name, False)
1342                ):
1343                    # no album match found: abort
1344                    continue
1345                    # if we reach this, we found a match
1346                if not isinstance(search_track, Track):
1347                    # ensure we return an actual Track object
1348                    return await self.mass.music.tracks.get(
1349                        item_id=search_track.item_id,
1350                        provider_instance_id_or_domain=search_track.provider,
1351                    )
1352                return search_track
1353
1354        # try to handle case where something is appended to the title
1355        for splitter in ("•", "-", "|", "(", "["):
1356            if splitter in track_name:
1357                return await self.get_track_by_name(
1358                    track_name=track_name.split(splitter)[0].strip(),
1359                    artist_name=artist_name,
1360                    album_name=None,
1361                    track_version=track_version,
1362                )
1363        # try to handle case where multiple artists are given as single string
1364        if artist_name and (artists := split_artists(artist_name, True)) and len(artists) > 1:
1365            for artist in artists:
1366                return await self.get_track_by_name(
1367                    track_name=track_name,
1368                    artist_name=artist.split(splitter)[0].strip(),
1369                    album_name=None,
1370                    track_version=track_version,
1371                )
1372        # allow non-exact album match as fallback
1373        if album_name:
1374            return await self.get_track_by_name(
1375                track_name=track_name,
1376                artist_name=artist_name,
1377                album_name=None,
1378                track_version=track_version,
1379            )
1380        # no match found
1381        return None
1382
1383    async def get_resume_position(
1384        self, media_item: Audiobook | PodcastEpisode, userid: str | None = None
1385    ) -> tuple[bool, int]:
1386        """
1387        Get progress (resume point) details for the given audiobook or episode.
1388
1389        This is a separate call to ensure the resume position is always up-to-date
1390        and because many providers have this info present on a dedicated endpoint.
1391
1392        Will be called right before playback starts to ensure the resume position is correct.
1393
1394        Returns a boolean with the fully_played status
1395        and an integer with the resume position in ms.
1396        """
1397        provider_fully_played = False
1398        provider_position_ms = 0
1399
1400        # Try to get position from providers
1401        for prov_mapping in media_item.provider_mappings:
1402            if not (
1403                provider := self.mass.get_provider(
1404                    prov_mapping.provider_instance, provider_type=MusicProvider
1405                )
1406            ):
1407                continue
1408            with suppress(NotImplementedError):
1409                (
1410                    provider_fully_played,
1411                    provider_position_ms,
1412                ) = await provider.get_resume_position(prov_mapping.item_id, media_item.media_type)
1413                break  # Use first provider that returns data
1414
1415        # Get MA's internal position from playlog
1416        ma_fully_played = False
1417        ma_position_ms = 0
1418        params = {
1419            "media_type": media_item.media_type.value,
1420            "item_id": media_item.item_id,
1421            "provider": media_item.provider,
1422        }
1423        if userid:
1424            params["userid"] = userid
1425        if db_entry := await self.database.get_row(DB_TABLE_PLAYLOG, params):
1426            ma_position_ms = db_entry["seconds_played"] * 1000 if db_entry["seconds_played"] else 0
1427            ma_fully_played = db_entry["fully_played"]
1428
1429        # Return the higher position to ensure users never lose progress
1430        if ma_position_ms >= provider_position_ms:
1431            return ma_fully_played, ma_position_ms
1432        return provider_fully_played, provider_position_ms
1433
1434    def get_controller(
1435        self, media_type: MediaType
1436    ) -> (
1437        ArtistsController
1438        | AlbumsController
1439        | TracksController
1440        | RadioController
1441        | PlaylistController
1442        | AudiobooksController
1443        | PodcastsController
1444        | GenreController
1445    ):
1446        """Return controller for MediaType."""
1447        if media_type == MediaType.ARTIST:
1448            return self.artists
1449        if media_type == MediaType.ALBUM:
1450            return self.albums
1451        if media_type == MediaType.TRACK:
1452            return self.tracks
1453        if media_type == MediaType.RADIO:
1454            return self.radio
1455        if media_type == MediaType.PLAYLIST:
1456            return self.playlists
1457        if media_type == MediaType.AUDIOBOOK:
1458            return self.audiobooks
1459        if media_type == MediaType.PODCAST:
1460            return self.podcasts
1461        if media_type == MediaType.PODCAST_EPISODE:
1462            return self.podcasts
1463        if media_type == MediaType.GENRE:
1464            return self.genres
1465        raise NotImplementedError
1466
1467    def get_provider_instances(
1468        self, domain: str, return_unavailable: bool = False
1469    ) -> list[MusicProvider]:
1470        """
1471        Return all provider instances for a given domain.
1472
1473        Note that this skips user filters so may only be called from internal code.
1474        """
1475        return cast(
1476            "list[MusicProvider]",
1477            self.mass.get_provider_instances(domain, return_unavailable, ProviderType.MUSIC),
1478        )
1479
1480    def get_unique_providers(self) -> list[str]:
1481        """
1482        Return all unique MusicProvider (instance or domain) ids.
1483
1484        This will return a set of provider instance ids but will only return
1485        a single instance_id per streaming provider domain.
1486
1487        Applies user provider filters (for non-admin users).
1488        """
1489        processed_domains: set[str] = set()
1490        # Get user provider filter if set
1491        user = get_current_user()
1492        user_provider_filter = user.provider_filter if user and user.provider_filter else None
1493        result: list[str] = []
1494        for provider in self.providers:
1495            if provider.is_streaming_provider and provider.domain in processed_domains:
1496                continue
1497            if user_provider_filter and provider.instance_id not in user_provider_filter:
1498                continue
1499            result.append(provider.instance_id)
1500            processed_domains.add(provider.domain)
1501        return result
1502
1503    async def cleanup_provider(self, provider_instance: str) -> None:
1504        """Cleanup provider records from the database."""
1505        if provider_instance.startswith(("filesystem", "jellyfin", "plex", "opensubsonic")):
1506            # removal of a local provider can become messy very fast due to the relations
1507            # such as images pointing at the files etc. so we just reset the whole db
1508            # TODO: Handle this more gracefully in the future where we remove the provider
1509            # and traverse the database to also remove all related items.
1510            self.logger.warning(
1511                "Removal of local provider detected, issuing full database reset..."
1512            )
1513            await self._reset_database()
1514            return
1515        deleted_providers = self.mass.config.get_raw_core_config_value(
1516            self.domain, CONF_DELETED_PROVIDERS, []
1517        )
1518        # we add the provider to this hidden config setting just to make sure that
1519        # we can survive this over a restart to make sure that entries are cleaned up
1520        if provider_instance not in deleted_providers:
1521            deleted_providers.append(provider_instance)
1522            self.mass.config.set_raw_core_config_value(
1523                self.domain, CONF_DELETED_PROVIDERS, deleted_providers
1524            )
1525            self.mass.config.save(True)
1526
1527        # always clear cache when a provider is removed
1528        await self.mass.cache.clear()
1529
1530        # cleanup media items from db matched to deleted provider
1531        self.logger.info(
1532            "Removing provider %s from library, this can take a a while...",
1533            provider_instance,
1534        )
1535        errors = 0
1536        for ctrl in (
1537            # order is important here to recursively cleanup bottom up
1538            self.mass.music.radio,
1539            self.mass.music.playlists,
1540            self.mass.music.tracks,
1541            self.mass.music.albums,
1542            self.mass.music.artists,
1543            self.mass.music.podcasts,
1544            self.mass.music.audiobooks,
1545            # run main controllers twice to rule out relations
1546            self.mass.music.tracks,
1547            self.mass.music.albums,
1548            self.mass.music.artists,
1549        ):
1550            query = (
1551                f"SELECT item_id FROM {DB_TABLE_PROVIDER_MAPPINGS} "
1552                f"WHERE media_type = '{ctrl.media_type}' "
1553                f"AND provider_instance = '{provider_instance}'"
1554            )
1555            for db_row in await self.database.get_rows_from_query(query, limit=100000):
1556                try:
1557                    await ctrl.remove_provider_mappings(db_row["item_id"], provider_instance)
1558                except Exception as err:
1559                    # we dont want the whole removal process to stall on one item
1560                    # so in case of an unexpected error, we log and move on.
1561                    self.logger.warning(
1562                        "Error while removing %s: %s",
1563                        db_row["item_id"],
1564                        str(err),
1565                        exc_info=err if self.logger.isEnabledFor(logging.DEBUG) else None,
1566                    )
1567                    errors += 1
1568
1569        # remove all orphaned items (not in provider mappings table anymore)
1570        query = (
1571            f"SELECT item_id FROM {DB_TABLE_PROVIDER_MAPPINGS} "
1572            f"WHERE provider_instance = '{provider_instance}'"
1573        )
1574        if remaining_items_count := await self.database.get_count_from_query(query):
1575            errors += remaining_items_count
1576
1577        # cleanup playlog table
1578        await self.mass.music.database.delete(
1579            DB_TABLE_PLAYLOG,
1580            {
1581                "provider": provider_instance,
1582            },
1583        )
1584
1585        if errors == 0:
1586            # cleanup successful, remove from the deleted_providers setting
1587            self.logger.info("Provider %s removed from library", provider_instance)
1588            deleted_providers.remove(provider_instance)
1589            self.mass.config.set_raw_core_config_value(
1590                self.domain, CONF_DELETED_PROVIDERS, deleted_providers
1591            )
1592        else:
1593            self.logger.warning(
1594                "Provider %s was not not fully removed from library", provider_instance
1595            )
1596
1597    async def schedule_provider_sync(self, provider_instance_id: str) -> None:
1598        """Schedule Library sync for given provider."""
1599        if not (provider := self.mass.get_provider(provider_instance_id)):
1600            return
1601        self.unschedule_provider_sync(provider.instance_id)
1602        for media_type in MediaType:
1603            if not provider.library_supported(media_type):
1604                continue
1605            await self._schedule_provider_mediatype_sync(provider, media_type, True)
1606
1607    def unschedule_provider_sync(self, provider_instance_id: str) -> None:
1608        """Unschedule Library sync for given provider."""
1609        # cancel all scheduled sync tasks
1610        for media_type in MediaType:
1611            key = f"sync_{provider_instance_id}_{media_type.value}"
1612            self.mass.cancel_timer(key)
1613        # cancel any running sync tasks
1614        for sync_task in list(self.in_progress_syncs):
1615            if sync_task.provider_instance == provider_instance_id:
1616                sync_task.task.cancel()
1617
1618    def match_provider_instances(
1619        self,
1620        item: MediaItemType,
1621    ) -> bool:
1622        """Match all provider instances for the given item."""
1623        mappings_added = False
1624        for provider_mapping in list(item.provider_mappings):
1625            if provider_mapping.is_unique:
1626                # unique mapping, no need to map
1627                continue
1628            if not (provider := self.mass.get_provider(provider_mapping.provider_instance)):
1629                continue
1630            if not provider.is_streaming_provider:
1631                continue
1632            provider_instances = self.get_provider_instances(
1633                provider.domain, return_unavailable=True
1634            )
1635            if len(provider_instances) <= 1:
1636                # only a single instance, no need to map
1637                continue
1638            for prov_instance in provider_instances:
1639                if prov_instance.instance_id == provider.instance_id:
1640                    continue
1641                if any(
1642                    pm.provider_instance == prov_instance.instance_id
1643                    for pm in item.provider_mappings
1644                ):
1645                    # mapping already exists
1646                    continue
1647                # create additional mapping for other provider instances of the same provider
1648                item.provider_mappings.add(
1649                    ProviderMapping(
1650                        item_id=provider_mapping.item_id,
1651                        provider_domain=provider.domain,
1652                        provider_instance=prov_instance.instance_id,
1653                        available=provider_mapping.available,
1654                        is_unique=provider_mapping.is_unique,
1655                        audio_format=provider_mapping.audio_format,
1656                        url=provider_mapping.url,
1657                        details=provider_mapping.details,
1658                        in_library=None,
1659                    )
1660                )
1661                mappings_added = True
1662        return mappings_added
1663
1664    @api_command("music/add_provider_mapping")
1665    async def add_provider_mapping(
1666        self, media_type: MediaType, db_id: str, mapping: ProviderMapping
1667    ) -> None:
1668        """Add provider mapping to the given library item."""
1669        ctrl = self.get_controller(media_type)
1670        await ctrl.add_provider_mappings(db_id, [mapping])
1671
1672    @api_command("music/remove_provider_mapping")
1673    async def remove_provider_mapping(
1674        self, media_type: MediaType, db_id: str, mapping: ProviderMapping
1675    ) -> None:
1676        """Remove provider mapping from the given library item."""
1677        ctrl = self.get_controller(media_type)
1678        await ctrl.remove_provider_mapping(db_id, mapping.provider_instance, mapping.item_id)
1679
1680    @api_command("music/match_providers")
1681    async def match_providers(self, media_type: MediaType, db_id: str) -> None:
1682        """Search for mappings on all providers for the given library item."""
1683        ctrl = self.get_controller(media_type)
1684        db_item = await ctrl.get_library_item(db_id)
1685        await ctrl.match_providers(db_item)
1686
1687    async def update_provider_mapping(
1688        self,
1689        media_type: MediaType,
1690        db_id: str | int,
1691        provider_instance_id: str,
1692        provider_item_id: str,
1693        *,
1694        available: bool | Any = UNSET,
1695        in_library: bool | Any = UNSET,
1696        is_unique: bool | None | Any = UNSET,
1697        url: str | None | Any = UNSET,
1698        details: str | None | Any = UNSET,
1699        audio_format: AudioFormat | Any = UNSET,
1700    ) -> None:
1701        """Update an existing provider mapping for a library item."""
1702        ctrl = self.get_controller(media_type)
1703        await ctrl.update_provider_mapping(
1704            item_id=db_id,
1705            provider_instance_id=provider_instance_id,
1706            provider_item_id=provider_item_id,
1707            available=available,
1708            in_library=in_library,
1709            is_unique=is_unique,
1710            url=url,
1711            details=details,
1712            audio_format=audio_format,
1713        )
1714
1715    async def _get_default_recommendations(self) -> list[RecommendationFolder]:
1716        """Return default recommendations."""
1717        return [
1718            RecommendationFolder(
1719                item_id="in_progress",
1720                provider="library",
1721                name="In progress",
1722                translation_key="in_progress_items",
1723                icon="mdi-motion-play",
1724                items=await self.in_progress_items(limit=10),
1725            ),
1726            RecommendationFolder(
1727                item_id="recently_played",
1728                provider="library",
1729                name="Recently played",
1730                translation_key="recently_played",
1731                icon="mdi-motion-play",
1732                items=await self.recently_played(limit=10, user_initiated_only=True),
1733            ),
1734            RecommendationFolder(
1735                item_id="recently_added_tracks",
1736                provider="library",
1737                name="Recently added tracks",
1738                translation_key="recently_added_tracks",
1739                icon="music-note-plus",
1740                items=await self.tracks.library_items(limit=10, order_by="timestamp_added_desc"),
1741            ),
1742            RecommendationFolder(
1743                item_id="recently_added_albums",
1744                provider="library",
1745                name="Recently added albums",
1746                translation_key="recently_added_albums",
1747                icon="music-note-plus",
1748                items=await self.albums.library_items(limit=10, order_by="timestamp_added_desc"),
1749            ),
1750            RecommendationFolder(
1751                item_id="random_artists",
1752                provider="library",
1753                name="Random artists",
1754                translation_key="random_artists",
1755                icon="mdi-account-music",
1756                items=await self.artists.library_items(limit=10, order_by="random_play_count"),
1757            ),
1758            RecommendationFolder(
1759                item_id="random_albums",
1760                provider="library",
1761                name="Random albums",
1762                translation_key="random_albums",
1763                icon="mdi-album",
1764                items=await self.albums.library_items(limit=10, order_by="random_play_count"),
1765            ),
1766            RecommendationFolder(
1767                item_id="recent_favorite_tracks",
1768                provider="library",
1769                name="Recently favorited tracks",
1770                translation_key="recent_favorite_tracks",
1771                icon="mdi-file-music",
1772                items=await self.tracks.library_items(
1773                    favorite=True, limit=10, order_by="timestamp_modified_desc"
1774                ),
1775            ),
1776            RecommendationFolder(
1777                item_id="favorite_playlists",
1778                provider="library",
1779                name="Favorite playlists",
1780                translation_key="favorite_playlists",
1781                icon="mdi-playlist-music",
1782                items=await self.playlists.library_items(
1783                    favorite=True, limit=10, order_by="random"
1784                ),
1785            ),
1786            RecommendationFolder(
1787                item_id="favorite_radio",
1788                provider="library",
1789                name="Favorite Radio stations",
1790                translation_key="favorite_radio_stations",
1791                icon="mdi-access-point",
1792                items=await self.radio.library_items(
1793                    favorite=True, limit=10, order_by="play_count_desc"
1794                ),
1795            ),
1796        ]
1797
1798    async def _get_provider_recommendations(
1799        self, provider: MusicProvider
1800    ) -> list[RecommendationFolder]:
1801        """Return recommendations from a provider."""
1802        try:
1803            return await provider.recommendations()
1804        except Exception as err:
1805            self.logger.warning(
1806                "Error while fetching recommendations from %s: %s",
1807                provider.name,
1808                str(err),
1809                exc_info=err if self.logger.isEnabledFor(logging.DEBUG) else None,
1810            )
1811            return []
1812
1813    def _start_provider_sync(self, provider: MusicProvider, media_type: MediaType) -> None:
1814        """Start sync task on provider and track progress."""
1815        # check if we're not already running a sync task for this provider/mediatype
1816        for sync_task in list(self.in_progress_syncs):
1817            if sync_task.provider_instance != provider.instance_id:
1818                continue
1819            if sync_task.task.done():
1820                continue
1821            if media_type in sync_task.media_types:
1822                self.logger.debug(
1823                    "Skip sync task for %s/%ss because another task is already in progress",
1824                    provider.name,
1825                    media_type.value,
1826                )
1827                return
1828
1829        async def run_sync() -> None:
1830            # Wrap the provider sync into a lock to prevent
1831            # race conditions when multiple providers are syncing at the same time.
1832            async with self._sync_lock:
1833                await provider.sync_library(media_type)
1834
1835        # we keep track of running sync tasks
1836        task = self.mass.create_task(run_sync())
1837        sync_spec = SyncTask(
1838            provider_domain=provider.domain,
1839            provider_instance=provider.instance_id,
1840            media_types=(media_type,),
1841            task=task,
1842        )
1843        self.in_progress_syncs.append(sync_spec)
1844
1845        self.mass.signal_event(EventType.SYNC_TASKS_UPDATED, data=self.in_progress_syncs)
1846
1847        def on_sync_task_done(task: asyncio.Task) -> None:
1848            self.in_progress_syncs.remove(sync_spec)
1849            if task.cancelled():
1850                return
1851            if task_err := task.exception():
1852                self.logger.warning(
1853                    "Sync task for %s/%ss completed with errors",
1854                    provider.name,
1855                    media_type.value,
1856                    exc_info=task_err if self.logger.isEnabledFor(10) else None,
1857                )
1858            else:
1859                self.logger.info("Sync task for %s/%ss completed", provider.name, media_type.value)
1860            self.mass.signal_event(EventType.SYNC_TASKS_UPDATED, data=self.in_progress_syncs)
1861            self.mass.create_task(
1862                self.mass.cache.set(
1863                    key=media_type.value,
1864                    data=self.mass.loop.time(),
1865                    provider=provider.instance_id,
1866                    category=CACHE_CATEGORY_LAST_SYNC,
1867                )
1868            )
1869            # schedule db cleanup after sync
1870            if not self.in_progress_syncs:
1871                self.mass.create_task(self._cleanup_database())
1872            # reschedule next execution
1873            self.mass.create_task(self._schedule_provider_mediatype_sync(provider, media_type))
1874
1875        task.add_done_callback(on_sync_task_done)
1876        return
1877
1878    def _sort_search_result(
1879        self,
1880        search_query: str,
1881        items: Sequence[MediaItemType | ItemMapping],
1882    ) -> UniqueList[MediaItemType | ItemMapping]:
1883        """Sort search results on priority/preference."""
1884        scored_items: list[tuple[int, MediaItemType | ItemMapping]] = []
1885        # search results are already sorted by (streaming) providers on relevance
1886        # but we prefer exact name matches and library items so we simply put those
1887        # on top of the list.
1888        safe_title_str = create_safe_string(search_query)
1889        if " - " in search_query:
1890            artist, title_alt = search_query.split(" - ", 1)
1891            safe_title_alt = create_safe_string(title_alt)
1892            safe_artist_str = create_safe_string(artist)
1893        else:
1894            safe_artist_str = None
1895            safe_title_alt = None
1896        for item in items:
1897            score = 0
1898            if create_safe_string(item.name) not in (safe_title_str, safe_title_alt):
1899                # literal name match is mandatory to get a score at all
1900                continue
1901            # bonus point if artist provided and exact match
1902            if safe_artist_str:
1903                artist: Artist | ItemMapping
1904                for artist in getattr(item, "artists", []):
1905                    if create_safe_string(artist.name) == safe_artist_str:
1906                        score += 1
1907            # bonus point for library items
1908            if item.provider == "library":
1909                score += 1
1910            scored_items.append((score, item))
1911        scored_items.sort(key=lambda x: x[0], reverse=True)
1912        # combine it all with uniquelist, so this will deduplicated by default
1913        # note that streaming provider results are already (most likely) sorted on relevance
1914        # so we add all remaining items in their original order. We just prioritize
1915        # exact name matches and library items.
1916        return UniqueList([*[x[1] for x in scored_items], *items])
1917
1918    async def _schedule_provider_mediatype_sync(
1919        self, provider: MusicProvider, media_type: MediaType, is_initial: bool = False
1920    ) -> None:
1921        """Schedule Library sync for given provider and media type."""
1922        job_key = f"sync_{provider.instance_id}_{media_type.value}"
1923        # cancel any existing timers
1924        self.mass.cancel_timer(job_key)
1925        # handle mediatype specific sync config
1926        conf_key = f"library_sync_{media_type}s"
1927        sync_conf = await self.mass.config.get_provider_config_value(provider.instance_id, conf_key)
1928        if not sync_conf:
1929            return
1930        conf_key = f"provider_sync_interval_{media_type.value}s"
1931        sync_interval = await self.mass.config.get_provider_config_value(
1932            provider.instance_id, conf_key, return_type=int
1933        )
1934        if sync_interval <= 0:
1935            # sync disabled for this media type
1936            return
1937        sync_interval = sync_interval * 60  # config interval is in minutes - convert to seconds
1938
1939        if is_initial:
1940            # schedule the first sync run
1941            initial_interval = 10
1942            if last_sync := await self.mass.cache.get(
1943                key=media_type.value,
1944                provider=provider.instance_id,
1945                category=CACHE_CATEGORY_LAST_SYNC,
1946            ):
1947                initial_interval += max(0, sync_interval - (self.mass.loop.time() - last_sync))
1948            sync_interval = initial_interval
1949
1950        self.mass.call_later(
1951            sync_interval,
1952            self._start_provider_sync,
1953            provider,
1954            media_type,
1955            task_id=job_key,
1956        )
1957
1958    async def _cleanup_database(self) -> None:
1959        """Perform database cleanup/maintenance."""
1960        self.logger.debug("Performing database cleanup...")
1961        # Remove playlog entries older than 90 days
1962        await self.database.delete_where_query(
1963            DB_TABLE_PLAYLOG, f"timestamp < strftime('%s','now') - {3600 * 24 * 90}"
1964        )
1965        # db tables cleanup
1966        for ctrl in (
1967            self.albums,
1968            self.artists,
1969            self.tracks,
1970            self.playlists,
1971            self.radio,
1972        ):
1973            # Provider mappings where the db item is removed
1974            query = (
1975                f"item_id not in (SELECT item_id from {ctrl.db_table}) "
1976                f"AND media_type = '{ctrl.media_type}'"
1977            )
1978            await self.database.delete_where_query(DB_TABLE_PROVIDER_MAPPINGS, query)
1979            # Orphaned db items
1980            query = (
1981                f"item_id not in (SELECT item_id from {DB_TABLE_PROVIDER_MAPPINGS} "
1982                f"WHERE media_type = '{ctrl.media_type}')"
1983            )
1984            await self.database.delete_where_query(ctrl.db_table, query)
1985            # Cleanup removed db items from the playlog
1986            where_clause = (
1987                f"media_type = '{ctrl.media_type}' AND provider = 'library' "
1988                f"AND item_id not in (select item_id from {ctrl.db_table})"
1989            )
1990            await self.mass.music.database.delete_where_query(DB_TABLE_PLAYLOG, where_clause)
1991        self.logger.debug("Database cleanup done")
1992
1993    async def _setup_database(self) -> None:
1994        """Initialize database."""
1995        db_path = os.path.join(self.mass.storage_path, "library.db")
1996        self._database = DatabaseConnection(db_path)
1997        await self._database.setup()
1998
1999        # always create db tables if they don't exist to prevent errors trying to access them later
2000        await self.__create_database_tables()
2001        try:
2002            if db_row := await self._database.get_row(DB_TABLE_SETTINGS, {"key": "version"}):
2003                prev_version = int(db_row["value"])
2004            else:
2005                prev_version = 0
2006        except (KeyError, ValueError):
2007            prev_version = 0
2008
2009        if prev_version not in (0, DB_SCHEMA_VERSION):
2010            # db version mismatch - we need to do a migration
2011            # make a backup of db file
2012            db_path_backup = db_path + ".backup"
2013            await asyncio.to_thread(shutil.copyfile, db_path, db_path_backup)
2014
2015            # handle db migration from previous schema(s) to this one
2016            try:
2017                await self.__migrate_database(prev_version)
2018            except Exception as err:
2019                # if the migration fails completely we reset the db
2020                # so the user at least can have a working situation back
2021                # a backup file is made with the previous version
2022                self.logger.error(
2023                    "Database migration failed - starting with a fresh library database, "
2024                    "a full rescan will be performed, this can take a while!",
2025                )
2026                if not isinstance(err, MusicAssistantError):
2027                    self.logger.exception(err)
2028
2029                await self._database.close()
2030                await asyncio.to_thread(os.remove, db_path)
2031                self._database = DatabaseConnection(db_path)
2032                await self._database.setup()
2033                await self.mass.cache.clear()
2034                await self.__create_database_tables()
2035
2036        # store current schema version
2037        await self._database.insert_or_replace(
2038            DB_TABLE_SETTINGS,
2039            {"key": "version", "value": str(DB_SCHEMA_VERSION), "type": "str"},
2040        )
2041        # create indexes and triggers if needed
2042        await self.__create_database_indexes()
2043        await self.__create_database_triggers()
2044        # compact db
2045        self.logger.debug("Compacting database...")
2046        try:
2047            await self._database.vacuum()
2048        except Exception as err:
2049            self.logger.warning("Database vacuum failed: %s", str(err))
2050        else:
2051            self.logger.debug("Compacting database done")
2052
2053    async def __migrate_database(self, prev_version: int) -> None:  # noqa: PLR0915
2054        """Perform a database migration."""
2055        self.logger.info(
2056            "Migrating database from version %s to %s", prev_version, DB_SCHEMA_VERSION
2057        )
2058
2059        if prev_version < 15:
2060            raise MusicAssistantError("Database schema version too old to migrate")
2061
2062        if prev_version <= 15:
2063            # add search_name and search_sort_name columns to all tables
2064            # and populate them with the name and sort_name values
2065            # this is to allow for local/case independent searches
2066            for table in (
2067                DB_TABLE_TRACKS,
2068                DB_TABLE_ALBUMS,
2069                DB_TABLE_ARTISTS,
2070                DB_TABLE_RADIOS,
2071                DB_TABLE_PLAYLISTS,
2072                DB_TABLE_AUDIOBOOKS,
2073                DB_TABLE_PODCASTS,
2074            ):
2075                try:
2076                    await self._database.execute(
2077                        f"ALTER TABLE {table} ADD COLUMN search_name TEXT DEFAULT '' NOT NULL"
2078                    )
2079                    await self._database.execute(
2080                        f"ALTER TABLE {table} ADD COLUMN search_sort_name TEXT DEFAULT '' NOT NULL"
2081                    )
2082                except Exception as err:
2083                    if "duplicate column" not in str(err):
2084                        raise
2085                # migrate all existing values
2086                async for db_row in self._database.iter_items(table):
2087                    await self._database.update(
2088                        table,
2089                        {"item_id": db_row["item_id"]},
2090                        {
2091                            "search_name": create_safe_string(db_row["name"], True, True),
2092                            "search_sort_name": create_safe_string(db_row["sort_name"], True, True),
2093                        },
2094                    )
2095
2096        if prev_version <= 16:
2097            # cleanup invalid release_date field in metadata
2098            for table in (
2099                DB_TABLE_TRACKS,
2100                DB_TABLE_ALBUMS,
2101                DB_TABLE_AUDIOBOOKS,
2102                DB_TABLE_PODCASTS,
2103            ):
2104                async for db_row in self._database.iter_items(table):
2105                    if '"release_date":null' in db_row["metadata"]:
2106                        continue
2107                    metadata = json_loads(db_row["metadata"])
2108                    try:
2109                        datetime.fromisoformat(metadata["release_date"])
2110                    except (KeyError, ValueError):
2111                        # this is not a valid date, so we set it to None
2112                        metadata["release_date"] = None
2113                        await self._database.update(
2114                            table,
2115                            {"item_id": db_row["item_id"]},
2116                            {
2117                                "metadata": serialize_to_json(metadata),
2118                            },
2119                        )
2120
2121        if prev_version <= 17:
2122            # migrate triggers to auto update timestamps
2123            # it had an error in the previous version where it was not created
2124            for db_table in (
2125                "artists",
2126                "albums",
2127                "tracks",
2128                "playlists",
2129                "radios",
2130                "audiobooks",
2131                "podcasts",
2132            ):
2133                await self._database.execute(f"DROP TRIGGER IF EXISTS update_{db_table}_timestamp;")
2134
2135        if prev_version <= 18:
2136            # add in_library column to provider_mappings table
2137            await self._database.execute(
2138                f"ALTER TABLE {DB_TABLE_PROVIDER_MAPPINGS} ADD COLUMN in_library "
2139                "BOOLEAN NOT NULL DEFAULT 0;"
2140            )
2141            # migrate existing entries in provider_mappings which are filesystem
2142            await self._database.execute(
2143                f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
2144                "WHERE provider_domain in ('filesystem_local', 'filesystem_smb');"
2145            )
2146
2147        if prev_version <= 20:
2148            # drop column cache_checksum from playlists table
2149            # this is no longer used and is a leftover from previous designs
2150            try:
2151                await self._database.execute(
2152                    f"ALTER TABLE {DB_TABLE_PLAYLISTS} DROP COLUMN cache_checksum"
2153                )
2154            except Exception as err:
2155                if "no such column" not in str(err):
2156                    raise
2157
2158        if prev_version <= 21:
2159            # drop table for smart fades analysis - it will be recreated with needed columns
2160            await self._database.execute(f"DROP TABLE IF EXISTS {DB_TABLE_SMART_FADES_ANALYSIS}")
2161            await self.__create_database_tables()
2162
2163        if prev_version <= 22:
2164            # add userid column to playlog table
2165            try:
2166                await self._database.execute(
2167                    f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN userid TEXT"
2168                )
2169            except Exception as err:
2170                if "duplicate column" not in str(err):
2171                    raise
2172            # Note: SQLite doesn't support modifying constraints directly
2173            # The UNIQUE constraint will be updated when the table is recreated
2174            # For now, we'll keep the old constraint and add a new one via unique index
2175            try:
2176                await self._database.execute(f"DROP INDEX IF EXISTS {DB_TABLE_PLAYLOG}_unique_idx")
2177                await self._database.execute(
2178                    f"CREATE UNIQUE INDEX {DB_TABLE_PLAYLOG}_unique_idx "
2179                    f"ON {DB_TABLE_PLAYLOG}(item_id,provider,media_type,userid)"
2180                )
2181            except Exception as err:
2182                # If we can't create the index due to duplicate entries, log and continue
2183                self.logger.warning("Could not create unique index on playlog: %s", err)
2184
2185        if prev_version <= 23:
2186            # add is_unique column to provider_mappings table
2187            try:
2188                await self._database.execute(
2189                    f"ALTER TABLE {DB_TABLE_PROVIDER_MAPPINGS} ADD COLUMN is_unique BOOLEAN"
2190                )
2191            except Exception as err:
2192                if "duplicate column" not in str(err):
2193                    raise
2194
2195        if prev_version <= 24:
2196            # add queue_id and user_initiated columns to playlog table
2197            try:
2198                await self._database.execute(
2199                    f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN queue_id TEXT"
2200                )
2201            except Exception as err:
2202                if "duplicate column" not in str(err):
2203                    raise
2204            try:
2205                await self._database.execute(
2206                    f"ALTER TABLE {DB_TABLE_PLAYLOG} "
2207                    "ADD COLUMN user_initiated BOOLEAN NOT NULL DEFAULT 1"
2208                )
2209            except Exception as err:
2210                if "duplicate column" not in str(err):
2211                    raise
2212
2213        if prev_version <= 26:
2214            # force in_library=True for provider mappings from non-streaming providers
2215            # streaming providers will be automatically added to library when synced
2216            await self._database.execute(
2217                f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
2218                "WHERE provider_domain NOT IN "
2219                "('spotify', 'deezer', 'tidal', 'qobuz', 'apple_music', 'ytmusic');"
2220            )
2221            # also set in_library=True for all radio items
2222            await self._database.execute(
2223                f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
2224                "WHERE media_type = 'radio';"
2225            )
2226            # remove invalid playlist provider mappings for playlists which are not in library
2227            await self._database.execute(
2228                f"DELETE FROM {DB_TABLE_PROVIDER_MAPPINGS} "
2229                "WHERE media_type = 'playlist' AND in_library = 0;"
2230            )
2231
2232        # save changes
2233        await self._database.commit()
2234
2235        # always clear the cache after a db migration
2236        await self.mass.cache.clear()
2237
2238    async def _reset_database(self) -> None:
2239        """Reset the database."""
2240        await self.close()
2241        db_path = os.path.join(self.mass.storage_path, "library.db")
2242        await asyncio.to_thread(os.remove, db_path)
2243        await self._setup_database()
2244        # initiate full sync
2245        await self.start_sync()
2246
2247    async def __create_database_tables(self) -> None:
2248        """Create database tables."""
2249        await self.database.execute(
2250            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_SETTINGS}(
2251                    [key] TEXT PRIMARY KEY,
2252                    [value] TEXT,
2253                    [type] TEXT
2254                );"""
2255        )
2256        await self.database.execute(
2257            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_PLAYLOG}(
2258                [id] INTEGER PRIMARY KEY AUTOINCREMENT,
2259                [item_id] TEXT NOT NULL,
2260                [provider] TEXT NOT NULL,
2261                [media_type] TEXT NOT NULL,
2262                [name] TEXT NOT NULL,
2263                [image] json,
2264                [timestamp] INTEGER DEFAULT 0,
2265                [fully_played] BOOLEAN,
2266                [seconds_played] INTEGER,
2267                [userid] TEXT NOT NULL,
2268                [queue_id] TEXT,
2269                [user_initiated] BOOLEAN NOT NULL DEFAULT 1,
2270                UNIQUE(item_id, provider, media_type, userid));"""
2271        )
2272        await self.database.execute(
2273            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUMS}(
2274                    [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2275                    [name] TEXT NOT NULL,
2276                    [sort_name] TEXT NOT NULL,
2277                    [version] TEXT,
2278                    [album_type] TEXT NOT NULL,
2279                    [year] INTEGER,
2280                    [favorite] BOOLEAN NOT NULL DEFAULT 0,
2281                    [metadata] json NOT NULL,
2282                    [external_ids] json NOT NULL,
2283                    [play_count] INTEGER NOT NULL DEFAULT 0,
2284                    [last_played] INTEGER NOT NULL DEFAULT 0,
2285                    [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2286                    [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2287                    [search_name] TEXT NOT NULL,
2288                    [search_sort_name] TEXT NOT NULL
2289                );"""
2290        )
2291        await self.database.execute(
2292            f"""
2293            CREATE TABLE IF NOT EXISTS {DB_TABLE_ARTISTS}(
2294            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2295            [name] TEXT NOT NULL,
2296            [sort_name] TEXT NOT NULL,
2297            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2298            [metadata] json NOT NULL,
2299            [external_ids] json NOT NULL,
2300            [play_count] INTEGER DEFAULT 0,
2301            [last_played] INTEGER DEFAULT 0,
2302            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2303            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2304            [search_name] TEXT NOT NULL,
2305            [search_sort_name] TEXT NOT NULL
2306            );"""
2307        )
2308        await self.database.execute(
2309            f"""
2310            CREATE TABLE IF NOT EXISTS {DB_TABLE_TRACKS}(
2311            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2312            [name] TEXT NOT NULL,
2313            [sort_name] TEXT NOT NULL,
2314            [version] TEXT,
2315            [duration] INTEGER,
2316            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2317            [metadata] json NOT NULL,
2318            [external_ids] json NOT NULL,
2319            [play_count] INTEGER DEFAULT 0,
2320            [last_played] INTEGER DEFAULT 0,
2321            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2322            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2323            [search_name] TEXT NOT NULL,
2324            [search_sort_name] TEXT NOT NULL
2325            );"""
2326        )
2327        await self.database.execute(
2328            f"""
2329            CREATE TABLE IF NOT EXISTS {DB_TABLE_PLAYLISTS}(
2330            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2331            [name] TEXT NOT NULL,
2332            [sort_name] TEXT NOT NULL,
2333            [owner] TEXT NOT NULL,
2334            [is_editable] BOOLEAN NOT NULL,
2335            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2336            [metadata] json NOT NULL,
2337            [external_ids] json NOT NULL,
2338            [play_count] INTEGER DEFAULT 0,
2339            [last_played] INTEGER DEFAULT 0,
2340            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2341            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2342            [search_name] TEXT NOT NULL,
2343            [search_sort_name] TEXT NOT NULL
2344            );"""
2345        )
2346        await self.database.execute(
2347            f"""
2348            CREATE TABLE IF NOT EXISTS {DB_TABLE_RADIOS}(
2349            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2350            [name] TEXT NOT NULL,
2351            [sort_name] TEXT NOT NULL,
2352            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2353            [metadata] json NOT NULL,
2354            [external_ids] json NOT NULL,
2355            [play_count] INTEGER DEFAULT 0,
2356            [last_played] INTEGER DEFAULT 0,
2357            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2358            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2359            [search_name] TEXT NOT NULL,
2360            [search_sort_name] TEXT NOT NULL
2361            );"""
2362        )
2363        await self.database.execute(
2364            f"""
2365            CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIOBOOKS}(
2366            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2367            [name] TEXT NOT NULL,
2368            [sort_name] TEXT NOT NULL,
2369            [version] TEXT,
2370            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2371            [publisher] TEXT,
2372            [authors] json NOT NULL,
2373            [narrators] json NOT NULL,
2374            [metadata] json NOT NULL,
2375            [duration] INTEGER,
2376            [external_ids] json NOT NULL,
2377            [play_count] INTEGER DEFAULT 0,
2378            [last_played] INTEGER DEFAULT 0,
2379            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2380            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2381            [search_name] TEXT NOT NULL,
2382            [search_sort_name] TEXT NOT NULL
2383            );"""
2384        )
2385        await self.database.execute(
2386            f"""
2387            CREATE TABLE IF NOT EXISTS {DB_TABLE_PODCASTS}(
2388            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2389            [name] TEXT NOT NULL,
2390            [sort_name] TEXT NOT NULL,
2391            [version] TEXT,
2392            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2393            [publisher] TEXT,
2394            [total_episodes] INTEGER NOT NULL,
2395            [metadata] json NOT NULL,
2396            [external_ids] json NOT NULL,
2397            [play_count] INTEGER NOT NULL DEFAULT 0,
2398            [last_played] INTEGER NOT NULL DEFAULT 0,
2399            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2400            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2401            [search_name] TEXT NOT NULL,
2402            [search_sort_name] TEXT NOT NULL
2403            );"""
2404        )
2405        await self.database.execute(
2406            f"""
2407            CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUM_TRACKS}(
2408            [id] INTEGER PRIMARY KEY AUTOINCREMENT,
2409            [track_id] INTEGER NOT NULL,
2410            [album_id] INTEGER NOT NULL,
2411            [disc_number] INTEGER NOT NULL,
2412            [track_number] INTEGER NOT NULL,
2413            FOREIGN KEY([track_id]) REFERENCES [tracks]([item_id]),
2414            FOREIGN KEY([album_id]) REFERENCES [albums]([item_id]),
2415            UNIQUE(track_id, album_id)
2416            );"""
2417        )
2418        await self.database.execute(
2419            f"""
2420            CREATE TABLE IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}(
2421            [media_type] TEXT NOT NULL,
2422            [item_id] INTEGER NOT NULL,
2423            [provider_domain] TEXT NOT NULL,
2424            [provider_instance] TEXT NOT NULL,
2425            [provider_item_id] TEXT NOT NULL,
2426            [available] BOOLEAN NOT NULL DEFAULT 1,
2427            [in_library] BOOLEAN NOT NULL DEFAULT 0,
2428            [is_unique] BOOLEAN,
2429            [url] text,
2430            [audio_format] json,
2431            [details] TEXT,
2432            UNIQUE(media_type, provider_instance, provider_item_id)
2433            );"""
2434        )
2435        await self.database.execute(
2436            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}(
2437            [track_id] INTEGER NOT NULL,
2438            [artist_id] INTEGER NOT NULL,
2439            FOREIGN KEY([track_id]) REFERENCES [tracks]([item_id]),
2440            FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
2441            UNIQUE(track_id, artist_id)
2442            );"""
2443        )
2444        await self.database.execute(
2445            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}(
2446            [album_id] INTEGER NOT NULL,
2447            [artist_id] INTEGER NOT NULL,
2448            FOREIGN KEY([album_id]) REFERENCES [albums]([item_id]),
2449            FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
2450            UNIQUE(album_id, artist_id)
2451            );"""
2452        )
2453
2454        await self.database.execute(
2455            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_LOUDNESS_MEASUREMENTS}(
2456                    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
2457                    [media_type] TEXT NOT NULL,
2458                    [item_id] TEXT NOT NULL,
2459                    [provider] TEXT NOT NULL,
2460                    [loudness] REAL,
2461                    [loudness_album] REAL,
2462                    UNIQUE(media_type,item_id,provider));"""
2463        )
2464
2465        await self.database.execute(
2466            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_SMART_FADES_ANALYSIS}(
2467                    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
2468                    [item_id] TEXT NOT NULL,
2469                    [provider] TEXT NOT NULL,
2470                    [fragment] INTEGER NOT NULL,
2471                    [bpm] REAL NOT NULL,
2472                    [beats] TEXT NOT NULL,
2473                    [downbeats] TEXT NOT NULL,
2474                    [confidence] REAL NOT NULL,
2475                    [duration] REAL,
2476                    [analysis_version] INTEGER DEFAULT 1,
2477                    [timestamp_created] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2478                    UNIQUE(item_id,provider,fragment));"""
2479        )
2480
2481        await self.database.commit()
2482
2483    async def __create_database_indexes(self) -> None:
2484        """Create database indexes."""
2485        for db_table in (
2486            DB_TABLE_ARTISTS,
2487            DB_TABLE_ALBUMS,
2488            DB_TABLE_TRACKS,
2489            DB_TABLE_PLAYLISTS,
2490            DB_TABLE_RADIOS,
2491            DB_TABLE_AUDIOBOOKS,
2492            DB_TABLE_PODCASTS,
2493        ):
2494            # index on favorite column
2495            await self.database.execute(
2496                f"CREATE INDEX IF NOT EXISTS {db_table}_favorite_idx on {db_table}(favorite);"
2497            )
2498            # index on name
2499            await self.database.execute(
2500                f"CREATE INDEX IF NOT EXISTS {db_table}_name_idx on {db_table}(name);"
2501            )
2502            # index on search_name (=lowercase name without diacritics)
2503            await self.database.execute(
2504                f"CREATE INDEX IF NOT EXISTS {db_table}_name_nocase_idx ON {db_table}(search_name);"
2505            )
2506            # index on sort_name
2507            await self.database.execute(
2508                f"CREATE INDEX IF NOT EXISTS {db_table}_sort_name_idx on {db_table}(sort_name);"
2509            )
2510            # index on search_sort_name (=lowercase sort_name without diacritics)
2511            await self.database.execute(
2512                f"CREATE INDEX IF NOT EXISTS {db_table}_search_sort_name_idx "
2513                f"ON {db_table}(search_sort_name);"
2514            )
2515            # index on external_ids
2516            await self.database.execute(
2517                f"CREATE INDEX IF NOT EXISTS {db_table}_external_ids_idx "
2518                f"ON {db_table}(external_ids);"
2519            )
2520            # index on timestamp_added
2521            await self.database.execute(
2522                f"CREATE INDEX IF NOT EXISTS {db_table}_timestamp_added_idx "
2523                f"on {db_table}(timestamp_added);"
2524            )
2525            # index on play_count
2526            await self.database.execute(
2527                f"CREATE INDEX IF NOT EXISTS {db_table}_play_count_idx on {db_table}(play_count);"
2528            )
2529            # index on last_played
2530            await self.database.execute(
2531                f"CREATE INDEX IF NOT EXISTS {db_table}_last_played_idx on {db_table}(last_played);"
2532            )
2533
2534        # indexes on provider_mappings table
2535        await self.database.execute(
2536            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_media_type_item_id_idx "
2537            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,item_id);"
2538        )
2539        await self.database.execute(
2540            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_provider_domain_idx "
2541            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_domain,provider_item_id);"
2542        )
2543        await self.database.execute(
2544            f"CREATE UNIQUE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_provider_instance_idx "
2545            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance,provider_item_id);"
2546        )
2547        await self.database.execute(
2548            "CREATE INDEX IF NOT EXISTS "
2549            f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_instance_idx "
2550            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance);"
2551        )
2552        await self.database.execute(
2553            "CREATE INDEX IF NOT EXISTS "
2554            f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_domain_idx "
2555            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_domain);"
2556        )
2557        await self.database.execute(
2558            "CREATE INDEX IF NOT EXISTS "
2559            f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_instance_library_idx "
2560            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance,in_library);"
2561        )
2562
2563        # indexes on track_artists table
2564        await self.database.execute(
2565            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}_track_id_idx "
2566            f"on {DB_TABLE_TRACK_ARTISTS}(track_id);"
2567        )
2568        await self.database.execute(
2569            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}_artist_id_idx "
2570            f"on {DB_TABLE_TRACK_ARTISTS}(artist_id);"
2571        )
2572        # indexes on album_artists table
2573        await self.database.execute(
2574            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}_album_id_idx "
2575            f"on {DB_TABLE_ALBUM_ARTISTS}(album_id);"
2576        )
2577        await self.database.execute(
2578            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}_artist_id_idx "
2579            f"on {DB_TABLE_ALBUM_ARTISTS}(artist_id);"
2580        )
2581        # index on loudness measurements table
2582        await self.database.execute(
2583            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_LOUDNESS_MEASUREMENTS}_idx "
2584            f"on {DB_TABLE_LOUDNESS_MEASUREMENTS}(media_type,item_id,provider);"
2585        )
2586        # index on smart fades analysis table
2587        await self.database.execute(
2588            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_SMART_FADES_ANALYSIS}_idx "
2589            f"on {DB_TABLE_SMART_FADES_ANALYSIS}(item_id,provider,fragment);"
2590        )
2591        # unique index on playlog table
2592        await self.database.execute(
2593            f"CREATE UNIQUE INDEX IF NOT EXISTS {DB_TABLE_PLAYLOG}_unique_idx "
2594            f"on {DB_TABLE_PLAYLOG}(item_id,provider,media_type,userid);"
2595        )
2596        await self.database.commit()
2597
2598    async def __create_database_triggers(self) -> None:
2599        """Create database triggers."""
2600        # triggers to auto update timestamps
2601        for db_table in (
2602            "artists",
2603            "albums",
2604            "tracks",
2605            "playlists",
2606            "radios",
2607            "audiobooks",
2608            "podcasts",
2609        ):
2610            await self.database.execute(
2611                f"""
2612                CREATE TRIGGER IF NOT EXISTS update_{db_table}_timestamp
2613                AFTER UPDATE ON {db_table}
2614                BEGIN
2615                    UPDATE {db_table} SET timestamp_modified=cast(strftime('%s','now') as int)
2616                    WHERE rowid = new.rowid;
2617                END;
2618                """
2619            )
2620        await self.database.commit()
2621
2622    async def correct_multi_instance_provider_mappings(self) -> None:
2623        """Correct provider mappings for multi-instance providers."""
2624        self.logger.debug("Correcting provider mappings for multi-instance providers...")
2625        multi_instance_providers: set[str] = set()
2626        for provider in self.providers:
2627            if len(self.get_provider_instances(provider.domain)) > 1:
2628                multi_instance_providers.add(provider.instance_id)
2629        if not multi_instance_providers:
2630            return  # no multi-instance providers found, nothing to do
2631
2632        for ctrl in (
2633            self.albums,
2634            self.artists,
2635            self.tracks,
2636            self.playlists,
2637            self.radio,
2638            self.audiobooks,
2639            self.podcasts,
2640        ):
2641            async for db_item in ctrl.iter_library_items(provider=list(multi_instance_providers)):
2642                if self.match_provider_instances(db_item):
2643                    await ctrl.update_item_in_library(db_item.item_id, db_item)
2644                # prevent overwhelming the event loop
2645                await asyncio.sleep(0.2)
2646        self.mass.config.set_raw_core_config_value(
2647            self.domain, LAST_PROVIDER_INSTANCE_SCAN, int(time.time())
2648        )
2649        self.logger.debug("Provider mappings correction done")
2650
2651    async def _get_user_for_provider(
2652        self, provider_mappings_or_instance_id: Iterable[ProviderMapping] | str
2653    ) -> User | None:
2654        """Try to get the MA User based on provider mappings and provider filter."""
2655        all_users = await self.mass.webserver.auth.list_users()
2656        for mapping_or_instance_id in provider_mappings_or_instance_id:
2657            for user in all_users:
2658                if not user.provider_filter:
2659                    continue
2660                if isinstance(mapping_or_instance_id, str):
2661                    if provider_mappings_or_instance_id in user.provider_filter:
2662                        return user
2663                elif mapping_or_instance_id.provider_instance in user.provider_filter:
2664                    return user
2665        return None
2666