music-assistant-server

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