"""SQL queries."""

from constants.common import VENDOR_APPLE, VENDOR_SPOTIFY


TABLE_PLAYLIST_TOP = {
    VENDOR_APPLE: 'tblAppleMusicWeeklyTopPlaylist',
    VENDOR_SPOTIFY: 'tblSpotifyWeeklyTopPlaylist',
}

TABLE_HISTORY_TEMP = {
    VENDOR_APPLE: 'tblAppleMusicPlaylistTrackSummaryTemp',
    VENDOR_SPOTIFY: 'tblSpotifyPlaylistTrackSummaryTemp',
}

SQL_SELECT_MAX_DATE = """SELECT MAX(`Date`) FROM {top_playlists};"""

SQL_SELECT_APPLE_PLAYLISTS_NO_HISTORY = """
SELECT DISTINCT pl.PlaylistId, pl.CountryCode
FROM tblAppleMusicWeeklyTopPlaylist pl
JOIN tblAppleMusicPlaylistTracklistHistory history
  ON history.PlaylistId = pl.PlaylistId AND history.StoreFront = pl.CountryCode
JOIN tblAppleMusicSong song
  ON song.Id = history.SongId AND song.Storefront = history.StoreFront
WHERE NOT EXISTS (
  SELECT 1
  FROM tblAppleMusicWeeklyTopPlaylistTrackSummary summary
  WHERE summary.PlaylistId = pl.PlaylistId AND summary.StoreFront = pl.CountryCode
) AND pl.PlaylistId != '' AND pl.Date = %s;
"""

SQL_SELECT_SPOTIFY_TOP_PLAYLISTS = """
SELECT DISTINCT p.PlaylistId
FROM (
  SELECT pl.PlaylistId
  FROM tblSpotifyWeeklyTopPlaylist pl
  WHERE pl.Date = %s
    AND pl.PlaylistId != ''
    AND LENGTH(pl.PlaylistId) = 22
  UNION
  SELECT pl.PlaylistId
  FROM tblSpotifyCustomTopPlaylist pl
) AS p;
"""

SQL_SELECT_SPOTIFY_PLAYLISTS_AGGREGATED = """
SELECT DISTINCT PlaylistId
FROM tblSpotifyWeeklyTopPlaylistTrackSummary
WHERE PlaylistId IN ({playlists});
"""

SQL_SELECT_SPOTIFY_PLAYLISTS_WITH_OLD_HISTORY = """
SELECT DISTINCT PlaylistId
FROM tblSpotifyPlaylistTrackListHistory2
WHERE PlaylistId IN ({playlists});
"""

SQL_SELECT_EXCESS_PLAYLISTS = {
    VENDOR_APPLE: """
SELECT DISTINCT summary.PlaylistId, summary.StoreFront
FROM tblAppleMusicWeeklyTopPlaylistTrackSummary summary
WHERE (summary.PlaylistId, summary.StoreFront) NOT IN (
  SELECT pl.PlaylistId, pl.CountryCode
  FROM tblAppleMusicWeeklyTopPlaylist pl
  WHERE pl.`Date` = %s
)
LIMIT {limit};
""",
    VENDOR_SPOTIFY: """
SELECT DISTINCT PlaylistId
FROM tblSpotifyWeeklyTopPlaylistTrackSummary
WHERE PlaylistId NOT IN (SELECT DISTINCT PlaylistId FROM tblSpotifyWeeklyTopPlaylist WHERE `Date` = %s)
  AND PlaylistId NOT IN (SELECT PlaylistId FROM tblSpotifyCustomTopPlaylist)
LIMIT {limit};
"""
}

SQL_DELETE_NON_TOP_PLAYLISTS = {
    VENDOR_APPLE: """
DELETE FROM tblAppleMusicWeeklyTopPlaylistTrackSummary
WHERE {filter};
""",
    VENDOR_SPOTIFY: """
DELETE FROM tblSpotifyWeeklyTopPlaylistTrackSummary
WHERE PlaylistId IN ({playlists});
"""
}

SQL_TEMPLATE_APPLE_FILTER_PlaylistId_StoreFront = "(PlaylistId = %s AND StoreFront = %s)"

SQL_TEMPLATE_APPLE_UPLOAD_FILTER_PlaylistId_StoreFront = "(PlaylistId = %s AND h.StoreFront = %s)"

SQL_UPLOAD_S3 = {
    VENDOR_APPLE: """
SELECT h.PlaylistId, s.ISRC, h.StoreFront, h.Date
FROM tblAppleMusicPlaylistTracklistHistory h
JOIN tblAppleMusicSong s ON s.Id = h.SongId AND s.Storefront = h.StoreFront
WHERE s.`ISRC` != '' AND ({filter})
INTO OUTFILE S3 '{s3_path}'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
""",
    VENDOR_SPOTIFY: """
SELECT h.PlaylistId, t.ISRC, h.Date
FROM tblSpotifyPlaylistTrackListHistory2 h
JOIN tblSpotifyTrack2 t ON t.TrackId = h.TrackId
WHERE PlaylistId IN ({playlists}) AND t.`ISRC` != ''
INTO OUTFILE S3 '{s3_path}'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
"""
}

SQL_DOWNLOAD_S3 = {
    VENDOR_APPLE: """
LOAD DATA FROM S3 PREFIX '{s3_path}' IGNORE
INTO TABLE tblAppleMusicWeeklyTopPlaylistTrackSummary
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(PlaylistId, ISRC, StoreFront, EntryDate, ExitDate);
""",
    VENDOR_SPOTIFY: """
LOAD DATA FROM S3 PREFIX '{s3_path}' IGNORE
INTO TABLE tblSpotifyWeeklyTopPlaylistTrackSummary
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(PlaylistId, ISRC, EntryDate, ExitDate);
"""
}

SQL_SELECT_PLAYLIST = {
    VENDOR_APPLE: """
SELECT DISTINCT PlaylistId, CountryCode
FROM tblAppleMusicWeeklyTopPlaylist
WHERE `Date` = %s AND (PlaylistId > %s OR (PlaylistId = %s AND CountryCode > %s))
ORDER BY PlaylistId, CountryCode
LIMIT {limit};
""",
    VENDOR_SPOTIFY: """
SELECT DISTINCT PlaylistId
FROM (
  SELECT PlaylistId
  FROM tblSpotifyCustomTopPlaylist
  UNION
  SELECT PlaylistId
  FROM tblSpotifyWeeklyTopPlaylist
  WHERE `Date` = %s AND LENGTH(PlaylistId) = 22
) AS pl
WHERE PlaylistId > %s
ORDER BY pl.PlaylistId
LIMIT {limit};
"""
}

SQL_CREATE_TEMP_TABLE = {
    VENDOR_APPLE: """
CREATE TABLE `{temp_table}` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `PlaylistId` varchar(40) NOT NULL,
  `ISRC` varchar(45) NOT NULL,
  `StoreFront` varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
""",
    VENDOR_SPOTIFY: """
CREATE TABLE `{temp_table}` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `PlaylistId` char(22) CHARACTER SET ascii NOT NULL,
  `ISRC` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
}

SQL_DROP_TEMP_TABLE = """DROP TABLE IF EXISTS `{history_temp}`;"""

SQL_TRUNCATE_TEMP_TABLE = """TRUNCATE TABLE `{history_temp}`;"""

SQL_COPY_HISTORY = {
    VENDOR_APPLE: """
INSERT INTO `{temp_table}` (`PlaylistId`, `ISRC`, `StoreFront`)
SELECT h.`PlaylistId`, s.`ISRC`, h.`StoreFront`
FROM `tblAppleMusicPlaylistTracklistHistory` h
JOIN tblAppleMusicSong s ON s.Id = h.SongId AND s.Storefront = h.StoreFront
WHERE h.`Date` = %s AND s.`ISRC` != ''
  AND ({filter});
""",
    VENDOR_SPOTIFY: """
INSERT INTO `{temp_table}` (`PlaylistId`, `ISRC`)
SELECT h.`PlaylistId`, t.`ISRC`
FROM `tblSpotifyPlaylistTrackListHistory2` h
JOIN tblSpotifyTrack2 t ON t.TrackId = h.TrackId
WHERE h.`Date` = %s AND t.`ISRC` != ''
  AND h.`PlaylistId` IN ({playlists});
"""
}

SQL_HISTORY_UPDATE = {
    VENDOR_APPLE: """
UPDATE tblAppleMusicWeeklyTopPlaylistTrackSummary ah
JOIN {temp_table} h
ON h.PlaylistId = ah.PlaylistId
  AND h.ISRC = ah.ISRC
  AND h.StoreFront = ah.StoreFront
SET ah.ExitDate = %s
WHERE ah.ExitDate >= %s
  AND ah.ExitDate <= %s
""",
    VENDOR_SPOTIFY: """
UPDATE tblSpotifyWeeklyTopPlaylistTrackSummary sh
JOIN {temp_table} h
ON h.PlaylistId = sh.PlaylistId
  AND h.ISRC = sh.ISRC
SET sh.ExitDate = %s
WHERE sh.ExitDate >= %s
  AND sh.ExitDate <= %s
"""
}

SQL_HISTORY_INSERT = {
    VENDOR_APPLE: """
INSERT INTO tblAppleMusicWeeklyTopPlaylistTrackSummary (PlaylistId, ISRC, StoreFront, EntryDate, ExitDate)
SELECT DISTINCT h.PlaylistId, h.ISRC, h.StoreFront, %s, %s
FROM {temp_table} h
LEFT JOIN tblAppleMusicWeeklyTopPlaylistTrackSummary ah
ON ah.PlaylistId = h.PlaylistId
  AND ah.ISRC = h.ISRC
  AND ah.StoreFront = h.StoreFront
  AND ah.ExitDate >= %s
  AND ah.EntryDate <= %s
WHERE ah.ISRC IS NULL
""",
    VENDOR_SPOTIFY: """
INSERT INTO tblSpotifyWeeklyTopPlaylistTrackSummary (PlaylistId, ISRC, EntryDate, ExitDate)
SELECT DISTINCT h.PlaylistId, h.ISRC, %s, %s
FROM {temp_table} h
LEFT JOIN tblSpotifyWeeklyTopPlaylistTrackSummary sh
ON sh.PlaylistId = h.PlaylistId
  AND sh.ISRC = h.ISRC
  AND sh.ExitDate >= %s
  AND sh.EntryDate <= %s
WHERE sh.ISRC IS NULL
"""
}