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