using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MySqlConnector;
using PetaPoco;
using PetaPoco.Business;
using Sony.Filtr.AppleMusic.Data;
using Sony.Filtr.AppleMusic.Data.Internal;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.DistributedCaching;
using Sony.Filtr.Utility;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.AppleMusic.Playlists
{
    public class AppleMusicPlaylistManager
    {
        public const string DefaultStorefrontForTrackList = "us";

        public static readonly string[] MajorStorefronts = new string[] { "us", "gb", "au", "ca", "de", "fr" };

        private readonly DistributedCacheHandler _distributedCacheHandler;

        private const string _cacheKeysCacheKey = "playlists_cache_keys_list";
        private const string _allPlaylistsCacheKey = "playlists_cache_key";

        public AppleMusicPlaylistManager(DistributedCacheHandler distributedCacheHandler)
        {
            _distributedCacheHandler = distributedCacheHandler;
        }

        public async Task<HashSet<string>> GetExistingPlaylistIdsAsync()
        {
            var result = new HashSet<string>();

            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = new MySqlCommand("SELECT Id FROM tblAppleMusicPlaylist", connection);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        result.Add(reader.GetString(0));
                    }
                }
            }

            return result;
        }

        public async Task<PaginatedContent<AppleMusicPlaylist>> GetPlaylistsAsync(int? buzzCategory = null, int? buzzUserId = null, string buzzCountryCode = null, string storefront = null, int? limit = null, int offset = 0)
        {
            MySqlCommand cmd;

            var limitString = offset + (limit.HasValue ? ", " + limit.Value : string.Empty);

            if (!buzzCategory.HasValue && !buzzUserId.HasValue && string.IsNullOrWhiteSpace(buzzCountryCode))
            {
                var sql = $"SELECT SQL_CALC_FOUND_ROWS p.Id, COALESCE(ps.name, p.Name) as name, COALESCE(ps.artwork, p.Artwork) as artwork, PlaylistType,COALESCE(awcp.CuratorId, p.CuratorId) as CuratorId, Removed, COALESCE(awcp.CuratorType, c.CuratorType) as CuratorType, p.LatestUpdate, p.AppleMusicUpdate " +
                          "FROM tblAppleMusicPlaylist AS p " +
                          "LEFT JOIN tblAppleMusicWhitelistedCuratedPlaylists as awcp on awcp.PlaylistId = p.Id " +
                          "LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId AND ps.storefront=@storefront " +
                          "LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.Id AND ip.musicServiceId=@appleMusicServiceId " +
                          "WHERE p.Removed = 0 " +
                          (limit.HasValue ? $"LIMIT {limitString}" : string.Empty);

                cmd = new MySqlCommand(sql);
            }
            else
            {
                var sql = $"SELECT SQL_CALC_FOUND_ROWS p.Id, COALESCE(ps.name, p.Name) as name, COALESCE(ps.artwork, p.Artwork) as artwork, PlaylistType,COALESCE(awcp.CuratorId, p.CuratorId) as CuratorId, Removed, COALESCE(awcp.CuratorType, c.CuratorType) as CuratorType, p.LatestUpdate, p.AppleMusicUpdate " +
                          "FROM tblAppleMusicPlaylist AS p " +
                          "LEFT JOIN tblAppleMusicWhitelistedCuratedPlaylists as awcp on awcp.PlaylistId = p.Id " +
                          "LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId AND ps.storefront=@storefront " +
                          "LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.Id AND ip.musicServiceId = @appleMusicServiceId " +
                          "INNER JOIN( " +
                          "SELECT u.CountryCode, c.Id AS cId FROM tblAppleMusicCurator AS c " +
                          "INNER JOIN BuzzUser AS u ON c.Id = u.Username " +
                          "WHERE (@countryCode IS NULL OR u.CountryCode = @countryCode) " +
                          "AND (@buzzUserId IS NULL OR u.ID = @buzzUserId) " +
                          "AND (@buzzCategory IS NULL OR u.CategoryId = @buzzCategory) " +
                          ") AS buzz ON COALESCE(awcp.CuratorId,p.CuratorId) = buzz.cId " +
                          "WHERE p.Removed = 0 " +
                          (limit.HasValue ? $"LIMIT {limitString}" : string.Empty);

                cmd = new MySqlCommand(sql);

                cmd.Parameters.AddWithValue("@countryCode", buzzCountryCode);
                cmd.Parameters.AddWithValue("@buzzUserId", buzzUserId);
                cmd.Parameters.AddWithValue("@buzzCategory", buzzCategory);
            }

            cmd.Parameters.AddWithValue("@appleMusicServiceId", (int)MusicService.AppleMusic);
            cmd.Parameters.AddWithValue("@storefront", storefront);

            var playlists = await GetPaginatedPlaylistsFromDbAsync(cmd, limit, offset);

            return playlists;
        }

        public async Task<PaginatedContent<AppleMusicPlaylist>> GetPlaylistsMostStreamedAsync(string storefronts, int? limit = null, int offset = 0)
        {
            MySqlCommand cmd;

            var limitString = offset + (limit.HasValue ? ", " + limit.Value : string.Empty);


            var sql = $"SELECT SQL_CALC_FOUND_ROWS p.Id, COALESCE(ps.name, p.Name) as name, " +
                      "COALESCE(ps.artwork, p.Artwork) as artwork, PlaylistType, " +
                      "COALESCE(awcp.CuratorId, p.CuratorId) as CuratorId, Removed, " +
                      "COALESCE(awcp.CuratorType, c.CuratorType) as CuratorType, p.LatestUpdate, p.AppleMusicUpdate " +
                      "FROM tblAppleMusicPlaylist AS p " +
                      "LEFT JOIN tblAppleMusicWhitelistedCuratedPlaylists as awcp on awcp.PlaylistId = p.Id " +
                      "LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId AND ps.storefront = @storefrontS " +
                      "LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                      "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.Id " +
                      "INNER JOIN tblAppleMusicContainerStreamSummary AS amcs on amcs.ContainerId = p.Id " +
                      "WHERE amcs.CountryCode = 'global' AND p.Removed = 0 " +
                      "ORDER by Streams28Days desc " +
                      (limit.HasValue ? $"LIMIT {limitString}" : string.Empty);

            cmd = new MySqlCommand(sql);

            cmd.Parameters.AddWithValue("@storefrontS", storefronts);

            var playlists = await GetPaginatedPlaylistsFromDbAsync(cmd, limit, offset);

            return playlists;
        }

        public List<AppleMusicWhitelistedCuratedPlaylist> GetAppleMusicWhitelistedCuratedPlaylists()
        {
            var whitelistedPlaylists = PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicWhitelistedCuratedPlaylist>();
            return whitelistedPlaylists;
        }

        public async Task<List<AppleMusicPlaylist>> GetPlaylistsByIds(IEnumerable<string> ids)
        {
            var playlists = new List<AppleMusicPlaylist>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {

                var sql = $"SELECT {PlaylistDatabaseFields} " +
                          "FROM tblAppleMusicPlaylist AS p " +
                          "LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId " +
                          "LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                          $"WHERE p.Id IN ({Maybe.ToCommaSeparated(ids)})";

                var cmd = new MySqlCommand(sql, conn);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var playlist = BuildPlaylistFromDb<AppleMusicPlaylist>(reader);

                    playlists.Add(playlist);
                }
            }

            return playlists;
        }

        public async Task<List<PlaylistCountryOverview>> GetPlaylistCountryOverviewAsync(StaticBuzzCategory buzzCategoryId)
        {
            var countryOverviews = new List<PlaylistCountryOverview>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT b.countryCode, COUNT(*) as playlistCount " +
                                   "FROM tblAppleMusicPlaylist AS p " +
                                   "LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                                   "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.Id AND ip.musicServiceId = @appleMusicServiceId " +
                                   "INNER JOIN BuzzUser AS b ON CAST(c.Id as char(500)) = b.Username AND b.musicServiceId = @appleMusicServiceId " +
                                   "WHERE b.CategoryId = @buzzCategoryId " +
                                   "AND p.Removed = 0 " +
                                   "GROUP BY b.countryCode ";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@appleMusicServiceId", MusicService.AppleMusic);
                cmd.Parameters.AddWithValue("@buzzCategoryId", (int)buzzCategoryId);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var countryOverview = new PlaylistCountryOverview()
                    {
                        CountryCode = reader.GetString("countryCode"),
                        PlaylistCount = reader.GetIntOrFallback("playlistCount", 0),
                    };

                    countryOverviews.Add(countryOverview);
                }

                reader.Close();
            }

            return countryOverviews;
        }

        public async Task<AppleMusicPlaylistWithTrackData> GetPlaylistsWithTrackAsync(string storefront, IEnumerable<string> isrcs)
        {
            var sql = $@"
SELECT 
	SQL_CALC_FOUND_ROWS DISTINCT {PlaylistDatabaseFields},
	MIN(tl.position) as position, 
    tl.Added, 
    tl.Storefront,  
    tl.PreviousPosition, 
    tl.LatestPositionChange,
	(SELECT COUNT(*) FROM tblAppleMusicPlaylistTracklist AS ip WHERE ip.playlistid=p.id AND ip.Storefront = tl.storefront) as trackCount,
	(SELECT MAX(added) FROM tblAppleMusicPlaylistTracklist AS ip WHERE ip.playlistid=p.id AND ip.Storefront = tl.storefront) as tracksLatestAdded,
    s.isrc
FROM tblAppleMusicPlaylist AS p
LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId AND ps.storefront=@storefront
LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id
LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.Id AND ip.musicServiceId=@appleMusicServiceId
INNER JOIN tblAppleMusicPlaylistTracklist as tl ON p.Id = tl.playlistId
INNER JOIN tblAppleMusicSong AS s ON s.id = tl.songId AND s.storefront = tl.storefront
WHERE p.Removed = 0 AND s.isrc IN ({Maybe.ToCommaSeparated(isrcs)}) AND (@storefront IS NULL OR tl.Storefront = @storefront)
group by p.Id, tl.Storefront, s.isrc;";

            var cmd = new MySqlCommand(sql);

            cmd.Parameters.AddWithValue("@storefront", storefront);
            cmd.Parameters.AddWithValue("@appleMusicServiceId", (int)MusicService.AppleMusic);

            var playlists = new PaginatedContent<AppleMusicPlaylistWithTrack>();

            var playlistsId = new HashSet<string>();

            var storefronts = new HashSet<string>();
            if (!string.IsNullOrWhiteSpace(storefront))
                storefronts.Add(storefront);

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                cmd.Connection = conn;

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var playlistId = reader.GetString("Id");

                        var currentPlaylist = playlists.Items.FirstOrDefault(p => p.Id.Equals(playlistId, StringComparison.InvariantCultureIgnoreCase));

                        if (currentPlaylist == null)
                        {
                            currentPlaylist = BuildPlaylistFromDb<AppleMusicPlaylistWithTrack>(reader);
                            currentPlaylist.Countries = new List<AppleMusicTracklistWithTrackInfo>();
                            playlists.Items.Add(currentPlaylist);
                            playlistsId.Add(currentPlaylist.Id);
                        }

                        string dbStorefront = reader.GetString("Storefront").ToLowerInvariant();
                        storefronts.Add(dbStorefront);

                        currentPlaylist.Countries.Add(new AppleMusicTracklistWithTrackInfo
                        {
                            Added = reader.GetUtcDateTimeOrDefault("Added"),
                            TrackPosition = reader.GetInt32("position"),
                            PlaylistTrackCount = reader.GetInt32("trackCount"),
                            Storefront = dbStorefront,
                            PreviousPosition = reader.GetIntOrDefault("PreviousPosition"),
                            LatestPositionChange = reader.GetUtcDateTimeOrDefault("LatestPositionChange"),
                            TrackLatestAdded = reader.GetUtcDateTimeOrDefault("tracksLatestAdded"),
                            Isrc = reader.GetString("isrc")
                        });
                    }
                }

                using (var countCommand = new MySqlCommand("Select FOUND_ROWS()", conn))
                {
                    var totalCount = (long)(await countCommand.ExecuteScalarAsync());

                    playlists.Pagination = new Pagination()
                    {
                        Limit = (int)totalCount,
                        Offset = 0,
                        Total = (int)totalCount
                    };
                }
            }
            var result = new AppleMusicPlaylistWithTrackData
            {
                Playlists = playlists,
                Storefronts = storefronts,
                PlaylistsId = playlistsId
            };

            return result;
        }

        private string GetPlaylistsCacheKey(int? buzzCategory, int? buzzUserId, string buzzCountryCode, int? limit, int offset)
        {
            var cacheKey = _allPlaylistsCacheKey;

            if (buzzCategory.HasValue)
                cacheKey += "_" + buzzCategory.Value;

            if (buzzUserId.HasValue)
                cacheKey += "_" + buzzUserId.Value;

            if (!string.IsNullOrWhiteSpace(buzzCountryCode))
                cacheKey += "_" + buzzCountryCode;

            if (limit.HasValue)
                cacheKey += "_" + limit.Value;

            if (offset > 0)
                cacheKey += "_" + offset;

            return cacheKey;
        }

        private void AddPlaylistsCache(string cacheKey, List<AppleMusicPlaylist> playlists)
        {
            var cachedValues = _distributedCacheHandler.Get(_cacheKeysCacheKey) as Dictionary<string, List<AppleMusicPlaylist>>;

            if (cachedValues == null)
            {
                cachedValues = new Dictionary<string, List<AppleMusicPlaylist>>();
            }

            if (cachedValues.ContainsKey(cacheKey))
            {
                cachedValues[cacheKey] = playlists;
            }
            else
            {
                cachedValues.Add(cacheKey, playlists);
            }

            _distributedCacheHandler.Put(_cacheKeysCacheKey, cachedValues);
        }

        private List<AppleMusicPlaylist> GetPlaylistsFromCache(string cacheKey)
        {
            var cachedValues = _distributedCacheHandler.Get(_cacheKeysCacheKey) as Dictionary<string, List<AppleMusicPlaylist>>;

            if (cachedValues == null || !cachedValues.ContainsKey(cacheKey))
            {
                return null;
            }

            return cachedValues[cacheKey];
        }

        private void DropPlaylistsCache()
        {
            _distributedCacheHandler.Remove(_cacheKeysCacheKey);
        }

        public async Task<List<AppleMusicPlaylist>> GetNotUpdatedPlaylistsAsync(string storefront = null)
        {
            var playlists = new List<AppleMusicPlaylist>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $"SELECT {PlaylistDatabaseFields} " +
                          "FROM tblAppleMusicPlaylist AS p " +
                          "LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId AND ps.storefront=@storefront " +
                          "LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.Id AND ip.musicServiceId=@appleMusicServiceId " +
                          "WHERE p.LatestUpdate IS NULL ";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@appleMusicServiceId", (int)MusicService.AppleMusic);
                cmd.Parameters.AddWithValue("@storefront", storefront);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var playlist = BuildPlaylistFromDb<AppleMusicPlaylist>(reader);

                    playlists.Add(playlist);
                }
            }

            return playlists;
        }

        //public async Task<DateTime?> GetLatestHistoricTracklistDateAsync(string playlistId, string storefront)
        //{
        //    DateTime? maxDate;

        //    using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
        //    {
        //        var sql = "SELECT MAX(Date) FROM tblAppleMusicPlaylistTracklistHistoryDate " +
        //                  "WHERE PlaylistId = @playlistId AND Storefront = @storefront";

        //        var cmd = new MySqlCommand(sql, conn);

        //        cmd.Parameters.AddWithValue("@playlistId", playlistId);
        //        cmd.Parameters.AddWithValue("@storefront", storefront);

        //        maxDate = await cmd.ExecuteScalarAsync() as DateTime?;
        //    }

        //    return maxDate;
        //}

        private async Task<PaginatedContent<AppleMusicPlaylist>> GetPaginatedPlaylistsFromDbAsync(MySqlCommand cmd, int? limit, int offset)
        {
            var playlists = new PaginatedContent<AppleMusicPlaylist>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                cmd.Connection = conn;

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var playlist = BuildPlaylistFromDb<AppleMusicPlaylist>(reader);

                        playlists.Items.Add(playlist);
                    }
                }

                using (var countCommand = new MySqlCommand("Select FOUND_ROWS()", conn))
                {
                    var totalCount = (long)(await countCommand.ExecuteScalarAsync());

                    playlists.Pagination = new Pagination()
                    {
                        Limit = limit,
                        Offset = offset,
                        Total = (int)totalCount
                    };
                }
            }

            return playlists;
        }


        private static T BuildPlaylistFromDb<T>(MySqlDataReader reader)
        where T : AppleMusicPlaylist, new()
        {
            var playlist = new T
            {
                Id = reader.GetSafeString("Id"),
                Name = reader.GetSafeString("Name"),
                Artwork = reader.GetSafeString("Artwork"),
                PlaylistType = reader.GetSafeString("PlaylistType"),
                CuratorId = reader.GetLongOrDefault("CuratorId"),
                CuratorType = reader.GetSafeString("CuratorType"),
                Removed = reader.GetBoolean("Removed"),
                LatestUpdate = reader.GetUtcDateTimeOrDefault("LatestUpdate"),
                AppleMusicUpdate = reader.GetUtcDateTimeOrDefault("AppleMusicUpdate")
            };

            return playlist;
        }

        private string PlaylistDatabaseFields => "p.Id, COALESCE(ps.name, p.Name) as name, COALESCE(ps.artwork, p.Artwork) as artwork, PlaylistType, CuratorId, Removed, CuratorType, p.LatestUpdate, p.AppleMusicUpdate";

        public async Task<AppleMusicPlaylist> GetPlaylistAsync(string playlistId, string storefront = null)
        {
            AppleMusicPlaylist playlist = null;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $"SELECT {PlaylistDatabaseFields} " +
                          "FROM tblAppleMusicPlaylist AS p " +
                          "LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId AND ps.storefront=@storefront " +
                          "LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                          "WHERE p.Id = @playlistId";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@playlistId", playlistId);
                cmd.Parameters.AddWithValue("@storefront", storefront);

                var reader = await cmd.ExecuteReaderAsync();

                if (await reader.ReadAsync())
                {
                    playlist = BuildPlaylistFromDb<AppleMusicPlaylist>(reader);
                }
            }

            return playlist;
        }

        private async Task AddPlaylistAsync(AppleMusicPlaylist playlist)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                const string sql = "INSERT INTO tblAppleMusicPlaylist (Id, Name,Artwork, PlaylistType ,CuratorId, LatestUpdate) " +
                                   "VALUES(@playlistId, @playlistName, @artwork, @playlistType, @curatorId, @latestUpdate)";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@playlistId", playlist.Id);
                cmd.Parameters.AddWithValue("@playlistName", playlist.Name);
                cmd.Parameters.AddWithValue("@artwork", playlist.Artwork);
                cmd.Parameters.AddWithValue("@playlistType", playlist.PlaylistType);
                cmd.Parameters.AddWithValue("@curatorId", playlist.CuratorId);
                cmd.Parameters.AddWithValue("@latestUpdate", playlist.LatestUpdate);

                await cmd.ExecuteNonQueryAsync();
            }

            DropPlaylistsCache();
        }

        public async Task UpdatePlaylistAsync(AppleMusicPlaylist playlist)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = @"
UPDATE tblAppleMusicPlaylist SET Name = @playlistName, Artwork = @artwork, PlaylistType = @playlistType, CuratorId = @curatorId, Removed = @playlistRemoved, LatestUpdate = CURRENT_TIMESTAMP, AppleMusicUpdate = @appleMusicUpdate
WHERE Id = @playlistId";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@playlistId", playlist.Id);
                cmd.Parameters.AddWithValue("@playlistName", playlist.Name);
                cmd.Parameters.AddWithValue("@artwork", playlist.Artwork);
                cmd.Parameters.AddWithValue("@playlistType", playlist.PlaylistType);
                cmd.Parameters.AddWithValue("@curatorId", playlist.CuratorId);
                cmd.Parameters.AddWithValue("@playlistRemoved", playlist.Removed);
                cmd.Parameters.AddWithValue("@appleMusicUpdate", playlist.AppleMusicUpdate);

                await cmd.ExecuteNonQueryAsync();
            }

            DropPlaylistsCache();
        }

        public async Task AddOrUpdatePlaylistAsync(AppleMusicPlaylist playlist)
        {
            var existingPlaylist = await GetPlaylistAsync(playlist.Id);
            if (existingPlaylist == null) { await AddPlaylistAsync(playlist); }
            else { await UpdatePlaylistAsync(playlist); }
        }

        public async Task TrackPlaylistTracklistUpdate(string playlistId, string market)
        {
            using (var connection = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var insertCommand = connection.CreateCommand();

                insertCommand.CommandText = "INSERT INTO tblApplePlaylistStatistics (PlaylistId, Market, TracklistLastUpdate) VALUES (@playlistId, @market, NOW());";
                insertCommand.Parameters.AddWithValue("@playlistId", playlistId);
                insertCommand.Parameters.AddWithValue("@market", market);

                await insertCommand.ExecuteNonQueryAsync();
            }
        }

        public async Task SetPlaylistCurrentTracklistAndTrackStatisticsAsync(AppleMusicPlaylist playlist, List<AppleMusicPlaylistTrack> tracks, string storeFront)
        {
            await this.SetPlaylistCurrentTracklistAsync(playlist, tracks, storeFront);

            await this.TrackPlaylistTracklistUpdate(playlist.Id, storeFront);
        }


        private async Task SetPlaylistCurrentTracklistAsync(AppleMusicPlaylist playlist, List<AppleMusicPlaylistTrack> tracks, string storeFront)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var tran = await conn.BeginTransactionAsync(System.Data.IsolationLevel.ReadUncommitted))
                {
                    try
                    {
                        var delCmd = new MySqlCommand("DELETE FROM tblAppleMusicPlaylistTracklist WHERE PlaylistId=@playlistId AND Storefront = @storefront", conn, tran);

                        delCmd.Parameters.Add(new MySqlParameter("@playlistId", playlist.Id));
                        delCmd.Parameters.Add(new MySqlParameter("@storefront", storeFront));

                        await delCmd.ExecuteNonQueryAsync();

                        if (tracks != null && tracks.Any())
                        {
                            var sql = "INSERT INTO tblAppleMusicPlaylistTracklist (PlaylistId, Storefront, Position, SongId, PreviousPosition, LatestPositionChange, Added) VALUES ";

                            List<string> paramValues = new List<string>();

                            foreach (var track in tracks)
                            {
                                var paramValue = "(" + string.Join(",",
                                                     "'" + MySqlHelper.EscapeString(playlist.Id) + "'",
                                                     "'" + MySqlHelper.EscapeString(storeFront) + "'",
                                                     track.Position,
                                                     track.SongId,
                                                     track.PreviousPosition?.ToString() ?? "NULL",
                                                     track.LatestPositionChange.HasValue ? "'" + MySqlHelper.EscapeString(track.LatestPositionChange.Value.ToString("s", CultureInfo.GetCultureInfo("sv-SE"))) + "'" : "NULL",
                                                     track.Added.HasValue ? "'" + MySqlHelper.EscapeString(track.Added.Value.ToString("s", CultureInfo.GetCultureInfo("sv-SE"))) + "'" : "NULL"
                                                 ) + ")";

                                paramValues.Add(paramValue);
                            }

                            sql += string.Join(",", paramValues);

                            var cmd = new MySqlCommand(sql, conn, tran);
                            await cmd.ExecuteNonQueryAsync();
                        }

                        tran.Commit();
                    }
                    catch (Exception)
                    {
                        tran.Rollback();

                        throw;
                    }
                }
            }
        }

        public async Task AddPlaylistHistoricTracklistAsync(AppleMusicPlaylist playlist, List<AppleMusicPlaylistTrack> tracks, string storeFront, DateTime date)
        {
            if (tracks == null || !tracks.Any()) { return; }

            Func<MySqlConnection, MySqlTransaction, string, Task> insertHistoryForTable = async (connection, transaction, tableName) =>
            {
                var command = connection.CreateCommand();

                command.Transaction = transaction;

                var sql = $"INSERT INTO {tableName} (PlaylistId, Position, SongId, Date, StoreFront) VALUES {{0}} ";

                List<string> paramValues = new List<string>();

                foreach (var track in tracks)
                {
                    var paramValue = "(" + string.Join(",",
                                         "'" + MySqlHelper.EscapeString(playlist.Id) + "'",
                                         track.Position,
                                         track.SongId,
                                         "'" + MySqlHelper.EscapeString(date.ToString("d", CultureInfo.GetCultureInfo("sv-SE"))) + "'",
                                         "'" + MySqlHelper.EscapeString(storeFront) + "'"
                                     ) + ")";

                    paramValues.Add(paramValue);
                }

                sql = string.Format(sql, string.Join(",", paramValues));

                command.CommandText = sql;

                await command.ExecuteNonQueryAsync();
            };

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var transaction = await conn.BeginTransactionAsync())
                {
                    await insertHistoryForTable(conn, transaction, "tblAppleMusicPlaylistTracklistHistory");

                    await insertHistoryForTable(conn, transaction, "tblAppleMusicPlaylistTracklistHistoryReduced2");

                    await SetPlaylistHistoricTracklistDateAsync(playlist, storeFront, date, conn, transaction);
                    transaction.Commit();
                }
            }
        }

        private async Task SetPlaylistHistoricTracklistDateAsync(AppleMusicPlaylist playlist, string storeFront, DateTime date, MySqlConnection connection, MySqlTransaction trans)
        {
            var sql = @"
INSERT INTO tblAppleMusicPlaylistTracklistHistoryDate (PlaylistId, Storefront, Date)
VALUES (@playlistId, @storefront, @date)
ON DUPLICATE KEY UPDATE Timestamp = CURRENT_TIMESTAMP";

            var cmd = new MySqlCommand(sql, connection);
            cmd.Transaction = trans;

            cmd.Parameters.AddWithValue("@playlistId", playlist.Id);
            cmd.Parameters.AddWithValue("@storefront", storeFront);
            cmd.Parameters.AddWithValue("@date", date);

            await cmd.ExecuteNonQueryAsync();
        }

        public async Task SetPlaylistHistoricTracklistDateAsync(AppleMusicPlaylist playlist, string storeFront, DateTime date)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                await this.SetPlaylistHistoricTracklistDateAsync(playlist, storeFront, date, conn, null);
            }
        }

        public async Task<Dictionary<string, List<DateTime>>> GetPlaylistHistoricTracklistDatesAsync(string playlistId)
        {
            var playlistDatesPerStorefront = new Dictionary<string, List<DateTime>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT Storefront, Timestamp FROM tblAppleMusicPlaylistTracklistHistoryDate WHERE PlaylistId = @playlistId ORDER BY Date DESC";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@playlistId", playlistId);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var storefront = reader.GetString("Storefront");
                    var date = reader.GetUtcDateTime("Timestamp");

                    if (!playlistDatesPerStorefront.ContainsKey(storefront))
                    {
                        playlistDatesPerStorefront.Add(storefront, new List<DateTime>());
                    }

                    playlistDatesPerStorefront[storefront].Add(date);
                }
            }

            return playlistDatesPerStorefront;
        }

        public async Task<List<AppleMusicPlaylistSong>> GetPlaylistHistoricTracklistAsync(string playlistId, string storefront, DateTime date)
        {
            var tracklist = new List<AppleMusicPlaylistSong>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT Position, h.SongId, s.Name, s.ArtistId, s.ArtistName, s.AlbumId, s.ArtworkUrl, s.ComposerName, s.TrackNumber, s.Duration, s.DiscNumber, s.Storefront, s.ISRC, a.ReleaseDate as albumReleaseDate, s.ReleaseDate as trackReleaseDate, a.Name as albumName " +
                                   "FROM tblAppleMusicPlaylistTracklistHistory AS h " +
                                   "LEFT JOIN tblAppleMusicSong AS s ON s.Id = h.SongId AND s.Storefront = @storefront " +
                                   "LEFT JOIN tblAppleMusicAlbum AS a ON a.Id = s.AlbumId AND a.Storefront = s.Storefront " +
                                   "WHERE PlaylistID = @playlistId " +
                                   "AND Date = @date AND h.Storefront = @storefront";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@playlistId", playlistId);
                cmd.Parameters.AddWithValue("@storefront", storefront);
                cmd.Parameters.AddWithValue("@date", date);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var playlistSong = new AppleMusicPlaylistSong()
                        {
                            Id = reader.GetLong("SongId"),
                            AlbumId = reader.GetLongOrFallback("AlbumId", 0),
                            ArtistId = reader.GetLongOrFallback("ArtistId", 0),
                            ArtistName = reader.GetString("ArtistName"),
                            ArtworkUrl = reader.GetString("ArtworkUrl"),
                            ComposerName = reader.GetString("ComposerName"),
                            DiscNumber = reader.GetIntOrFallback("DiscNumber", 0),
                            Duration = reader.GetIntOrFallback("Duration", 0),
                            ISRC = reader.GetString("ISRC"),
                            Name = reader.GetString("Name"),
                            Position = reader.GetIntOrFallback("Position", 0),
                            TrackNumber = reader.GetIntOrFallback("TrackNumber", 0),
                            Storefront = reader.GetString("Storefront"),
                            AlbumReleaseDate = reader.GetDateTimeOrDefault("albumReleaseDate"),
                            ReleaseDate = reader.GetDateTimeOrDefault("trackReleaseDate"),
                            AlbumName = reader.GetString("albumName")
                        };

                        tracklist.Add(playlistSong);
                    }
                }
            }

            return tracklist;
        }


        public async Task<List<AppleMusicPlaylistSong>> GetPlaylistCurrentTracklistAsync(string playlistId, string storefront)
        {
            var tracklist = new List<AppleMusicPlaylistSong>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = @"
SELECT Position, h.SongId, s.Name, s.ArtistId, s.ArtistName, s.AlbumId, s.ArtworkUrl, s.ComposerName, s.TrackNumber, s.Duration, s.DiscNumber, s.Storefront, s.ISRC, s.ReleaseDate,
h.PreviousPosition, h.LatestPositionChange, h.Added, a.ReleaseDate as albumReleaseDate, a.Name as albumName
FROM tblAppleMusicPlaylistTracklist AS h
LEFT JOIN tblAppleMusicSong AS s ON s.Id = h.SongId AND s.Storefront = @storefront
LEFT JOIN tblAppleMusicAlbum AS a ON a.Id = s.AlbumId AND a.Storefront = s.Storefront
WHERE PlaylistID = @playlistId AND h.Storefront = @storefront
ORDER BY Position ASC";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@playlistId", playlistId);
                cmd.Parameters.AddWithValue("@storefront", storefront);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var playlistSong = new AppleMusicPlaylistSong()
                        {
                            Id = reader.GetLong("SongId"),
                            AlbumId = reader.GetLongOrFallback("AlbumId", 0),
                            ArtistId = reader.GetLongOrFallback("ArtistId", 0),
                            ArtistName = reader.GetString("ArtistName"),
                            ArtworkUrl = reader.GetString("ArtworkUrl"),
                            ComposerName = reader.GetString("ComposerName"),
                            DiscNumber = reader.GetIntOrFallback("DiscNumber", 0),
                            Duration = reader.GetIntOrFallback("Duration", 0),
                            ISRC = reader.GetString("ISRC"),
                            Name = reader.GetString("Name"),
                            Position = reader.GetIntOrFallback("Position", 0),
                            TrackNumber = reader.GetIntOrFallback("TrackNumber", 0),
                            Storefront = reader.GetString("Storefront"),
                            PreviousPosition = reader.GetIntOrDefault("PreviousPosition"),
                            LatestPositionChange = reader.GetDateTimeOrDefault("LatestPositionChange"),
                            Added = reader.GetUtcDateTimeOrDefault("Added"),
                            ReleaseDate = reader.GetDateTimeOrDefault("ReleaseDate"),
                            AlbumReleaseDate = reader.GetDateTimeOrDefault("albumReleaseDate"),
                            AlbumName = reader.GetString("albumName")
                        };

                        tracklist.Add(playlistSong);
                    }
                }
            }

            return tracklist;
        }

        public async Task<Dictionary<string, List<long>>> GetSongIdsWithoutInformationByStorefrontAsync()
        {
            var songIdsByStorefront = new Dictionary<string, List<long>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT h.SongId, h.Storefront " +
                                   "FROM tblAppleMusicPlaylistTracklist AS h " +
                                   "LEFT JOIN tblAppleMusicSong AS s ON s.Id = h.SongId AND s.Storefront = h.storefront " +
                                   "WHERE s.Id IS NULL " +
                                   "GROUP BY h.SongId, h.Storefront";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var songId = reader.GetInt64(0);
                            var storefront = reader.GetString(1);

                            if (string.IsNullOrWhiteSpace(storefront))
                                continue;

                            storefront = storefront.ToLowerInvariant();

                            if (!songIdsByStorefront.ContainsKey(storefront))
                                songIdsByStorefront.Add(storefront, new List<long>());

                            songIdsByStorefront[storefront].Add(songId);
                        }
                    }
                }
            }

            return songIdsByStorefront;
        }

        public async Task<List<AppleMusicPlaylist>> GetNonAppleCuratedPlaylistWithoutTracklistAsync(DateTime currentTracklistDate, string storefront = null)
        {
            var playlists = new List<AppleMusicPlaylist>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $"SELECT  p.Id, COALESCE(ps.name, p.Name) as name, COALESCE(ps.artwork, p.Artwork) as artwork, PlaylistType,COALESCE(awcp.CuratorId, p.CuratorId) as CuratorId, Removed, COALESCE(awcp.CuratorType, c.CuratorType) as CuratorType, p.LatestUpdate, p.AppleMusicUpdate FROM tblAppleMusicPlaylist p " +
                          "LEFT JOIN tblAppleMusicWhitelistedCuratedPlaylists as awcp on awcp.PlaylistId = p.Id " +
                          "LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId AND ps.storefront=@storefront " +
                          "LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                          "LEFT JOIN(SELECT CAST(Username AS char(20)) AS Username, CategoryId FROM BuzzUser WHERE MusicServiceId = @appleMusicServiceId) AS u ON p.CuratorId = u.Username " +
                          "WHERE (u.CategoryId IS NULL OR u.CategoryId <> @appleCuratorBuzzCategory) AND (p.LatestUpdate IS NULL OR p.LatestUpdate < @latestDate) ";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@appleMusicServiceId", (int)MusicService.AppleMusic);
                cmd.Parameters.AddWithValue("@storefront", storefront);
                cmd.Parameters.AddWithValue("@latestDate", currentTracklistDate);
                cmd.Parameters.AddWithValue("@appleCuratorBuzzCategory", (int)StaticBuzzCategory.AppleCurator);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var playlist = BuildPlaylistFromDb<AppleMusicPlaylist>(reader);

                    playlists.Add(playlist);
                }
            }

            return playlists;
        }

        public async Task<IEnumerable<AppleMusicPreviousPlaylist>> GetPreviousPlaylistsAsync(IEnumerable<string> isrcs, bool excludeCurrentPlaylists, string storefront = null)
        {
            var previousPlaylists = new List<AppleMusicPreviousPlaylist>();

            var sqlBuilder = new StringBuilder();

            if (excludeCurrentPlaylists)
            {
                sqlBuilder.Append($"select {PlaylistDatabaseFields}, h.Storefront, MIN(Date) as FirstDate, MAX(DATE) as LatestDate, s.isrc " +
                      $"FROM tblAppleMusicPlaylist AS p " +
                      $"LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId ");

                if (!string.IsNullOrWhiteSpace(storefront))
                    sqlBuilder.Append($"AND ps.storefront = '{Maybe.ToSingleString(storefront)}' ");
                else
                    sqlBuilder.Append($"AND ps.storefront = NULL ");

                sqlBuilder.Append("LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                      "INNER JOIN tblAppleMusicPlaylistTracklistHistory AS h ON p.Id = h.PlaylistId " +
                      "INNER JOIN tblAppleMusicSong AS s ON h.SongId = s.Id AND h.StoreFront = s.Storefront " +
                      "LEFT JOIN tblAppleMusicPlaylistTracklist AS t ON h.PlaylistId = t.PlaylistId AND h.StoreFront = t.Storefront AND h.SongId = t.SongId " +
                      $"WHERE s.ISRC IN ({Maybe.ToCommaSeparated(isrcs)}) AND t.PlaylistId IS NULL " +
                      "GROUP BY h.PlaylistId, h.Storefront, s.isrc " +
                      "ORDER BY LatestDate");
            }
            else
            {
                // Same as above but skips a join with playlisttracklist table
                sqlBuilder.Append($"select {PlaylistDatabaseFields}, h.Storefront, MIN(Date) as FirstDate, MAX(DATE) as LatestDate, s.isrc " +
                      $"FROM tblAppleMusicPlaylist AS p " +
                      $"LEFT JOIN tblAppleMusicPlaylistStorefrontData AS ps ON p.Id = ps.playlistId ");

                if (!string.IsNullOrWhiteSpace(storefront))
                    sqlBuilder.Append($"AND ps.storefront = '{Maybe.ToSingleString(storefront)}' ");
                else
                    sqlBuilder.Append($"AND ps.storefront = NULL ");

                sqlBuilder.Append("LEFT JOIN tblAppleMusicCurator AS c ON p.CuratorId = c.Id " +
                      "INNER JOIN tblAppleMusicPlaylistTracklistHistory AS h ON p.Id = h.PlaylistId " +
                      "INNER JOIN tblAppleMusicSong AS s ON h.SongId = s.Id AND h.StoreFront = s.Storefront " +
                      $"WHERE s.ISRC IN ({Maybe.ToCommaSeparated(isrcs)}) " +
                      "GROUP BY h.PlaylistId, h.Storefront, s.ISRC " +
                      "ORDER BY LatestDate");
            }

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                string sql = sqlBuilder.ToString();

                var cmd = new MySqlCommand(sql, conn);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var playlistFromDb = BuildPlaylistFromDb<AppleMusicPreviousPlaylist>(reader);

                    var previousPlaylist = previousPlaylists.FirstOrDefault(p => p.Id.Equals(playlistFromDb.Id, StringComparison.InvariantCulture));

                    if (previousPlaylist == null)
                    {
                        previousPlaylist = playlistFromDb;
                        playlistFromDb.Countries = new List<AppleMusicPreviousTracklistOccurance>();
                        previousPlaylists.Add(previousPlaylist);
                    }

                    var storeFront = reader.GetString("Storefront");
                    var isrc = reader.GetString("isrc");
                    var latestDate = reader.GetDateTime("LatestDate");
                    var earliestDate = reader.GetDateTime("FirstDate");
                    var days = (latestDate - earliestDate).Days + 1; //+1 since we want the inclusive count.

                    previousPlaylist.Countries.Add(new AppleMusicPreviousTracklistOccurance()
                    {
                        Storefront = storeFront,
                        DaysInPlaylist = days,
                        LatestDate = latestDate,
                        EarliestDate = earliestDate,
                        Isrc = isrc
                    });
                }
            }

            return previousPlaylists;
        }

        public async Task<List<AppleMusicHistoricTracklistPosition>> GetHistoricTrackPositionsAsync(string playlistId, string isrc, string storefront, DateTime startDate, DateTime endDate)
        {
            var trackPositions = new List<AppleMusicHistoricTracklistPosition>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = @"
SELECT h.Position, d.Timestamp, COUNT(h2.SongId) AS TrackCount, h.SongId, h.Storefront, s.ISRC
FROM tblAppleMusicPlaylistTracklistHistory AS h
INNER JOIN tblAppleMusicSong AS s ON h.SongId = s.Id AND h.StoreFront = s.Storefront
INNER JOIN tblAppleMusicPlaylistTracklistHistoryDate AS d ON h.playlistId = d.playlistId AND h.date = d.date AND d.Storefront = h.Storefront
INNER JOIN tblAppleMusicPlaylistTracklistHistory AS h2 ON h2.PlaylistId = h.playlistId AND h2.Date = h.Date AND h2.StoreFront = h.Storefront
WHERE s.ISRC = @isrc
AND h.PlaylistId = @playlistId
AND h.Storefront = @storefront
AND h.date BETWEEN @startDate AND @endDate
GROUP BY s.isrc, h.date
ORDER BY h.Date DESC";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@isrc", isrc);
                cmd.Parameters.AddWithValue("@storefront", storefront);
                cmd.Parameters.AddWithValue("@playlistId", playlistId);
                cmd.Parameters.AddWithValue("@startDate", startDate);
                cmd.Parameters.AddWithValue("@endDate", endDate);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    trackPositions.Add(new AppleMusicHistoricTracklistPosition
                    {
                        Position = reader.GetInt32("Position"),
                        Date = reader.GetDateTime("Timestamp"),
                        TotalTracks = reader.GetInt32("TrackCount"),
                        ISRC = reader.GetString("ISRC"),
                        SongId = reader.GetString("SongId")
                    });
                }
            }

            return trackPositions;
        }

        public async Task<IEnumerable<AppleMusicHistoricTrackSummary>> GetHistoricTrackSummariesAsync(IEnumerable<string> isrcs, IEnumerable<string> playlistIds, DateTime datePivot, string storefront = null)
        {
            if (!playlistIds.Any())
                return Enumerable.Empty<AppleMusicHistoricTrackSummary>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sbSql = new StringBuilder();

                // This also prevent from sql-injections
                var playlistsId = Maybe.ToCommaSeparated(playlistIds.Aggregate((a, b) => $"{a},{b}"));
                string isrcValue = Maybe.ToCommaSeparated(isrcs);

                sbSql.Append($@"SELECT 
                                h.PlaylistId,
                                h.Storefront,
                                hi.earliestDate as EarliestDate,
                                MIN(h.Position) as EarliestPosition, 
                                s.Isrc
                            FROM tblAppleMusicPlaylistTracklistHistory AS h
                                INNER JOIN ( 
                                       SELECT h.playlistId, h.storefront, MIN(date) as earliestDate
                                       FROM tblAppleMusicPlaylistTracklistHistory AS h 
                                       INNER JOIN tblAppleMusicSong AS s ON h.SongId = s.Id AND h.Storefront = s.Storefront WHERE s.isrc in ({isrcValue}) ");

                if (!string.IsNullOrWhiteSpace(storefront))
                    sbSql.Append($" AND h.Storefront = '{storefront}' ");

                sbSql.Append(
                          $@" GROUP BY h.playlistId, h.storefront, s.isrc
                          ) AS hi ON hi.playlistId = h.PlaylistId AND hi.storefront = h.storefront AND hi.earliestDate = h.date
                          INNER JOIN tblAppleMusicSong AS s ON h.SongId = s.Id AND h.Storefront = s.Storefront
                          WHERE h.PlaylistId IN ({playlistsId}) AND s.isrc in ({isrcValue})
                          GROUP BY h.PlaylistId, h.Storefront, s.isrc");

                string sql = sbSql.ToString();

                var data = await conn.QueryAsync<AppleMusicHistoricTrackSummary>(sql, commandType: System.Data.CommandType.Text);
                data = data.DateToUtc();

                return data;
            }
        }

        public async Task<List<AppleMusicHistoricTrackSummary>> GetHistoricTrackSummariesForPlaylistAsync(string playlistId, string storefront)
        {
            var results = new List<AppleMusicHistoricTrackSummary>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = @"
SELECT 
    s.isrc,
    h.Storefront,
    MIN(hi.earliestDate) AS earliestDate,
    MIN(h.Position) as position
FROM
    tblAppleMusicPlaylistTracklistHistory AS h
        INNER JOIN
    (SELECT 
        h.playlistId,
            songIds.Id AS songId,
            songIds.Isrc,
            h.storefront,
            MIN(h.date) AS earliestDate
    FROM
        tblAppleMusicPlaylistTracklistHistory AS h
    INNER JOIN (SELECT DISTINCT
        song2.Id, song2.ISRC
    FROM
        tblAppleMusicSong song2
    WHERE
        ISRC IN (SELECT 
                song.ISRC
            FROM
                tblAppleMusicSong song
            WHERE
                Id IN (SELECT 
                        list.SongId
                    FROM
                        tblAppleMusicPlaylistTracklist AS list
                    WHERE
                        list.PlaylistId = @playlistId
                            AND list.Storefront = @storefront)
                    AND song.Storefront = @storefront)
            AND song2.Storefront = @storefront) AS songIds ON h.SongId = songIds.Id
    WHERE
        h.PlaylistId = @playlistId
            AND h.Storefront = @storefront
    GROUP BY h.playlistId , h.storefront , songIds.Id , songIds.ISRC) AS hi ON hi.playlistId = h.PlaylistId
        AND hi.storefront = h.storefront
        AND hi.earliestDate = h.date
        AND hi.songId = h.songId
        INNER JOIN
    tblAppleMusicSong AS s ON h.SongId = s.Id
        AND h.Storefront = s.Storefront
GROUP BY s.isrc , h.Storefront";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@playlistId", playlistId);
                    cmd.Parameters.AddWithValue("@storefront", storefront);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var isrc = reader.GetString("Isrc");
                            var earliestDate = reader.GetUtcDateTime("EarliestDate");
                            var earliestPosition = reader.GetInt32("position");

                            results.Add(new AppleMusicHistoricTrackSummary
                            {
                                PlaylistId = playlistId,
                                Isrc = isrc,
                                Storefront = storefront,
                                EarliestDate = earliestDate,
                                EarliestPosition = earliestPosition,
                            });
                        }
                    }
                }
            }

            return results;
        }

        public List<AppleMusicHistoricTrackListDate> GetHistoricTrackListsForDateAsync(DateTime currentUtcDate)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicHistoricTrackListDate>("WHERE Date=@0", currentUtcDate);
        }

        public async Task SetStorefrontSpecificDataAsync(string storefront, string playlistId, string name, string artwork)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "INSERT IGNORE INTO tblAppleMusicPlaylistStorefrontData (PlaylistId, Storefront, Name, Artwork) VALUES (@playlistId, @storefront, @name, @artwork) ON DUPLICATE KEY UPDATE Name=@name, Artwork = @artwork";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@playlistId", playlistId);
                    cmd.Parameters.AddWithValue("@storefront", storefront);
                    cmd.Parameters.AddWithValue("@name", name);// playlist.attributes.name);
                    cmd.Parameters.AddWithValue("@artwork", artwork);// playlist.attributes.artwork?.url);

                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }

        [TableName("tblAppleMusicPlaylistTracklistHistoryDate")]
        public class AppleMusicHistoricTrackListDate
        {
            public string Storefront { get; set; }

            public string PlaylistId { get; set; }

            public DateTime Date { get; set; }
        }

        public class AppleMusicPlaylistWithTrackData
        {
            public PaginatedContent<AppleMusicPlaylistWithTrack> Playlists { get; set; }
            public IEnumerable<string> Storefronts { get; set; }
            public IEnumerable<string> PlaylistsId { get; set; }
        }
    }
}