"""GenreSubgenreMapping.

Singleton class that supports retrieval and memoized storage of
genre/subgenre mappings given a store id.
"""

from feed_sender.util import mysql


class GenreSubgenreMapping(object):
    """SQL to retrieve genre-subgenre mapping."""

    def __init__(self):
        """Initialize class to maintain the memoized mapping."""
        self._genre_subgenre_mapping = dict()

    def fetch_genre_mapping(self, store_id):
        """Get Genre Subgenre mapping.

        Returns:
            dict: dictionary of genre and subgenre with
                key genre_id_subgenre_id. Example:
                {'4_494': {'subgenre': None, 'genre': 'Punk'}}
        """
        if not self._genre_subgenre_mapping:
            sql = """
                SELECT g.genre_id, sg.orchard_id AS subgenre_id,
                  dmg.dms_master_genre, dms.dms_master_subgenre
                FROM dms_subgenre_mapping dsm
                INNER JOIN dms_master_subgenre dms
                  ON dsm.dms_master_subgenre_id = dms.dms_master_subgenre_id
                INNER JOIN dms_master_genre dmg
                  ON dmg.dms_master_genre_id = dms.dms_master_genre_id
                INNER JOIN subgenre sg
                  ON sg.orchard_id = dsm.orchard_subgenre_id
                INNER JOIN genre g ON sg.genre_id = g.genre_id
                WHERE
                  dmg.customer_master_master_id = :customer_master_master_id
                UNION
                SELECT g.genre_id, sg.orchard_id AS subgenre_id,
                  dmg.dms_master_genre, NULL AS dms_master_subgenre
                FROM dms_genre_mapping dgm
                INNER JOIN dms_master_genre dmg
                  ON dgm.dms_master_genre_id = dmg.dms_master_genre_id
                INNER JOIN subgenre sg
                  ON sg.orchard_id = dgm.orchard_subgenre_id
                INNER JOIN genre g ON sg.genre_id = g.genre_id
                WHERE customer_master_master_id = :customer_master_master_id
                ORDER BY genre_id, subgenre_id
            """

            results = mysql.fetchall(
                sql, customer_master_master_id=store_id)

            for result in results:
                key = '{}_{}'.format(
                    result.get('genre_id'), result.get('subgenre_id'))
                self._genre_subgenre_mapping[key] = {
                    'genre': result.get('dms_master_genre'),
                    'subgenre': result.get('dms_master_subgenre')
                }

        return self._genre_subgenre_mapping