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