insert_dapd_spotify_playlists = '''
INSERT INTO playlist(
    id,
    data_source_id,
    storefront_id,
    created_at,
    updated_at,
    playlist_type,
    name,
    username,
    label,
    expired_at,
    ttl_priority_id,
    is_personalized,
    is_ignored,
    save_tracklist,
    save_tracklist_history)
SELECT
    %(id)s,
    %(data_source_id)s,
    %(storefront_id)s,
    %(created_at)s,
    %(updated_at)s,
    %(playlist_type)s,
    %(name)s,
    %(username)s,
    %(label)s,
    %(expired_at)s,
    %(ttl_priority_id)s,
    cast(%(is_personalized)s as bool),
    cast(%(is_ignored)s as bool),
    cast(%(save_tracklist)s as bool),
    cast(%(save_tracklist_history)s as bool)
WHERE NOT EXISTS
    (SELECT 1 FROM playlist WHERE id=%(id)s);
'''


insert_dapd_spotify_playlists_countries = '''
update
    playlist
set
    storefront_id = c.storefront_id
from
    (values {}) as c(playlist_id, storefront_id)
where
    c.playlist_id = playlist.id;
'''


select_dapd_spotify_playlists = '''
SELECT
    p.PlaylistId AS id,
    p.Name AS name,
    p.UpdateDate as updated_at,
    CASE
        WHEN (
            p.CountryCode IS NULL
            OR p.CountryCode = 'null')
        THEN
            'not_defined'
        WHEN
            p.CountryCode = '_gl'
        THEN
            'global'
        WHEN
            p.CountryCode = ''
        THEN
            'empty'
        ELSE
            LOWER(p.CountryCode)
    END AS storefront_id,
    p.User as username,
    CASE
        WHEN (
            bu.CategoryID = 5
            AND (
                TRIM(p.Name) LIKE '%%Radio'
                OR tspp.PlaylistId IS NOT NULL))
        THEN
            'algorithmic'
        WHEN (
            bu.CategoryID = 5
            AND tspp.PlaylistId IS NULL)
        THEN
            'editorial'
        WHEN
            bu.CategoryID = 1
        THEN
            'sony-owned'
        ELSE
            'other'
    END AS playlist_type,
    CASE
        WHEN (
            top_playlists.PlaylistId IS NOT NULL
            AND bu.CategoryID in (1, 5))
            OR
            top_priority_playlists.PlaylistId IS NOT NULL
        THEN
            8
        WHEN
            bu.CategoryID in (1, 5)
        THEN
            7
        ELSE
            5
    END as ttl_priority_id,
    IF(tspp.PlaylistId is not null, true, false) as is_personalized,
    IF(ip.PlaylistId is not null, true, false) as is_ignored,
    p.SaveTracklist as save_tracklist,
    CASE
        WHEN (
            p.SaveTracklist IS TRUE
            AND (
                track_list_history.PlaylistId IS NOT NULL
                OR bu.CategoryID in (1, 5, 7)
            ))
        THEN
            TRUE
        ELSE
            FALSE
    END as save_tracklist_history
FROM
    tblSpotifyPlaylist AS p
LEFT JOIN (
    SELECT
        Username,
        CategoryID,
        CountryCode
    FROM
        BuzzUser
    WHERE
        BuzzUser.MusicServiceId = 1
    ) AS bu
ON
    bu.Username = p.User
LEFT JOIN
    tblSpotifyPersonalizedPlaylist tspp
ON
    tspp.PlaylistId = p.PlaylistId
LEFT JOIN
    tblPlaylistIgnoredPlaylist ip
ON
    p.PlaylistUri = ip.PlaylistId
LEFT JOIN (
    SELECT
        DISTINCT PlaylistId
    FROM
        tblSpotifyWeeklyTopPlaylist tswtp
    WHERE
        `Date` = (SELECT max(`Date`) FROM tblSpotifyWeeklyTopPlaylist tswtp)) as top_playlists
ON
    p.PlaylistId = top_playlists.PlaylistId
LEFT JOIN
    tblSpotifyPlaylistTracklistHistoryMode track_list_history
ON
    p.PlaylistId = track_list_history.PlaylistId
LEFT JOIN
    tblSpotifyTopPriorityPlaylists top_priority_playlists
ON
    p.PlaylistId = top_priority_playlists.PlaylistId
ORDER BY
    p.PlaylistId
LIMIT
    50000
OFFSET
    %(offset)s
'''

select_dapd_spotify_playlists_countries = '''
SELECT
    p.PlaylistId AS playlist_id,
    CASE
        WHEN (
            p.CountryCode IS NOT NULL
            AND p.CountryCode not in ('null', ''))
        THEN
            p.CountryCode
        ELSE
            bu.CountryCode
    END as storefront_id
FROM
    tblSpotifyPlaylist AS p
LEFT JOIN
    BuzzUser AS bu
ON
    bu.Username = p.User
ORDER BY
    p.PlaylistId
LIMIT
    50000
OFFSET
    %(offset)s;
'''


select_dapd_spotify_playlists_count = '''
SELECT
    COUNT(*)
FROM
    tblSpotifyPlaylist
'''

select_dapd_spotify_playlists_countries_count = '''
SELECT
    count(*)
FROM
    tblSpotifyPlaylist AS p
LEFT JOIN
    BuzzUser AS bu
ON
    bu.Username = p.User
ORDER BY
    p.PlaylistId;
'''


select_dapd_apple_playlists = '''
SELECT
    p.Id AS id,
    COALESCE(sf.Name, p.Name) AS name,
    sf.Storefront AS storefront_id,
    p.LatestUpdate as updated_at,
    p.PlaylistType as playlist_type,
    CASE
        WHEN (
            top_playlists.PlaylistId IS NOT NULL
            AND buzz_user.CategoryID in (1, 11))
        THEN
            8
        WHEN (
            sf.Storefront in %(top_storefronts)s
            AND most_streamed.ContainerId IS NOT NULL)
        THEN
            11
        WHEN
            buzz_user.CategoryID = 11
            AND sf.Storefront in %(major_storefronts)s
        THEN
            10
        ELSE
            5
    END as ttl_priority_id,
    buzz_user.Username as username
FROM
    tblAppleMusicPlaylist AS p
LEFT JOIN
    tblAppleMusicPlaylistStorefrontData sf
ON
    p.Id = sf.PlaylistId
LEFT JOIN
    BuzzUser buzz_user
ON
    buzz_user.Username = p.CuratorId
LEFT JOIN (
    SELECT
        PlaylistId,
        CountryCode
    FROM
        tblAppleMusicWeeklyTopPlaylist weekly_top
    WHERE
        `Date` = (SELECT max(`Date`) FROM tblAppleMusicWeeklyTopPlaylist weekly_top)
    GROUP BY
        PlaylistId,
        CountryCode) as top_playlists
ON
    p.Id = top_playlists.PlaylistId
    AND sf.Storefront = top_playlists.CountryCode
LEFT JOIN (
    SELECT
        DISTINCT ContainerId
    FROM
        tblAppleMusicContainerStreamSummary
    WHERE
        CountryCode = 'global'
    ORDER BY
        Streams28Days desc
    LIMIT
        1000) most_streamed
ON
    p.Id = most_streamed.ContainerId
WHERE
    sf.Storefront IS NOT NULL
    AND buzz_user.MusicServiceId = 6
ORDER BY
    p.Id
LIMIT
    10000
OFFSET
    %(offset)s
'''  # noqa: E501

select_dapd_apple_playlists_2 = '''
SELECT
    p.Id AS id,
    COALESCE(sf.Name, p.Name) AS name,
    sf.Storefront AS storefront_id,
    p.LatestUpdate as updated_at,
    p.PlaylistType as playlist_type,
    CASE
        WHEN (
            sf.Storefront in %(top_storefronts)s
            AND most_streamed.ContainerId IS NOT NULL)
        THEN
            11
        ELSE
            5
    END as ttl_priority_id,
    null as username
FROM
    tblAppleMusicPlaylist AS p
LEFT JOIN
    tblAppleMusicPlaylistStorefrontData sf
ON
    p.Id = sf.PlaylistId
LEFT JOIN (
    SELECT
        PlaylistId,
        CountryCode
    FROM
        tblAppleMusicWeeklyTopPlaylist weekly_top
    WHERE
        `Date` = (SELECT max(`Date`) FROM tblAppleMusicWeeklyTopPlaylist weekly_top)
    GROUP BY
        PlaylistId,
        CountryCode) as top_playlists
ON
    p.Id = top_playlists.PlaylistId
    AND sf.Storefront = top_playlists.CountryCode
LEFT JOIN (
    SELECT
        DISTINCT ContainerId
    FROM
        tblAppleMusicContainerStreamSummary
    WHERE
        CountryCode = 'global'
    ORDER BY
        Streams28Days desc
    LIMIT
        1000) most_streamed
ON
    p.Id = most_streamed.ContainerId
WHERE
    sf.Storefront IS NOT NULL
    AND p.CuratorId IS NULL
ORDER BY
    p.Id
LIMIT
    10000
OFFSET
    %(offset)s
'''  # noqa: E501


select_dapd_apple_playlists_count = '''
SELECT
    COUNT(*)
FROM
    tblAppleMusicPlaylist p
LEFT JOIN
    tblAppleMusicPlaylistStorefrontData sf
ON
    p.Id = sf.PlaylistId
LEFT JOIN
    BuzzUser bu
ON
    p.CuratorId = bu.Username
WHERE
    sf.Storefront IS NOT NULL
    AND bu.MusicServiceId = 6
'''


select_dapd_apple_playlists_count_2 = '''
SELECT
    COUNT(*)
FROM
    tblAppleMusicPlaylist p
LEFT JOIN
    tblAppleMusicPlaylistStorefrontData sf
ON
    p.Id = sf.PlaylistId
WHERE
    sf.Storefront IS NOT NULL
    AND p.CuratorId IS NULL
'''


select_dapd_spotify_markets = '''
SELECT
    storefront.id
FROM
    storefront
JOIN
    data_source
ON
    data_source.id = storefront.data_source_id
WHERE
    data_source.name = 'spotify'
'''


insert_dapd_spotify_storefront = '''
INSERT INTO
    storefront (id, data_source_id, name, created_at, ttl_id, expired_at)
VALUES (
    %(market)s,
    (SELECT data_source.id FROM data_source WHERE data_source.name='spotify'),
    %(market)s,
    now(),
    20,
    now())
'''


select_dapd_apple_music_markets = '''
SELECT
    storefront.id
FROM
    storefront
JOIN
    data_source
ON
    data_source.id = storefront.data_source_id
WHERE
    data_source.name = 'apple_music'
'''


insert_dapd_apple_music_storefront = '''
INSERT INTO
    storefront (id, data_source_id, name, created_at, ttl_id, expired_at)
VALUES (
    %(market)s,
    (SELECT data_source.id FROM data_source WHERE data_source.name='apple_music'),
    %(market)s,
    now(),
    20,
    now())
'''