from ..base import PrepareBase
from ..contsants import NON_SIGNED_TRACK_GENRE_TABLE


__all__ = ["TrackGenre"]


class TrackGenre(PrepareBase):
    depends_on = {
        "preparation.non_signed.track.prepare",
        "preparation.non_signed.artist.genre",
    }
    priority = 1
    table_name: str = NON_SIGNED_TRACK_GENRE_TABLE

    query: str = """
        SELECT ID, GENRE
        FROM (SELECT T_NON_SIGNED_TRACK.ID              AS ID,
                     IFF(V_CM_TAG.NAME <> 'Others' AND V_CM_TAG.NAME IS NOT NULL, V_CM_TAG.NAME,
                         NON_SIGNED_ARTIST_GENRE.GENRE) AS GENRE
              FROM DNA.DNA_PUBLIC.T_NON_SIGNED_TRACK AS T_NON_SIGNED_TRACK
                       LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.L_CM_TRACK_TAG2 AS L_CM_TRACK_TAG2
                                 ON T_NON_SIGNED_TRACK.ID = L_CM_TRACK_TAG2.CM_TRACK
                       LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_CM_TAG AS V_CM_TAG
                                 ON L_CM_TRACK_TAG2.CM_TAG = V_CM_TAG.ID
                                     AND L_CM_TRACK_TAG2.LINK_TYPE = 'cm_track_genre'
                       LEFT JOIN DNA.DNA_PUBLIC.NON_SIGNED_ARTIST_GENRE
                                 ON NON_SIGNED_ARTIST_GENRE.ID = T_NON_SIGNED_TRACK.ARTIST_ID AND
                                    (V_CM_TAG.NAME IS NULL OR V_CM_TAG.NAME = 'Others'))
        WHERE GENRE IS NOT NULL
        GROUP BY ID, GENRE
    """