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