music-assistant-server

1"""Manage MediaItems of type Genre."""
2
3from __future__ import annotations
4
5import asyncio
6import json
7import logging
8import time
9from typing import TYPE_CHECKING, Any
10
11from music_assistant_models.enums import EventType, ImageType, MediaType
12from music_assistant_models.media_items import (
13    Album,
14    Artist,
15    Genre,
16    MediaItemImage,
17    MediaItemMetadata,
18    RecommendationFolder,
19    Track,
20)
21from music_assistant_models.unique_list import UniqueList
22
23from music_assistant.constants import (
24    DB_TABLE_ALBUMS,
25    DB_TABLE_ARTISTS,
26    DB_TABLE_AUDIOBOOKS,
27    DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
28    DB_TABLE_GENRES,
29    DB_TABLE_PLAYLISTS,
30    DB_TABLE_PODCASTS,
31    DB_TABLE_RADIOS,
32    DB_TABLE_TRACKS,
33    DEFAULT_GENRE_MAPPING,
34    GENRE_ICONS_DIR,
35)
36from music_assistant.helpers.compare import create_safe_string
37from music_assistant.helpers.database import UNSET
38from music_assistant.helpers.json import serialize_to_json
39
40from .base import MediaControllerBase
41
42if TYPE_CHECKING:
43    from music_assistant_models.event import MassEvent
44
45    from music_assistant import MusicAssistant
46
47
48class GenreController(MediaControllerBase[Genre]):
49    """Controller for Genre entities."""
50
51    db_table = DB_TABLE_GENRES
52    media_type = MediaType.GENRE
53    item_cls = Genre
54
55    def __init__(self, mass: MusicAssistant) -> None:
56        """Initialize class."""
57        super().__init__(mass)
58        # Background scanner state tracking
59        self._scanner_running: bool = False
60        self._last_scan_time: float = 0
61        self._last_scan_mapped: int = 0
62        self.base_query = f"""
63        SELECT
64            {DB_TABLE_GENRES}.*,
65            (SELECT JSON_GROUP_ARRAY(
66                json_object(
67                    'item_id', provider_mappings.provider_item_id,
68                    'provider_domain', provider_mappings.provider_domain,
69                    'provider_instance', provider_mappings.provider_instance,
70                    'available', provider_mappings.available,
71                    'audio_format', json(provider_mappings.audio_format),
72                    'url', provider_mappings.url,
73                    'details', provider_mappings.details,
74                    'in_library', provider_mappings.in_library,
75                    'is_unique', provider_mappings.is_unique
76                )) FROM provider_mappings
77                WHERE provider_mappings.item_id = {DB_TABLE_GENRES}.item_id
78                AND provider_mappings.media_type = '{MediaType.GENRE.value}'
79            ) AS provider_mappings
80        FROM {DB_TABLE_GENRES}"""
81
82        # register extra api handlers
83        self.mass.register_api_command(
84            "music/genres/add_alias", self.add_alias, required_role="admin"
85        )
86        self.mass.register_api_command(
87            "music/genres/remove_alias", self.remove_alias, required_role="admin"
88        )
89        self.mass.register_api_command(
90            "music/genres/add_media_mapping", self.add_media_mapping, required_role="admin"
91        )
92        self.mass.register_api_command(
93            "music/genres/remove_media_mapping",
94            self.remove_media_mapping,
95            required_role="admin",
96        )
97        self.mass.register_api_command(
98            "music/genres/promote_alias",
99            self.promote_alias_to_genre,
100            required_role="admin",
101        )
102        self.mass.register_api_command(
103            "music/genres/restore_defaults",
104            self.restore_default_genres,
105            required_role="admin",
106        )
107        self.mass.register_api_command(
108            "music/genres/add",
109            self.add_item_to_library,
110            required_role="admin",
111        )
112        self.mass.register_api_command(
113            "music/genres/overview",
114            self.get_overview,
115        )
116        self.mass.register_api_command(
117            "music/genres/radio_mode_base_tracks",
118            self.get_radio_mode_base_tracks,
119        )
120        self.mass.register_api_command(
121            "music/genres/scan_mappings",
122            self.scan_mappings,
123            required_role="admin",
124        )
125        self.mass.register_api_command(
126            "music/genres/scanner_status",
127            self.get_scanner_status,
128        )
129        self.mass.register_api_command(
130            "music/genres/genres_for_media_item",
131            self.get_genres_for_media_item,
132        )
133
134        # Run genre mapping scanner after library sync completes
135        self.mass.subscribe(self._on_sync_tasks_updated, EventType.SYNC_TASKS_UPDATED)
136
137    @staticmethod
138    def _get_genre_icon_metadata(translation_key: str | None) -> MediaItemMetadata | None:
139        """Build metadata with genre icon image if an SVG exists for the translation key.
140
141        :param translation_key: The genre's translation key (matches SVG filename).
142        """
143        if not translation_key:
144            return None
145        icon_path = GENRE_ICONS_DIR / f"{translation_key}.svg"
146        if not icon_path.is_file():
147            return None
148        image = MediaItemImage(
149            type=ImageType.THUMB,
150            path=str(icon_path),
151            provider="builtin",
152        )
153        return MediaItemMetadata(images=UniqueList([image]))
154
155    @staticmethod
156    def _dedup_aliases(existing: list[str], new: list[str]) -> list[str]:
157        """Merge alias lists, deduplicating by normalized form (create_safe_string).
158
159        Preserves the first occurrence's original casing.
160
161        :param existing: Current aliases (ordering preserved).
162        :param new: New aliases to add if not already present.
163        """
164        seen: set[str] = set()
165        result: list[str] = []
166        for alias in [*existing, *new]:
167            norm = create_safe_string(alias, True, True)
168            if norm and norm not in seen:
169                seen.add(norm)
170                result.append(alias)
171        return result
172
173    @property
174    def _search_filter_clause(self) -> str:
175        """Return search filter that also matches genre aliases."""
176        return (
177            f"({self.db_table}.search_name LIKE :search"
178            " OR EXISTS("
179            f"SELECT 1 FROM json_each({self.db_table}.genre_aliases) "
180            "WHERE LOWER(json_each.value) LIKE :search_raw))"
181        )
182
183    async def _add_library_item(self, item: Genre, overwrite_existing: bool = False) -> int:
184        """Add a new genre record to the database."""
185        aliases: list[str] = list(item.genre_aliases) if item.genre_aliases else [item.name]
186        # Ensure the genre's own name is always in aliases (normalized comparison)
187        name_norm = create_safe_string(item.name, True, True)
188        if not any(create_safe_string(a, True, True) == name_norm for a in aliases):
189            aliases.insert(0, item.name)
190        db_id = await self.mass.music.database.insert(
191            self.db_table,
192            {
193                "name": item.name,
194                "sort_name": item.sort_name,
195                "translation_key": item.translation_key,
196                "description": item.metadata.description if item.metadata else None,
197                "favorite": item.favorite,
198                "metadata": serialize_to_json(item.metadata),
199                "external_ids": serialize_to_json(item.external_ids),
200                "genre_aliases": serialize_to_json(aliases),
201                "play_count": 0,
202                "last_played": 0,
203                "search_name": create_safe_string(item.name, True, True),
204                "search_sort_name": create_safe_string(item.sort_name or "", True, True),
205                "timestamp_added": UNSET,
206            },
207        )
208        self.logger.debug("added %s to database (id: %s)", item.name, db_id)
209        return db_id
210
211    async def _update_library_item(
212        self, item_id: str | int, update: Genre, overwrite: bool = False
213    ) -> None:
214        """Update existing genre record in the database."""
215        db_id = int(item_id)
216        cur_item = await self.get_library_item(db_id)
217        metadata = update.metadata if overwrite else cur_item.metadata.update(update.metadata)
218        cur_item.external_ids.update(update.external_ids)
219        name = update.name if overwrite else cur_item.name
220        sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name
221        existing_description = await self._get_description(db_id)
222        description = (
223            update.metadata.description
224            if update.metadata and update.metadata.description is not None
225            else None
226            if overwrite
227            else existing_description
228        )
229        # Merge aliases: keep existing, add any new from update (normalized dedup)
230        existing_aliases = list(cur_item.genre_aliases) if cur_item.genre_aliases else []
231        update_aliases = list(update.genre_aliases) if update.genre_aliases else []
232        if overwrite:
233            merged_aliases = self._dedup_aliases(update_aliases, [name])
234        else:
235            merged_aliases = self._dedup_aliases(existing_aliases, [*update_aliases, name])
236
237        await self.mass.music.database.update(
238            self.db_table,
239            {"item_id": db_id},
240            {
241                "name": name,
242                "sort_name": sort_name,
243                "translation_key": update.translation_key
244                if overwrite
245                else cur_item.translation_key,
246                "description": description,
247                "favorite": update.favorite,
248                "metadata": serialize_to_json(metadata),
249                "external_ids": serialize_to_json(
250                    update.external_ids if overwrite else cur_item.external_ids
251                ),
252                "genre_aliases": serialize_to_json(merged_aliases),
253                "search_name": create_safe_string(name, True, True),
254                "search_sort_name": create_safe_string(sort_name or "", True, True),
255                "timestamp_added": UNSET,
256            },
257        )
258        self.logger.debug("updated %s in database: (id %s)", update.name, db_id)
259
260    async def library_items(
261        self,
262        favorite: bool | None = None,
263        search: str | None = None,
264        limit: int = 500,
265        offset: int = 0,
266        order_by: str = "sort_name",
267        provider: str | list[str] | None = None,
268        genre: int | list[int] | None = None,
269        **kwargs: Any,
270    ) -> list[Genre]:
271        """Get genres in the library.
272
273        :param genre: NOT SUPPORTED - Filtering genres by genres doesn't make sense.
274        """
275        if genre is not None:
276            msg = "genre parameter is not supported for Genre.library_items()"
277            raise ValueError(msg)
278        # Genres are library-only items without provider_mappings, so ignore
279        # the provider filter (the frontend always sends provider="library").
280        # Pass raw lowered search for alias matching (search_raw),
281        # since the normalized :search param strips spaces/special chars.
282        extra_params: dict[str, Any] | None = None
283        if search:
284            extra_params = {"search_raw": f"%{search.strip().lower()}%"}
285        return await self.get_library_items_by_query(
286            favorite=favorite,
287            search=search,
288            limit=limit,
289            offset=offset,
290            order_by=order_by,
291            extra_query_params=extra_params,
292        )
293
294    async def radio_mode_base_tracks(
295        self,
296        item: Genre,
297        preferred_provider_instances: list[str] | None = None,
298    ) -> list[Track]:
299        """Get the list of base tracks for a genre.
300
301        :param item: The Genre to get base tracks for.
302        :param preferred_provider_instances: List of preferred provider instance IDs to use.
303        """
304        db_id = int(item.item_id)
305        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
306        query = (
307            f"EXISTS(SELECT 1 FROM {gm} gm "
308            "WHERE gm.media_id = tracks.item_id "
309            "AND gm.media_type = 'track' "
310            "AND gm.genre_id = :genre_id)"
311        )
312        return await self.mass.music.tracks.get_library_items_by_query(
313            extra_query_parts=[query],
314            extra_query_params={"genre_id": db_id},
315            limit=50,
316            order_by="random",
317        )
318
319    async def mapped_media(
320        self,
321        item: Genre,
322        limit: int = 0,
323        offset: int = 0,
324        track_limit: int | None = None,
325        album_limit: int | None = None,
326        artist_limit: int | None = None,
327        order_by: str | None = None,
328    ) -> tuple[list[Track], list[Album], list[Artist]]:
329        """Return tracks, albums, and artists mapped to a genre.
330
331        :param item: The genre to fetch mapped media for.
332        :param limit: Default limit applied to all media types (0 = unlimited).
333        :param offset: Offset for pagination.
334        :param track_limit: Override limit for tracks (defaults to limit).
335        :param album_limit: Override limit for albums (defaults to limit).
336        :param artist_limit: Override limit for artists (defaults to limit).
337        :param order_by: Sort order for all queries (e.g. "random").
338        """
339        db_id = int(item.item_id)
340        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
341        t_limit = track_limit if track_limit is not None else limit
342        a_limit = album_limit if album_limit is not None else limit
343        ar_limit = artist_limit if artist_limit is not None else limit
344
345        track_query = (
346            f"EXISTS(SELECT 1 FROM {gm} gm "
347            "WHERE gm.media_id = tracks.item_id "
348            "AND gm.media_type = 'track' AND gm.genre_id = :genre_id)"
349        )
350        album_query = (
351            f"EXISTS(SELECT 1 FROM {gm} gm "
352            "WHERE gm.media_id = albums.item_id "
353            "AND gm.media_type = 'album' AND gm.genre_id = :genre_id)"
354        )
355        artist_query = (
356            f"EXISTS(SELECT 1 FROM {gm} gm "
357            "WHERE gm.media_id = artists.item_id "
358            "AND gm.media_type = 'artist' AND gm.genre_id = :genre_id)"
359        )
360
361        tracks, albums, artists = await asyncio.gather(
362            self.mass.music.tracks.get_library_items_by_query(
363                extra_query_parts=[track_query],
364                extra_query_params={"genre_id": db_id},
365                limit=t_limit,
366                offset=offset,
367                order_by=order_by,
368            ),
369            self.mass.music.albums.get_library_items_by_query(
370                extra_query_parts=[album_query],
371                extra_query_params={"genre_id": db_id},
372                limit=a_limit,
373                offset=offset,
374                order_by=order_by,
375            ),
376            self.mass.music.artists.get_library_items_by_query(
377                extra_query_parts=[artist_query],
378                extra_query_params={"genre_id": db_id},
379                limit=ar_limit,
380                offset=offset,
381                order_by=order_by,
382            ),
383        )
384        return tracks, albums, artists
385
386    async def get_genres_for_media_item(
387        self, media_type: MediaType, media_id: str | int
388    ) -> list[Genre]:
389        """Return all genres mapped to a given media item.
390
391        :param media_type: The type of media item.
392        :param media_id: The database ID of the media item.
393        """
394        media_id_int = int(media_id)
395        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
396        query = (
397            f"EXISTS(SELECT 1 FROM {gm} gm "
398            f"WHERE gm.genre_id = {self.db_table}.item_id "
399            "AND gm.media_type = :media_type AND gm.media_id = :media_id)"
400        )
401        return await self.get_library_items_by_query(
402            extra_query_parts=[query],
403            extra_query_params={
404                "media_type": media_type.value,
405                "media_id": media_id_int,
406            },
407        )
408
409    async def get_radio_mode_base_tracks(
410        self,
411        item_id: str,
412        provider_instance_id_or_domain: str | None = None,
413        preferred_provider_instances: list[str] | None = None,
414    ) -> list[Track]:
415        """Return base tracks for genre radio mode."""
416        provider = provider_instance_id_or_domain or "library"
417        item = await self.get(item_id, provider)
418        return await self.radio_mode_base_tracks(item, preferred_provider_instances)
419
420    async def get_overview(
421        self,
422        item_id: str,
423        provider_instance_id_or_domain: str | None = None,
424        limit: int = 25,
425    ) -> list[RecommendationFolder]:
426        """Return overview rows for a genre (all media types)."""
427        provider = provider_instance_id_or_domain or "library"
428        item = await self.get(item_id, provider)
429        db_id = int(item.item_id)
430        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
431        media_rows: list[tuple[MediaType, str]] = [
432            (MediaType.ARTIST, "Artists"),
433            (MediaType.ALBUM, "Albums"),
434            (MediaType.TRACK, "Tracks"),
435            (MediaType.PLAYLIST, "Playlists"),
436            (MediaType.RADIO, "Radio"),
437            (MediaType.PODCAST, "Podcasts"),
438            (MediaType.AUDIOBOOK, "Audiobooks"),
439        ]
440
441        async def _fetch_media_type(
442            media_type: MediaType, title: str
443        ) -> RecommendationFolder | None:
444            ctrl = self.mass.music.get_controller(media_type)
445            query = (
446                f"EXISTS(SELECT 1 FROM {gm} gm "
447                f"WHERE gm.media_id = {ctrl.db_table}.item_id "
448                "AND gm.media_type = :media_type "
449                "AND gm.genre_id = :genre_id)"
450            )
451            items = await ctrl.get_library_items_by_query(
452                extra_query_parts=[query],
453                extra_query_params={
454                    "genre_id": db_id,
455                    "media_type": media_type.value,
456                },
457                limit=limit,
458            )
459            if not items:
460                return None
461            return RecommendationFolder(
462                item_id=f"genre_{media_type.value}",
463                name=title,
464                provider="library",
465                items=UniqueList(items[:limit]),
466            )
467
468        results = await asyncio.gather(*[_fetch_media_type(mt, title) for mt, title in media_rows])
469        return [r for r in results if r is not None]
470
471    async def match_providers(self, db_item: Genre) -> None:
472        """No provider matching for genres at this time."""
473        return
474
475    async def restore_default_genres(self, full_restore: bool = False) -> list[Genre]:
476        """Restore default genres from genre_mapping.json.
477
478        :param full_restore: If True, delete all existing genres and recreate from defaults.
479                            If False (default), only add missing genres and ensure aliases exist.
480        """
481        if full_restore:
482            self.logger.warning("Performing FULL restore - deleting all existing genres")
483            await self.mass.music.database.delete(DB_TABLE_GENRE_MEDIA_ITEM_MAPPING)
484            await self.mass.music.database.delete(DB_TABLE_GENRES)
485            existing = set()
486        else:
487            rows = await self.mass.music.database.get_rows_from_query(
488                f"SELECT search_name FROM {DB_TABLE_GENRES}", limit=0
489            )
490            existing = {row["search_name"] for row in rows}
491
492        created_ids: list[int] = []
493        for entry in DEFAULT_GENRE_MAPPING:
494            name = entry.get("genre")
495            if not name:
496                continue
497            normalized = self._normalize_genre_name(name)
498            if not normalized:
499                continue
500            name_value, sort_name, search_name, search_sort_name = normalized
501            all_aliases = [name_value, *entry.get("aliases", [])]
502
503            # Partial restore: Ensure aliases are up to date
504            if search_name in existing:
505                if db_row := await self.mass.music.database.get_row(
506                    DB_TABLE_GENRES, {"search_name": search_name}
507                ):
508                    genre_id = int(db_row["item_id"])
509                    await self._ensure_aliases(genre_id, all_aliases)
510                continue
511
512            # Create new genre
513            translation_key = entry.get("translation_key")
514            icon_metadata = self._get_genre_icon_metadata(translation_key)
515            genre_id = await self.mass.music.database.insert(
516                DB_TABLE_GENRES,
517                {
518                    "name": name_value,
519                    "sort_name": sort_name,
520                    "translation_key": translation_key,
521                    "description": None,
522                    "favorite": 0,
523                    "metadata": serialize_to_json(icon_metadata.to_dict() if icon_metadata else {}),
524                    "external_ids": serialize_to_json(set()),
525                    "genre_aliases": serialize_to_json(all_aliases),
526                    "play_count": 0,
527                    "last_played": 0,
528                    "search_name": search_name,
529                    "search_sort_name": search_sort_name,
530                    "timestamp_added": UNSET,
531                },
532            )
533            created_ids.append(genre_id)
534            existing.add(search_name)
535
536        if full_restore:
537            await self._bulk_scan_media_genres()
538
539        if not created_ids:
540            return []
541        return [await self.get_library_item(item_id) for item_id in created_ids]
542
543    async def _bulk_scan_media_genres(self) -> None:
544        """Bulk-scan all media items and rebuild genre mappings using CTE.
545
546        Uses the same approach as the initial migration: extracts all unique genre names
547        from metadata.genres across all media tables, resolves them to genre IDs via alias
548        lookup, then does a single INSERT per media type using a CTE join.
549        """
550        db = self.mass.music.database
551
552        media_tables = (
553            (DB_TABLE_TRACKS, MediaType.TRACK),
554            (DB_TABLE_ALBUMS, MediaType.ALBUM),
555            (DB_TABLE_ARTISTS, MediaType.ARTIST),
556            (DB_TABLE_PLAYLISTS, MediaType.PLAYLIST),
557            (DB_TABLE_RADIOS, MediaType.RADIO),
558            (DB_TABLE_AUDIOBOOKS, MediaType.AUDIOBOOK),
559            (DB_TABLE_PODCASTS, MediaType.PODCAST),
560        )
561
562        # Build alias -> genre_ids lookup from all genres in the database.
563        # One alias can map to multiple genres (n:n relationship).
564        alias_to_genre: dict[str, list[int]] = {}
565        genre_rows = await db.get_rows_from_query(
566            f"SELECT item_id, genre_aliases FROM {DB_TABLE_GENRES}", limit=0
567        )
568        for row in genre_rows:
569            genre_id = int(row["item_id"])
570            aliases = json.loads(row["genre_aliases"]) if row["genre_aliases"] else []
571            for alias in aliases:
572                norm = create_safe_string(alias.strip(), True, True)
573                if norm:
574                    alias_to_genre.setdefault(norm, [])
575                    if genre_id not in alias_to_genre[norm]:
576                        alias_to_genre[norm].append(genre_id)
577
578        # Extract all unique raw genre names from metadata across all media tables
579        union_parts = [
580            f"SELECT DISTINCT TRIM(g.value) AS raw_name "
581            f"FROM {table}, json_each(json_extract({table}.metadata, '$.genres')) AS g "
582            f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
583            f"AND json_extract({table}.metadata, '$.genres') != '[]'"
584            for table, _ in media_tables
585        ]
586        unique_names_sql = " UNION ".join(union_parts)
587        rows = await db.get_rows_from_query(unique_names_sql, limit=0)
588        unique_raw_names = [row["raw_name"] for row in rows if row["raw_name"]]
589
590        self.logger.debug(
591            "Bulk genre scan - discovered %d unique genre names", len(unique_raw_names)
592        )
593
594        # Resolve each raw name to genre_ids via alias lookup.
595        # One raw name can map to multiple genres (n:n).
596        raw_name_to_genres: dict[str, list[int]] = {}
597        for raw_name in unique_raw_names:
598            norm = create_safe_string(raw_name.strip(), True, True)
599            if not norm:
600                continue
601            if norm in alias_to_genre:
602                raw_name_to_genres[raw_name] = alias_to_genre[norm]
603                self.logger.debug(
604                    "Bulk scan - resolved %r -> genre_ids %s (alias match)",
605                    raw_name,
606                    alias_to_genre[norm],
607                )
608            else:
609                resolved_ids = await self._find_genres_for_alias(raw_name)
610                if resolved_ids:
611                    raw_name_to_genres[raw_name] = resolved_ids
612                    alias_to_genre[norm] = resolved_ids
613                    self.logger.debug(
614                        "Bulk scan - resolved %r -> genre_ids %s (new genre)",
615                        raw_name,
616                        resolved_ids,
617                    )
618
619        self.logger.info(
620            "Bulk genre scan - resolved %d unique genre names", len(raw_name_to_genres)
621        )
622
623        # Add discovered raw names as aliases to their resolved genres so that
624        # future searches by raw name (e.g. "Synthpop") find the parent genre
625        # even when the stored alias differs (e.g. "synth-pop").
626        genre_new_aliases: dict[int, list[str]] = {}
627        for raw_name, gids in raw_name_to_genres.items():
628            for gid in gids:
629                genre_new_aliases.setdefault(gid, []).append(raw_name)
630        for gid, new_aliases in genre_new_aliases.items():
631            await self._ensure_aliases(gid, new_aliases)
632
633        # Build CTE with (raw_name, genre_id) pairs. One raw name can produce
634        # multiple rows when it maps to multiple genres (n:n).
635        if raw_name_to_genres:
636            cte_values = ", ".join(
637                f"(LOWER('{name.replace(chr(39), chr(39) + chr(39))}'), {gid})"
638                for name, gids in raw_name_to_genres.items()
639                for gid in gids
640            )
641            cte = f"WITH genre_lookup(raw_name, genre_id) AS (VALUES {cte_values})"
642
643            for table, media_type in media_tables:
644                full_query = (
645                    f"{cte} INSERT OR REPLACE INTO {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}"
646                    f"(genre_id, media_id, media_type, alias) "
647                    f"SELECT gl.genre_id, {table}.item_id, "
648                    f"'{media_type.value}', TRIM(g.value) "
649                    f"FROM {table}, "
650                    f"json_each(json_extract({table}.metadata, '$.genres')) AS g "
651                    f"JOIN genre_lookup gl ON gl.raw_name = LOWER(TRIM(g.value)) "
652                    f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
653                    f"AND json_extract({table}.metadata, '$.genres') != '[]'"
654                )
655                await db.execute(full_query)
656            await db.commit()
657
658        self.logger.info(
659            "Bulk genre scan completed - mapped %d unique names to genres",
660            len(raw_name_to_genres),
661        )
662
663    async def _bulk_scan_unmapped_genres(self) -> int:
664        """Scan only unmapped media items and create genre mappings using CTE.
665
666        Similar to _bulk_scan_media_genres but filters to items not yet in
667        genre_media_item_mapping. Used by the incremental scanner after syncs.
668
669        :return: Total number of items mapped.
670        """
671        db = self.mass.music.database
672        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
673
674        media_tables = (
675            (DB_TABLE_TRACKS, MediaType.TRACK),
676            (DB_TABLE_ALBUMS, MediaType.ALBUM),
677            (DB_TABLE_ARTISTS, MediaType.ARTIST),
678            (DB_TABLE_PLAYLISTS, MediaType.PLAYLIST),
679            (DB_TABLE_RADIOS, MediaType.RADIO),
680            (DB_TABLE_AUDIOBOOKS, MediaType.AUDIOBOOK),
681            (DB_TABLE_PODCASTS, MediaType.PODCAST),
682        )
683
684        # Build alias -> genre_ids lookup (n:n) from all genres in the database.
685        alias_to_genre: dict[str, list[int]] = {}
686        genre_rows = await db.get_rows_from_query(
687            f"SELECT item_id, genre_aliases FROM {DB_TABLE_GENRES}", limit=0
688        )
689        for row in genre_rows:
690            genre_id = int(row["item_id"])
691            aliases = json.loads(row["genre_aliases"]) if row["genre_aliases"] else []
692            for alias in aliases:
693                norm = create_safe_string(alias.strip(), True, True)
694                if norm:
695                    alias_to_genre.setdefault(norm, [])
696                    if genre_id not in alias_to_genre[norm]:
697                        alias_to_genre[norm].append(genre_id)
698
699        # Extract all unique raw genre names from media items.
700        # We don't filter by unmapped items here because a media item may
701        # have some genres mapped but not all (e.g. added a new genre tag).
702        union_parts = [
703            f"SELECT DISTINCT TRIM(g.value) AS raw_name "
704            f"FROM {table}, json_each(json_extract({table}.metadata, '$.genres')) AS g "
705            f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
706            f"AND json_extract({table}.metadata, '$.genres') != '[]'"
707            for table, _mtype in media_tables
708        ]
709        unique_names_sql = " UNION ".join(union_parts)
710        rows = await db.get_rows_from_query(unique_names_sql, limit=0)
711        unique_raw_names = [row["raw_name"] for row in rows if row["raw_name"]]
712
713        if not unique_raw_names:
714            return 0
715
716        self.logger.debug(
717            "Incremental genre scan - discovered %d unique genre names from unmapped items",
718            len(unique_raw_names),
719        )
720
721        # Resolve each raw name to genre_ids (n:n)
722        raw_name_to_genres: dict[str, list[int]] = {}
723        for raw_name in unique_raw_names:
724            norm = create_safe_string(raw_name.strip(), True, True)
725            if not norm:
726                continue
727            if norm in alias_to_genre:
728                raw_name_to_genres[raw_name] = alias_to_genre[norm]
729                self.logger.debug(
730                    "Scanner - resolved %r -> genre_ids %s (alias match)",
731                    raw_name,
732                    alias_to_genre[norm],
733                )
734            else:
735                resolved_ids = await self._find_genres_for_alias(raw_name)
736                if resolved_ids:
737                    raw_name_to_genres[raw_name] = resolved_ids
738                    alias_to_genre[norm] = resolved_ids
739                    self.logger.debug(
740                        "Scanner - resolved %r -> genre_ids %s (new genre)",
741                        raw_name,
742                        resolved_ids,
743                    )
744
745        if not raw_name_to_genres:
746            return 0
747
748        # Add discovered raw names as aliases to their resolved genres
749        genre_new_aliases: dict[int, list[str]] = {}
750        for raw_name, gids in raw_name_to_genres.items():
751            for gid in gids:
752                genre_new_aliases.setdefault(gid, []).append(raw_name)
753        for gid, new_aliases in genre_new_aliases.items():
754            await self._ensure_aliases(gid, new_aliases)
755
756        # Build CTE with n:n pairs and INSERT only for unmapped items
757        cte_values = ", ".join(
758            f"(LOWER('{name.replace(chr(39), chr(39) + chr(39))}'), {gid})"
759            for name, gids in raw_name_to_genres.items()
760            for gid in gids
761        )
762        cte = f"WITH genre_lookup(raw_name, genre_id) AS (VALUES {cte_values})"
763
764        count_before = await db.get_count(gm)
765        for table, media_type in media_tables:
766            full_query = (
767                f"{cte} INSERT OR IGNORE INTO {gm}"
768                f"(genre_id, media_id, media_type, alias) "
769                f"SELECT gl.genre_id, {table}.item_id, "
770                f"'{media_type.value}', TRIM(g.value) "
771                f"FROM {table}, "
772                f"json_each(json_extract({table}.metadata, '$.genres')) AS g "
773                f"JOIN genre_lookup gl ON gl.raw_name = LOWER(TRIM(g.value)) "
774                f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
775                f"AND json_extract({table}.metadata, '$.genres') != '[]' "
776                f"AND NOT EXISTS ("
777                f"SELECT 1 FROM {gm} ex "
778                f"WHERE ex.genre_id = gl.genre_id "
779                f"AND ex.media_id = {table}.item_id "
780                f"AND ex.media_type = '{media_type.value}')"
781            )
782            await db.execute(full_query)
783        await db.commit()
784        count_after = await db.get_count(gm)
785
786        return count_after - count_before
787
788    async def remove_item_from_library(self, item_id: str | int, recursive: bool = True) -> None:
789        """Delete genre record from the database."""
790        db_id = int(item_id)
791        await self.mass.music.database.delete(
792            DB_TABLE_GENRE_MEDIA_ITEM_MAPPING, {"genre_id": db_id}
793        )
794        await super().remove_item_from_library(item_id, recursive)
795
796    async def add_alias(self, genre_id: str | int, alias: str) -> Genre:
797        """Add an alias string to a genre.
798
799        :param genre_id: Database ID of the genre.
800        :param alias: Alias string to add.
801        """
802        db_id = int(genre_id)
803        genre = await self.get_library_item(db_id)
804        aliases = list(genre.genre_aliases) if genre.genre_aliases else []
805        aliases = self._dedup_aliases(aliases, [alias])
806        await self.mass.music.database.update(
807            self.db_table,
808            {"item_id": db_id},
809            {"genre_aliases": serialize_to_json(aliases)},
810        )
811        updated = await self.get_library_item(db_id)
812        self.mass.signal_event(EventType.MEDIA_ITEM_UPDATED, updated.uri, updated)
813        return updated
814
815    async def remove_alias(self, genre_id: str | int, alias: str) -> Genre:
816        """Remove an alias string from a genre.
817
818        :param genre_id: Database ID of the genre.
819        :param alias: Alias string to remove.
820        :raises ValueError: If trying to remove the genre's own name.
821        """
822        db_id = int(genre_id)
823        genre = await self.get_library_item(db_id)
824        if create_safe_string(alias, True, True) == create_safe_string(genre.name, True, True):
825            msg = (
826                f"Cannot remove self-alias '{alias}' from genre '{genre.name}'. "
827                f"Delete the genre instead."
828            )
829            raise ValueError(msg)
830        aliases = list(genre.genre_aliases) if genre.genre_aliases else []
831        alias_norm = create_safe_string(alias, True, True)
832        aliases = [a for a in aliases if create_safe_string(a, True, True) != alias_norm]
833        await self.mass.music.database.update(
834            self.db_table,
835            {"item_id": db_id},
836            {"genre_aliases": serialize_to_json(aliases)},
837        )
838        # Remove media mappings that were created via this alias (case-insensitive)
839        await self.mass.music.database.execute(
840            f"DELETE FROM {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING} "
841            "WHERE genre_id = :genre_id AND LOWER(alias) = LOWER(:alias)",
842            {"genre_id": db_id, "alias": alias},
843        )
844        updated = await self.get_library_item(db_id)
845        self.mass.signal_event(EventType.MEDIA_ITEM_UPDATED, updated.uri, updated)
846        return updated
847
848    async def add_media_mapping(
849        self, genre_id: str | int, media_type: MediaType, media_id: str | int, alias: str
850    ) -> None:
851        """Map a media item to a genre.
852
853        :param genre_id: Database ID of the genre.
854        :param media_type: Type of media item (track, album, artist).
855        :param media_id: Database ID of the media item.
856        :param alias: The alias string that caused this mapping.
857        """
858        await self.mass.music.database.insert(
859            DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
860            {
861                "genre_id": int(genre_id),
862                "media_id": int(media_id),
863                "media_type": media_type.value,
864                "alias": alias,
865            },
866            allow_replace=True,
867        )
868
869    async def remove_media_mapping(
870        self, genre_id: str | int, media_type: MediaType, media_id: str | int
871    ) -> None:
872        """Remove a media item mapping from a genre.
873
874        :param genre_id: Database ID of the genre.
875        :param media_type: Type of media item (track, album, artist).
876        :param media_id: Database ID of the media item.
877        """
878        await self.mass.music.database.delete(
879            DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
880            {
881                "genre_id": int(genre_id),
882                "media_id": int(media_id),
883                "media_type": media_type.value,
884            },
885        )
886
887    async def promote_alias_to_genre(self, genre_id: str | int, alias: str) -> Genre:
888        """Promote an alias to become a standalone genre.
889
890        Creates a new Genre with the alias's name, moves all media mappings
891        for that alias to the new genre, and removes the alias from the
892        original genre.
893
894        :param genre_id: Database ID of the source genre.
895        :param alias: The alias string to promote.
896        :return: The newly created Genre.
897        """
898        db_genre_id = int(genre_id)
899        source_genre = await self.get_library_item(db_genre_id)
900
901        if create_safe_string(alias, True, True) == create_safe_string(
902            source_genre.name, True, True
903        ):
904            msg = (
905                f"Cannot promote self-alias '{alias}'. "
906                f"This alias is the primary name for genre '{source_genre.name}'."
907            )
908            raise ValueError(msg)
909
910        # Create new genre with the alias as its name
911        new_genre = Genre(
912            item_id="0",
913            provider="library",
914            name=alias,
915            sort_name=alias,
916            translation_key=None,
917            provider_mappings=set(),
918            favorite=False,
919        )
920        created_genre = await self.add_item_to_library(new_genre)
921        new_genre_id = int(created_genre.item_id)
922
923        # Move media mappings from source genre to new genre for this alias (case-insensitive)
924        await self.mass.music.database.execute(
925            f"UPDATE {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING} "
926            "SET genre_id = :new_id WHERE genre_id = :old_id AND LOWER(alias) = LOWER(:alias)",
927            {"new_id": new_genre_id, "old_id": db_genre_id, "alias": alias},
928        )
929
930        # Remove alias from source genre (normalized comparison)
931        alias_norm = create_safe_string(alias, True, True)
932        aliases = list(source_genre.genre_aliases) if source_genre.genre_aliases else []
933        aliases = [a for a in aliases if create_safe_string(a, True, True) != alias_norm]
934        await self.mass.music.database.update(
935            self.db_table,
936            {"item_id": db_genre_id},
937            {"genre_aliases": serialize_to_json(list(aliases))},
938        )
939
940        return await self.get_library_item(new_genre_id)
941
942    async def sync_media_item_genres(
943        self, media_type: MediaType, media_id: str | int, genre_names: set[str]
944    ) -> None:
945        """Sync genre mappings for a media item.
946
947        Ensures genre records exist and updates genre-media mappings.
948        Removes mappings that are no longer present in the incoming genre_names set.
949
950        :param media_type: The type of media item being synced.
951        :param media_id: The database ID of the media item.
952        :param genre_names: Set of genre names from the provider.
953        """
954        media_id_int = int(media_id)
955        gm = DB_TABLE_GENRE_MEDIA_ITEM_MAPPING
956
957        # Build target set: (genre_id, alias_name) from incoming names.
958        # One alias can map to multiple genres (n:n).
959        target_mappings: dict[int, str] = {}
960        for name in genre_names:
961            normalized = self._normalize_genre_name(name)
962            if not normalized:
963                continue
964            genre_ids = await self._find_genres_for_alias(normalized[0])
965            for gid in genre_ids:
966                if gid not in target_mappings:
967                    target_mappings[gid] = normalized[0]
968
969        # Get current genre_ids from database
970        rows = await self.mass.music.database.get_rows_from_query(
971            f"SELECT genre_id FROM {gm} WHERE media_type = :media_type AND media_id = :media_id",
972            {"media_type": media_type.value, "media_id": media_id_int},
973            limit=0,
974        )
975        existing_genre_ids = {int(row["genre_id"]) for row in rows}
976
977        to_add = set(target_mappings.keys()) - existing_genre_ids
978        to_remove = existing_genre_ids - set(target_mappings.keys())
979
980        for genre_id in to_remove:
981            await self.mass.music.database.delete(
982                gm,
983                {
984                    "genre_id": genre_id,
985                    "media_id": media_id_int,
986                    "media_type": media_type.value,
987                },
988            )
989
990        for genre_id in to_add:
991            await self.mass.music.database.insert(
992                gm,
993                {
994                    "genre_id": genre_id,
995                    "media_id": media_id_int,
996                    "media_type": media_type.value,
997                    "alias": target_mappings[genre_id],
998                },
999                allow_replace=True,
1000            )
1001
1002    async def _ensure_aliases(self, genre_id: int, aliases: list[str]) -> None:
1003        """Ensure a genre has all the specified aliases in its genre_aliases JSON.
1004
1005        :param genre_id: Database ID of the genre.
1006        :param aliases: List of alias strings that should be present.
1007        """
1008        genre = await self.get_library_item(genre_id)
1009        existing = list(genre.genre_aliases) if genre.genre_aliases else []
1010        merged = self._dedup_aliases(existing, aliases)
1011        if len(merged) != len(existing):
1012            await self.mass.music.database.update(
1013                self.db_table,
1014                {"item_id": genre_id},
1015                {"genre_aliases": serialize_to_json(merged)},
1016            )
1017
1018    async def _find_genres_for_alias(self, name: str) -> list[int]:
1019        """Find all genres that own the given alias name, or create a new genre.
1020
1021        An alias can map to multiple genres (n:n relationship). For example,
1022        "anime" could be an alias of both an "Anime" genre and an "Anime Music" genre.
1023        If no genre owns this alias, creates a new genre.
1024
1025        :param name: The alias name to find/create a genre for.
1026        :return: List of genre IDs (empty if name is invalid).
1027        """
1028        normalized = self._normalize_genre_name(name)
1029        if not normalized:
1030            return []
1031        name_value, sort_name, search_name, search_sort_name = normalized
1032
1033        async with self._db_add_lock:
1034            found_ids: list[int] = []
1035
1036            # Check if a genre exists with this name as its own name
1037            if db_row := await self.mass.music.database.get_row(
1038                DB_TABLE_GENRES, {"search_name": search_name}
1039            ):
1040                found_ids.append(int(db_row["item_id"]))
1041
1042            # Search genre_aliases JSON columns (case-insensitive, can match multiple)
1043            rows = await self.mass.music.database.get_rows_from_query(
1044                f"SELECT item_id FROM {DB_TABLE_GENRES} "
1045                "WHERE EXISTS("
1046                "SELECT 1 FROM json_each(genre_aliases) "
1047                "WHERE LOWER(json_each.value) = LOWER(:alias_name)"
1048                ")",
1049                {"alias_name": name_value},
1050                limit=0,
1051            )
1052            for row in rows:
1053                gid = int(row["item_id"])
1054                if gid not in found_ids:
1055                    found_ids.append(gid)
1056
1057            # Also check via normalized comparison (create_safe_string).
1058            # This catches genres that stages 1-2 miss due to normalization
1059            # differences, e.g. genre A has "synthpop", genre B has "synth-pop"
1060            # — both normalize to "synthpop" but LOWER can't bridge the gap.
1061            all_genres = await self.mass.music.database.get_rows_from_query(
1062                f"SELECT item_id, genre_aliases FROM {DB_TABLE_GENRES}", limit=0
1063            )
1064            for row in all_genres:
1065                aliases = json.loads(row["genre_aliases"]) if row["genre_aliases"] else []
1066                for alias in aliases:
1067                    if create_safe_string(alias.strip(), True, True) == search_name:
1068                        gid = int(row["item_id"])
1069                        if gid not in found_ids:
1070                            found_ids.append(gid)
1071
1072            if found_ids:
1073                return found_ids
1074
1075            # No genre owns this alias — create a new one
1076            new_id = await self.mass.music.database.insert(
1077                DB_TABLE_GENRES,
1078                {
1079                    "name": name_value,
1080                    "sort_name": sort_name,
1081                    "description": None,
1082                    "favorite": 0,
1083                    "metadata": serialize_to_json({}),
1084                    "external_ids": serialize_to_json(set()),
1085                    "genre_aliases": serialize_to_json([name_value]),
1086                    "play_count": 0,
1087                    "last_played": 0,
1088                    "search_name": search_name,
1089                    "search_sort_name": search_sort_name,
1090                    "timestamp_added": UNSET,
1091                },
1092            )
1093            return [new_id]
1094
1095    async def _get_description(self, item_id: int) -> str | None:
1096        if db_row := await self.mass.music.database.get_row(DB_TABLE_GENRES, {"item_id": item_id}):
1097            return dict(db_row).get("description")
1098        return None
1099
1100    @staticmethod
1101    def _normalize_genre_name(raw_name: str) -> tuple[str, str, str, str] | None:
1102        """Normalize a raw genre name for storage and search.
1103
1104        :param raw_name: Raw genre name from provider.
1105        :return: Tuple of (name, sort_name, search_name, search_sort_name) or None if invalid.
1106        """
1107        name = raw_name.strip()
1108        if not name:
1109            return None
1110        sort_name = name
1111        search_name = create_safe_string(name, True, True)
1112        if not search_name:
1113            return None
1114        search_sort_name = create_safe_string(sort_name or "", True, True)
1115        return name, sort_name, search_name, search_sort_name
1116
1117    def _on_sync_tasks_updated(self, _event: MassEvent) -> None:
1118        """Trigger genre mapping scan when all sync tasks complete."""
1119        if self.mass.music.in_progress_syncs or self._scanner_running:
1120            return
1121        self._scanner_running = True
1122        self.mass.create_task(self._scan_genre_mappings())
1123
1124    async def _scan_genre_mappings(self) -> None:
1125        """Scan media items with metadata.genres and map them to genres.
1126
1127        Triggered after library sync completes or via manual API call.
1128        Callers must set _scanner_running = True before calling this method.
1129        """
1130        # Double-check syncs haven't started since the event was dispatched
1131        if self.mass.music.in_progress_syncs:
1132            self.logger.debug("Syncs still in progress, deferring genre scan")
1133            self._scanner_running = False
1134            return
1135        self._last_scan_time = time.time()
1136
1137        try:
1138            self.logger.debug("Starting genre mapping scan...")
1139            self._last_scan_mapped = await self._bulk_scan_unmapped_genres()
1140            self.logger.info(
1141                "Genre mapping scan completed: %d items mapped (%.1fs)",
1142                self._last_scan_mapped,
1143                time.time() - self._last_scan_time,
1144            )
1145
1146        except Exception as err:
1147            self.logger.error(
1148                "Error in genre mapping scanner: %s",
1149                str(err),
1150                exc_info=err if self.logger.isEnabledFor(logging.DEBUG) else None,
1151            )
1152
1153        finally:
1154            self._scanner_running = False
1155
1156    async def scan_mappings(self) -> dict[str, Any]:
1157        """Manually trigger a genre mapping scan (admin only).
1158
1159        :return: Status information about the scan trigger.
1160        """
1161        if self._scanner_running:
1162            return {
1163                "status": "already_running",
1164                "message": "Genre mapping scanner is already running",
1165            }
1166
1167        self._scanner_running = True
1168        self.mass.create_task(self._scan_genre_mappings())
1169
1170        return {
1171            "status": "triggered",
1172            "message": "Genre mapping scan triggered",
1173            "last_scan": self._last_scan_time,
1174        }
1175
1176    async def get_scanner_status(self) -> dict[str, Any]:
1177        """Get status of the genre mapping background scanner.
1178
1179        :return: Scanner status information.
1180        """
1181        return {
1182            "running": self._scanner_running,
1183            "last_scan_time": self._last_scan_time,
1184            "last_scan_ago_seconds": (
1185                int(time.time() - self._last_scan_time) if self._last_scan_time else None
1186            ),
1187            "last_scan_mapped": self._last_scan_mapped,
1188        }
1189