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 _merge_library_item_references(self, target_id: int, source_id: int) -> None:
1391        """Transfer media mappings and exclusions owned by a merged genre."""
1392        await self._merge_genre_references(target_id, source_id)
1393
1394    async def _validate_library_item_merge(self, target: Genre, source: Genre) -> None:
1395        """Validate that two genres belong to the same taxonomy."""
1396        await super()._validate_library_item_merge(target, source)
1397        if target.content_type != source.content_type:
1398            msg = (
1399                f"Cannot merge genre '{source.name}' into '{target.name}': "
1400                "genres must belong to the same taxonomy (music / podcast / audiobook)."
1401            )
1402            raise InvalidDataError(msg)
1403
1404    async def _bulk_scan_media_genres(self) -> None:
1405        """
1406        Bulk-scan all media items and rebuild genre mappings using CTE.
1407
1408        Resolution is scoped per genre taxonomy (music / audiobook / podcast): for each bucket
1409        the genre names from that bucket's tables are resolved against — and created within —
1410        only that taxonomy's genres, then mapped with a single INSERT per media type.
1411        """
1412        db = self.mass.music.database
1413        excl = DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION
1414        total_resolved = 0
1415
1416        for content_type, tables in GENRE_BUCKETS:
1417            # Build alias and primary-name lookups for this taxonomy. Primary-name match takes
1418            # priority over alias match so a bare "pop" tag only maps to the Pop genre, not every
1419            # genre that accumulated "pop" as a secondary alias.
1420            alias_to_genre, primary_name_to_genre = await self._build_genre_lookup(content_type)
1421
1422            union_parts = [
1423                f"SELECT DISTINCT TRIM(g.value) AS raw_name "
1424                f"FROM {table}, "
1425                f"json_each(json_extract({table}.metadata, '$.genres')) AS g "
1426                f"WHERE TRIM(g.value) != ''"
1427                for table, _ in tables
1428            ]
1429            unique_names_sql = " UNION ".join(union_parts)
1430            rows = await db.get_rows_from_query(unique_names_sql, limit=0)
1431            unique_raw_names = [row["raw_name"] for row in rows if row["raw_name"]]
1432
1433            # Resolve each raw name to genre_ids within this taxonomy.
1434            # One raw name can map to multiple genres (n:n), except when a genre's primary name
1435            # exactly matches the normalised tag — in that case use only that single genre.
1436            raw_name_to_genres: dict[str, list[int]] = {}
1437            for raw_name in unique_raw_names:
1438                norm = create_safe_string(raw_name.strip(), True, True)
1439                if not norm:
1440                    continue
1441                if norm in primary_name_to_genre:
1442                    raw_name_to_genres[raw_name] = [primary_name_to_genre[norm]]
1443                elif norm in alias_to_genre:
1444                    raw_name_to_genres[raw_name] = alias_to_genre[norm]
1445                else:
1446                    resolved_ids = await self._find_genres_for_alias(raw_name, content_type)
1447                    if resolved_ids:
1448                        raw_name_to_genres[raw_name] = resolved_ids
1449                        alias_to_genre[norm] = resolved_ids
1450
1451            total_resolved += len(raw_name_to_genres)
1452
1453            # Add discovered raw names as aliases to their resolved genres so that future
1454            # searches by raw name (e.g. "Synthpop") find the parent genre even when the stored
1455            # alias differs (e.g. "synth-pop").
1456            genre_new_aliases: dict[int, list[str]] = {}
1457            for raw_name, gids in raw_name_to_genres.items():
1458                for gid in gids:
1459                    genre_new_aliases.setdefault(gid, []).append(raw_name)
1460            for gid, new_aliases in genre_new_aliases.items():
1461                await self._ensure_aliases(gid, new_aliases)
1462
1463            if not raw_name_to_genres:
1464                continue
1465
1466            # Build CTE with (raw_name, genre_id) pairs and INSERT mappings for this bucket's
1467            # tables. One raw name can produce multiple rows when it maps to multiple genres.
1468            cte_values = ", ".join(
1469                f"(LOWER('{name.replace(chr(39), chr(39) + chr(39))}'), {gid})"
1470                for name, gids in raw_name_to_genres.items()
1471                for gid in gids
1472            )
1473            cte = f"WITH genre_lookup(raw_name, genre_id) AS (VALUES {cte_values})"
1474
1475            for table, media_type in tables:
1476                full_query = (
1477                    f"{cte} INSERT OR REPLACE INTO {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}"
1478                    f"(genre_id, media_id, media_type, alias) "
1479                    f"SELECT gl.genre_id, {table}.item_id, "
1480                    f"'{media_type.value}', TRIM(g.value) "
1481                    f"FROM {table}, "
1482                    f"json_each(CASE WHEN json_valid({table}.metadata) "
1483                    f"THEN json_extract({table}.metadata, '$.genres') END) AS g "
1484                    f"JOIN genre_lookup gl ON gl.raw_name = LOWER(TRIM(g.value)) "
1485                    f"WHERE TRIM(g.value) != '' "
1486                    f"AND NOT EXISTS ("
1487                    f"SELECT 1 FROM {excl} e "
1488                    f"WHERE e.genre_id = gl.genre_id "
1489                    f"AND e.media_id = {table}.item_id "
1490                    f"AND e.media_type = '{media_type.value}')"
1491                )
1492                await db.execute(full_query)
1493            await db.commit()
1494
1495        self.logger.info(
1496            "Bulk genre scan completed - mapped %d unique names to genres", total_resolved
1497        )
1498        await self._propagate_genre_mappings_to_parents()
1499
1500    async def _cleanup_stale_genre_mappings(self) -> None:
1501        """
1502        Remove genre mappings where the alias is no longer in the media item's metadata.genres.
1503
1504        A mapping is considered stale when the alias stored in the mapping is no longer present
1505        in the media item's current metadata.genres. This includes items where metadata.genres
1506        is empty or null — all mappings for such items are removed. Empty non-default genres
1507        (those without a translation_key) are also deleted.
1508        """
1509        db = self.mass.music.database
1510        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
1511
1512        count_before = await db.get_count(gm)
1513
1514        for table, media_type in MEDIA_TABLES:
1515            # Orphan pass: remove mappings whose media item no longer exists.
1516            # Runs regardless of is_manual — an orphan is always garbage.
1517            await db.delete_where_query(
1518                gm,
1519                f"media_type = '{media_type.value}' "
1520                f"AND NOT EXISTS ("
1521                f"  SELECT 1 FROM {table} "
1522                f"  WHERE {table}.item_id = {gm}.media_id"
1523                f")",
1524            )
1525            # Stale-alias pass: media item exists but the alias has dropped out
1526            # of metadata.genres. Manual mappings are excluded: their alias is
1527            # never written to metadata.genres.
1528            await db.delete_where_query(
1529                gm,
1530                f"media_type = '{media_type.value}' "
1531                f"AND alias IS NOT NULL "
1532                f"AND is_manual = 0 "
1533                f"AND NOT EXISTS ("
1534                f"  SELECT 1 FROM {table}, "
1535                f"  json_each(json_extract({table}.metadata, '$.genres')) AS g "
1536                f"  WHERE {table}.item_id = {gm}.media_id "
1537                f"  AND LOWER(TRIM(g.value)) = LOWER({gm}.alias)"
1538                f")",
1539            )
1540            # Cross-namespace pass: remove scanner-created mappings whose genre lives in a
1541            # different taxonomy than the item's media type. This re-homes legacy mappings
1542            # created before content_type namespacing (e.g. a podcast pointing at the music
1543            # "Spoken Word" genre); the scan then re-maps the item into its own taxonomy.
1544            # Manual mappings are preserved.
1545            expected = genre_content_type_for(media_type)
1546            expected_literal = "NULL" if expected is None else f"'{expected.value}'"
1547            await db.delete_where_query(
1548                gm,
1549                f"media_type = '{media_type.value}' "
1550                f"AND is_manual = 0 "
1551                f"AND genre_id IN ("
1552                f"  SELECT item_id FROM {DB_TABLE_GENRES} "
1553                f"  WHERE content_type IS NOT {expected_literal}"
1554                f")",
1555            )
1556
1557        mappings_removed = count_before - await db.get_count(gm)
1558        if mappings_removed:
1559            self.logger.info("Genre scan: removed %d stale genre mappings", mappings_removed)
1560
1561        # Delete playlog entries for empty non-default genres before removing them, to avoid
1562        # orphaned playlog rows pointing to genres that no longer exist.
1563        # is_default = 0 identifies non-default genres; default genres are always kept
1564        # even if they become unmapped/empty.
1565        excl = DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION
1566        await db.delete_where_query(
1567            DB_TABLE_PLAYLOG,
1568            f"media_type = '{MediaType.GENRE.value}' "
1569            f"AND item_id IN ("
1570            f"  SELECT item_id FROM {DB_TABLE_GENRES} "
1571            f"  WHERE is_default = 0 "
1572            f"  AND is_excluded = 0 "
1573            f"  AND NOT EXISTS ("
1574            f"    SELECT 1 FROM {gm} WHERE {gm}.genre_id = {DB_TABLE_GENRES}.item_id"
1575            f"  ) "
1576            f"  AND NOT EXISTS ("
1577            f"    SELECT 1 FROM {excl} WHERE {excl}.genre_id = {DB_TABLE_GENRES}.item_id"
1578            f"  )"
1579            f")",
1580        )
1581        genres_before = await db.get_count(DB_TABLE_GENRES)
1582        await db.delete_where_query(
1583            DB_TABLE_GENRES,
1584            f"is_default = 0 "
1585            f"AND is_excluded = 0 "
1586            f"AND NOT EXISTS ("
1587            f"  SELECT 1 FROM {gm} WHERE {gm}.genre_id = {DB_TABLE_GENRES}.item_id"
1588            f") "
1589            f"AND NOT EXISTS ("
1590            f"  SELECT 1 FROM {excl} WHERE {excl}.genre_id = {DB_TABLE_GENRES}.item_id"
1591            f")",
1592        )
1593        genres_deleted = genres_before - await db.get_count(DB_TABLE_GENRES)
1594        if genres_deleted:
1595            self.logger.info("Genre scan: deleted %d empty non-default genres", genres_deleted)
1596
1597    async def _bulk_scan_unmapped_genres(self) -> int:
1598        """
1599        Scan only unmapped media items and create genre mappings using CTE.
1600
1601        Similar to _bulk_scan_media_genres but filters to items not yet in
1602        genre_media_item_mapping. Used by the incremental scanner after syncs.
1603
1604        :return: Total number of items mapped.
1605        """
1606        await self._cleanup_stale_genre_mappings()
1607
1608        db = self.mass.music.database
1609        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
1610        excl = DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION
1611        count_before = await db.get_count(gm)
1612        mapped_any = False
1613
1614        # Resolve and map each taxonomy (music / audiobook / podcast) separately so genre
1615        # names only resolve against — and new genres are created within — their own namespace.
1616        for content_type, tables in GENRE_BUCKETS:
1617            alias_to_genre, primary_name_to_genre = await self._build_genre_lookup(content_type)
1618
1619            # Extract all unique raw genre names from this taxonomy's media items.
1620            # We don't filter by unmapped items here because a media item may have some
1621            # genres mapped but not all (e.g. added a new genre tag).
1622            union_parts = [
1623                f"SELECT DISTINCT TRIM(g.value) AS raw_name "
1624                f"FROM {table}, json_each(json_extract({table}.metadata, '$.genres')) AS g "
1625                f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
1626                f"AND json_extract({table}.metadata, '$.genres') != '[]'"
1627                for table, _mtype in tables
1628            ]
1629            unique_names_sql = " UNION ".join(union_parts)
1630            rows = await db.get_rows_from_query(unique_names_sql, limit=0)
1631            unique_raw_names = [row["raw_name"] for row in rows if row["raw_name"]]
1632            if not unique_raw_names:
1633                continue
1634
1635            # Resolve each raw name to genre_ids within this taxonomy. Primary-name match takes
1636            # priority over alias match so a bare "pop" tag only maps to the Pop genre, not every
1637            # genre that accumulated "pop" as a secondary alias.
1638            raw_name_to_genres: dict[str, list[int]] = {}
1639            for raw_name in unique_raw_names:
1640                norm = create_safe_string(raw_name.strip(), True, True)
1641                if not norm:
1642                    continue
1643                if norm in primary_name_to_genre:
1644                    raw_name_to_genres[raw_name] = [primary_name_to_genre[norm]]
1645                elif norm in alias_to_genre:
1646                    raw_name_to_genres[raw_name] = alias_to_genre[norm]
1647                else:
1648                    resolved_ids = await self._find_genres_for_alias(raw_name, content_type)
1649                    if resolved_ids:
1650                        raw_name_to_genres[raw_name] = resolved_ids
1651                        alias_to_genre[norm] = resolved_ids
1652
1653            if not raw_name_to_genres:
1654                continue
1655
1656            # Add discovered raw names as aliases to their resolved genres
1657            genre_new_aliases: dict[int, list[str]] = {}
1658            for raw_name, gids in raw_name_to_genres.items():
1659                for gid in gids:
1660                    genre_new_aliases.setdefault(gid, []).append(raw_name)
1661            for gid, new_aliases in genre_new_aliases.items():
1662                await self._ensure_aliases(gid, new_aliases)
1663
1664            # Build CTE with n:n pairs and INSERT only for unmapped items
1665            cte_values = ", ".join(
1666                f"(LOWER('{name.replace(chr(39), chr(39) + chr(39))}'), {gid})"
1667                for name, gids in raw_name_to_genres.items()
1668                for gid in gids
1669            )
1670            cte = f"WITH genre_lookup(raw_name, genre_id) AS (VALUES {cte_values})"
1671
1672            for table, media_type in tables:
1673                full_query = (
1674                    f"{cte} INSERT OR REPLACE INTO {gm}"
1675                    f"(genre_id, media_id, media_type, alias) "
1676                    f"SELECT gl.genre_id, {table}.item_id, "
1677                    f"'{media_type.value}', TRIM(g.value) "
1678                    f"FROM {table}, "
1679                    f"json_each(json_extract({table}.metadata, '$.genres')) AS g "
1680                    f"JOIN genre_lookup gl ON gl.raw_name = LOWER(TRIM(g.value)) "
1681                    f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
1682                    f"AND json_extract({table}.metadata, '$.genres') != '[]' "
1683                    f"AND NOT EXISTS ("
1684                    f"SELECT 1 FROM {gm} ex "
1685                    f"WHERE ex.genre_id = gl.genre_id "
1686                    f"AND ex.media_id = {table}.item_id "
1687                    f"AND ex.media_type = '{media_type.value}' "
1688                    f"AND ex.is_derived = 0) "
1689                    f"AND NOT EXISTS ("
1690                    f"SELECT 1 FROM {excl} e "
1691                    f"WHERE e.genre_id = gl.genre_id "
1692                    f"AND e.media_id = {table}.item_id "
1693                    f"AND e.media_type = '{media_type.value}')"
1694                )
1695                await db.execute(full_query)
1696            mapped_any = True
1697
1698        if mapped_any:
1699            await db.commit()
1700            await self._propagate_genre_mappings_to_parents()
1701        count_after = await db.get_count(gm)
1702
1703        return count_after - count_before
1704
1705    async def _propagate_genre_mappings_to_parents(self) -> None:
1706        """
1707        Propagate track genre mappings to albums and artists for filesystem provider instances.
1708
1709        Only runs when at least one filesystem_local or filesystem_smb provider instance has
1710        the 'propagate_track_genres' config option enabled. Albums and artists that already
1711        have their own genre metadata (e.g. from an NFO file) are skipped.
1712
1713        Derived mappings are stored with is_derived=1 and rebuilt from scratch on each
1714        call, so stale derived mappings are never left behind.
1715        The genre_media_item_exclusion table is respected — excluded pairs are never derived.
1716        """
1717        enabled_instance_ids: list[str] = []
1718        for p in self.mass.music.providers:
1719            if p.domain in {"filesystem_local", "filesystem_smb"}:
1720                enabled = await self.mass.config.get_provider_config_value(
1721                    p.instance_id, "propagate_track_genres", default=False
1722                )
1723                if enabled:
1724                    enabled_instance_ids.append(p.instance_id)
1725
1726        db = self.mass.music.database
1727        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
1728
1729        # Always wipe previously derived mappings first so that disabling propagation
1730        # on a provider immediately removes its derived entries, not just on next run.
1731        await db.execute(
1732            f"DELETE FROM {gm} WHERE is_derived = 1 AND media_type IN ('album', 'artist')"
1733        )
1734
1735        if not enabled_instance_ids:
1736            await db.commit()
1737            return
1738
1739        excl = DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION
1740        pm = DB_TABLE_PROVIDER_MAPPINGS
1741        ids_sql = ", ".join(f"'{x}'" for x in enabled_instance_ids)
1742
1743        # Derive album genres: inherit each track genre mapping onto the track's album,
1744        # provided the album has no own genre metadata and the pair is not excluded.
1745        await db.execute(
1746            f"INSERT OR IGNORE INTO {gm} (genre_id, media_id, media_type, alias, is_derived) "
1747            f"SELECT DISTINCT m.genre_id, at.album_id, 'album', NULL, 1 "
1748            f"FROM {gm} m "
1749            f"JOIN {DB_TABLE_ALBUM_TRACKS} at "
1750            f"  ON m.media_id = at.track_id AND m.media_type = 'track' "
1751            f"JOIN {pm} p ON p.item_id = at.track_id AND p.media_type = 'track' "
1752            f"  AND p.provider_instance IN ({ids_sql}) "
1753            f"JOIN {DB_TABLE_ALBUMS} alb ON alb.item_id = at.album_id "
1754            f"WHERE ("
1755            f"  json_extract(alb.metadata, '$.genres') IS NULL "
1756            f"  OR json_extract(alb.metadata, '$.genres') = '[]'"
1757            f") "
1758            f"AND NOT EXISTS ("
1759            f"  SELECT 1 FROM {excl} e "
1760            f"  WHERE e.genre_id = m.genre_id "
1761            f"    AND e.media_id = at.album_id "
1762            f"    AND e.media_type = 'album'"
1763            f")"
1764        )
1765
1766        # Derive artist genres: inherit each track genre mapping onto the track's artist,
1767        # provided the artist has no own genre metadata and the pair is not excluded.
1768        await db.execute(
1769            f"INSERT OR IGNORE INTO {gm} (genre_id, media_id, media_type, alias, is_derived) "
1770            f"SELECT DISTINCT m.genre_id, ta.artist_id, 'artist', NULL, 1 "
1771            f"FROM {gm} m "
1772            f"JOIN {DB_TABLE_TRACK_ARTISTS} ta "
1773            f"  ON m.media_id = ta.track_id AND m.media_type = 'track' "
1774            f"JOIN {pm} p ON p.item_id = ta.track_id AND p.media_type = 'track' "
1775            f"  AND p.provider_instance IN ({ids_sql}) "
1776            f"JOIN {DB_TABLE_ARTISTS} art ON art.item_id = ta.artist_id "
1777            f"WHERE ("
1778            f"  json_extract(art.metadata, '$.genres') IS NULL "
1779            f"  OR json_extract(art.metadata, '$.genres') = '[]'"
1780            f") "
1781            f"AND NOT EXISTS ("
1782            f"  SELECT 1 FROM {excl} e "
1783            f"  WHERE e.genre_id = m.genre_id "
1784            f"    AND e.media_id = ta.artist_id "
1785            f"    AND e.media_type = 'artist'"
1786            f")"
1787        )
1788
1789        await db.commit()
1790
1791    async def _find_genre_ids_for_alias(self, alias_norm: str) -> list[int]:
1792        """
1793        Return ids of non-excluded genres that claim the given alias.
1794
1795        :param alias_norm: Alias normalised via ``create_safe_string``.
1796        """
1797        rows = await self.mass.music.database.get_rows_from_query(
1798            f"SELECT item_id, genre_aliases FROM {DB_TABLE_GENRES} WHERE is_excluded = 0",
1799            limit=0,
1800        )
1801        found: list[int] = []
1802        for row in rows:
1803            aliases = json.loads(row["genre_aliases"]) if row["genre_aliases"] else []
1804            if any(create_safe_string(a.strip(), True, True) == alias_norm for a in aliases):
1805                found.append(int(row["item_id"]))
1806        return found
1807
1808    async def _seed_default_genres(
1809        self,
1810        content_type: MediaType | None,
1811        mapping: list[dict[str, Any]],
1812        full_restore: bool,
1813    ) -> list[int]:
1814        """
1815        Seed the curated default genres for a single taxonomy.
1816
1817        Inserts missing default genres (is_default=1) scoped to ``content_type`` and tops up the
1818        aliases of any that already exist. Inserts are staged without committing.
1819
1820        :param content_type: Taxonomy to seed (None = music/general).
1821        :param mapping: The curated genre/alias entries for this taxonomy.
1822        :param full_restore: When True the table was just wiped, so every entry is treated as new.
1823        :return: The item_ids of the genres created in this taxonomy.
1824        """
1825        content_type_value = content_type.value if content_type else None
1826        if full_restore:
1827            existing: set[str] = set()
1828        else:
1829            rows = await self.mass.music.database.get_rows_from_query(
1830                f"SELECT search_name FROM {DB_TABLE_GENRES} WHERE content_type IS :content_type",
1831                {"content_type": content_type_value},
1832                limit=0,
1833            )
1834            existing = {row["search_name"] for row in rows}
1835
1836        created_ids: list[int] = []
1837        for entry in mapping:
1838            name = entry.get("genre")
1839            if not name:
1840                continue
1841            normalized = self._normalize_genre_name(name)
1842            if not normalized:
1843                continue
1844            name_value, sort_name, search_name, search_sort_name = normalized
1845            all_aliases = [name_value, *entry.get("aliases", [])]
1846            translation_key = entry.get("translation_key")
1847            icon_metadata = self._get_genre_icon_metadata(translation_key, content_type)
1848
1849            # Partial restore: top up aliases on the existing genre and refresh its icon
1850            # (icons may have been added to the resources dir after it was first seeded).
1851            if search_name in existing:
1852                rows = await self.mass.music.database.get_rows_from_query(
1853                    f"SELECT item_id, metadata FROM {DB_TABLE_GENRES} "
1854                    "WHERE search_name = :search_name AND content_type IS :content_type",
1855                    {"search_name": search_name, "content_type": content_type_value},
1856                    limit=1,
1857                )
1858                if rows:
1859                    genre_id = int(rows[0]["item_id"])
1860                    await self._ensure_aliases(genre_id, all_aliases)
1861                    if icon_metadata is not None:
1862                        current_md = json.loads(rows[0]["metadata"]) if rows[0]["metadata"] else {}
1863                        fresh_images = icon_metadata.to_dict().get("images")
1864                        if current_md.get("images") != fresh_images:
1865                            current_md["images"] = fresh_images
1866                            await self.mass.music.database.update(
1867                                DB_TABLE_GENRES,
1868                                {"item_id": genre_id},
1869                                {"metadata": serialize_to_json(current_md)},
1870                            )
1871                continue
1872
1873            # Stage new genre insert without committing yet (batch all in one transaction)
1874            cursor = await self.mass.music.database.execute(
1875                f"INSERT INTO {DB_TABLE_GENRES}"
1876                "(name, sort_name, translation_key, description, favorite, metadata, "
1877                "genre_aliases, play_count, last_played, "
1878                "search_name, search_sort_name, is_default, content_type) "
1879                "VALUES (:name, :sort_name, :translation_key, :description, :favorite, "
1880                ":metadata, :genre_aliases, :play_count, :last_played, "
1881                ":search_name, :search_sort_name, :is_default, :content_type)",
1882                {
1883                    "name": name_value,
1884                    "sort_name": sort_name,
1885                    "translation_key": translation_key,
1886                    "description": None,
1887                    "favorite": 0,
1888                    "metadata": serialize_to_json(icon_metadata.to_dict() if icon_metadata else {}),
1889                    "genre_aliases": serialize_to_json(all_aliases),
1890                    "play_count": 0,
1891                    "last_played": 0,
1892                    "search_name": search_name,
1893                    "search_sort_name": search_sort_name,
1894                    "is_default": 1,
1895                    "content_type": content_type_value,
1896                },
1897            )
1898            created_ids.append(cursor.lastrowid)
1899            existing.add(search_name)
1900        return created_ids
1901
1902    async def _build_genre_lookup(
1903        self, content_type: MediaType | None
1904    ) -> tuple[dict[str, list[int]], dict[str, int]]:
1905        """
1906        Build alias and primary-name lookup dicts from the genres in a single taxonomy.
1907
1908        :param content_type: Genre taxonomy to scope the lookup to (None = music/general).
1909        :return: Tuple of (alias_to_genre, primary_name_to_genre).
1910            alias_to_genre maps normalised alias -> list of genre_ids (n:n).
1911            primary_name_to_genre maps normalised primary name -> single genre_id.
1912        """
1913        alias_to_genre: dict[str, list[int]] = {}
1914        primary_name_to_genre: dict[str, int] = {}
1915        genre_rows = await self.mass.music.database.get_rows_from_query(
1916            f"SELECT item_id, search_name, genre_aliases FROM {DB_TABLE_GENRES} "
1917            "WHERE is_excluded = 0 AND content_type IS :content_type",
1918            {"content_type": content_type.value if content_type else None},
1919            limit=0,
1920        )
1921        for row in genre_rows:
1922            genre_id = int(row["item_id"])
1923            if row["search_name"]:
1924                primary_name_to_genre[row["search_name"]] = genre_id
1925            aliases = json.loads(row["genre_aliases"]) if row["genre_aliases"] else []
1926            for alias in aliases:
1927                norm = create_safe_string(alias.strip(), True, True)
1928                if norm:
1929                    alias_to_genre.setdefault(norm, [])
1930                    if genre_id not in alias_to_genre[norm]:
1931                        alias_to_genre[norm].append(genre_id)
1932        return alias_to_genre, primary_name_to_genre
1933
1934    async def _resolve_genre_names_cached(
1935        self, genre_names: set[str], content_type: MediaType | None
1936    ) -> set[int] | None:
1937        """
1938        Resolve genre names to genre ids using a short-lived cached taxonomy snapshot.
1939
1940        :param genre_names: Raw genre names from the provider.
1941        :param content_type: Genre taxonomy to resolve within (None = music/general).
1942        :return: The resolved genre ids, or None when any name is unknown to the
1943            taxonomy and a full resolution (with genre creation) is required.
1944        """
1945        cache_key = content_type.value if content_type else None
1946        lookup = self._sync_lookup_cache.get(cache_key)
1947        if lookup is None or (time.monotonic() - lookup.built_at) > SYNC_GENRE_LOOKUP_TTL:
1948            lookup = await self._build_sync_genre_lookup(content_type)
1949            self._sync_lookup_cache[cache_key] = lookup
1950        target_ids: set[int] = set()
1951        for name in genre_names:
1952            if not (normalized := self._normalize_genre_name(name)):
1953                continue
1954            search_name = normalized[2]
1955            # primary-name match takes priority over alias match, and names matching
1956            # an excluded genre deliberately resolve to nothing (mirrors
1957            # _find_genres_for_alias, which the full path uses)
1958            if (genre_id := lookup.primary_name_to_genre.get(search_name)) is not None:
1959                target_ids.add(genre_id)
1960            elif genre_ids := lookup.alias_to_genre.get(search_name):
1961                target_ids.update(genre_ids)
1962            elif search_name not in lookup.excluded_names:
1963                return None
1964        return target_ids
1965
1966    async def _build_sync_genre_lookup(self, content_type: MediaType | None) -> _SyncGenreLookup:
1967        """Build a fresh in-memory genre lookup snapshot for a single taxonomy."""
1968        alias_to_genre, primary_name_to_genre = await self._build_genre_lookup(content_type)
1969        excluded_rows = await self.mass.music.database.get_rows_from_query(
1970            f"SELECT search_name FROM {DB_TABLE_GENRES} "
1971            "WHERE is_excluded = 1 AND content_type IS :content_type",
1972            {"content_type": content_type.value if content_type else None},
1973            limit=0,
1974        )
1975        return _SyncGenreLookup(
1976            built_at=time.monotonic(),
1977            primary_name_to_genre=primary_name_to_genre,
1978            alias_to_genre=alias_to_genre,
1979            excluded_names={row["search_name"] for row in excluded_rows},
1980        )
1981
1982    async def _ensure_aliases(self, genre_id: int, aliases: list[str]) -> None:
1983        """
1984        Ensure a genre has all the specified aliases in its genre_aliases JSON.
1985
1986        :param genre_id: Database ID of the genre.
1987        :param aliases: List of alias strings that should be present.
1988        """
1989        genre = await self.get_library_item(genre_id)
1990        existing = list(genre.genre_aliases) if genre.genre_aliases else []
1991        merged = self._dedup_aliases(existing, aliases)
1992        if len(merged) != len(existing):
1993            await self.mass.music.database.update(
1994                self.db_table,
1995                {"item_id": genre_id},
1996                {"genre_aliases": serialize_to_json(merged)},
1997            )
1998
1999    async def _find_genres_for_alias(self, name: str, content_type: MediaType | None) -> list[int]:
2000        """
2001        Find all genres in a taxonomy that own the given alias name, or create a new genre.
2002
2003        An alias can map to multiple genres (n:n relationship). For example,
2004        "anime" could be an alias of both an "Anime" genre and an "Anime Music" genre.
2005        If no genre owns this alias, creates a new genre in this taxonomy.
2006
2007        :param name: The alias name to find/create a genre for.
2008        :param content_type: Genre taxonomy to scope lookup/creation to (None = music/general).
2009        :return: List of genre IDs (empty if name is invalid).
2010        """
2011        normalized = self._normalize_genre_name(name)
2012        if not normalized:
2013            return []
2014        name_value, sort_name, search_name, search_sort_name = normalized
2015        content_type_value = content_type.value if content_type else None
2016
2017        async with self._db_add_lock:
2018            found_ids: list[int] = []
2019
2020            # Check if a non-excluded genre in this taxonomy exists with this name as its own
2021            # primary name. If so, return immediately — an exact primary-name match takes full
2022            # priority over alias scanning. This prevents broad tags like "pop" from fanning out
2023            # to every genre that accumulated "pop" as a secondary alias (Rock, Punk, etc.).
2024            primary = await self.mass.music.database.get_rows_from_query(
2025                f"SELECT item_id FROM {DB_TABLE_GENRES} "
2026                "WHERE search_name = :search_name AND is_excluded = 0 "
2027                "AND content_type IS :content_type",
2028                {"search_name": search_name, "content_type": content_type_value},
2029                limit=1,
2030            )
2031            if primary:
2032                return [int(primary[0]["item_id"])]
2033
2034            # Search genre_aliases JSON columns (case-insensitive, can match multiple)
2035            rows = await self.mass.music.database.get_rows_from_query(
2036                f"SELECT item_id FROM {DB_TABLE_GENRES} "
2037                "WHERE is_excluded = 0 AND content_type IS :content_type AND EXISTS("
2038                "SELECT 1 FROM json_each(genre_aliases) "
2039                "WHERE LOWER(json_each.value) = LOWER(:alias_name)"
2040                ")",
2041                {"alias_name": name_value, "content_type": content_type_value},
2042                limit=0,
2043            )
2044            for row in rows:
2045                gid = int(row["item_id"])
2046                if gid not in found_ids:
2047                    found_ids.append(gid)
2048
2049            # Also check via normalized comparison (create_safe_string).
2050            # This catches genres that stages 1-2 miss due to normalization
2051            # differences, e.g. genre A has "synthpop", genre B has "synth-pop"
2052            # — both normalize to "synthpop" but LOWER can't bridge the gap.
2053            all_genres = await self.mass.music.database.get_rows_from_query(
2054                f"SELECT item_id, genre_aliases FROM {DB_TABLE_GENRES} "
2055                "WHERE is_excluded = 0 AND content_type IS :content_type",
2056                {"content_type": content_type_value},
2057                limit=0,
2058            )
2059            for row in all_genres:
2060                aliases = json.loads(row["genre_aliases"]) if row["genre_aliases"] else []
2061                for alias in aliases:
2062                    if create_safe_string(alias.strip(), True, True) == search_name:
2063                        gid = int(row["item_id"])
2064                        if gid not in found_ids:
2065                            found_ids.append(gid)
2066
2067            if found_ids:
2068                return found_ids
2069
2070            # Check if this name was deliberately excluded in this taxonomy before creating
2071            excluded = await self.mass.music.database.get_rows_from_query(
2072                f"SELECT item_id FROM {DB_TABLE_GENRES} "
2073                "WHERE search_name = :search_name AND is_excluded = 1 "
2074                "AND content_type IS :content_type",
2075                {"search_name": search_name, "content_type": content_type_value},
2076                limit=1,
2077            )
2078            if excluded:
2079                return []
2080
2081            # No genre owns this alias — create a new one in this taxonomy
2082            new_id = await self.mass.music.database.insert(
2083                DB_TABLE_GENRES,
2084                {
2085                    "name": name_value,
2086                    "sort_name": sort_name,
2087                    "description": None,
2088                    "favorite": 0,
2089                    "metadata": serialize_to_json({}),
2090                    "genre_aliases": serialize_to_json([name_value]),
2091                    "play_count": 0,
2092                    "last_played": 0,
2093                    "search_name": search_name,
2094                    "search_sort_name": search_sort_name,
2095                    "timestamp_added": UNSET,
2096                    "is_default": 0,
2097                    "content_type": content_type_value,
2098                },
2099            )
2100            return [new_id]
2101
2102    async def _get_description(self, item_id: int) -> str | None:
2103        if db_row := await self.mass.music.database.get_row(DB_TABLE_GENRES, {"item_id": item_id}):
2104            return dict(db_row).get("description")
2105        return None
2106
2107    @staticmethod
2108    def _normalize_genre_name(raw_name: str) -> tuple[str, str, str, str] | None:
2109        """
2110        Normalize a raw genre name for storage and search.
2111
2112        :param raw_name: Raw genre name from provider.
2113        :return: Tuple of (name, sort_name, search_name, search_sort_name) or None if invalid.
2114        """
2115        name = raw_name.strip()
2116        if not name:
2117            return None
2118        sort_name = name
2119        search_name = create_safe_string(name, True, True)
2120        if not search_name:
2121            return None
2122        search_sort_name = create_safe_string(sort_name or "", True, True)
2123        return name, sort_name, search_name, search_sort_name
2124
2125    def _on_music_sync_completed(self, _event: MassEvent) -> None:
2126        """Trigger genre mapping scan when music sync tasks have completed."""
2127        self._queue_genre_mapping_scan_task()
2128
2129    def _queue_genre_mapping_scan_task(self) -> BackgroundTask:
2130        """Queue the genre mapping scanner as a managed background task."""
2131        self.register_scheduled_scan_task()
2132        return self.mass.tasks.run_task(GENRE_SCAN_TASK_ID)
2133
2134    def _get_genre_scan_task(self) -> BackgroundTask | None:
2135        """Return the latest managed genre scan task, if any."""
2136        try:
2137            return self.mass.tasks.get_task(GENRE_SCAN_TASK_ID)
2138        except InvalidDataError:
2139            return None
2140
2141    @property
2142    def _genre_scan_running(self) -> bool:
2143        """Return whether the managed genre scan is currently queued or running."""
2144        if not (task := self._get_genre_scan_task()):
2145            return False
2146        return task.status in (TaskStatus.PENDING, TaskStatus.RUNNING)
2147
2148    async def _scan_genre_mappings(self) -> None:
2149        """
2150        Scan media items with metadata.genres and map them to genres.
2151
2152        Triggered after library sync completes or via manual API call.
2153        """
2154        # Double-check syncs haven't started since the event was dispatched
2155        if self.mass.music.active_sync_tasks:
2156            self.logger.debug("Syncs still in progress, deferring genre scan")
2157            update_current_task_progress_text("Waiting for music sync completion")
2158            return
2159        self._last_scan_time = time.time()
2160
2161        try:
2162            self.logger.debug("Starting genre mapping scan...")
2163            update_current_task_progress_text("Scanning unmapped genre metadata")
2164            self._last_scan_mapped = await self._bulk_scan_unmapped_genres()
2165            update_current_task_progress_text(f"Mapped {self._last_scan_mapped} genre reference(s)")
2166            self.logger.info(
2167                "Genre mapping scan completed: %d items mapped (%.1fs)",
2168                self._last_scan_mapped,
2169                time.time() - self._last_scan_time,
2170            )
2171
2172        except Exception as err:
2173            self.logger.error(
2174                "Error in genre mapping scanner: %s",
2175                str(err),
2176                exc_info=err if self.logger.isEnabledFor(logging.DEBUG) else None,
2177            )
2178
2179    def _parse_summary_row(self, db_row: Mapping[str, Any]) -> GenreSummary:
2180        """Parse a raw summary db row into a GenreSummary object."""
2181        item = cast("GenreSummary", super()._parse_summary_row(db_row))
2182        # only overwrite the (name-derived) translation key when explicitly stored
2183        if translation_key := db_row["translation_key"]:
2184            item.translation_key = translation_key
2185        if content_type := db_row["content_type"]:
2186            item.content_type = MediaType(content_type)
2187        return item
2188