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 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        if user:
1189            # NOTE: if no user was found, we will alter the playlog for all users
1190            params["userid"] = user.user_id
1191
1192        # update generic playlog table (when not playing)
1193        if not is_playing:
1194            await self.database.insert(
1195                DB_TABLE_PLAYLOG,
1196                params,
1197                allow_replace=True,
1198            )
1199
1200        # forward to provider(s) to sync resume state (e.g. for audiobooks)
1201        for prov_mapping in media_item.provider_mappings:
1202            if (
1203                user
1204                and user.provider_filter
1205                and prov_mapping.provider_instance not in user.provider_filter
1206            ):
1207                continue
1208            if music_prov := self.mass.get_provider(prov_mapping.provider_instance):
1209                self.mass.create_task(
1210                    music_prov.on_played(
1211                        media_type=media_item.media_type,
1212                        prov_item_id=prov_mapping.item_id,
1213                        fully_played=fully_played,
1214                        position=seconds_played,
1215                        media_item=media_item,
1216                        is_playing=is_playing,
1217                    )
1218                )
1219
1220        # also update playcount in library table (if fully played)
1221        if not fully_played or is_playing:
1222            return
1223        if not (ctrl := self.get_controller(media_item.media_type)):
1224            # skip non media items (e.g. plugin source)
1225            return
1226        db_item = await ctrl.get_library_item_by_prov_id(media_item.item_id, media_item.provider)
1227        if db_item:
1228            await self.database.execute(
1229                f"UPDATE {ctrl.db_table} SET play_count = play_count + 1, "
1230                f"last_played = {timestamp} WHERE item_id = {db_item.item_id}"
1231            )
1232        await self.database.commit()
1233
1234    @api_command("music/mark_unplayed")
1235    async def mark_item_unplayed(
1236        self,
1237        media_item: MediaItemType,
1238        userid: str | None = None,
1239    ) -> None:
1240        """
1241        Mark item as unplayed in playlog.
1242
1243        :param media_item: The media item to mark as unplayed.
1244        :param all_users: If True, mark the item as unplayed for all users.
1245        :param userid: The user ID to mark the item as unplayed for (instead of the current user).
1246        """
1247        params = {
1248            "item_id": media_item.item_id,
1249            "provider": media_item.provider,
1250            "media_type": media_item.media_type.value,
1251        }
1252        # try to figure out the user that triggered the action
1253        user: User | None = None
1254        if userid:
1255            # userid overridden by parameter
1256            user = await self.mass.webserver.auth.get_user(userid)
1257        elif session_user := get_current_user():
1258            # this is the active session user that triggered the action
1259            user = session_user
1260        elif provider_user := await self._get_user_for_provider(media_item.provider_mappings):
1261            # based on configured provider filter we can try to find a user
1262            user = provider_user
1263
1264        if user:
1265            # NOTE: if no user was found, we will alter the playlog for all users
1266            params["userid"] = user.user_id
1267
1268        await self.database.delete(DB_TABLE_PLAYLOG, params)
1269        # forward to provider(s) to sync resume state (e.g. for audiobooks)
1270        for prov_mapping in media_item.provider_mappings:
1271            if (
1272                user
1273                and user.provider_filter
1274                and prov_mapping.provider_instance not in user.provider_filter
1275            ):
1276                continue
1277            if music_prov := self.mass.get_provider(prov_mapping.provider_instance):
1278                self.mass.create_task(
1279                    music_prov.on_played(
1280                        media_type=media_item.media_type,
1281                        prov_item_id=prov_mapping.item_id,
1282                        fully_played=False,
1283                        position=0,
1284                        media_item=media_item,
1285                    )
1286                )
1287        # also update playcount in library table
1288        ctrl = self.get_controller(media_item.media_type)
1289        db_item = await ctrl.get_library_item_by_prov_id(media_item.item_id, media_item.provider)
1290        if db_item:
1291            await self.database.execute(
1292                f"UPDATE {ctrl.db_table} SET play_count = play_count - 1, "
1293                f"last_played = 0 WHERE item_id = {db_item.item_id}"
1294            )
1295            await self.database.commit()
1296
1297    @api_command("music/track_by_name")
1298    async def get_track_by_name(
1299        self,
1300        track_name: str,
1301        artist_name: str | None = None,
1302        album_name: str | None = None,
1303        track_version: str | None = None,
1304    ) -> Track | None:
1305        """Get a track by its name, optionally with artist and album."""
1306        if track_version is None:
1307            track_name, version = parse_title_and_version(track_name)
1308        search_query = f"{artist_name} - {track_name}" if artist_name else track_name
1309        search_result = await self.mass.music.search(
1310            search_query=search_query,
1311            media_types=[MediaType.TRACK],
1312        )
1313        for allow_item_mapping in (False, True):
1314            for search_track in search_result.tracks:
1315                is_track = isinstance(search_track, Track)
1316                if not allow_item_mapping and not is_track:
1317                    continue
1318                if not compare_strings(track_name, search_track.name):
1319                    continue
1320                if not compare_version(version, search_track.version):
1321                    continue
1322                # check optional artist(s)
1323                if artist_name and is_track:
1324                    for artist in search_track.artists:
1325                        if compare_strings(artist_name, artist.name, False):
1326                            break
1327                    else:
1328                        # no artist match found: abort
1329                        continue
1330                # check optional album
1331                if (
1332                    album_name
1333                    and is_track
1334                    and not compare_strings(album_name, search_track.album.name, False)
1335                ):
1336                    # no album match found: abort
1337                    continue
1338                    # if we reach this, we found a match
1339                if not isinstance(search_track, Track):
1340                    # ensure we return an actual Track object
1341                    return await self.mass.music.tracks.get(
1342                        item_id=search_track.item_id,
1343                        provider_instance_id_or_domain=search_track.provider,
1344                    )
1345                return search_track
1346
1347        # try to handle case where something is appended to the title
1348        for splitter in ("•", "-", "|", "(", "["):
1349            if splitter in track_name:
1350                return await self.get_track_by_name(
1351                    track_name=track_name.split(splitter)[0].strip(),
1352                    artist_name=artist_name,
1353                    album_name=None,
1354                    track_version=track_version,
1355                )
1356        # try to handle case where multiple artists are given as single string
1357        if artist_name and (artists := split_artists(artist_name, True)) and len(artists) > 1:
1358            for artist in artists:
1359                return await self.get_track_by_name(
1360                    track_name=track_name,
1361                    artist_name=artist.split(splitter)[0].strip(),
1362                    album_name=None,
1363                    track_version=track_version,
1364                )
1365        # allow non-exact album match as fallback
1366        if album_name:
1367            return await self.get_track_by_name(
1368                track_name=track_name,
1369                artist_name=artist_name,
1370                album_name=None,
1371                track_version=track_version,
1372            )
1373        # no match found
1374        return None
1375
1376    async def get_resume_position(
1377        self, media_item: Audiobook | PodcastEpisode, userid: str | None = None
1378    ) -> tuple[bool, int]:
1379        """
1380        Get progress (resume point) details for the given audiobook or episode.
1381
1382        This is a separate call to ensure the resume position is always up-to-date
1383        and because many providers have this info present on a dedicated endpoint.
1384
1385        Will be called right before playback starts to ensure the resume position is correct.
1386
1387        Returns a boolean with the fully_played status
1388        and an integer with the resume position in ms.
1389        """
1390        provider_fully_played = False
1391        provider_position_ms = 0
1392
1393        # Try to get position from providers
1394        for prov_mapping in media_item.provider_mappings:
1395            if not (
1396                provider := self.mass.get_provider(
1397                    prov_mapping.provider_instance, provider_type=MusicProvider
1398                )
1399            ):
1400                continue
1401            with suppress(NotImplementedError):
1402                (
1403                    provider_fully_played,
1404                    provider_position_ms,
1405                ) = await provider.get_resume_position(prov_mapping.item_id, media_item.media_type)
1406                break  # Use first provider that returns data
1407
1408        # Get MA's internal position from playlog
1409        ma_fully_played = False
1410        ma_position_ms = 0
1411        params = {
1412            "media_type": media_item.media_type.value,
1413            "item_id": media_item.item_id,
1414            "provider": media_item.provider,
1415        }
1416        if userid:
1417            params["userid"] = userid
1418        if db_entry := await self.database.get_row(DB_TABLE_PLAYLOG, params):
1419            ma_position_ms = db_entry["seconds_played"] * 1000 if db_entry["seconds_played"] else 0
1420            ma_fully_played = db_entry["fully_played"]
1421
1422        # Return the higher position to ensure users never lose progress
1423        if ma_position_ms >= provider_position_ms:
1424            return ma_fully_played, ma_position_ms
1425        return provider_fully_played, provider_position_ms
1426
1427    def get_controller(
1428        self, media_type: MediaType
1429    ) -> (
1430        ArtistsController
1431        | AlbumsController
1432        | TracksController
1433        | RadioController
1434        | PlaylistController
1435        | AudiobooksController
1436        | PodcastsController
1437        | GenreController
1438    ):
1439        """Return controller for MediaType."""
1440        if media_type == MediaType.ARTIST:
1441            return self.artists
1442        if media_type == MediaType.ALBUM:
1443            return self.albums
1444        if media_type == MediaType.TRACK:
1445            return self.tracks
1446        if media_type == MediaType.RADIO:
1447            return self.radio
1448        if media_type == MediaType.PLAYLIST:
1449            return self.playlists
1450        if media_type == MediaType.AUDIOBOOK:
1451            return self.audiobooks
1452        if media_type == MediaType.PODCAST:
1453            return self.podcasts
1454        if media_type == MediaType.PODCAST_EPISODE:
1455            return self.podcasts
1456        if media_type == MediaType.GENRE:
1457            return self.genres
1458        raise NotImplementedError
1459
1460    def get_provider_instances(
1461        self, domain: str, return_unavailable: bool = False
1462    ) -> list[MusicProvider]:
1463        """
1464        Return all provider instances for a given domain.
1465
1466        Note that this skips user filters so may only be called from internal code.
1467        """
1468        return cast(
1469            "list[MusicProvider]",
1470            self.mass.get_provider_instances(domain, return_unavailable, ProviderType.MUSIC),
1471        )
1472
1473    def get_unique_providers(self) -> list[str]:
1474        """
1475        Return all unique MusicProvider (instance or domain) ids.
1476
1477        This will return a set of provider instance ids but will only return
1478        a single instance_id per streaming provider domain.
1479
1480        Applies user provider filters (for non-admin users).
1481        """
1482        processed_domains: set[str] = set()
1483        # Get user provider filter if set
1484        user = get_current_user()
1485        user_provider_filter = user.provider_filter if user and user.provider_filter else None
1486        result: list[str] = []
1487        for provider in self.providers:
1488            if provider.is_streaming_provider and provider.domain in processed_domains:
1489                continue
1490            if user_provider_filter and provider.instance_id not in user_provider_filter:
1491                continue
1492            result.append(provider.instance_id)
1493            processed_domains.add(provider.domain)
1494        return result
1495
1496    async def cleanup_provider(self, provider_instance: str) -> None:
1497        """Cleanup provider records from the database."""
1498        if provider_instance.startswith(("filesystem", "jellyfin", "plex", "opensubsonic")):
1499            # removal of a local provider can become messy very fast due to the relations
1500            # such as images pointing at the files etc. so we just reset the whole db
1501            # TODO: Handle this more gracefully in the future where we remove the provider
1502            # and traverse the database to also remove all related items.
1503            self.logger.warning(
1504                "Removal of local provider detected, issuing full database reset..."
1505            )
1506            await self._reset_database()
1507            return
1508        deleted_providers = self.mass.config.get_raw_core_config_value(
1509            self.domain, CONF_DELETED_PROVIDERS, []
1510        )
1511        # we add the provider to this hidden config setting just to make sure that
1512        # we can survive this over a restart to make sure that entries are cleaned up
1513        if provider_instance not in deleted_providers:
1514            deleted_providers.append(provider_instance)
1515            self.mass.config.set_raw_core_config_value(
1516                self.domain, CONF_DELETED_PROVIDERS, deleted_providers
1517            )
1518            self.mass.config.save(True)
1519
1520        # always clear cache when a provider is removed
1521        await self.mass.cache.clear()
1522
1523        # cleanup media items from db matched to deleted provider
1524        self.logger.info(
1525            "Removing provider %s from library, this can take a a while...",
1526            provider_instance,
1527        )
1528        errors = 0
1529        for ctrl in (
1530            # order is important here to recursively cleanup bottom up
1531            self.mass.music.radio,
1532            self.mass.music.playlists,
1533            self.mass.music.tracks,
1534            self.mass.music.albums,
1535            self.mass.music.artists,
1536            self.mass.music.podcasts,
1537            self.mass.music.audiobooks,
1538            # run main controllers twice to rule out relations
1539            self.mass.music.tracks,
1540            self.mass.music.albums,
1541            self.mass.music.artists,
1542        ):
1543            query = (
1544                f"SELECT item_id FROM {DB_TABLE_PROVIDER_MAPPINGS} "
1545                f"WHERE media_type = '{ctrl.media_type}' "
1546                f"AND provider_instance = '{provider_instance}'"
1547            )
1548            for db_row in await self.database.get_rows_from_query(query, limit=100000):
1549                try:
1550                    await ctrl.remove_provider_mappings(db_row["item_id"], provider_instance)
1551                except Exception as err:
1552                    # we dont want the whole removal process to stall on one item
1553                    # so in case of an unexpected error, we log and move on.
1554                    self.logger.warning(
1555                        "Error while removing %s: %s",
1556                        db_row["item_id"],
1557                        str(err),
1558                        exc_info=err if self.logger.isEnabledFor(logging.DEBUG) else None,
1559                    )
1560                    errors += 1
1561
1562        # remove all orphaned items (not in provider mappings table anymore)
1563        query = (
1564            f"SELECT item_id FROM {DB_TABLE_PROVIDER_MAPPINGS} "
1565            f"WHERE provider_instance = '{provider_instance}'"
1566        )
1567        if remaining_items_count := await self.database.get_count_from_query(query):
1568            errors += remaining_items_count
1569
1570        # cleanup playlog table
1571        await self.mass.music.database.delete(
1572            DB_TABLE_PLAYLOG,
1573            {
1574                "provider": provider_instance,
1575            },
1576        )
1577
1578        if errors == 0:
1579            # cleanup successful, remove from the deleted_providers setting
1580            self.logger.info("Provider %s removed from library", provider_instance)
1581            deleted_providers.remove(provider_instance)
1582            self.mass.config.set_raw_core_config_value(
1583                self.domain, CONF_DELETED_PROVIDERS, deleted_providers
1584            )
1585        else:
1586            self.logger.warning(
1587                "Provider %s was not not fully removed from library", provider_instance
1588            )
1589
1590    async def schedule_provider_sync(self, provider_instance_id: str) -> None:
1591        """Schedule Library sync for given provider."""
1592        if not (provider := self.mass.get_provider(provider_instance_id)):
1593            return
1594        self.unschedule_provider_sync(provider.instance_id)
1595        for media_type in MediaType:
1596            if not provider.library_supported(media_type):
1597                continue
1598            await self._schedule_provider_mediatype_sync(provider, media_type, True)
1599
1600    def unschedule_provider_sync(self, provider_instance_id: str) -> None:
1601        """Unschedule Library sync for given provider."""
1602        # cancel all scheduled sync tasks
1603        for media_type in MediaType:
1604            key = f"sync_{provider_instance_id}_{media_type.value}"
1605            self.mass.cancel_timer(key)
1606        # cancel any running sync tasks
1607        for sync_task in self.in_progress_syncs:
1608            if sync_task.provider_instance == provider_instance_id:
1609                sync_task.task.cancel()
1610
1611    def match_provider_instances(
1612        self,
1613        item: MediaItemType,
1614    ) -> bool:
1615        """Match all provider instances for the given item."""
1616        mappings_added = False
1617        for provider_mapping in list(item.provider_mappings):
1618            if provider_mapping.is_unique:
1619                # unique mapping, no need to map
1620                continue
1621            if not (provider := self.mass.get_provider(provider_mapping.provider_instance)):
1622                continue
1623            if not provider.is_streaming_provider:
1624                continue
1625            provider_instances = self.get_provider_instances(
1626                provider.domain, return_unavailable=True
1627            )
1628            if len(provider_instances) <= 1:
1629                # only a single instance, no need to map
1630                continue
1631            for prov_instance in provider_instances:
1632                if prov_instance.instance_id == provider.instance_id:
1633                    continue
1634                if any(
1635                    pm.provider_instance == prov_instance.instance_id
1636                    for pm in item.provider_mappings
1637                ):
1638                    # mapping already exists
1639                    continue
1640                # create additional mapping for other provider instances of the same provider
1641                item.provider_mappings.add(
1642                    ProviderMapping(
1643                        item_id=provider_mapping.item_id,
1644                        provider_domain=provider.domain,
1645                        provider_instance=prov_instance.instance_id,
1646                        available=provider_mapping.available,
1647                        is_unique=provider_mapping.is_unique,
1648                        audio_format=provider_mapping.audio_format,
1649                        url=provider_mapping.url,
1650                        details=provider_mapping.details,
1651                        in_library=None,
1652                    )
1653                )
1654                mappings_added = True
1655        return mappings_added
1656
1657    @api_command("music/add_provider_mapping")
1658    async def add_provider_mapping(
1659        self, media_type: MediaType, db_id: str, mapping: ProviderMapping
1660    ) -> None:
1661        """Add provider mapping to the given library item."""
1662        ctrl = self.get_controller(media_type)
1663        await ctrl.add_provider_mappings(db_id, [mapping])
1664
1665    @api_command("music/remove_provider_mapping")
1666    async def remove_provider_mapping(
1667        self, media_type: MediaType, db_id: str, mapping: ProviderMapping
1668    ) -> None:
1669        """Remove provider mapping from the given library item."""
1670        ctrl = self.get_controller(media_type)
1671        await ctrl.remove_provider_mapping(db_id, mapping.provider_instance, mapping.item_id)
1672
1673    @api_command("music/match_providers")
1674    async def match_providers(self, media_type: MediaType, db_id: str) -> None:
1675        """Search for mappings on all providers for the given library item."""
1676        ctrl = self.get_controller(media_type)
1677        db_item = await ctrl.get_library_item(db_id)
1678        await ctrl.match_providers(db_item)
1679
1680    async def update_provider_mapping(
1681        self,
1682        media_type: MediaType,
1683        db_id: str | int,
1684        provider_instance_id: str,
1685        provider_item_id: str,
1686        *,
1687        available: bool | Any = UNSET,
1688        in_library: bool | Any = UNSET,
1689        is_unique: bool | None | Any = UNSET,
1690        url: str | None | Any = UNSET,
1691        details: str | None | Any = UNSET,
1692        audio_format: AudioFormat | Any = UNSET,
1693    ) -> None:
1694        """Update an existing provider mapping for a library item."""
1695        ctrl = self.get_controller(media_type)
1696        await ctrl.update_provider_mapping(
1697            item_id=db_id,
1698            provider_instance_id=provider_instance_id,
1699            provider_item_id=provider_item_id,
1700            available=available,
1701            in_library=in_library,
1702            is_unique=is_unique,
1703            url=url,
1704            details=details,
1705            audio_format=audio_format,
1706        )
1707
1708    async def _get_default_recommendations(self) -> list[RecommendationFolder]:
1709        """Return default recommendations."""
1710        return [
1711            RecommendationFolder(
1712                item_id="in_progress",
1713                provider="library",
1714                name="In progress",
1715                translation_key="in_progress_items",
1716                icon="mdi-motion-play",
1717                items=await self.in_progress_items(limit=10),
1718            ),
1719            RecommendationFolder(
1720                item_id="recently_played",
1721                provider="library",
1722                name="Recently played",
1723                translation_key="recently_played",
1724                icon="mdi-motion-play",
1725                items=await self.recently_played(limit=10, user_initiated_only=True),
1726            ),
1727            RecommendationFolder(
1728                item_id="recently_added_tracks",
1729                provider="library",
1730                name="Recently added tracks",
1731                translation_key="recently_added_tracks",
1732                icon="music-note-plus",
1733                items=await self.tracks.library_items(limit=10, order_by="timestamp_added_desc"),
1734            ),
1735            RecommendationFolder(
1736                item_id="recently_added_albums",
1737                provider="library",
1738                name="Recently added albums",
1739                translation_key="recently_added_albums",
1740                icon="music-note-plus",
1741                items=await self.albums.library_items(limit=10, order_by="timestamp_added_desc"),
1742            ),
1743            RecommendationFolder(
1744                item_id="random_artists",
1745                provider="library",
1746                name="Random artists",
1747                translation_key="random_artists",
1748                icon="mdi-account-music",
1749                items=await self.artists.library_items(limit=10, order_by="random_play_count"),
1750            ),
1751            RecommendationFolder(
1752                item_id="random_albums",
1753                provider="library",
1754                name="Random albums",
1755                translation_key="random_albums",
1756                icon="mdi-album",
1757                items=await self.albums.library_items(limit=10, order_by="random_play_count"),
1758            ),
1759            RecommendationFolder(
1760                item_id="recent_favorite_tracks",
1761                provider="library",
1762                name="Recently favorited tracks",
1763                translation_key="recent_favorite_tracks",
1764                icon="mdi-file-music",
1765                items=await self.tracks.library_items(
1766                    favorite=True, limit=10, order_by="timestamp_modified_desc"
1767                ),
1768            ),
1769            RecommendationFolder(
1770                item_id="favorite_playlists",
1771                provider="library",
1772                name="Favorite playlists",
1773                translation_key="favorite_playlists",
1774                icon="mdi-playlist-music",
1775                items=await self.playlists.library_items(
1776                    favorite=True, limit=10, order_by="random"
1777                ),
1778            ),
1779            RecommendationFolder(
1780                item_id="favorite_radio",
1781                provider="library",
1782                name="Favorite Radio stations",
1783                translation_key="favorite_radio_stations",
1784                icon="mdi-access-point",
1785                items=await self.radio.library_items(
1786                    favorite=True, limit=10, order_by="play_count_desc"
1787                ),
1788            ),
1789        ]
1790
1791    async def _get_provider_recommendations(
1792        self, provider: MusicProvider
1793    ) -> list[RecommendationFolder]:
1794        """Return recommendations from a provider."""
1795        try:
1796            return await provider.recommendations()
1797        except Exception as err:
1798            self.logger.warning(
1799                "Error while fetching recommendations from %s: %s",
1800                provider.name,
1801                str(err),
1802                exc_info=err if self.logger.isEnabledFor(logging.DEBUG) else None,
1803            )
1804            return []
1805
1806    def _start_provider_sync(self, provider: MusicProvider, media_type: MediaType) -> None:
1807        """Start sync task on provider and track progress."""
1808        # check if we're not already running a sync task for this provider/mediatype
1809        for sync_task in self.in_progress_syncs:
1810            if sync_task.provider_instance != provider.instance_id:
1811                continue
1812            if sync_task.task.done():
1813                continue
1814            if media_type in sync_task.media_types:
1815                self.logger.debug(
1816                    "Skip sync task for %s/%ss because another task is already in progress",
1817                    provider.name,
1818                    media_type.value,
1819                )
1820                return
1821
1822        async def run_sync() -> None:
1823            # Wrap the provider sync into a lock to prevent
1824            # race conditions when multiple providers are syncing at the same time.
1825            async with self._sync_lock:
1826                await provider.sync_library(media_type)
1827
1828        # we keep track of running sync tasks
1829        task = self.mass.create_task(run_sync())
1830        sync_spec = SyncTask(
1831            provider_domain=provider.domain,
1832            provider_instance=provider.instance_id,
1833            media_types=(media_type,),
1834            task=task,
1835        )
1836        self.in_progress_syncs.append(sync_spec)
1837
1838        self.mass.signal_event(EventType.SYNC_TASKS_UPDATED, data=self.in_progress_syncs)
1839
1840        def on_sync_task_done(task: asyncio.Task) -> None:
1841            self.in_progress_syncs.remove(sync_spec)
1842            if task.cancelled():
1843                return
1844            if task_err := task.exception():
1845                self.logger.warning(
1846                    "Sync task for %s/%ss completed with errors",
1847                    provider.name,
1848                    media_type.value,
1849                    exc_info=task_err if self.logger.isEnabledFor(10) else None,
1850                )
1851            else:
1852                self.logger.info("Sync task for %s/%ss completed", provider.name, media_type.value)
1853            self.mass.signal_event(EventType.SYNC_TASKS_UPDATED, data=self.in_progress_syncs)
1854            self.mass.create_task(
1855                self.mass.cache.set(
1856                    key=media_type.value,
1857                    data=self.mass.loop.time(),
1858                    provider=provider.instance_id,
1859                    category=CACHE_CATEGORY_LAST_SYNC,
1860                )
1861            )
1862            # schedule db cleanup after sync
1863            if not self.in_progress_syncs:
1864                self.mass.create_task(self._cleanup_database())
1865            # reschedule next execution
1866            self.mass.create_task(self._schedule_provider_mediatype_sync(provider, media_type))
1867
1868        task.add_done_callback(on_sync_task_done)
1869        return
1870
1871    def _sort_search_result(
1872        self,
1873        search_query: str,
1874        items: Sequence[MediaItemType | ItemMapping],
1875    ) -> UniqueList[MediaItemType | ItemMapping]:
1876        """Sort search results on priority/preference."""
1877        scored_items: list[tuple[int, MediaItemType | ItemMapping]] = []
1878        # search results are already sorted by (streaming) providers on relevance
1879        # but we prefer exact name matches and library items so we simply put those
1880        # on top of the list.
1881        safe_title_str = create_safe_string(search_query)
1882        if " - " in search_query:
1883            artist, title_alt = search_query.split(" - ", 1)
1884            safe_title_alt = create_safe_string(title_alt)
1885            safe_artist_str = create_safe_string(artist)
1886        else:
1887            safe_artist_str = None
1888            safe_title_alt = None
1889        for item in items:
1890            score = 0
1891            if create_safe_string(item.name) not in (safe_title_str, safe_title_alt):
1892                # literal name match is mandatory to get a score at all
1893                continue
1894            # bonus point if artist provided and exact match
1895            if safe_artist_str:
1896                artist: Artist | ItemMapping
1897                for artist in getattr(item, "artists", []):
1898                    if create_safe_string(artist.name) == safe_artist_str:
1899                        score += 1
1900            # bonus point for library items
1901            if item.provider == "library":
1902                score += 1
1903            scored_items.append((score, item))
1904        scored_items.sort(key=lambda x: x[0], reverse=True)
1905        # combine it all with uniquelist, so this will deduplicated by default
1906        # note that streaming provider results are already (most likely) sorted on relevance
1907        # so we add all remaining items in their original order. We just prioritize
1908        # exact name matches and library items.
1909        return UniqueList([*[x[1] for x in scored_items], *items])
1910
1911    async def _schedule_provider_mediatype_sync(
1912        self, provider: MusicProvider, media_type: MediaType, is_initial: bool = False
1913    ) -> None:
1914        """Schedule Library sync for given provider and media type."""
1915        job_key = f"sync_{provider.instance_id}_{media_type.value}"
1916        # cancel any existing timers
1917        self.mass.cancel_timer(job_key)
1918        # handle mediatype specific sync config
1919        conf_key = f"library_sync_{media_type}s"
1920        sync_conf = await self.mass.config.get_provider_config_value(provider.instance_id, conf_key)
1921        if not sync_conf:
1922            return
1923        conf_key = f"provider_sync_interval_{media_type.value}s"
1924        sync_interval = await self.mass.config.get_provider_config_value(
1925            provider.instance_id, conf_key, return_type=int
1926        )
1927        if sync_interval <= 0:
1928            # sync disabled for this media type
1929            return
1930        sync_interval = sync_interval * 60  # config interval is in minutes - convert to seconds
1931
1932        if is_initial:
1933            # schedule the first sync run
1934            initial_interval = 10
1935            if last_sync := await self.mass.cache.get(
1936                key=media_type.value,
1937                provider=provider.instance_id,
1938                category=CACHE_CATEGORY_LAST_SYNC,
1939            ):
1940                initial_interval += max(0, sync_interval - (self.mass.loop.time() - last_sync))
1941            sync_interval = initial_interval
1942
1943        self.mass.call_later(
1944            sync_interval,
1945            self._start_provider_sync,
1946            provider,
1947            media_type,
1948            task_id=job_key,
1949        )
1950
1951    async def _cleanup_database(self) -> None:
1952        """Perform database cleanup/maintenance."""
1953        self.logger.debug("Performing database cleanup...")
1954        # Remove playlog entries older than 90 days
1955        await self.database.delete_where_query(
1956            DB_TABLE_PLAYLOG, f"timestamp < strftime('%s','now') - {3600 * 24 * 90}"
1957        )
1958        # db tables cleanup
1959        for ctrl in (
1960            self.albums,
1961            self.artists,
1962            self.tracks,
1963            self.playlists,
1964            self.radio,
1965        ):
1966            # Provider mappings where the db item is removed
1967            query = (
1968                f"item_id not in (SELECT item_id from {ctrl.db_table}) "
1969                f"AND media_type = '{ctrl.media_type}'"
1970            )
1971            await self.database.delete_where_query(DB_TABLE_PROVIDER_MAPPINGS, query)
1972            # Orphaned db items
1973            query = (
1974                f"item_id not in (SELECT item_id from {DB_TABLE_PROVIDER_MAPPINGS} "
1975                f"WHERE media_type = '{ctrl.media_type}')"
1976            )
1977            await self.database.delete_where_query(ctrl.db_table, query)
1978            # Cleanup removed db items from the playlog
1979            where_clause = (
1980                f"media_type = '{ctrl.media_type}' AND provider = 'library' "
1981                f"AND item_id not in (select item_id from {ctrl.db_table})"
1982            )
1983            await self.mass.music.database.delete_where_query(DB_TABLE_PLAYLOG, where_clause)
1984        self.logger.debug("Database cleanup done")
1985
1986    async def _setup_database(self) -> None:
1987        """Initialize database."""
1988        db_path = os.path.join(self.mass.storage_path, "library.db")
1989        self._database = DatabaseConnection(db_path)
1990        await self._database.setup()
1991
1992        # always create db tables if they don't exist to prevent errors trying to access them later
1993        await self.__create_database_tables()
1994        try:
1995            if db_row := await self._database.get_row(DB_TABLE_SETTINGS, {"key": "version"}):
1996                prev_version = int(db_row["value"])
1997            else:
1998                prev_version = 0
1999        except (KeyError, ValueError):
2000            prev_version = 0
2001
2002        if prev_version not in (0, DB_SCHEMA_VERSION):
2003            # db version mismatch - we need to do a migration
2004            # make a backup of db file
2005            db_path_backup = db_path + ".backup"
2006            await asyncio.to_thread(shutil.copyfile, db_path, db_path_backup)
2007
2008            # handle db migration from previous schema(s) to this one
2009            try:
2010                await self.__migrate_database(prev_version)
2011            except Exception as err:
2012                # if the migration fails completely we reset the db
2013                # so the user at least can have a working situation back
2014                # a backup file is made with the previous version
2015                self.logger.error(
2016                    "Database migration failed - starting with a fresh library database, "
2017                    "a full rescan will be performed, this can take a while!",
2018                )
2019                if not isinstance(err, MusicAssistantError):
2020                    self.logger.exception(err)
2021
2022                await self._database.close()
2023                await asyncio.to_thread(os.remove, db_path)
2024                self._database = DatabaseConnection(db_path)
2025                await self._database.setup()
2026                await self.mass.cache.clear()
2027                await self.__create_database_tables()
2028
2029        # store current schema version
2030        await self._database.insert_or_replace(
2031            DB_TABLE_SETTINGS,
2032            {"key": "version", "value": str(DB_SCHEMA_VERSION), "type": "str"},
2033        )
2034        # create indexes and triggers if needed
2035        await self.__create_database_indexes()
2036        await self.__create_database_triggers()
2037        # compact db
2038        self.logger.debug("Compacting database...")
2039        try:
2040            await self._database.vacuum()
2041        except Exception as err:
2042            self.logger.warning("Database vacuum failed: %s", str(err))
2043        else:
2044            self.logger.debug("Compacting database done")
2045
2046    async def __migrate_database(self, prev_version: int) -> None:  # noqa: PLR0915
2047        """Perform a database migration."""
2048        self.logger.info(
2049            "Migrating database from version %s to %s", prev_version, DB_SCHEMA_VERSION
2050        )
2051
2052        if prev_version < 15:
2053            raise MusicAssistantError("Database schema version too old to migrate")
2054
2055        if prev_version <= 15:
2056            # add search_name and search_sort_name columns to all tables
2057            # and populate them with the name and sort_name values
2058            # this is to allow for local/case independent searches
2059            for table in (
2060                DB_TABLE_TRACKS,
2061                DB_TABLE_ALBUMS,
2062                DB_TABLE_ARTISTS,
2063                DB_TABLE_RADIOS,
2064                DB_TABLE_PLAYLISTS,
2065                DB_TABLE_AUDIOBOOKS,
2066                DB_TABLE_PODCASTS,
2067            ):
2068                try:
2069                    await self._database.execute(
2070                        f"ALTER TABLE {table} ADD COLUMN search_name TEXT DEFAULT '' NOT NULL"
2071                    )
2072                    await self._database.execute(
2073                        f"ALTER TABLE {table} ADD COLUMN search_sort_name TEXT DEFAULT '' NOT NULL"
2074                    )
2075                except Exception as err:
2076                    if "duplicate column" not in str(err):
2077                        raise
2078                # migrate all existing values
2079                async for db_row in self._database.iter_items(table):
2080                    await self._database.update(
2081                        table,
2082                        {"item_id": db_row["item_id"]},
2083                        {
2084                            "search_name": create_safe_string(db_row["name"], True, True),
2085                            "search_sort_name": create_safe_string(db_row["sort_name"], True, True),
2086                        },
2087                    )
2088
2089        if prev_version <= 16:
2090            # cleanup invalid release_date field in metadata
2091            for table in (
2092                DB_TABLE_TRACKS,
2093                DB_TABLE_ALBUMS,
2094                DB_TABLE_AUDIOBOOKS,
2095                DB_TABLE_PODCASTS,
2096            ):
2097                async for db_row in self._database.iter_items(table):
2098                    if '"release_date":null' in db_row["metadata"]:
2099                        continue
2100                    metadata = json_loads(db_row["metadata"])
2101                    try:
2102                        datetime.fromisoformat(metadata["release_date"])
2103                    except (KeyError, ValueError):
2104                        # this is not a valid date, so we set it to None
2105                        metadata["release_date"] = None
2106                        await self._database.update(
2107                            table,
2108                            {"item_id": db_row["item_id"]},
2109                            {
2110                                "metadata": serialize_to_json(metadata),
2111                            },
2112                        )
2113
2114        if prev_version <= 17:
2115            # migrate triggers to auto update timestamps
2116            # it had an error in the previous version where it was not created
2117            for db_table in (
2118                "artists",
2119                "albums",
2120                "tracks",
2121                "playlists",
2122                "radios",
2123                "audiobooks",
2124                "podcasts",
2125            ):
2126                await self._database.execute(f"DROP TRIGGER IF EXISTS update_{db_table}_timestamp;")
2127
2128        if prev_version <= 18:
2129            # add in_library column to provider_mappings table
2130            await self._database.execute(
2131                f"ALTER TABLE {DB_TABLE_PROVIDER_MAPPINGS} ADD COLUMN in_library "
2132                "BOOLEAN NOT NULL DEFAULT 0;"
2133            )
2134            # migrate existing entries in provider_mappings which are filesystem
2135            await self._database.execute(
2136                f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
2137                "WHERE provider_domain in ('filesystem_local', 'filesystem_smb');"
2138            )
2139
2140        if prev_version <= 20:
2141            # drop column cache_checksum from playlists table
2142            # this is no longer used and is a leftover from previous designs
2143            try:
2144                await self._database.execute(
2145                    f"ALTER TABLE {DB_TABLE_PLAYLISTS} DROP COLUMN cache_checksum"
2146                )
2147            except Exception as err:
2148                if "no such column" not in str(err):
2149                    raise
2150
2151        if prev_version <= 21:
2152            # drop table for smart fades analysis - it will be recreated with needed columns
2153            await self._database.execute(f"DROP TABLE IF EXISTS {DB_TABLE_SMART_FADES_ANALYSIS}")
2154            await self.__create_database_tables()
2155
2156        if prev_version <= 22:
2157            # add userid column to playlog table
2158            try:
2159                await self._database.execute(
2160                    f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN userid TEXT"
2161                )
2162            except Exception as err:
2163                if "duplicate column" not in str(err):
2164                    raise
2165            # Note: SQLite doesn't support modifying constraints directly
2166            # The UNIQUE constraint will be updated when the table is recreated
2167            # For now, we'll keep the old constraint and add a new one via unique index
2168            try:
2169                await self._database.execute(f"DROP INDEX IF EXISTS {DB_TABLE_PLAYLOG}_unique_idx")
2170                await self._database.execute(
2171                    f"CREATE UNIQUE INDEX {DB_TABLE_PLAYLOG}_unique_idx "
2172                    f"ON {DB_TABLE_PLAYLOG}(item_id,provider,media_type,userid)"
2173                )
2174            except Exception as err:
2175                # If we can't create the index due to duplicate entries, log and continue
2176                self.logger.warning("Could not create unique index on playlog: %s", err)
2177
2178        if prev_version <= 23:
2179            # add is_unique column to provider_mappings table
2180            try:
2181                await self._database.execute(
2182                    f"ALTER TABLE {DB_TABLE_PROVIDER_MAPPINGS} ADD COLUMN is_unique BOOLEAN"
2183                )
2184            except Exception as err:
2185                if "duplicate column" not in str(err):
2186                    raise
2187
2188        if prev_version <= 24:
2189            # add queue_id and user_initiated columns to playlog table
2190            try:
2191                await self._database.execute(
2192                    f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN queue_id TEXT"
2193                )
2194            except Exception as err:
2195                if "duplicate column" not in str(err):
2196                    raise
2197            try:
2198                await self._database.execute(
2199                    f"ALTER TABLE {DB_TABLE_PLAYLOG} "
2200                    "ADD COLUMN user_initiated BOOLEAN NOT NULL DEFAULT 1"
2201                )
2202            except Exception as err:
2203                if "duplicate column" not in str(err):
2204                    raise
2205
2206        if prev_version <= 26:
2207            # force in_library=True for provider mappings from non-streaming providers
2208            # streaming providers will be automatically added to library when synced
2209            await self._database.execute(
2210                f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
2211                "WHERE provider_domain NOT IN "
2212                "('spotify', 'deezer', 'tidal', 'qobuz', 'apple_music', 'ytmusic');"
2213            )
2214            # also set in_library=True for all radio items
2215            await self._database.execute(
2216                f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
2217                "WHERE media_type = 'radio';"
2218            )
2219            # remove invalid playlist provider mappings for playlists which are not in library
2220            await self._database.execute(
2221                f"DELETE FROM {DB_TABLE_PROVIDER_MAPPINGS} "
2222                "WHERE media_type = 'playlist' AND in_library = 0;"
2223            )
2224
2225        # save changes
2226        await self._database.commit()
2227
2228        # always clear the cache after a db migration
2229        await self.mass.cache.clear()
2230
2231    async def _reset_database(self) -> None:
2232        """Reset the database."""
2233        await self.close()
2234        db_path = os.path.join(self.mass.storage_path, "library.db")
2235        await asyncio.to_thread(os.remove, db_path)
2236        await self._setup_database()
2237        # initiate full sync
2238        await self.start_sync()
2239
2240    async def __create_database_tables(self) -> None:
2241        """Create database tables."""
2242        await self.database.execute(
2243            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_SETTINGS}(
2244                    [key] TEXT PRIMARY KEY,
2245                    [value] TEXT,
2246                    [type] TEXT
2247                );"""
2248        )
2249        await self.database.execute(
2250            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_PLAYLOG}(
2251                [id] INTEGER PRIMARY KEY AUTOINCREMENT,
2252                [item_id] TEXT NOT NULL,
2253                [provider] TEXT NOT NULL,
2254                [media_type] TEXT NOT NULL,
2255                [name] TEXT NOT NULL,
2256                [image] json,
2257                [timestamp] INTEGER DEFAULT 0,
2258                [fully_played] BOOLEAN,
2259                [seconds_played] INTEGER,
2260                [userid] TEXT NOT NULL,
2261                [queue_id] TEXT,
2262                [user_initiated] BOOLEAN NOT NULL DEFAULT 1,
2263                UNIQUE(item_id, provider, media_type, userid));"""
2264        )
2265        await self.database.execute(
2266            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUMS}(
2267                    [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2268                    [name] TEXT NOT NULL,
2269                    [sort_name] TEXT NOT NULL,
2270                    [version] TEXT,
2271                    [album_type] TEXT NOT NULL,
2272                    [year] INTEGER,
2273                    [favorite] BOOLEAN NOT NULL DEFAULT 0,
2274                    [metadata] json NOT NULL,
2275                    [external_ids] json NOT NULL,
2276                    [play_count] INTEGER NOT NULL DEFAULT 0,
2277                    [last_played] INTEGER NOT NULL DEFAULT 0,
2278                    [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2279                    [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2280                    [search_name] TEXT NOT NULL,
2281                    [search_sort_name] TEXT NOT NULL
2282                );"""
2283        )
2284        await self.database.execute(
2285            f"""
2286            CREATE TABLE IF NOT EXISTS {DB_TABLE_ARTISTS}(
2287            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2288            [name] TEXT NOT NULL,
2289            [sort_name] TEXT NOT NULL,
2290            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2291            [metadata] json NOT NULL,
2292            [external_ids] json NOT NULL,
2293            [play_count] INTEGER DEFAULT 0,
2294            [last_played] INTEGER DEFAULT 0,
2295            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2296            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2297            [search_name] TEXT NOT NULL,
2298            [search_sort_name] TEXT NOT NULL
2299            );"""
2300        )
2301        await self.database.execute(
2302            f"""
2303            CREATE TABLE IF NOT EXISTS {DB_TABLE_TRACKS}(
2304            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2305            [name] TEXT NOT NULL,
2306            [sort_name] TEXT NOT NULL,
2307            [version] TEXT,
2308            [duration] INTEGER,
2309            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2310            [metadata] json NOT NULL,
2311            [external_ids] json NOT NULL,
2312            [play_count] INTEGER DEFAULT 0,
2313            [last_played] INTEGER DEFAULT 0,
2314            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2315            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2316            [search_name] TEXT NOT NULL,
2317            [search_sort_name] TEXT NOT NULL
2318            );"""
2319        )
2320        await self.database.execute(
2321            f"""
2322            CREATE TABLE IF NOT EXISTS {DB_TABLE_PLAYLISTS}(
2323            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2324            [name] TEXT NOT NULL,
2325            [sort_name] TEXT NOT NULL,
2326            [owner] TEXT NOT NULL,
2327            [is_editable] BOOLEAN NOT NULL,
2328            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2329            [metadata] json NOT NULL,
2330            [external_ids] json NOT NULL,
2331            [play_count] INTEGER DEFAULT 0,
2332            [last_played] INTEGER DEFAULT 0,
2333            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2334            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2335            [search_name] TEXT NOT NULL,
2336            [search_sort_name] TEXT NOT NULL
2337            );"""
2338        )
2339        await self.database.execute(
2340            f"""
2341            CREATE TABLE IF NOT EXISTS {DB_TABLE_RADIOS}(
2342            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2343            [name] TEXT NOT NULL,
2344            [sort_name] TEXT NOT NULL,
2345            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2346            [metadata] json NOT NULL,
2347            [external_ids] json NOT NULL,
2348            [play_count] INTEGER DEFAULT 0,
2349            [last_played] INTEGER DEFAULT 0,
2350            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2351            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2352            [search_name] TEXT NOT NULL,
2353            [search_sort_name] TEXT NOT NULL
2354            );"""
2355        )
2356        await self.database.execute(
2357            f"""
2358            CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIOBOOKS}(
2359            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2360            [name] TEXT NOT NULL,
2361            [sort_name] TEXT NOT NULL,
2362            [version] TEXT,
2363            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2364            [publisher] TEXT,
2365            [authors] json NOT NULL,
2366            [narrators] json NOT NULL,
2367            [metadata] json NOT NULL,
2368            [duration] INTEGER,
2369            [external_ids] json NOT NULL,
2370            [play_count] INTEGER DEFAULT 0,
2371            [last_played] INTEGER DEFAULT 0,
2372            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2373            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2374            [search_name] TEXT NOT NULL,
2375            [search_sort_name] TEXT NOT NULL
2376            );"""
2377        )
2378        await self.database.execute(
2379            f"""
2380            CREATE TABLE IF NOT EXISTS {DB_TABLE_PODCASTS}(
2381            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
2382            [name] TEXT NOT NULL,
2383            [sort_name] TEXT NOT NULL,
2384            [version] TEXT,
2385            [favorite] BOOLEAN NOT NULL DEFAULT 0,
2386            [publisher] TEXT,
2387            [total_episodes] INTEGER NOT NULL,
2388            [metadata] json NOT NULL,
2389            [external_ids] json NOT NULL,
2390            [play_count] INTEGER NOT NULL DEFAULT 0,
2391            [last_played] INTEGER NOT NULL DEFAULT 0,
2392            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2393            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
2394            [search_name] TEXT NOT NULL,
2395            [search_sort_name] TEXT NOT NULL
2396            );"""
2397        )
2398        await self.database.execute(
2399            f"""
2400            CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUM_TRACKS}(
2401            [id] INTEGER PRIMARY KEY AUTOINCREMENT,
2402            [track_id] INTEGER NOT NULL,
2403            [album_id] INTEGER NOT NULL,
2404            [disc_number] INTEGER NOT NULL,
2405            [track_number] INTEGER NOT NULL,
2406            FOREIGN KEY([track_id]) REFERENCES [tracks]([item_id]),
2407            FOREIGN KEY([album_id]) REFERENCES [albums]([item_id]),
2408            UNIQUE(track_id, album_id)
2409            );"""
2410        )
2411        await self.database.execute(
2412            f"""
2413            CREATE TABLE IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}(
2414            [media_type] TEXT NOT NULL,
2415            [item_id] INTEGER NOT NULL,
2416            [provider_domain] TEXT NOT NULL,
2417            [provider_instance] TEXT NOT NULL,
2418            [provider_item_id] TEXT NOT NULL,
2419            [available] BOOLEAN NOT NULL DEFAULT 1,
2420            [in_library] BOOLEAN NOT NULL DEFAULT 0,
2421            [is_unique] BOOLEAN,
2422            [url] text,
2423            [audio_format] json,
2424            [details] TEXT,
2425            UNIQUE(media_type, provider_instance, provider_item_id)
2426            );"""
2427        )
2428        await self.database.execute(
2429            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}(
2430            [track_id] INTEGER NOT NULL,
2431            [artist_id] INTEGER NOT NULL,
2432            FOREIGN KEY([track_id]) REFERENCES [tracks]([item_id]),
2433            FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
2434            UNIQUE(track_id, artist_id)
2435            );"""
2436        )
2437        await self.database.execute(
2438            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}(
2439            [album_id] INTEGER NOT NULL,
2440            [artist_id] INTEGER NOT NULL,
2441            FOREIGN KEY([album_id]) REFERENCES [albums]([item_id]),
2442            FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
2443            UNIQUE(album_id, artist_id)
2444            );"""
2445        )
2446
2447        await self.database.execute(
2448            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_LOUDNESS_MEASUREMENTS}(
2449                    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
2450                    [media_type] TEXT NOT NULL,
2451                    [item_id] TEXT NOT NULL,
2452                    [provider] TEXT NOT NULL,
2453                    [loudness] REAL,
2454                    [loudness_album] REAL,
2455                    UNIQUE(media_type,item_id,provider));"""
2456        )
2457
2458        await self.database.execute(
2459            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_SMART_FADES_ANALYSIS}(
2460                    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
2461                    [item_id] TEXT NOT NULL,
2462                    [provider] TEXT NOT NULL,
2463                    [fragment] INTEGER NOT NULL,
2464                    [bpm] REAL NOT NULL,
2465                    [beats] TEXT NOT NULL,
2466                    [downbeats] TEXT NOT NULL,
2467                    [confidence] REAL NOT NULL,
2468                    [duration] REAL,
2469                    [analysis_version] INTEGER DEFAULT 1,
2470                    [timestamp_created] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
2471                    UNIQUE(item_id,provider,fragment));"""
2472        )
2473
2474        await self.database.commit()
2475
2476    async def __create_database_indexes(self) -> None:
2477        """Create database indexes."""
2478        for db_table in (
2479            DB_TABLE_ARTISTS,
2480            DB_TABLE_ALBUMS,
2481            DB_TABLE_TRACKS,
2482            DB_TABLE_PLAYLISTS,
2483            DB_TABLE_RADIOS,
2484            DB_TABLE_AUDIOBOOKS,
2485            DB_TABLE_PODCASTS,
2486        ):
2487            # index on favorite column
2488            await self.database.execute(
2489                f"CREATE INDEX IF NOT EXISTS {db_table}_favorite_idx on {db_table}(favorite);"
2490            )
2491            # index on name
2492            await self.database.execute(
2493                f"CREATE INDEX IF NOT EXISTS {db_table}_name_idx on {db_table}(name);"
2494            )
2495            # index on search_name (=lowercase name without diacritics)
2496            await self.database.execute(
2497                f"CREATE INDEX IF NOT EXISTS {db_table}_name_nocase_idx ON {db_table}(search_name);"
2498            )
2499            # index on sort_name
2500            await self.database.execute(
2501                f"CREATE INDEX IF NOT EXISTS {db_table}_sort_name_idx on {db_table}(sort_name);"
2502            )
2503            # index on search_sort_name (=lowercase sort_name without diacritics)
2504            await self.database.execute(
2505                f"CREATE INDEX IF NOT EXISTS {db_table}_search_sort_name_idx "
2506                f"ON {db_table}(search_sort_name);"
2507            )
2508            # index on external_ids
2509            await self.database.execute(
2510                f"CREATE INDEX IF NOT EXISTS {db_table}_external_ids_idx "
2511                f"ON {db_table}(external_ids);"
2512            )
2513            # index on timestamp_added
2514            await self.database.execute(
2515                f"CREATE INDEX IF NOT EXISTS {db_table}_timestamp_added_idx "
2516                f"on {db_table}(timestamp_added);"
2517            )
2518            # index on play_count
2519            await self.database.execute(
2520                f"CREATE INDEX IF NOT EXISTS {db_table}_play_count_idx on {db_table}(play_count);"
2521            )
2522            # index on last_played
2523            await self.database.execute(
2524                f"CREATE INDEX IF NOT EXISTS {db_table}_last_played_idx on {db_table}(last_played);"
2525            )
2526
2527        # indexes on provider_mappings table
2528        await self.database.execute(
2529            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_media_type_item_id_idx "
2530            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,item_id);"
2531        )
2532        await self.database.execute(
2533            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_provider_domain_idx "
2534            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_domain,provider_item_id);"
2535        )
2536        await self.database.execute(
2537            f"CREATE UNIQUE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_provider_instance_idx "
2538            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance,provider_item_id);"
2539        )
2540        await self.database.execute(
2541            "CREATE INDEX IF NOT EXISTS "
2542            f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_instance_idx "
2543            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance);"
2544        )
2545        await self.database.execute(
2546            "CREATE INDEX IF NOT EXISTS "
2547            f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_domain_idx "
2548            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_domain);"
2549        )
2550        await self.database.execute(
2551            "CREATE INDEX IF NOT EXISTS "
2552            f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_instance_library_idx "
2553            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance,in_library);"
2554        )
2555
2556        # indexes on track_artists table
2557        await self.database.execute(
2558            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}_track_id_idx "
2559            f"on {DB_TABLE_TRACK_ARTISTS}(track_id);"
2560        )
2561        await self.database.execute(
2562            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}_artist_id_idx "
2563            f"on {DB_TABLE_TRACK_ARTISTS}(artist_id);"
2564        )
2565        # indexes on album_artists table
2566        await self.database.execute(
2567            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}_album_id_idx "
2568            f"on {DB_TABLE_ALBUM_ARTISTS}(album_id);"
2569        )
2570        await self.database.execute(
2571            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}_artist_id_idx "
2572            f"on {DB_TABLE_ALBUM_ARTISTS}(artist_id);"
2573        )
2574        # index on loudness measurements table
2575        await self.database.execute(
2576            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_LOUDNESS_MEASUREMENTS}_idx "
2577            f"on {DB_TABLE_LOUDNESS_MEASUREMENTS}(media_type,item_id,provider);"
2578        )
2579        # index on smart fades analysis table
2580        await self.database.execute(
2581            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_SMART_FADES_ANALYSIS}_idx "
2582            f"on {DB_TABLE_SMART_FADES_ANALYSIS}(item_id,provider,fragment);"
2583        )
2584        # unique index on playlog table
2585        await self.database.execute(
2586            f"CREATE UNIQUE INDEX IF NOT EXISTS {DB_TABLE_PLAYLOG}_unique_idx "
2587            f"on {DB_TABLE_PLAYLOG}(item_id,provider,media_type,userid);"
2588        )
2589        await self.database.commit()
2590
2591    async def __create_database_triggers(self) -> None:
2592        """Create database triggers."""
2593        # triggers to auto update timestamps
2594        for db_table in (
2595            "artists",
2596            "albums",
2597            "tracks",
2598            "playlists",
2599            "radios",
2600            "audiobooks",
2601            "podcasts",
2602        ):
2603            await self.database.execute(
2604                f"""
2605                CREATE TRIGGER IF NOT EXISTS update_{db_table}_timestamp
2606                AFTER UPDATE ON {db_table}
2607                BEGIN
2608                    UPDATE {db_table} SET timestamp_modified=cast(strftime('%s','now') as int)
2609                    WHERE rowid = new.rowid;
2610                END;
2611                """
2612            )
2613        await self.database.commit()
2614
2615    async def correct_multi_instance_provider_mappings(self) -> None:
2616        """Correct provider mappings for multi-instance providers."""
2617        self.logger.debug("Correcting provider mappings for multi-instance providers...")
2618        multi_instance_providers: set[str] = set()
2619        for provider in self.providers:
2620            if len(self.get_provider_instances(provider.domain)) > 1:
2621                multi_instance_providers.add(provider.instance_id)
2622        if not multi_instance_providers:
2623            return  # no multi-instance providers found, nothing to do
2624
2625        for ctrl in (
2626            self.albums,
2627            self.artists,
2628            self.tracks,
2629            self.playlists,
2630            self.radio,
2631            self.audiobooks,
2632            self.podcasts,
2633        ):
2634            async for db_item in ctrl.iter_library_items(provider=list(multi_instance_providers)):
2635                if self.match_provider_instances(db_item):
2636                    await ctrl.update_item_in_library(db_item.item_id, db_item)
2637                # prevent overwhelming the event loop
2638                await asyncio.sleep(0.2)
2639        self.mass.config.set_raw_core_config_value(
2640            self.domain, LAST_PROVIDER_INSTANCE_SCAN, int(time.time())
2641        )
2642        self.logger.debug("Provider mappings correction done")
2643
2644    async def _get_user_for_provider(
2645        self, provider_mappings_or_instance_id: Iterable[ProviderMapping] | str
2646    ) -> User | None:
2647        """Try to get the MA User based on provider mappings and provider filter."""
2648        all_users = await self.mass.webserver.auth.list_users()
2649        for mapping_or_instance_id in provider_mappings_or_instance_id:
2650            for user in all_users:
2651                if not user.provider_filter:
2652                    continue
2653                if isinstance(mapping_or_instance_id, str):
2654                    if provider_mappings_or_instance_id in user.provider_filter:
2655                        return user
2656                elif mapping_or_instance_id.provider_instance in user.provider_filter:
2657                    return user
2658        return None
2659