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