from apollo_utils.core.constants.dsp import DSP

AGGREGATE_TRACKS_TOP_PLAYLISTS_APPLE = """
SELECT DISTINCT
  h2.PLAYLIST_ID AS PlaylistId,
  h2.ISRC,
  h2.MARKET_CODE AS StoreFront,
  h2.DATE AS EntryDate,
  CASE WHEN h2.IsRight = 1 THEN h2.DATE ELSE h2.NEXTDATE END AS ExitDate
FROM (
  SELECT
    h1.PLAYLIST_ID,
    h1.ISRC,
    h1.MARKET_CODE,
    h1.DATE,
    h1.IsLeft,
    h1.IsRight,
    LEAD(h1.DATE, 1, h1.DATE) OVER (PARTITION BY h1.PLAYLIST_ID, h1.ISRC, h1.MARKET_CODE ORDER BY h1.DATE) AS NEXTDATE
  FROM (
    SELECT
      h.PLAYLIST_ID,
      h.ISRC,
      m.MARKET_CODE,
      h.DATE,
      CASE WHEN h.DATE < DATEADD(day, -2, LEAD(h.DATE, 1, DATE('2900-12-01')) OVER (
        PARTITION BY h.PLAYLIST_ID, h.ISRC, h.MARKET_ID ORDER BY h.DATE)
      ) THEN 1 ELSE 0 END AS IsRight,
      CASE WHEN h.DATE > DATEADD(day, 2, LAG(h.DATE,1 , DATE('1900-01-01')) OVER (
        PARTITION BY h.PLAYLIST_ID, h.ISRC, h.MARKET_ID ORDER BY h.DATE)
      ) THEN 1 ELSE 0 END AS IsLeft
    FROM {main_database}.{main_schema}.FACT_PLAYLIST_TRACK_HISTORY AS h
    JOIN {main_database}.{main_schema}.DIM_MARKET AS m
      ON m.MARKET_ID = h.MARKET_ID
    JOIN {top_database}.{top_schema}.APPLE_TOP_PLAYLISTS tp
      ON tp.PLAYLIST_ID = h.PLAYLIST_ID AND tp.COUNTRY_CODE = m.MARKET_CODE
    JOIN {main_database}.{main_schema}.DIM_PLAYLIST_EXTRA pe
      ON pe.PLAYLIST_ID = tp.PLAYLIST_ID AND pe.MARKET_ID = h.MARKET_ID AND NOT pe.IS_IGNORED AND NOT pe.IS_REMOVED
    WHERE h.DSP_ID = 2
      AND h.ISRC IS NOT NULL
      AND h.ISRC != ''
      AND tp.DATE = (SELECT MAX(tp1.DATE) FROM {top_database}.{top_schema}.APPLE_TOP_PLAYLISTS as tp1)
  ) AS h1
  WHERE h1.IsLeft = 1 OR h1.IsRight = 1
) AS h2
WHERE h2.IsLeft = 1
"""

AGGREGATE_TRACKS_TOP_PLAYLISTS_SPOTIFY = """
SELECT DISTINCT
  h2.PLAYLIST_ID AS PlaylistId,
  h2.ISRC,
  h2.DATE AS EntryDate,
  CASE WHEN h2.IsRight = 1 THEN h2.DATE ELSE h2.NEXTDATE END AS ExitDate
FROM (
  SELECT
    h1.PLAYLIST_ID,
    h1.ISRC,
    h1.DATE,
    h1.IsLeft,
    h1.IsRight,
    LEAD(h1.DATE, 1, h1.DATE) OVER (PARTITION BY h1.PLAYLIST_ID, h1.ISRC ORDER BY h1.DATE) AS NEXTDATE
  FROM (
    SELECT
      h.PLAYLIST_ID,
      h.ISRC,
      h.DATE,
      CASE WHEN h.DATE < DATEADD(day, -2, LEAD(h.DATE, 1, DATE('2900-12-01')) OVER (
        PARTITION BY h.PLAYLIST_ID, h.ISRC ORDER BY h.DATE)
      ) THEN 1 ELSE 0 END AS IsRight,
      CASE WHEN h.DATE > DATEADD(day, 2, LAG(h.DATE,1 , DATE('1900-01-01')) OVER (
        PARTITION BY h.PLAYLIST_ID, h.ISRC ORDER BY h.DATE)
      ) THEN 1 ELSE 0 END AS IsLeft
      FROM {main_database}.{main_schema}.FACT_PLAYLIST_TRACK_HISTORY AS h
      WHERE h.PLAYLIST_ID IN (
        SELECT tp.PLAYLIST_ID
        FROM {top_database}.{top_schema}.TOP_PLAYLIST tp
        JOIN {main_database}.{main_schema}.DIM_PLAYLIST_EXTRA pe
          ON pe.PLAYLIST_ID = tp.PLAYLIST_ID AND NOT pe.IS_IGNORED AND NOT pe.IS_REMOVED
        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'
          )
      )
      AND h.DSP_ID = 1
      AND h.ISRC IS NOT NULL
      AND h.ISRC != ''
  ) AS h1
  WHERE h1.IsLeft = 1 OR h1.IsRight = 1
) AS h2
WHERE h2.IsLeft = 1"""

AGGREGATE_TRACKS_TOP_PLAYLISTS = {
    DSP.APPLE.value: AGGREGATE_TRACKS_TOP_PLAYLISTS_APPLE,
    DSP.SPOTIFY.value: AGGREGATE_TRACKS_TOP_PLAYLISTS_SPOTIFY,
}