from apollo_utils.core.constants.dsp import DSP

WEEKLY_TOP_PLAYLISTS_APPLE = """
SELECT tp.PLAYLIST_ID, tp.DATE, tp.COUNTRY_CODE, tp.RANK
FROM {top_database}.{top_schema}.APPLE_TOP_PLAYLISTS tp
WHERE tp.DATE = (SELECT MAX(tp1.DATE) FROM {top_database}.{top_schema}.APPLE_TOP_PLAYLISTS as tp1)
"""

WEEKLY_TOP_PLAYLISTS_SPOTIFY = """
SELECT tp.PLAYLIST_ID, tp.DATE, tp.COUNTRY_CODE, tp.RANK
FROM {top_database}.{top_schema}.TOP_PLAYLIST tp
WHERE tp.TYPE = 'TOP'
  AND tp.DSP = 'spotify'
  AND tp.DATE = (
    SELECT MAX(tp1.DATE)
    FROM {top_database}.{top_schema}.TOP_PLAYLIST AS tp1
    WHERE tp1.TYPE = 'TOP' AND tp1.DSP = 'spotify'
  )
"""

WEEKLY_TOP_PLAYLISTS = {DSP.APPLE.value: WEEKLY_TOP_PLAYLISTS_APPLE, DSP.SPOTIFY.value: WEEKLY_TOP_PLAYLISTS_SPOTIFY}