music-assistant-server

1"""Manage MediaItems of type Genre."""
2
3from __future__ import annotations
4
5import asyncio
6import json
7import logging
8import time
9from dataclasses import dataclass
10from typing import TYPE_CHECKING, Any, cast
11
12from music_assistant_models.auth import Scope
13from music_assistant_models.background_task import BackgroundTask, TaskSchedule
14from music_assistant_models.enums import EventType, ImageType, MediaType, TaskStatus
15from music_assistant_models.errors import InvalidDataError
16from music_assistant_models.helpers import create_safe_string
17from music_assistant_models.media_items import (
18    Album,
19    Artist,
20    Genre,
21    GenreSummary,
22    MediaItemImage,
23    MediaItemMetadata,
24    RecommendationFolder,
25    Track,
26)
27from music_assistant_models.unique_list import UniqueList
28
29from music_assistant.constants import (
30    DB_TABLE_ALBUM_TRACKS,
31    DB_TABLE_ALBUMS,
32    DB_TABLE_ARTISTS,
33    DB_TABLE_AUDIOBOOKS,
34    DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION,
35    DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
36    DB_TABLE_GENRES,
37    DB_TABLE_PLAYLISTS,
38    DB_TABLE_PLAYLOG,
39    DB_TABLE_PODCASTS,
40    DB_TABLE_PROVIDER_MAPPINGS,
41    DB_TABLE_RADIOS,
42    DB_TABLE_TRACK_ARTISTS,
43    DB_TABLE_TRACKS,
44    DEFAULT_AUDIOBOOK_GENRE_MAPPING,
45    DEFAULT_GENRE_MAPPING,
46    DEFAULT_PODCAST_GENRE_MAPPING,
47    GENRE_ICONS_DIR_NAME,
48    RESOURCES_DIR,
49)
50from music_assistant.controllers.music.helpers import search_name_match_clause
51from music_assistant.controllers.tasks.context import update_current_task_progress_text
52from music_assistant.helpers.database import UNSET
53from music_assistant.helpers.datetime import local_clock_time_to_utc
54from music_assistant.helpers.json import json_loads, serialize_to_json
55
56from .base import MediaControllerBase
57
58if TYPE_CHECKING:
59    from collections.abc import Mapping
60
61    from music_assistant_models.event import MassEvent
62
63    from music_assistant import MusicAssistant
64
65
66MEDIA_TABLES: tuple[tuple[str, MediaType], ...] = (
67    (DB_TABLE_TRACKS, MediaType.TRACK),
68    (DB_TABLE_ALBUMS, MediaType.ALBUM),
69    (DB_TABLE_ARTISTS, MediaType.ARTIST),
70    (DB_TABLE_PLAYLISTS, MediaType.PLAYLIST),
71    (DB_TABLE_RADIOS, MediaType.RADIO),
72    (DB_TABLE_AUDIOBOOKS, MediaType.AUDIOBOOK),
73    (DB_TABLE_PODCASTS, MediaType.PODCAST),
74)
75
76# Genre taxonomy buckets: a genre content_type (None = music/general) and the media tables
77# whose items belong to that taxonomy. Genre resolution and creation are scoped per bucket so
78# a podcast "Comedy" never resolves onto (or merges with) the music "Comedy" genre.
79GENRE_BUCKETS: tuple[tuple[MediaType | None, tuple[tuple[str, MediaType], ...]], ...] = (
80    (
81        None,
82        (
83            (DB_TABLE_TRACKS, MediaType.TRACK),
84            (DB_TABLE_ALBUMS, MediaType.ALBUM),
85            (DB_TABLE_ARTISTS, MediaType.ARTIST),
86            (DB_TABLE_PLAYLISTS, MediaType.PLAYLIST),
87            (DB_TABLE_RADIOS, MediaType.RADIO),
88        ),
89    ),
90    (MediaType.AUDIOBOOK, ((DB_TABLE_AUDIOBOOKS, MediaType.AUDIOBOOK),)),
91    (MediaType.PODCAST, ((DB_TABLE_PODCASTS, MediaType.PODCAST),)),
92)
93GENRE_SCAN_TASK_ID = "genre_mapping_scan"
94
95# lifetime of the cached per-taxonomy genre lookup used by sync_media_item_genres;
96# kept short so user edits to genres/aliases are picked up quickly by a running sync
97SYNC_GENRE_LOOKUP_TTL = 5.0
98
99
100@dataclass(slots=True)
101class _SyncGenreLookup:
102    """In-memory snapshot of a genre taxonomy for fast name -> genre_ids resolution."""
103
104    built_at: float
105    primary_name_to_genre: dict[str, int]
106    alias_to_genre: dict[str, list[int]]
107    excluded_names: set[str]
108
109
110# Curated default genres per taxonomy: (content_type, mapping). Music keeps content_type None;
111# podcast/audiobook seed their own namespaced default genres (iTunes / Audible-style lists).
112DEFAULT_GENRE_TAXONOMIES: tuple[tuple[MediaType | None, list[dict[str, Any]]], ...] = (
113    (None, DEFAULT_GENRE_MAPPING),
114    (MediaType.PODCAST, DEFAULT_PODCAST_GENRE_MAPPING),
115    (MediaType.AUDIOBOOK, DEFAULT_AUDIOBOOK_GENRE_MAPPING),
116)
117
118
119def genre_content_type_for(media_type: MediaType) -> MediaType | None:
120    """Return the genre taxonomy (content_type) a given media type belongs to (None = music)."""
121    if media_type == MediaType.AUDIOBOOK:
122        return MediaType.AUDIOBOOK
123    if media_type in (MediaType.PODCAST, MediaType.PODCAST_EPISODE):
124        return MediaType.PODCAST
125    return None
126
127
128class GenreController(MediaControllerBase[Genre]):
129    """Controller for Genre entities."""
130
131    db_table = DB_TABLE_GENRES
132    media_type = MediaType.GENRE
133    item_cls = Genre
134    summary_item_cls = GenreSummary
135
136    def __init__(self, mass: MusicAssistant) -> None:
137        """Initialize class."""
138        super().__init__(mass)
139        self._last_scan_time: float = 0
140        self._last_scan_mapped: int = 0
141        self._sync_lookup_cache: dict[str | None, _SyncGenreLookup] = {}
142
143        # register extra api handlers
144        self.mass.register_api_command(
145            "music/genres/add_alias", self.add_alias, required_scope=Scope.LIBRARY_MANAGE
146        )
147        self.mass.register_api_command(
148            "music/genres/remove_alias", self.remove_alias, required_scope=Scope.LIBRARY_MANAGE
149        )
150        self.mass.register_api_command(
151            "music/genres/add_media_mapping",
152            self.add_media_mapping,
153            required_scope=Scope.LIBRARY_MANAGE,
154        )
155        self.mass.register_api_command(
156            "music/genres/remove_media_mapping",
157            self.remove_media_mapping,
158            required_scope=Scope.LIBRARY_MANAGE,
159        )
160        self.mass.register_api_command(
161            "music/genres/promote_alias",
162            self.promote_alias_to_genre,
163            required_scope=Scope.LIBRARY_MANAGE,
164        )
165        self.mass.register_api_command(
166            "music/genres/restore_defaults",
167            self.restore_default_genres,
168            required_scope=Scope.LIBRARY_MANAGE,
169        )
170        self.mass.register_api_command(
171            "music/genres/add",
172            self.add_item_to_library,
173            required_scope=Scope.LIBRARY_MANAGE,
174        )
175        self.mass.register_api_command(
176            "music/genres/overview",
177            self.get_overview,
178            required_scope=Scope.LIBRARY_READ,
179        )
180        self.mass.register_api_command(
181            "music/genres/tracks",
182            self.tracks,
183            required_scope=Scope.LIBRARY_READ,
184        )
185        self.mass.register_api_command(
186            "music/genres/albums",
187            self.albums,
188            required_scope=Scope.LIBRARY_READ,
189        )
190        self.mass.register_api_command(
191            "music/genres/scan_mappings",
192            self.scan_mappings,
193            required_scope=Scope.LIBRARY_MANAGE,
194        )
195        self.mass.register_api_command(
196            "music/genres/scanner_status",
197            self.get_scanner_status,
198            required_scope=Scope.LIBRARY_READ,
199        )
200        self.mass.register_api_command(
201            "music/genres/genres_for_media_item",
202            self.get_genres_for_media_item,
203            required_scope=Scope.LIBRARY_READ,
204        )
205        self.mass.register_api_command(
206            "music/genres/genre_exclusions_for_media_item",
207            self.get_genre_exclusions_for_media_item,
208            required_scope=Scope.LIBRARY_READ,
209        )
210        self.mass.register_api_command(
211            "music/genres/exclude_genre_from_media_item",
212            self.exclude_genre_from_media_item,
213            required_scope=Scope.LIBRARY_MANAGE,
214        )
215        self.mass.register_api_command(
216            "music/genres/remove_genre_exclusion",
217            self.remove_genre_exclusion,
218            required_scope=Scope.LIBRARY_MANAGE,
219        )
220        self.mass.register_api_command(
221            "music/genres/merge",
222            self.merge_genres,
223            required_scope=Scope.LIBRARY_MANAGE,
224        )
225        self.mass.register_api_command(
226            "music/genres/media_counts",
227            self.get_genre_media_counts,
228            required_scope=Scope.LIBRARY_READ,
229        )
230        self.mass.register_api_command(
231            "music/genres/global_exclusions",
232            self.get_global_genre_exclusions,
233            required_scope=Scope.LIBRARY_READ,
234        )
235        self.mass.register_api_command(
236            "music/genres/remove_global_exclusion",
237            self.remove_global_genre_exclusion,
238            required_scope=Scope.LIBRARY_MANAGE,
239        )
240
241        # Run genre mapping scanner after library sync completes
242        self.mass.subscribe(self._on_music_sync_completed, EventType.MUSIC_SYNC_COMPLETED)
243
244    @property
245    def base_query(self) -> tuple[str, dict[str, Any]]:
246        """Return the base SELECT query for genres and its bound query params."""
247        # Use a derived table to filter out globally excluded genres so all queries
248        # built by the base class (which appends its own WHERE) stay valid SQL.
249        query = f"""
250        SELECT
251            {DB_TABLE_GENRES}.*,
252            {self._external_ids_query()} AS external_ids,
253            (SELECT JSON_GROUP_ARRAY(
254                json_object(
255                    'item_id', provider_mappings.provider_item_id,
256                    'provider_domain', provider_mappings.provider_domain,
257                    'provider_instance', provider_mappings.provider_instance,
258                    'available', provider_mappings.available,
259                    'audio_format', json(provider_mappings.audio_format),
260                    'url', provider_mappings.url,
261                    'details', provider_mappings.details,
262                    'in_library', provider_mappings.in_library,
263                    'is_unique', provider_mappings.is_unique
264                )) FROM provider_mappings
265                WHERE provider_mappings.item_id = {DB_TABLE_GENRES}.item_id
266                AND provider_mappings.media_type = '{MediaType.GENRE.value}'
267            ) AS provider_mappings
268        FROM (SELECT * FROM {DB_TABLE_GENRES} WHERE is_excluded = 0) AS {DB_TABLE_GENRES}"""
269        return query, {}
270
271    @property
272    def summary_query(self) -> tuple[str, dict[str, Any]]:
273        """Return the slim SELECT query used for genre summary listings."""
274        # Same derived table as the base query so excluded genres stay hidden.
275        query = f"""
276        SELECT
277            {self._summary_base_columns()},
278            {DB_TABLE_GENRES}.translation_key,
279            {DB_TABLE_GENRES}.content_type,
280            {self._provider_mappings_query()} AS provider_mappings
281        FROM (SELECT * FROM {DB_TABLE_GENRES} WHERE is_excluded = 0) AS {DB_TABLE_GENRES}"""
282        return query, {}
283
284    async def library_count(self, favorite_only: bool = False) -> int:
285        """
286        Return the total number of genres in the library.
287
288        Never restricted by the current user's provider filter.
289
290        :param favorite_only: Only count genres marked as favorite.
291        """
292        # Genres are library-only items without provider_mappings, so - just like
293        # library_items below - the user's provider filter does not apply here.
294        if favorite_only:
295            sql_query = f"SELECT item_id FROM {self.db_table} WHERE favorite = 1"
296            return await self.mass.music.database.get_count_from_query(sql_query)
297        return await self.mass.music.database.get_count(self.db_table)
298
299    async def library_items(  # noqa: PLR0913
300        self,
301        favorite: bool | None = None,
302        search: str | None = None,
303        limit: int = 500,
304        offset: int = 0,
305        order_by: str = "sort_name",
306        provider: str | list[str] | None = None,
307        genre: int | list[int] | None = None,
308        played_only: bool = False,
309        hide_empty: bool | None = None,
310        media_type: MediaType | None = None,
311        content_type: str | None = None,
312        *,
313        summary: bool = True,
314        **kwargs: Any,
315    ) -> list[Genre]:
316        """
317        Get genres in the library.
318
319        :param genre: NOT SUPPORTED - Filtering genres by genres doesn't make sense.
320        :param hide_empty: Only applies when media_type is not set.
321            True: only return genres that have at least one media mapping.
322            False: return all genres including unmapped ones.
323            None (default): only return default genres (those with a translation_key).
324        :param media_type: When set, return all genres (including non-defaults) that have
325            at least one mapping for this media type. Takes precedence over hide_empty.
326        :param content_type: When set, restrict to genres of one taxonomy: "music" (the
327            general/music taxonomy, stored as NULL), "podcast" or "audiobook". Composes with
328            hide_empty, so e.g. content_type="podcast" + hide_empty=None returns only the
329            default podcast genres.
330        :param summary: When True (default), return slim summary items containing only the
331            fields needed for a list view. Set to False to get fully hydrated items.
332        """
333        if genre is not None:
334            msg = "genre parameter is not supported for Genre.library_items()"
335            raise ValueError(msg)
336        # Genres are library-only items without provider_mappings, so ignore
337        # the provider filter (the frontend always sends provider="library").
338        # Pass raw lowered search for alias matching (search_raw),
339        # since the normalized :search param strips spaces/special chars.
340        extra_params: dict[str, Any] = {}
341        extra_parts: list[str] = []
342        if search:
343            extra_params["search_raw"] = f"%{search.strip().lower()}%"
344        if content_type == "music":
345            # the music/general taxonomy is stored as a NULL content_type
346            extra_parts.append(f"{self.db_table}.content_type IS NULL")
347        elif content_type is not None:
348            # restrict to a single taxonomy; composes (AND) with the media_type/hide_empty clause
349            extra_parts.append(f"{self.db_table}.content_type IS :filter_content_type")
350            extra_params["filter_content_type"] = content_type
351        if media_type is not None:
352            # media_type implies non-empty: return all genres (including non-default) that
353            # have at least one mapping for the requested type.
354            gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
355            extra_parts.append(
356                f"EXISTS(SELECT 1 FROM {gm} gm_mt "
357                f"WHERE gm_mt.genre_id = {self.db_table}.item_id "
358                "AND gm_mt.media_type = :filter_media_type)"
359            )
360            extra_params["filter_media_type"] = media_type.value
361        elif hide_empty is None:
362            extra_parts.append(f"{self.db_table}.translation_key IS NOT NULL")
363        elif hide_empty:
364            gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
365            extra_parts.append(
366                f"EXISTS(SELECT 1 FROM {gm} gm WHERE gm.genre_id = {self.db_table}.item_id)"
367            )
368        items = await self.get_library_items_by_query(
369            favorite=favorite,
370            search=search,
371            limit=limit,
372            offset=offset,
373            order_by=order_by,
374            extra_query_params=extra_params,
375            extra_query_parts=extra_parts,
376            played_only=played_only,
377            summary=summary,
378        )
379        if kwargs.get("_localized_fallback", True) and search and not items:
380            # retry with the canonical name behind a localized query, so genres are findable
381            # by the name shown in the user's language (see _localized_search_fallback)
382            return await self._localized_search_fallback(
383                search,
384                limit=limit,
385                offset=offset,
386                favorite=favorite,
387                order_by=order_by,
388                played_only=played_only,
389                hide_empty=hide_empty,
390                media_type=media_type,
391                content_type=content_type,
392                summary=summary,
393            )
394        return items
395
396    async def tracks(
397        self,
398        item_id: str | int,
399        limit: int = 500,
400        offset: int = 0,
401        order_by: str | None = None,
402    ) -> list[Track]:
403        """
404        Return the tracks mapped to a genre.
405
406        :param item_id: The genre's library item ID.
407        :param limit: Maximum number of tracks to return (0 = unlimited).
408        :param offset: Offset for pagination.
409        :param order_by: Sort order (e.g. "random").
410        """
411        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
412        query = (
413            f"EXISTS(SELECT 1 FROM {gm} gm "
414            "WHERE gm.media_id = tracks.item_id "
415            "AND gm.media_type = 'track' AND gm.genre_id = :genre_id)"
416        )
417        return await self.mass.music.tracks.get_library_items_by_query(
418            extra_query_parts=[query],
419            extra_query_params={"genre_id": int(item_id)},
420            limit=limit,
421            offset=offset,
422            order_by=order_by,
423        )
424
425    async def albums(
426        self,
427        item_id: str | int,
428        limit: int = 500,
429        offset: int = 0,
430        order_by: str | None = None,
431    ) -> list[Album]:
432        """
433        Return the albums mapped to a genre.
434
435        :param item_id: The genre's library item ID.
436        :param limit: Maximum number of albums to return (0 = unlimited).
437        :param offset: Offset for pagination.
438        :param order_by: Sort order (e.g. "random").
439        """
440        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
441        query = (
442            f"EXISTS(SELECT 1 FROM {gm} gm "
443            "WHERE gm.media_id = albums.item_id "
444            "AND gm.media_type = 'album' AND gm.genre_id = :genre_id)"
445        )
446        return await self.mass.music.albums.get_library_items_by_query(
447            extra_query_parts=[query],
448            extra_query_params={"genre_id": int(item_id)},
449            limit=limit,
450            offset=offset,
451            order_by=order_by,
452        )
453
454    async def mapped_media(
455        self,
456        item: Genre,
457        limit: int = 0,
458        offset: int = 0,
459        track_limit: int | None = None,
460        album_limit: int | None = None,
461        artist_limit: int | None = None,
462        order_by: str | None = None,
463    ) -> tuple[list[Track], list[Album], list[Artist]]:
464        """
465        Return tracks, albums, and artists mapped to a genre.
466
467        :param item: The genre to fetch mapped media for.
468        :param limit: Default limit applied to all media types (0 = unlimited).
469        :param offset: Offset for pagination.
470        :param track_limit: Override limit for tracks (defaults to limit).
471        :param album_limit: Override limit for albums (defaults to limit).
472        :param artist_limit: Override limit for artists (defaults to limit).
473        :param order_by: Sort order for all queries (e.g. "random").
474        """
475        db_id = int(item.item_id)
476        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
477        t_limit = track_limit if track_limit is not None else limit
478        a_limit = album_limit if album_limit is not None else limit
479        ar_limit = artist_limit if artist_limit is not None else limit
480        artist_query = (
481            f"EXISTS(SELECT 1 FROM {gm} gm "
482            "WHERE gm.media_id = artists.item_id "
483            "AND gm.media_type = 'artist' AND gm.genre_id = :genre_id)"
484        )
485
486        tracks, albums, artists = await asyncio.gather(
487            self.tracks(db_id, limit=t_limit, offset=offset, order_by=order_by),
488            self.albums(db_id, limit=a_limit, offset=offset, order_by=order_by),
489            self.mass.music.artists.get_library_items_by_query(
490                extra_query_parts=[artist_query],
491                extra_query_params={"genre_id": db_id},
492                limit=ar_limit,
493                offset=offset,
494                order_by=order_by,
495            ),
496        )
497        return tracks, albums, artists
498
499    async def get_genres_for_media_item(
500        self, media_type: MediaType, media_id: str | int
501    ) -> list[Genre]:
502        """
503        Return all genres mapped to a given media item.
504
505        :param media_type: The type of media item.
506        :param media_id: The database ID of the media item.
507        """
508        try:
509            media_id_int = int(media_id)
510        except ValueError, TypeError:
511            return []
512        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
513        query = (
514            f"EXISTS(SELECT 1 FROM {gm} gm "
515            f"WHERE gm.genre_id = {self.db_table}.item_id "
516            "AND gm.media_type = :media_type AND gm.media_id = :media_id)"
517        )
518        return await self.get_library_items_by_query(
519            extra_query_parts=[query],
520            extra_query_params={
521                "media_type": media_type.value,
522                "media_id": media_id_int,
523            },
524        )
525
526    async def get_genre_exclusions_for_media_item(
527        self, media_type: MediaType, media_id: str | int
528    ) -> list[Genre]:
529        """
530        Return all genres excluded from a given media item.
531
532        :param media_type: The type of media item.
533        :param media_id: The database ID of the media item.
534        """
535        try:
536            media_id_int = int(media_id)
537        except ValueError, TypeError:
538            return []
539        excl = DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION
540        query = (
541            f"EXISTS(SELECT 1 FROM {excl} e "
542            f"WHERE e.genre_id = {self.db_table}.item_id "
543            "AND e.media_type = :media_type AND e.media_id = :media_id)"
544        )
545        return await self.get_library_items_by_query(
546            extra_query_parts=[query],
547            extra_query_params={
548                "media_type": media_type.value,
549                "media_id": media_id_int,
550            },
551        )
552
553    async def has_derived_genre_mappings(self, media_type: MediaType, media_id: str | int) -> bool:
554        """
555        Return True if this media item has propagation-derived genre mappings.
556
557        :param media_type: The type of media item.
558        :param media_id: The database ID of the media item.
559        """
560        try:
561            media_id_int = int(media_id)
562        except ValueError, TypeError:
563            return False
564        row = await self.mass.music.database.get_row(
565            DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
566            {"media_type": media_type.value, "media_id": media_id_int, "is_derived": 1},
567        )
568        return row is not None
569
570    async def get_overview(
571        self,
572        item_id: str,
573        provider_instance_id_or_domain: str | None = None,
574        limit: int = 25,
575    ) -> list[RecommendationFolder]:
576        """Return overview rows for a genre (all media types)."""
577        provider = provider_instance_id_or_domain or "library"
578        item = await self.get(item_id, provider)
579        db_id = int(item.item_id)
580        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
581        media_rows: list[tuple[MediaType, str, str]] = [
582            (MediaType.ARTIST, "Artists", "artists"),
583            (MediaType.ALBUM, "Albums", "albums"),
584            (MediaType.TRACK, "Tracks", "tracks"),
585            (MediaType.PLAYLIST, "Playlists", "playlists"),
586            (MediaType.RADIO, "Radio", "radios"),
587            (MediaType.PODCAST, "Podcasts", "podcasts"),
588            (MediaType.AUDIOBOOK, "Audiobooks", "audiobooks"),
589        ]
590
591        async def _fetch_media_type(
592            media_type: MediaType, title: str, translation_key: str
593        ) -> RecommendationFolder | None:
594            ctrl = self.mass.music.get_controller(media_type)
595            query = (
596                f"EXISTS(SELECT 1 FROM {gm} gm "
597                f"WHERE gm.media_id = {ctrl.db_table}.item_id "
598                "AND gm.media_type = :media_type "
599                "AND gm.genre_id = :genre_id)"
600            )
601            items = await ctrl.get_library_items_by_query(
602                extra_query_parts=[query],
603                extra_query_params={
604                    "genre_id": db_id,
605                    "media_type": media_type.value,
606                },
607                limit=limit,
608            )
609            if not items:
610                return None
611            return RecommendationFolder(
612                item_id=f"genre_{media_type.value}",
613                name=title,
614                translation_key=translation_key,
615                provider="library",
616                items=UniqueList(items[:limit]),
617            )
618
619        results = await asyncio.gather(
620            *[_fetch_media_type(mt, title, key) for mt, title, key in media_rows]
621        )
622        return [r for r in results if r is not None]
623
624    async def get_genre_media_counts(self, genre_ids: list[str]) -> dict[str, dict[str, int]]:
625        """
626        Return media item counts per media type for each requested genre.
627
628        :param genre_ids: List of genre database IDs to query.
629        :return: Mapping of genre_id -> {media_type -> count}.
630        """
631        if not genre_ids:
632            return {}
633        try:
634            int_ids = [int(gid) for gid in genre_ids]
635        except (TypeError, ValueError) as err:
636            raise InvalidDataError(f"Invalid genre_id value: {err}") from err
637        norm_ids = [str(i) for i in int_ids]
638        placeholders = ",".join(norm_ids)
639        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
640        rows = await self.mass.music.database.get_rows_from_query(
641            f"SELECT {gm}.genre_id, {gm}.media_type, COUNT(*) AS cnt "
642            f"FROM {gm} "
643            f"WHERE {gm}.genre_id IN ({placeholders}) "
644            f"AND EXISTS ("
645            f"  SELECT 1 FROM provider_mappings pm "
646            f"  WHERE pm.item_id = {gm}.media_id "
647            f"  AND pm.media_type = {gm}.media_type "
648            f"  AND pm.in_library = 1"
649            f") "
650            f"GROUP BY {gm}.genre_id, {gm}.media_type",
651            limit=0,
652        )
653        empty: dict[str, int] = {mt.value: 0 for _, mt in MEDIA_TABLES}
654        result: dict[str, dict[str, int]] = {nid: dict(empty) for nid in norm_ids}
655        for row in rows:
656            gid = str(row["genre_id"])
657            if gid in result:
658                result[gid][row["media_type"]] = row["cnt"]
659        return result
660
661    async def match_providers(self, db_item: Genre) -> None:
662        """No provider matching for genres at this time."""
663        return
664
665    async def restore_default_genres(
666        self, full_restore: bool = False, content_type: str | None = None
667    ) -> list[Genre]:
668        """
669        Restore default genres for one or every taxonomy (music, podcast, audiobook).
670
671        :param full_restore: If True, delete all existing genres and recreate from defaults
672                            (always covers every taxonomy). If False (default), only add
673                            missing genres and ensure aliases exist.
674        :param content_type: Restrict a non-destructive restore to a single taxonomy:
675                            "music", "podcast" or "audiobook". None or "all" restores every
676                            taxonomy. Ignored when full_restore is True.
677        """
678        if full_restore:
679            self.logger.warning("Performing FULL restore - deleting all existing genres")
680            await self.mass.music.database.delete(DB_TABLE_GENRE_MEDIA_ITEM_MAPPING)
681            await self.mass.music.database.delete(DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION)
682            await self.mass.music.database.delete(
683                DB_TABLE_PLAYLOG, {"media_type": MediaType.GENRE.value}
684            )
685            await self.mass.music.database.delete(DB_TABLE_GENRES)
686
687        taxonomies = DEFAULT_GENRE_TAXONOMIES
688        if not full_restore and content_type is not None and content_type != "all":
689            # the music taxonomy is stored as a NULL content_type
690            wanted = None if content_type == "music" else MediaType(content_type)
691            taxonomies = tuple(t for t in DEFAULT_GENRE_TAXONOMIES if t[0] == wanted)
692            if not taxonomies:
693                msg = f"Unknown genre taxonomy: {content_type}"
694                raise ValueError(msg)
695
696        created_ids: list[int] = []
697        for taxonomy_content_type, mapping in taxonomies:
698            created_ids.extend(
699                await self._seed_default_genres(taxonomy_content_type, mapping, full_restore)
700            )
701
702        if created_ids:
703            await self.mass.music.database.commit()
704
705        if full_restore:
706            await self._bulk_scan_media_genres()
707
708        if not created_ids:
709            return []
710        return [await self.get_library_item(item_id) for item_id in created_ids]
711
712    async def remove_item_from_library(
713        self, item_id: str | int, recursive: bool = True, exclude_globally: bool = True
714    ) -> None:
715        """
716        Delete genre record from the database.
717
718        :param item_id: Database ID of the genre to remove.
719        :param recursive: Unused for genres, kept for base-class compatibility.
720        :param exclude_globally: If True (default), soft-delete the genre so the scanner
721            will not recreate it. If False, hard-delete the row (used internally by
722            merge_genres where the source should not appear in the exclusion list).
723        """
724        db_id = int(item_id)
725        await self.mass.music.database.delete(
726            DB_TABLE_GENRE_MEDIA_ITEM_MAPPING, {"genre_id": db_id}
727        )
728        await self.mass.music.database.delete(
729            DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION, {"genre_id": db_id}
730        )
731        if exclude_globally:
732            # Fetch the item while it is still visible (base_query hides is_excluded=1 rows).
733            library_item = await self.get_library_item(db_id)
734            await self.mass.music.database.update(
735                DB_TABLE_GENRES, {"item_id": db_id}, {"is_excluded": 1}
736            )
737            self.mass.signal_event(EventType.MEDIA_ITEM_DELETED, library_item.uri, library_item)
738        else:
739            await super().remove_item_from_library(item_id, recursive)
740
741    async def add_alias(self, genre_id: str | int, alias: str) -> Genre:
742        """
743        Add an alias string to a genre.
744
745        :param genre_id: Database ID of the genre.
746        :param alias: Alias string to add.
747        """
748        db_id = int(genre_id)
749        genre = await self.get_library_item(db_id)
750        aliases = list(genre.genre_aliases) if genre.genre_aliases else []
751        aliases = self._dedup_aliases(aliases, [alias])
752        await self.mass.music.database.update(
753            self.db_table,
754            {"item_id": db_id},
755            {"genre_aliases": serialize_to_json(aliases)},
756        )
757        updated = await self.get_library_item(db_id)
758        self.mass.signal_event(EventType.MEDIA_ITEM_UPDATED, updated.uri, updated)
759        return updated
760
761    async def remove_alias(self, genre_id: str | int, alias: str) -> Genre:
762        """
763        Remove an alias string from a genre.
764
765        :param genre_id: Database ID of the genre.
766        :param alias: Alias string to remove.
767        :raises ValueError: If trying to remove the genre's own name.
768        """
769        db_id = int(genre_id)
770        genre = await self.get_library_item(db_id)
771        if create_safe_string(alias, True, True) == create_safe_string(genre.name, True, True):
772            msg = (
773                f"Cannot remove self-alias '{alias}' from genre '{genre.name}'. "
774                f"Delete the genre instead."
775            )
776            raise ValueError(msg)
777        aliases = list(genre.genre_aliases) if genre.genre_aliases else []
778        alias_norm = create_safe_string(alias, True, True)
779        aliases = [a for a in aliases if create_safe_string(a, True, True) != alias_norm]
780        await self.mass.music.database.update(
781            self.db_table,
782            {"item_id": db_id},
783            {"genre_aliases": serialize_to_json(aliases)},
784        )
785        # Remove media mappings that were created via this alias (case-insensitive)
786        await self.mass.music.database.execute(
787            f"DELETE FROM {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING} "
788            "WHERE genre_id = :genre_id AND LOWER(alias) = LOWER(:alias)",
789            {"genre_id": db_id, "alias": alias},
790        )
791        # Derived album/artist rows can be left orphaned by the deleted track rows.
792        await self._propagate_genre_mappings_to_parents()
793        updated = await self.get_library_item(db_id)
794        self.mass.signal_event(EventType.MEDIA_ITEM_UPDATED, updated.uri, updated)
795        return updated
796
797    async def add_media_mapping(
798        self,
799        genre_id: str | int,
800        media_type: MediaType,
801        media_id: str | int,
802        alias: str | None = None,
803    ) -> None:
804        """
805        Map a media item to a genre.
806
807        :param genre_id: Database ID of the genre.
808        :param media_type: Type of media item (track, album, artist).
809        :param media_id: Database ID of the media item.
810        :param alias: The alias string that caused this mapping. If not provided,
811            the genre's primary name is used.
812        """
813        if alias is None:
814            genre = await self.get_library_item(int(genre_id))
815            alias = genre.name
816        await self.mass.music.database.insert(
817            DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
818            {
819                "genre_id": int(genre_id),
820                "media_id": int(media_id),
821                "media_type": media_type.value,
822                "alias": alias,
823                "is_manual": 1,
824            },
825            allow_replace=True,
826        )
827
828    async def remove_media_mapping(
829        self, genre_id: str | int, media_type: MediaType, media_id: str | int
830    ) -> None:
831        """
832        Remove a media item mapping from a genre.
833
834        If the mapping was derived (propagated from child tracks), an exclusion is
835        automatically inserted so the next propagation scan does not re-derive it.
836
837        :param genre_id: Database ID of the genre.
838        :param media_type: Type of media item (track, album, artist).
839        :param media_id: Database ID of the media item.
840        """
841        row = await self.mass.music.database.get_row(
842            DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
843            {"genre_id": int(genre_id), "media_id": int(media_id), "media_type": media_type.value},
844        )
845        if row and row["is_derived"]:
846            await self.exclude_genre_from_media_item(genre_id, media_type, media_id)
847            return
848        await self.mass.music.database.delete(
849            DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
850            {
851                "genre_id": int(genre_id),
852                "media_id": int(media_id),
853                "media_type": media_type.value,
854            },
855        )
856
857    async def exclude_genre_from_media_item(
858        self,
859        genre_id: str | int,
860        media_type: MediaType,
861        media_id: str | int,
862    ) -> None:
863        """
864        Permanently exclude a genre from being mapped to a media item.
865
866        Records the exclusion so the scanner will never re-add this mapping.
867        Any existing mapping for this genre/media pair is removed immediately.
868
869        :param genre_id: Database ID of the genre.
870        :param media_type: Type of media item (track, album, artist, etc.).
871        :param media_id: Database ID of the media item.
872        """
873        params = {
874            "genre_id": int(genre_id),
875            "media_id": int(media_id),
876            "media_type": media_type.value,
877        }
878        db = self.mass.music.database
879        # Run both statements without committing between them so the exclusion insert
880        # and the mapping delete are committed atomically.
881        await db.execute(
882            f"INSERT OR REPLACE INTO {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}"
883            "(genre_id, media_id, media_type) VALUES (:genre_id, :media_id, :media_type)",
884            params,
885        )
886        await db.execute(
887            f"DELETE FROM {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING} "
888            "WHERE genre_id = :genre_id AND media_id = :media_id AND media_type = :media_type",
889            params,
890        )
891        await db.commit()
892
893    async def remove_genre_exclusion(
894        self,
895        genre_id: str | int,
896        media_type: MediaType,
897        media_id: str | int,
898    ) -> None:
899        """
900        Remove a genre exclusion, allowing the scanner to re-map it on the next run.
901
902        :param genre_id: Database ID of the genre.
903        :param media_type: Type of media item (track, album, artist, etc.).
904        :param media_id: Database ID of the media item.
905        """
906        await self.mass.music.database.delete(
907            DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION,
908            {
909                "genre_id": int(genre_id),
910                "media_id": int(media_id),
911                "media_type": media_type.value,
912            },
913        )
914
915    async def get_global_genre_exclusions(self) -> list[dict[str, object]]:
916        """Return all globally excluded genres."""
917        rows = await self.mass.music.database.get_rows_from_query(
918            f"SELECT item_id, name, sort_name, search_name, translation_key, metadata "
919            f"FROM {DB_TABLE_GENRES} WHERE is_excluded = 1 ORDER BY sort_name",
920            limit=0,
921        )
922        result = []
923        for row in rows:
924            entry = dict(row)
925            if raw_metadata := entry.get("metadata"):
926                entry["metadata"] = json_loads(raw_metadata)
927            result.append(entry)
928        return result
929
930    async def remove_global_genre_exclusion(self, genre_id: int) -> Genre:
931        """
932        Lift a global genre exclusion, making the genre visible and scannable again.
933
934        :param genre_id: Database ID of the excluded genre (item_id in genres table).
935        :return: The restored Genre.
936        """
937        row = await self.mass.music.database.get_row(
938            DB_TABLE_GENRES, {"item_id": genre_id, "is_excluded": 1}
939        )
940        if not row:
941            msg = f"No globally excluded genre found with id {genre_id}"
942            raise KeyError(msg)
943        await self.mass.music.database.update(
944            DB_TABLE_GENRES, {"item_id": genre_id}, {"is_excluded": 0}
945        )
946        library_item = await self.get_library_item(genre_id)
947        self.mass.signal_event(EventType.MEDIA_ITEM_ADDED, library_item.uri, library_item)
948        return library_item
949
950    async def promote_alias_to_genre(self, genre_id: str | int, alias: str) -> Genre:
951        """
952        Promote an alias to become a standalone genre.
953
954        Every genre that claimed the alias loses it, and all media mapped via
955        the alias is moved to the new genre.
956
957        :param genre_id: Database ID of the source genre.
958        :param alias: The alias string to promote.
959        :return: The newly created Genre.
960        """
961        db_genre_id = int(genre_id)
962        source_genre = await self.get_library_item(db_genre_id)
963        alias_norm = create_safe_string(alias, True, True)
964
965        if alias_norm == create_safe_string(source_genre.name, True, True):
966            msg = (
967                f"Cannot promote self-alias '{alias}'. "
968                f"This alias is the primary name for genre '{source_genre.name}'."
969            )
970            raise ValueError(msg)
971
972        owning_ids = await self._find_genre_ids_for_alias(alias_norm)
973        if db_genre_id not in owning_ids:
974            owning_ids.append(db_genre_id)
975
976        new_genre = Genre(
977            item_id="0",
978            provider="library",
979            name=alias,
980            sort_name=alias,
981            translation_key=None,
982            provider_mappings=set(),
983            favorite=False,
984            # the promoted genre stays in the same taxonomy as the genre it came from
985            content_type=source_genre.content_type,
986        )
987        created_genre = await self.add_item_to_library(new_genre)
988        new_genre_id = int(created_genre.item_id)
989
990        # UPDATE OR REPLACE drops any pre-existing mapping on the new genre for
991        # the same (media_id, media_type) so the moved row wins.
992        placeholders = ", ".join(str(g) for g in owning_ids)
993        await self.mass.music.database.execute(
994            f"UPDATE OR REPLACE {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING} "
995            f"SET genre_id = :new_id "
996            f"WHERE genre_id IN ({placeholders}) AND LOWER(alias) = LOWER(:alias)",
997            {"new_id": new_genre_id, "alias": alias},
998        )
999
1000        for owning_id in owning_ids:
1001            owning = await self.get_library_item(owning_id)
1002            # Defensive: a genre whose primary name equals the alias would hit
1003            # the self-alias guard in remove_alias; skip rather than raise.
1004            if create_safe_string(owning.name, True, True) == alias_norm:
1005                continue
1006            owning_aliases = list(owning.genre_aliases) if owning.genre_aliases else []
1007            filtered = [
1008                a for a in owning_aliases if create_safe_string(a, True, True) != alias_norm
1009            ]
1010            if len(filtered) != len(owning_aliases):
1011                await self.mass.music.database.update(
1012                    self.db_table,
1013                    {"item_id": owning_id},
1014                    {"genre_aliases": serialize_to_json(filtered)},
1015                )
1016
1017        # Derived album/artist rows still point at the old source genres; rebuild
1018        # them from the moved track mappings.
1019        await self._propagate_genre_mappings_to_parents()
1020
1021        return await self.get_library_item(new_genre_id)
1022
1023    async def merge_genres(self, genre_ids: list[str | int], target_genre_id: str | int) -> Genre:
1024        """
1025        Merge one or more genres into a target genre.
1026
1027        Transfers all aliases and media mappings from the source genres to the
1028        target, then deletes the source genres. Aliases and mappings are
1029        deduplicated so no duplicates are created on the target.
1030
1031        :param genre_ids: List of genre IDs to merge into the target.
1032        :param target_genre_id: Database ID of the genre to merge into.
1033        """
1034        target_id = int(target_genre_id)
1035        source_ids = [int(gid) for gid in genre_ids]
1036
1037        if target_id in source_ids:
1038            msg = "Target genre cannot be in the list of genres to merge"
1039            raise ValueError(msg)
1040        if not source_ids:
1041            msg = "No genre IDs provided to merge"
1042            raise ValueError(msg)
1043
1044        target_genre = await self.get_library_item(target_id)
1045        db = self.mass.music.database
1046        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
1047
1048        # Collect and merge aliases from all source genres into the target. Genres can only be
1049        # merged within the same taxonomy — merging e.g. a podcast genre into a music genre
1050        # would attach spoken-word items to a music genre (and be undone by the next scan).
1051        all_new_aliases: list[str] = []
1052        for source_id in source_ids:
1053            source_genre = await self.get_library_item(source_id)
1054            if source_genre.content_type != target_genre.content_type:
1055                msg = (
1056                    f"Cannot merge genre '{source_genre.name}' into '{target_genre.name}': "
1057                    "genres must belong to the same taxonomy (music / podcast / audiobook)."
1058                )
1059                raise ValueError(msg)
1060            if source_genre.genre_aliases:
1061                all_new_aliases.extend(source_genre.genre_aliases)
1062
1063        existing_aliases = list(target_genre.genre_aliases) if target_genre.genre_aliases else []
1064        merged_aliases = self._dedup_aliases(existing_aliases, all_new_aliases)
1065        await db.update(
1066            self.db_table,
1067            {"item_id": target_id},
1068            {"genre_aliases": serialize_to_json(merged_aliases)},
1069        )
1070
1071        # Transfer media mappings from source genres to target (deduplicated)
1072        placeholders = ", ".join(str(sid) for sid in source_ids)
1073        await db.execute(
1074            f"INSERT OR IGNORE INTO {gm} (genre_id, media_id, media_type, alias) "
1075            f"SELECT :target_id, media_id, media_type, alias FROM {gm} "
1076            f"WHERE genre_id IN ({placeholders})",
1077            {"target_id": target_id},
1078        )
1079
1080        # Hard-delete source genres: merging is not a user exclusion so sources must
1081        # not appear in the global exclusion list.
1082        for source_id in source_ids:
1083            await self.remove_item_from_library(source_id, exclude_globally=False)
1084
1085        # Rebuild derived album/artist rows against the merged track mappings.
1086        await self._propagate_genre_mappings_to_parents()
1087
1088        updated = await self.get_library_item(target_id)
1089        self.mass.signal_event(EventType.MEDIA_ITEM_UPDATED, updated.uri, updated)
1090        return updated
1091
1092    async def sync_media_item_genres(
1093        self, media_type: MediaType, media_id: str | int, genre_names: set[str]
1094    ) -> None:
1095        """
1096        Sync genre mappings for a media item.
1097
1098        Ensures genre records exist and updates genre-media mappings.
1099        Removes mappings that are no longer present in the incoming genre_names set.
1100
1101        :param media_type: The type of media item being synced.
1102        :param media_id: The database ID of the media item.
1103        :param genre_names: Set of genre names from the provider.
1104        """
1105        media_id_int = int(media_id)
1106        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
1107        content_type = genre_content_type_for(media_type)
1108
1109        # fast path for the (very common) unchanged case: resolve the incoming names
1110        # against a short-lived cached snapshot of this taxonomy — the same resolution
1111        # the full path performs — and skip all writes when the resolved genre ids
1112        # match the stored mappings exactly. Unknown names require genre creation, so
1113        # they (and any mismatch) fall through to the full path below.
1114        target_ids = await self._resolve_genre_names_cached(genre_names, content_type)
1115        if target_ids is not None:
1116            stored_rows = await self.mass.music.database.get_rows_from_query(
1117                f"SELECT DISTINCT genre_id FROM {gm} "
1118                "WHERE media_type = :media_type AND media_id = :media_id",
1119                {"media_type": media_type.value, "media_id": media_id_int},
1120                limit=0,
1121            )
1122            if {int(row["genre_id"]) for row in stored_rows} == target_ids:
1123                return
1124
1125        # batch the (possible) genre creations and mapping changes into a single commit
1126        async with self.mass.music.database.deferred_commit():
1127            # Build target set: (genre_id, alias_name) from incoming names.
1128            # One alias can map to multiple genres (n:n). Genres resolve within the taxonomy
1129            # the item belongs to, so a podcast tag never lands on a music genre.
1130            target_mappings: dict[int, str] = {}
1131            for name in genre_names:
1132                normalized = self._normalize_genre_name(name)
1133                if not normalized:
1134                    continue
1135                genre_ids = await self._find_genres_for_alias(normalized[0], content_type)
1136                for gid in genre_ids:
1137                    if gid not in target_mappings:
1138                        target_mappings[gid] = normalized[0]
1139
1140            # Get current genre_ids from database
1141            rows = await self.mass.music.database.get_rows_from_query(
1142                f"SELECT genre_id FROM {gm} "
1143                "WHERE media_type = :media_type AND media_id = :media_id",
1144                {"media_type": media_type.value, "media_id": media_id_int},
1145                limit=0,
1146            )
1147            existing_genre_ids = {int(row["genre_id"]) for row in rows}
1148
1149            to_add = set(target_mappings.keys()) - existing_genre_ids
1150            to_remove = existing_genre_ids - set(target_mappings.keys())
1151
1152            for genre_id in to_remove:
1153                await self.mass.music.database.delete(
1154                    gm,
1155                    {
1156                        "genre_id": genre_id,
1157                        "media_id": media_id_int,
1158                        "media_type": media_type.value,
1159                    },
1160                )
1161
1162            for genre_id in to_add:
1163                await self.mass.music.database.insert(
1164                    gm,
1165                    {
1166                        "genre_id": genre_id,
1167                        "media_id": media_id_int,
1168                        "media_type": media_type.value,
1169                        "alias": target_mappings[genre_id],
1170                    },
1171                    allow_replace=True,
1172                )
1173
1174    def register_scheduled_scan_task(self) -> BackgroundTask:
1175        """Register the recurring genre mapping scan task."""
1176        utc_hour, utc_minute = local_clock_time_to_utc(4, 0)
1177        desired_schedule = TaskSchedule.daily(hour=utc_hour, minute=utc_minute)
1178        return self.mass.tasks.register_scheduled_task(
1179            task_id=GENRE_SCAN_TASK_ID,
1180            name="Scan genre mappings",
1181            handler=self._scan_genre_mappings,
1182            schedule=desired_schedule,
1183            translation_key="scan_genre_mappings",
1184            translation_owner=self.translation_owner,
1185            metadata={
1186                "task_domain": "genre_mapping_scan",
1187            },
1188            allow_retry=True,
1189        )
1190
1191    async def scan_mappings(self) -> dict[str, Any]:
1192        """
1193        Manually trigger a genre mapping scan (admin only).
1194
1195        :return: Status information about the scan trigger.
1196        """
1197        if self._genre_scan_running:
1198            return {
1199                "status": "already_running",
1200                "message": "Genre mapping scanner is already running",
1201            }
1202
1203        self._queue_genre_mapping_scan_task()
1204
1205        return {
1206            "status": "triggered",
1207            "message": "Genre mapping scan triggered",
1208            "last_scan": self._last_scan_time,
1209        }
1210
1211    async def get_scanner_status(self) -> dict[str, Any]:
1212        """
1213        Get status of the genre mapping background scanner.
1214
1215        :return: Scanner status information.
1216        """
1217        return {
1218            "running": self._genre_scan_running,
1219            "last_scan_time": self._last_scan_time,
1220            "last_scan_ago_seconds": (
1221                int(time.time() - self._last_scan_time) if self._last_scan_time else None
1222            ),
1223            "last_scan_mapped": self._last_scan_mapped,
1224        }
1225
1226    @staticmethod
1227    def _get_genre_icon_metadata(
1228        translation_key: str | None, content_type: MediaType | None = None
1229    ) -> MediaItemMetadata | None:
1230        """
1231        Build metadata with the genre icon image if an SVG exists for the translation key.
1232
1233        Spoken-word taxonomies keep their icons in a per-content_type subdir
1234        (``genres/podcast/<key>.svg``); the flat ``genres/<key>.svg`` (music, or a
1235        shared symbol) is used as a fallback.
1236
1237        :param translation_key: The genre's translation key (matches the SVG filename).
1238        :param content_type: The genre's taxonomy (None = music/general).
1239        """
1240        if not translation_key:
1241            return None
1242        # taxonomy-specific icon first, then the flat/shared one
1243        rel_candidates: list[str] = []
1244        if content_type is not None:
1245            rel_candidates.append(f"{content_type.value}/{translation_key}.svg")
1246        rel_candidates.append(f"{translation_key}.svg")
1247        for rel in rel_candidates:
1248            if RESOURCES_DIR.joinpath(GENRE_ICONS_DIR_NAME, rel).is_file():
1249                image = MediaItemImage(
1250                    type=ImageType.THUMB,
1251                    path=f"{GENRE_ICONS_DIR_NAME}/{rel}",
1252                    provider="builtin",
1253                )
1254                return MediaItemMetadata(images=UniqueList([image]))
1255        return None
1256
1257    @staticmethod
1258    def _dedup_aliases(existing: list[str], new: list[str]) -> list[str]:
1259        """
1260        Merge alias lists, deduplicating by normalized form (create_safe_string).
1261
1262        Preserves the first occurrence's original casing.
1263
1264        :param existing: Current aliases (ordering preserved).
1265        :param new: New aliases to add if not already present.
1266        """
1267        seen: set[str] = set()
1268        result: list[str] = []
1269        for alias in [*existing, *new]:
1270            norm = create_safe_string(alias, True, True)
1271            if norm and norm not in seen:
1272                seen.add(norm)
1273                result.append(alias)
1274        return result
1275
1276    def _search_filter_clause(self, search: str, query_params: dict[str, Any]) -> str:
1277        """Return search filter that also matches genre aliases."""
1278        name_clause = search_name_match_clause(self.db_table, search, "search", query_params)
1279        return (
1280            f"({name_clause}"
1281            " OR EXISTS("
1282            f"SELECT 1 FROM json_each({self.db_table}.genre_aliases) "
1283            "WHERE LOWER(json_each.value) LIKE :search_raw))"
1284        )
1285
1286    async def _add_library_item(self, item: Genre, overwrite_existing: bool = False) -> int:
1287        """Add a new genre record to the database."""
1288        aliases: list[str] = list(item.genre_aliases) if item.genre_aliases else [item.name]
1289        # Ensure the genre's own name is always in aliases (normalized comparison)
1290        name_norm = create_safe_string(item.name, True, True)
1291        if not any(create_safe_string(a, True, True) == name_norm for a in aliases):
1292            aliases.insert(0, item.name)
1293        content_type_value = item.content_type.value if item.content_type else None
1294        # If a soft-deleted genre with the same name in the same taxonomy exists, restore it
1295        # instead of inserting (scoped by content_type so a podcast "Comedy" never restores a
1296        # soft-deleted music "Comedy").
1297        excl_rows = await self.mass.music.database.get_rows_from_query(
1298            f"SELECT item_id FROM {DB_TABLE_GENRES} "
1299            "WHERE search_name = :search_name AND is_excluded = 1 "
1300            "AND content_type IS :content_type",
1301            {"search_name": name_norm, "content_type": content_type_value},
1302            limit=1,
1303        )
1304        if excl_rows:
1305            db_id = int(excl_rows[0]["item_id"])
1306            await self.mass.music.database.update(
1307                DB_TABLE_GENRES, {"item_id": db_id}, {"is_excluded": 0}
1308            )
1309            self.logger.debug("restored soft-deleted genre %s (id: %s)", item.name, db_id)
1310            return db_id
1311        db_id = await self.mass.music.database.insert(
1312            self.db_table,
1313            {
1314                "name": item.name,
1315                "sort_name": item.sort_name,
1316                "translation_key": item.translation_key,
1317                "description": item.metadata.description if item.metadata else None,
1318                "favorite": item.favorite,
1319                "metadata": serialize_to_json(item.metadata),
1320                "genre_aliases": serialize_to_json(aliases),
1321                "play_count": 0,
1322                "last_played": 0,
1323                "search_name": create_safe_string(item.name, True, True),
1324                "search_sort_name": create_safe_string(item.sort_name or "", True, True),
1325                "timestamp_added": UNSET,
1326                "is_default": 0,
1327                "content_type": content_type_value,
1328            },
1329        )
1330        # update/set external id lookup table
1331        await self.set_external_ids(db_id, item.external_ids)
1332        self.logger.debug("added %s to database (id: %s)", item.name, db_id)
1333        return db_id
1334
1335    async def _update_library_item(
1336        self, item_id: str | int, update: Genre, overwrite: bool = False
1337    ) -> None:
1338        """Update existing genre record in the database."""
1339        db_id = int(item_id)
1340        cur_item = await self.get_library_item(db_id)
1341        metadata = update.metadata if overwrite else cur_item.metadata.update(update.metadata)
1342        cur_item.external_ids.update(update.external_ids)
1343        name = update.name if overwrite else cur_item.name
1344        sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name
1345        existing_description = await self._get_description(db_id)
1346        description = (
1347            update.metadata.description
1348            if update.metadata and update.metadata.description is not None
1349            else None
1350            if overwrite
1351            else existing_description
1352        )
1353        # Merge aliases: keep existing, add any new from update (normalized dedup)
1354        existing_aliases = list(cur_item.genre_aliases) if cur_item.genre_aliases else []
1355        update_aliases = list(update.genre_aliases) if update.genre_aliases else []
1356        if overwrite:
1357            merged_aliases = self._dedup_aliases(update_aliases, [name])
1358        else:
1359            merged_aliases = self._dedup_aliases(existing_aliases, [*update_aliases, name])
1360
1361        # content_type (the genre's taxonomy) is set at creation and never changed by an edit,
1362        # so an update — even with overwrite — must not clobber it.
1363        content_type = cur_item.content_type
1364
1365        await self.mass.music.database.update(
1366            self.db_table,
1367            {"item_id": db_id},
1368            {
1369                "name": name,
1370                "sort_name": sort_name,
1371                "translation_key": update.translation_key
1372                if overwrite
1373                else cur_item.translation_key,
1374                "description": description,
1375                "favorite": update.favorite,
1376                "metadata": serialize_to_json(metadata),
1377                "genre_aliases": serialize_to_json(merged_aliases),
1378                "search_name": create_safe_string(name, True, True),
1379                "search_sort_name": create_safe_string(sort_name or "", True, True),
1380                "timestamp_added": UNSET,
1381                "content_type": content_type.value if content_type else None,
1382            },
1383        )
1384        # update/set external id lookup table
1385        await self.set_external_ids(
1386            db_id, update.external_ids if overwrite else cur_item.external_ids
1387        )
1388        self.logger.debug("updated %s in database: (id %s)", update.name, db_id)
1389
1390    async def _bulk_scan_media_genres(self) -> None:
1391        """
1392        Bulk-scan all media items and rebuild genre mappings using CTE.
1393
1394        Resolution is scoped per genre taxonomy (music / audiobook / podcast): for each bucket
1395        the genre names from that bucket's tables are resolved against — and created within —
1396        only that taxonomy's genres, then mapped with a single INSERT per media type.
1397        """
1398        db = self.mass.music.database
1399        excl = DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION
1400        total_resolved = 0
1401
1402        for content_type, tables in GENRE_BUCKETS:
1403            # Build alias and primary-name lookups for this taxonomy. Primary-name match takes
1404            # priority over alias match so a bare "pop" tag only maps to the Pop genre, not every
1405            # genre that accumulated "pop" as a secondary alias.
1406            alias_to_genre, primary_name_to_genre = await self._build_genre_lookup(content_type)
1407
1408            union_parts = [
1409                f"SELECT DISTINCT TRIM(g.value) AS raw_name "
1410                f"FROM {table}, "
1411                f"json_each(json_extract({table}.metadata, '$.genres')) AS g "
1412                f"WHERE TRIM(g.value) != ''"
1413                for table, _ in tables
1414            ]
1415            unique_names_sql = " UNION ".join(union_parts)
1416            rows = await db.get_rows_from_query(unique_names_sql, limit=0)
1417            unique_raw_names = [row["raw_name"] for row in rows if row["raw_name"]]
1418
1419            # Resolve each raw name to genre_ids within this taxonomy.
1420            # One raw name can map to multiple genres (n:n), except when a genre's primary name
1421            # exactly matches the normalised tag — in that case use only that single genre.
1422            raw_name_to_genres: dict[str, list[int]] = {}
1423            for raw_name in unique_raw_names:
1424                norm = create_safe_string(raw_name.strip(), True, True)
1425                if not norm:
1426                    continue
1427                if norm in primary_name_to_genre:
1428                    raw_name_to_genres[raw_name] = [primary_name_to_genre[norm]]
1429                elif norm in alias_to_genre:
1430                    raw_name_to_genres[raw_name] = alias_to_genre[norm]
1431                else:
1432                    resolved_ids = await self._find_genres_for_alias(raw_name, content_type)
1433                    if resolved_ids:
1434                        raw_name_to_genres[raw_name] = resolved_ids
1435                        alias_to_genre[norm] = resolved_ids
1436
1437            total_resolved += len(raw_name_to_genres)
1438
1439            # Add discovered raw names as aliases to their resolved genres so that future
1440            # searches by raw name (e.g. "Synthpop") find the parent genre even when the stored
1441            # alias differs (e.g. "synth-pop").
1442            genre_new_aliases: dict[int, list[str]] = {}
1443            for raw_name, gids in raw_name_to_genres.items():
1444                for gid in gids:
1445                    genre_new_aliases.setdefault(gid, []).append(raw_name)
1446            for gid, new_aliases in genre_new_aliases.items():
1447                await self._ensure_aliases(gid, new_aliases)
1448
1449            if not raw_name_to_genres:
1450                continue
1451
1452            # Build CTE with (raw_name, genre_id) pairs and INSERT mappings for this bucket's
1453            # tables. One raw name can produce multiple rows when it maps to multiple genres.
1454            cte_values = ", ".join(
1455                f"(LOWER('{name.replace(chr(39), chr(39) + chr(39))}'), {gid})"
1456                for name, gids in raw_name_to_genres.items()
1457                for gid in gids
1458            )
1459            cte = f"WITH genre_lookup(raw_name, genre_id) AS (VALUES {cte_values})"
1460
1461            for table, media_type in tables:
1462                full_query = (
1463                    f"{cte} INSERT OR REPLACE INTO {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}"
1464                    f"(genre_id, media_id, media_type, alias) "
1465                    f"SELECT gl.genre_id, {table}.item_id, "
1466                    f"'{media_type.value}', TRIM(g.value) "
1467                    f"FROM {table}, "
1468                    f"json_each(CASE WHEN json_valid({table}.metadata) "
1469                    f"THEN json_extract({table}.metadata, '$.genres') END) AS g "
1470                    f"JOIN genre_lookup gl ON gl.raw_name = LOWER(TRIM(g.value)) "
1471                    f"WHERE TRIM(g.value) != '' "
1472                    f"AND NOT EXISTS ("
1473                    f"SELECT 1 FROM {excl} e "
1474                    f"WHERE e.genre_id = gl.genre_id "
1475                    f"AND e.media_id = {table}.item_id "
1476                    f"AND e.media_type = '{media_type.value}')"
1477                )
1478                await db.execute(full_query)
1479            await db.commit()
1480
1481        self.logger.info(
1482            "Bulk genre scan completed - mapped %d unique names to genres", total_resolved
1483        )
1484        await self._propagate_genre_mappings_to_parents()
1485
1486    async def _cleanup_stale_genre_mappings(self) -> None:
1487        """
1488        Remove genre mappings where the alias is no longer in the media item's metadata.genres.
1489
1490        A mapping is considered stale when the alias stored in the mapping is no longer present
1491        in the media item's current metadata.genres. This includes items where metadata.genres
1492        is empty or null — all mappings for such items are removed. Empty non-default genres
1493        (those without a translation_key) are also deleted.
1494        """
1495        db = self.mass.music.database
1496        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
1497
1498        count_before = await db.get_count(gm)
1499
1500        for table, media_type in MEDIA_TABLES:
1501            # Orphan pass: remove mappings whose media item no longer exists.
1502            # Runs regardless of is_manual — an orphan is always garbage.
1503            await db.delete_where_query(
1504                gm,
1505                f"media_type = '{media_type.value}' "
1506                f"AND NOT EXISTS ("
1507                f"  SELECT 1 FROM {table} "
1508                f"  WHERE {table}.item_id = {gm}.media_id"
1509                f")",
1510            )
1511            # Stale-alias pass: media item exists but the alias has dropped out
1512            # of metadata.genres. Manual mappings are excluded: their alias is
1513            # never written to metadata.genres.
1514            await db.delete_where_query(
1515                gm,
1516                f"media_type = '{media_type.value}' "
1517                f"AND alias IS NOT NULL "
1518                f"AND is_manual = 0 "
1519                f"AND NOT EXISTS ("
1520                f"  SELECT 1 FROM {table}, "
1521                f"  json_each(json_extract({table}.metadata, '$.genres')) AS g "
1522                f"  WHERE {table}.item_id = {gm}.media_id "
1523                f"  AND LOWER(TRIM(g.value)) = LOWER({gm}.alias)"
1524                f")",
1525            )
1526            # Cross-namespace pass: remove scanner-created mappings whose genre lives in a
1527            # different taxonomy than the item's media type. This re-homes legacy mappings
1528            # created before content_type namespacing (e.g. a podcast pointing at the music
1529            # "Spoken Word" genre); the scan then re-maps the item into its own taxonomy.
1530            # Manual mappings are preserved.
1531            expected = genre_content_type_for(media_type)
1532            expected_literal = "NULL" if expected is None else f"'{expected.value}'"
1533            await db.delete_where_query(
1534                gm,
1535                f"media_type = '{media_type.value}' "
1536                f"AND is_manual = 0 "
1537                f"AND genre_id IN ("
1538                f"  SELECT item_id FROM {DB_TABLE_GENRES} "
1539                f"  WHERE content_type IS NOT {expected_literal}"
1540                f")",
1541            )
1542
1543        mappings_removed = count_before - await db.get_count(gm)
1544        if mappings_removed:
1545            self.logger.info("Genre scan: removed %d stale genre mappings", mappings_removed)
1546
1547        # Delete playlog entries for empty non-default genres before removing them, to avoid
1548        # orphaned playlog rows pointing to genres that no longer exist.
1549        # is_default = 0 identifies non-default genres; default genres are always kept
1550        # even if they become unmapped/empty.
1551        excl = DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION
1552        await db.delete_where_query(
1553            DB_TABLE_PLAYLOG,
1554            f"media_type = '{MediaType.GENRE.value}' "
1555            f"AND item_id IN ("
1556            f"  SELECT item_id FROM {DB_TABLE_GENRES} "
1557            f"  WHERE is_default = 0 "
1558            f"  AND is_excluded = 0 "
1559            f"  AND NOT EXISTS ("
1560            f"    SELECT 1 FROM {gm} WHERE {gm}.genre_id = {DB_TABLE_GENRES}.item_id"
1561            f"  ) "
1562            f"  AND NOT EXISTS ("
1563            f"    SELECT 1 FROM {excl} WHERE {excl}.genre_id = {DB_TABLE_GENRES}.item_id"
1564            f"  )"
1565            f")",
1566        )
1567        genres_before = await db.get_count(DB_TABLE_GENRES)
1568        await db.delete_where_query(
1569            DB_TABLE_GENRES,
1570            f"is_default = 0 "
1571            f"AND is_excluded = 0 "
1572            f"AND NOT EXISTS ("
1573            f"  SELECT 1 FROM {gm} WHERE {gm}.genre_id = {DB_TABLE_GENRES}.item_id"
1574            f") "
1575            f"AND NOT EXISTS ("
1576            f"  SELECT 1 FROM {excl} WHERE {excl}.genre_id = {DB_TABLE_GENRES}.item_id"
1577            f")",
1578        )
1579        genres_deleted = genres_before - await db.get_count(DB_TABLE_GENRES)
1580        if genres_deleted:
1581            self.logger.info("Genre scan: deleted %d empty non-default genres", genres_deleted)
1582
1583    async def _bulk_scan_unmapped_genres(self) -> int:
1584        """
1585        Scan only unmapped media items and create genre mappings using CTE.
1586
1587        Similar to _bulk_scan_media_genres but filters to items not yet in
1588        genre_media_item_mapping. Used by the incremental scanner after syncs.
1589
1590        :return: Total number of items mapped.
1591        """
1592        await self._cleanup_stale_genre_mappings()
1593
1594        db = self.mass.music.database
1595        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
1596        excl = DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION
1597        count_before = await db.get_count(gm)
1598        mapped_any = False
1599
1600        # Resolve and map each taxonomy (music / audiobook / podcast) separately so genre
1601        # names only resolve against — and new genres are created within — their own namespace.
1602        for content_type, tables in GENRE_BUCKETS:
1603            alias_to_genre, primary_name_to_genre = await self._build_genre_lookup(content_type)
1604
1605            # Extract all unique raw genre names from this taxonomy's media items.
1606            # We don't filter by unmapped items here because a media item may have some
1607            # genres mapped but not all (e.g. added a new genre tag).
1608            union_parts = [
1609                f"SELECT DISTINCT TRIM(g.value) AS raw_name "
1610                f"FROM {table}, json_each(json_extract({table}.metadata, '$.genres')) AS g "
1611                f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
1612                f"AND json_extract({table}.metadata, '$.genres') != '[]'"
1613                for table, _mtype in tables
1614            ]
1615            unique_names_sql = " UNION ".join(union_parts)
1616            rows = await db.get_rows_from_query(unique_names_sql, limit=0)
1617            unique_raw_names = [row["raw_name"] for row in rows if row["raw_name"]]
1618            if not unique_raw_names:
1619                continue
1620
1621            # Resolve each raw name to genre_ids within this taxonomy. Primary-name match takes
1622            # priority over alias match so a bare "pop" tag only maps to the Pop genre, not every
1623            # genre that accumulated "pop" as a secondary alias.
1624            raw_name_to_genres: dict[str, list[int]] = {}
1625            for raw_name in unique_raw_names:
1626                norm = create_safe_string(raw_name.strip(), True, True)
1627                if not norm:
1628                    continue
1629                if norm in primary_name_to_genre:
1630                    raw_name_to_genres[raw_name] = [primary_name_to_genre[norm]]
1631                elif norm in alias_to_genre:
1632                    raw_name_to_genres[raw_name] = alias_to_genre[norm]
1633                else:
1634                    resolved_ids = await self._find_genres_for_alias(raw_name, content_type)
1635                    if resolved_ids:
1636                        raw_name_to_genres[raw_name] = resolved_ids
1637                        alias_to_genre[norm] = resolved_ids
1638
1639            if not raw_name_to_genres:
1640                continue
1641
1642            # Add discovered raw names as aliases to their resolved genres
1643            genre_new_aliases: dict[int, list[str]] = {}
1644            for raw_name, gids in raw_name_to_genres.items():
1645                for gid in gids:
1646                    genre_new_aliases.setdefault(gid, []).append(raw_name)
1647            for gid, new_aliases in genre_new_aliases.items():
1648                await self._ensure_aliases(gid, new_aliases)
1649
1650            # Build CTE with n:n pairs and INSERT only for unmapped items
1651            cte_values = ", ".join(
1652                f"(LOWER('{name.replace(chr(39), chr(39) + chr(39))}'), {gid})"
1653                for name, gids in raw_name_to_genres.items()
1654                for gid in gids
1655            )
1656            cte = f"WITH genre_lookup(raw_name, genre_id) AS (VALUES {cte_values})"
1657
1658            for table, media_type in tables:
1659                full_query = (
1660                    f"{cte} INSERT OR REPLACE INTO {gm}"
1661                    f"(genre_id, media_id, media_type, alias) "
1662                    f"SELECT gl.genre_id, {table}.item_id, "
1663                    f"'{media_type.value}', TRIM(g.value) "
1664                    f"FROM {table}, "
1665                    f"json_each(json_extract({table}.metadata, '$.genres')) AS g "
1666                    f"JOIN genre_lookup gl ON gl.raw_name = LOWER(TRIM(g.value)) "
1667                    f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
1668                    f"AND json_extract({table}.metadata, '$.genres') != '[]' "
1669                    f"AND NOT EXISTS ("
1670                    f"SELECT 1 FROM {gm} ex "
1671                    f"WHERE ex.genre_id = gl.genre_id "
1672                    f"AND ex.media_id = {table}.item_id "
1673                    f"AND ex.media_type = '{media_type.value}' "
1674                    f"AND ex.is_derived = 0) "
1675                    f"AND NOT EXISTS ("
1676                    f"SELECT 1 FROM {excl} e "
1677                    f"WHERE e.genre_id = gl.genre_id "
1678                    f"AND e.media_id = {table}.item_id "
1679                    f"AND e.media_type = '{media_type.value}')"
1680                )
1681                await db.execute(full_query)
1682            mapped_any = True
1683
1684        if mapped_any:
1685            await db.commit()
1686            await self._propagate_genre_mappings_to_parents()
1687        count_after = await db.get_count(gm)
1688
1689        return count_after - count_before
1690
1691    async def _propagate_genre_mappings_to_parents(self) -> None:
1692        """
1693        Propagate track genre mappings to albums and artists for filesystem provider instances.
1694
1695        Only runs when at least one filesystem_local or filesystem_smb provider instance has
1696        the 'propagate_track_genres' config option enabled. Albums and artists that already
1697        have their own genre metadata (e.g. from an NFO file) are skipped.
1698
1699        Derived mappings are stored with is_derived=1 and rebuilt from scratch on each
1700        call, so stale derived mappings are never left behind.
1701        The genre_media_item_exclusion table is respected — excluded pairs are never derived.
1702        """
1703        enabled_instance_ids: list[str] = []
1704        for p in self.mass.music.providers:
1705            if p.domain in {"filesystem_local", "filesystem_smb"}:
1706                enabled = await self.mass.config.get_provider_config_value(
1707                    p.instance_id, "propagate_track_genres", default=False
1708                )
1709                if enabled:
1710                    enabled_instance_ids.append(p.instance_id)
1711
1712        db = self.mass.music.database
1713        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
1714
1715        # Always wipe previously derived mappings first so that disabling propagation
1716        # on a provider immediately removes its derived entries, not just on next run.
1717        await db.execute(
1718            f"DELETE FROM {gm} WHERE is_derived = 1 AND media_type IN ('album', 'artist')"
1719        )
1720
1721        if not enabled_instance_ids:
1722            await db.commit()
1723            return
1724
1725        excl = DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION
1726        pm = DB_TABLE_PROVIDER_MAPPINGS
1727        ids_sql = ", ".join(f"'{x}'" for x in enabled_instance_ids)
1728
1729        # Derive album genres: inherit each track genre mapping onto the track's album,
1730        # provided the album has no own genre metadata and the pair is not excluded.
1731        await db.execute(
1732            f"INSERT OR IGNORE INTO {gm} (genre_id, media_id, media_type, alias, is_derived) "
1733            f"SELECT DISTINCT m.genre_id, at.album_id, 'album', NULL, 1 "
1734            f"FROM {gm} m "
1735            f"JOIN {DB_TABLE_ALBUM_TRACKS} at "
1736            f"  ON m.media_id = at.track_id AND m.media_type = 'track' "
1737            f"JOIN {pm} p ON p.item_id = at.track_id AND p.media_type = 'track' "
1738            f"  AND p.provider_instance IN ({ids_sql}) "
1739            f"JOIN {DB_TABLE_ALBUMS} alb ON alb.item_id = at.album_id "
1740            f"WHERE ("
1741            f"  json_extract(alb.metadata, '$.genres') IS NULL "
1742            f"  OR json_extract(alb.metadata, '$.genres') = '[]'"
1743            f") "
1744            f"AND NOT EXISTS ("
1745            f"  SELECT 1 FROM {excl} e "
1746            f"  WHERE e.genre_id = m.genre_id "
1747            f"    AND e.media_id = at.album_id "
1748            f"    AND e.media_type = 'album'"
1749            f")"
1750        )
1751
1752        # Derive artist genres: inherit each track genre mapping onto the track's artist,
1753        # provided the artist has no own genre metadata and the pair is not excluded.
1754        await db.execute(
1755            f"INSERT OR IGNORE INTO {gm} (genre_id, media_id, media_type, alias, is_derived) "
1756            f"SELECT DISTINCT m.genre_id, ta.artist_id, 'artist', NULL, 1 "
1757            f"FROM {gm} m "
1758            f"JOIN {DB_TABLE_TRACK_ARTISTS} ta "
1759            f"  ON m.media_id = ta.track_id AND m.media_type = 'track' "
1760            f"JOIN {pm} p ON p.item_id = ta.track_id AND p.media_type = 'track' "
1761            f"  AND p.provider_instance IN ({ids_sql}) "
1762            f"JOIN {DB_TABLE_ARTISTS} art ON art.item_id = ta.artist_id "
1763            f"WHERE ("
1764            f"  json_extract(art.metadata, '$.genres') IS NULL "
1765            f"  OR json_extract(art.metadata, '$.genres') = '[]'"
1766            f") "
1767            f"AND NOT EXISTS ("
1768            f"  SELECT 1 FROM {excl} e "
1769            f"  WHERE e.genre_id = m.genre_id "
1770            f"    AND e.media_id = ta.artist_id "
1771            f"    AND e.media_type = 'artist'"
1772            f")"
1773        )
1774
1775        await db.commit()
1776
1777    async def _find_genre_ids_for_alias(self, alias_norm: str) -> list[int]:
1778        """
1779        Return ids of non-excluded genres that claim the given alias.
1780
1781        :param alias_norm: Alias normalised via ``create_safe_string``.
1782        """
1783        rows = await self.mass.music.database.get_rows_from_query(
1784            f"SELECT item_id, genre_aliases FROM {DB_TABLE_GENRES} WHERE is_excluded = 0",
1785            limit=0,
1786        )
1787        found: list[int] = []
1788        for row in rows:
1789            aliases = json.loads(row["genre_aliases"]) if row["genre_aliases"] else []
1790            if any(create_safe_string(a.strip(), True, True) == alias_norm for a in aliases):
1791                found.append(int(row["item_id"]))
1792        return found
1793
1794    async def _seed_default_genres(
1795        self,
1796        content_type: MediaType | None,
1797        mapping: list[dict[str, Any]],
1798        full_restore: bool,
1799    ) -> list[int]:
1800        """
1801        Seed the curated default genres for a single taxonomy.
1802
1803        Inserts missing default genres (is_default=1) scoped to ``content_type`` and tops up the
1804        aliases of any that already exist. Inserts are staged without committing.
1805
1806        :param content_type: Taxonomy to seed (None = music/general).
1807        :param mapping: The curated genre/alias entries for this taxonomy.
1808        :param full_restore: When True the table was just wiped, so every entry is treated as new.
1809        :return: The item_ids of the genres created in this taxonomy.
1810        """
1811        content_type_value = content_type.value if content_type else None
1812        if full_restore:
1813            existing: set[str] = set()
1814        else:
1815            rows = await self.mass.music.database.get_rows_from_query(
1816                f"SELECT search_name FROM {DB_TABLE_GENRES} WHERE content_type IS :content_type",
1817                {"content_type": content_type_value},
1818                limit=0,
1819            )
1820            existing = {row["search_name"] for row in rows}
1821
1822        created_ids: list[int] = []
1823        for entry in mapping:
1824            name = entry.get("genre")
1825            if not name:
1826                continue
1827            normalized = self._normalize_genre_name(name)
1828            if not normalized:
1829                continue
1830            name_value, sort_name, search_name, search_sort_name = normalized
1831            all_aliases = [name_value, *entry.get("aliases", [])]
1832            translation_key = entry.get("translation_key")
1833            icon_metadata = self._get_genre_icon_metadata(translation_key, content_type)
1834
1835            # Partial restore: top up aliases on the existing genre and refresh its icon
1836            # (icons may have been added to the resources dir after it was first seeded).
1837            if search_name in existing:
1838                rows = await self.mass.music.database.get_rows_from_query(
1839                    f"SELECT item_id, metadata FROM {DB_TABLE_GENRES} "
1840                    "WHERE search_name = :search_name AND content_type IS :content_type",
1841                    {"search_name": search_name, "content_type": content_type_value},
1842                    limit=1,
1843                )
1844                if rows:
1845                    genre_id = int(rows[0]["item_id"])
1846                    await self._ensure_aliases(genre_id, all_aliases)
1847                    if icon_metadata is not None:
1848                        current_md = json.loads(rows[0]["metadata"]) if rows[0]["metadata"] else {}
1849                        fresh_images = icon_metadata.to_dict().get("images")
1850                        if current_md.get("images") != fresh_images:
1851                            current_md["images"] = fresh_images
1852                            await self.mass.music.database.update(
1853                                DB_TABLE_GENRES,
1854                                {"item_id": genre_id},
1855                                {"metadata": serialize_to_json(current_md)},
1856                            )
1857                continue
1858
1859            # Stage new genre insert without committing yet (batch all in one transaction)
1860            cursor = await self.mass.music.database.execute(
1861                f"INSERT INTO {DB_TABLE_GENRES}"
1862                "(name, sort_name, translation_key, description, favorite, metadata, "
1863                "genre_aliases, play_count, last_played, "
1864                "search_name, search_sort_name, is_default, content_type) "
1865                "VALUES (:name, :sort_name, :translation_key, :description, :favorite, "
1866                ":metadata, :genre_aliases, :play_count, :last_played, "
1867                ":search_name, :search_sort_name, :is_default, :content_type)",
1868                {
1869                    "name": name_value,
1870                    "sort_name": sort_name,
1871                    "translation_key": translation_key,
1872                    "description": None,
1873                    "favorite": 0,
1874                    "metadata": serialize_to_json(icon_metadata.to_dict() if icon_metadata else {}),
1875                    "genre_aliases": serialize_to_json(all_aliases),
1876                    "play_count": 0,
1877                    "last_played": 0,
1878                    "search_name": search_name,
1879                    "search_sort_name": search_sort_name,
1880                    "is_default": 1,
1881                    "content_type": content_type_value,
1882                },
1883            )
1884            created_ids.append(cursor.lastrowid)
1885            existing.add(search_name)
1886        return created_ids
1887
1888    async def _build_genre_lookup(
1889        self, content_type: MediaType | None
1890    ) -> tuple[dict[str, list[int]], dict[str, int]]:
1891        """
1892        Build alias and primary-name lookup dicts from the genres in a single taxonomy.
1893
1894        :param content_type: Genre taxonomy to scope the lookup to (None = music/general).
1895        :return: Tuple of (alias_to_genre, primary_name_to_genre).
1896            alias_to_genre maps normalised alias -> list of genre_ids (n:n).
1897            primary_name_to_genre maps normalised primary name -> single genre_id.
1898        """
1899        alias_to_genre: dict[str, list[int]] = {}
1900        primary_name_to_genre: dict[str, int] = {}
1901        genre_rows = await self.mass.music.database.get_rows_from_query(
1902            f"SELECT item_id, search_name, genre_aliases FROM {DB_TABLE_GENRES} "
1903            "WHERE is_excluded = 0 AND content_type IS :content_type",
1904            {"content_type": content_type.value if content_type else None},
1905            limit=0,
1906        )
1907        for row in genre_rows:
1908            genre_id = int(row["item_id"])
1909            if row["search_name"]:
1910                primary_name_to_genre[row["search_name"]] = genre_id
1911            aliases = json.loads(row["genre_aliases"]) if row["genre_aliases"] else []
1912            for alias in aliases:
1913                norm = create_safe_string(alias.strip(), True, True)
1914                if norm:
1915                    alias_to_genre.setdefault(norm, [])
1916                    if genre_id not in alias_to_genre[norm]:
1917                        alias_to_genre[norm].append(genre_id)
1918        return alias_to_genre, primary_name_to_genre
1919
1920    async def _resolve_genre_names_cached(
1921        self, genre_names: set[str], content_type: MediaType | None
1922    ) -> set[int] | None:
1923        """
1924        Resolve genre names to genre ids using a short-lived cached taxonomy snapshot.
1925
1926        :param genre_names: Raw genre names from the provider.
1927        :param content_type: Genre taxonomy to resolve within (None = music/general).
1928        :return: The resolved genre ids, or None when any name is unknown to the
1929            taxonomy and a full resolution (with genre creation) is required.
1930        """
1931        cache_key = content_type.value if content_type else None
1932        lookup = self._sync_lookup_cache.get(cache_key)
1933        if lookup is None or (time.monotonic() - lookup.built_at) > SYNC_GENRE_LOOKUP_TTL:
1934            lookup = await self._build_sync_genre_lookup(content_type)
1935            self._sync_lookup_cache[cache_key] = lookup
1936        target_ids: set[int] = set()
1937        for name in genre_names:
1938            if not (normalized := self._normalize_genre_name(name)):
1939                continue
1940            search_name = normalized[2]
1941            # primary-name match takes priority over alias match, and names matching
1942            # an excluded genre deliberately resolve to nothing (mirrors
1943            # _find_genres_for_alias, which the full path uses)
1944            if (genre_id := lookup.primary_name_to_genre.get(search_name)) is not None:
1945                target_ids.add(genre_id)
1946            elif genre_ids := lookup.alias_to_genre.get(search_name):
1947                target_ids.update(genre_ids)
1948            elif search_name not in lookup.excluded_names:
1949                return None
1950        return target_ids
1951
1952    async def _build_sync_genre_lookup(self, content_type: MediaType | None) -> _SyncGenreLookup:
1953        """Build a fresh in-memory genre lookup snapshot for a single taxonomy."""
1954        alias_to_genre, primary_name_to_genre = await self._build_genre_lookup(content_type)
1955        excluded_rows = await self.mass.music.database.get_rows_from_query(
1956            f"SELECT search_name FROM {DB_TABLE_GENRES} "
1957            "WHERE is_excluded = 1 AND content_type IS :content_type",
1958            {"content_type": content_type.value if content_type else None},
1959            limit=0,
1960        )
1961        return _SyncGenreLookup(
1962            built_at=time.monotonic(),
1963            primary_name_to_genre=primary_name_to_genre,
1964            alias_to_genre=alias_to_genre,
1965            excluded_names={row["search_name"] for row in excluded_rows},
1966        )
1967
1968    async def _ensure_aliases(self, genre_id: int, aliases: list[str]) -> None:
1969        """
1970        Ensure a genre has all the specified aliases in its genre_aliases JSON.
1971
1972        :param genre_id: Database ID of the genre.
1973        :param aliases: List of alias strings that should be present.
1974        """
1975        genre = await self.get_library_item(genre_id)
1976        existing = list(genre.genre_aliases) if genre.genre_aliases else []
1977        merged = self._dedup_aliases(existing, aliases)
1978        if len(merged) != len(existing):
1979            await self.mass.music.database.update(
1980                self.db_table,
1981                {"item_id": genre_id},
1982                {"genre_aliases": serialize_to_json(merged)},
1983            )
1984
1985    async def _find_genres_for_alias(self, name: str, content_type: MediaType | None) -> list[int]:
1986        """
1987        Find all genres in a taxonomy that own the given alias name, or create a new genre.
1988
1989        An alias can map to multiple genres (n:n relationship). For example,
1990        "anime" could be an alias of both an "Anime" genre and an "Anime Music" genre.
1991        If no genre owns this alias, creates a new genre in this taxonomy.
1992
1993        :param name: The alias name to find/create a genre for.
1994        :param content_type: Genre taxonomy to scope lookup/creation to (None = music/general).
1995        :return: List of genre IDs (empty if name is invalid).
1996        """
1997        normalized = self._normalize_genre_name(name)
1998        if not normalized:
1999            return []
2000        name_value, sort_name, search_name, search_sort_name = normalized
2001        content_type_value = content_type.value if content_type else None
2002
2003        async with self._db_add_lock:
2004            found_ids: list[int] = []
2005
2006            # Check if a non-excluded genre in this taxonomy exists with this name as its own
2007            # primary name. If so, return immediately — an exact primary-name match takes full
2008            # priority over alias scanning. This prevents broad tags like "pop" from fanning out
2009            # to every genre that accumulated "pop" as a secondary alias (Rock, Punk, etc.).
2010            primary = await self.mass.music.database.get_rows_from_query(
2011                f"SELECT item_id FROM {DB_TABLE_GENRES} "
2012                "WHERE search_name = :search_name AND is_excluded = 0 "
2013                "AND content_type IS :content_type",
2014                {"search_name": search_name, "content_type": content_type_value},
2015                limit=1,
2016            )
2017            if primary:
2018                return [int(primary[0]["item_id"])]
2019
2020            # Search genre_aliases JSON columns (case-insensitive, can match multiple)
2021            rows = await self.mass.music.database.get_rows_from_query(
2022                f"SELECT item_id FROM {DB_TABLE_GENRES} "
2023                "WHERE is_excluded = 0 AND content_type IS :content_type AND EXISTS("
2024                "SELECT 1 FROM json_each(genre_aliases) "
2025                "WHERE LOWER(json_each.value) = LOWER(:alias_name)"
2026                ")",
2027                {"alias_name": name_value, "content_type": content_type_value},
2028                limit=0,
2029            )
2030            for row in rows:
2031                gid = int(row["item_id"])
2032                if gid not in found_ids:
2033                    found_ids.append(gid)
2034
2035            # Also check via normalized comparison (create_safe_string).
2036            # This catches genres that stages 1-2 miss due to normalization
2037            # differences, e.g. genre A has "synthpop", genre B has "synth-pop"
2038            # — both normalize to "synthpop" but LOWER can't bridge the gap.
2039            all_genres = await self.mass.music.database.get_rows_from_query(
2040                f"SELECT item_id, genre_aliases FROM {DB_TABLE_GENRES} "
2041                "WHERE is_excluded = 0 AND content_type IS :content_type",
2042                {"content_type": content_type_value},
2043                limit=0,
2044            )
2045            for row in all_genres:
2046                aliases = json.loads(row["genre_aliases"]) if row["genre_aliases"] else []
2047                for alias in aliases:
2048                    if create_safe_string(alias.strip(), True, True) == search_name:
2049                        gid = int(row["item_id"])
2050                        if gid not in found_ids:
2051                            found_ids.append(gid)
2052
2053            if found_ids:
2054                return found_ids
2055
2056            # Check if this name was deliberately excluded in this taxonomy before creating
2057            excluded = await self.mass.music.database.get_rows_from_query(
2058                f"SELECT item_id FROM {DB_TABLE_GENRES} "
2059                "WHERE search_name = :search_name AND is_excluded = 1 "
2060                "AND content_type IS :content_type",
2061                {"search_name": search_name, "content_type": content_type_value},
2062                limit=1,
2063            )
2064            if excluded:
2065                return []
2066
2067            # No genre owns this alias — create a new one in this taxonomy
2068            new_id = await self.mass.music.database.insert(
2069                DB_TABLE_GENRES,
2070                {
2071                    "name": name_value,
2072                    "sort_name": sort_name,
2073                    "description": None,
2074                    "favorite": 0,
2075                    "metadata": serialize_to_json({}),
2076                    "genre_aliases": serialize_to_json([name_value]),
2077                    "play_count": 0,
2078                    "last_played": 0,
2079                    "search_name": search_name,
2080                    "search_sort_name": search_sort_name,
2081                    "timestamp_added": UNSET,
2082                    "is_default": 0,
2083                    "content_type": content_type_value,
2084                },
2085            )
2086            return [new_id]
2087
2088    async def _get_description(self, item_id: int) -> str | None:
2089        if db_row := await self.mass.music.database.get_row(DB_TABLE_GENRES, {"item_id": item_id}):
2090            return dict(db_row).get("description")
2091        return None
2092
2093    @staticmethod
2094    def _normalize_genre_name(raw_name: str) -> tuple[str, str, str, str] | None:
2095        """
2096        Normalize a raw genre name for storage and search.
2097
2098        :param raw_name: Raw genre name from provider.
2099        :return: Tuple of (name, sort_name, search_name, search_sort_name) or None if invalid.
2100        """
2101        name = raw_name.strip()
2102        if not name:
2103            return None
2104        sort_name = name
2105        search_name = create_safe_string(name, True, True)
2106        if not search_name:
2107            return None
2108        search_sort_name = create_safe_string(sort_name or "", True, True)
2109        return name, sort_name, search_name, search_sort_name
2110
2111    def _on_music_sync_completed(self, _event: MassEvent) -> None:
2112        """Trigger genre mapping scan when music sync tasks have completed."""
2113        self._queue_genre_mapping_scan_task()
2114
2115    def _queue_genre_mapping_scan_task(self) -> BackgroundTask:
2116        """Queue the genre mapping scanner as a managed background task."""
2117        self.register_scheduled_scan_task()
2118        return self.mass.tasks.run_task(GENRE_SCAN_TASK_ID)
2119
2120    def _get_genre_scan_task(self) -> BackgroundTask | None:
2121        """Return the latest managed genre scan task, if any."""
2122        try:
2123            return self.mass.tasks.get_task(GENRE_SCAN_TASK_ID)
2124        except InvalidDataError:
2125            return None
2126
2127    @property
2128    def _genre_scan_running(self) -> bool:
2129        """Return whether the managed genre scan is currently queued or running."""
2130        if not (task := self._get_genre_scan_task()):
2131            return False
2132        return task.status in (TaskStatus.PENDING, TaskStatus.RUNNING)
2133
2134    async def _scan_genre_mappings(self) -> None:
2135        """
2136        Scan media items with metadata.genres and map them to genres.
2137
2138        Triggered after library sync completes or via manual API call.
2139        """
2140        # Double-check syncs haven't started since the event was dispatched
2141        if self.mass.music.active_sync_tasks:
2142            self.logger.debug("Syncs still in progress, deferring genre scan")
2143            update_current_task_progress_text("Waiting for music sync completion")
2144            return
2145        self._last_scan_time = time.time()
2146
2147        try:
2148            self.logger.debug("Starting genre mapping scan...")
2149            update_current_task_progress_text("Scanning unmapped genre metadata")
2150            self._last_scan_mapped = await self._bulk_scan_unmapped_genres()
2151            update_current_task_progress_text(f"Mapped {self._last_scan_mapped} genre reference(s)")
2152            self.logger.info(
2153                "Genre mapping scan completed: %d items mapped (%.1fs)",
2154                self._last_scan_mapped,
2155                time.time() - self._last_scan_time,
2156            )
2157
2158        except Exception as err:
2159            self.logger.error(
2160                "Error in genre mapping scanner: %s",
2161                str(err),
2162                exc_info=err if self.logger.isEnabledFor(logging.DEBUG) else None,
2163            )
2164
2165    def _parse_summary_row(self, db_row: Mapping[str, Any]) -> GenreSummary:
2166        """Parse a raw summary db row into a GenreSummary object."""
2167        item = cast("GenreSummary", super()._parse_summary_row(db_row))
2168        # only overwrite the (name-derived) translation key when explicitly stored
2169        if translation_key := db_row["translation_key"]:
2170            item.translation_key = translation_key
2171        if content_type := db_row["content_type"]:
2172            item.content_type = MediaType(content_type)
2173        return item
2174