using Dapper;
using MoreLinq;
using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Abstractions;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.DistributedCaching;
using Sony.Filtr.ErrorLogging;
using Sony.Filtr.Playlists.Extensions;
using Sony.Filtr.Playlists.Models;
using Sony.Filtr.Playlists.Spotify.Model;
using Sony.Filtr.SpotifyWebAPI.Model;
using Sony.Filtr.Utility;
using Sony.Filtr.Utility.Extensions;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sony.Filtr.Playlists.Spotify
{
    public class SpotifyPlaylistManager
    {
        private readonly IApplicationInstanceManager _applicationInstanceManager;
        private readonly SpotifyPlaylistFactory _playlistFactory;
        private readonly DistributedCacheHandler _distributedCacheHandler;

        public SpotifyPlaylistManager(IApplicationInstanceManager applicationInstanceManager, SpotifyPlaylistFactory playlistFactory, DistributedCacheHandler distributedCacheHandler)
        {
            _applicationInstanceManager = applicationInstanceManager;
            _playlistFactory = playlistFactory;
            _distributedCacheHandler = distributedCacheHandler;
        }

        public async Task<SpotifyTrackAnalyzeData[]> GetPlaylistTracksAnalyzeDataById(string playlistId)
        {
            var tracks = new Dictionary<string, SpotifyTrackAnalyzeData>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                string sql = @"
select 
	track.TrackId, 
    track.Name as TrackName, 
    track.ISRC, 
    track.Popularity,
    trackAlbum.TrackPosition,
    trackList.Added,
    album.Upc,
    album.SmallImageFilename,
    album.AlbumId,
    artist.Name as ArtistName,
    artist.ArtistId as ArtistId,
    trackList.PlaylistIndex
from tblSpotifyPlaylistTrackList2 trackList
inner join tblSpotifyTrack2 track on track.TrackId = trackList.TrackId
inner join tblSpotifyTrackAlbum trackAlbum on track.TrackId = trackAlbum.TrackId
inner join tblSpotifyAlbum album on trackAlbum.AlbumId = album.AlbumId
inner join tblSpotifyTrackArtist trackArtist on trackArtist.TrackId = track.TrackId
inner join tblSpotifyArtist artist on trackArtist.ArtistId = artist.ArtistId

where trackList.PlaylistId = @playlistId

order by track.TrackId, trackArtist.ArtistOrder";

                Func<SpotifyTrackAnalyzeData, string> toKey = data => $"{data.TrackId}+{data.PlaylistIndex}";

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

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var track = reader.ToSpotifyTrackAnalyzeData();

                        var trackKey = toKey(track);

                        if (tracks.ContainsKey(trackKey))
                        {
                            tracks[trackKey].Artists.Add(track.Artists.First());
                        }
                        else
                        {
                            tracks.Add(trackKey, track);
                        }
                    }
                }
            }

            return tracks.Values.ToArray();
        }

        public async Task<IEnumerable<UpdatePlaylistTrack>> GetPlaylistPersonalizedTracksAsync(string playlistId, IList<string> spotifyTracksIsrc)
        {
            var result = new List<UpdatePlaylistTrack>();

            if (spotifyTracksIsrc.Any() == false || spotifyTracksIsrc.Where(i => !String.IsNullOrEmpty(i)).Any() == false)
            {
                return result;
            }

            try
            {
                using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
                {
                    var sql = $@"
                        select Isrc, TrackId, Added from tblSpotifyPersonalizedPlaylistTrackList
                        where PlaylistId = '{playlistId}'
                            and Isrc not in ({string.Join(",", spotifyTracksIsrc.Where(i => !String.IsNullOrEmpty(i)).Select(x => $"'{x}'"))})
                        order by TrackId";

                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            var rowNumber = 0;
                            while (await reader.ReadAsync())
                            {
                                var isrc = reader.GetString("Isrc");
                                var trackId = reader.GetString("TrackId");
                                var added = reader.GetUtcDateTimeOrDefault("Added");
                                result.Add(new UpdatePlaylistTrack
                                {
                                    Track = new GeneralSpotifyTrack
                                    {
                                        Isrc = isrc,
                                        Id = trackId
                                    },
                                    Added = added,
                                    PlaylistIndex = spotifyTracksIsrc.Count + rowNumber
                                });

                                rowNumber++;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }

            return result;
        }

        public async Task AddSpotifyPlaylistsForTrackingAsync(IEnumerable<SpotifyPlaylistTrackingReference> spotifyPlaylists)
        {
            if (spotifyPlaylists.Any())
            {
                await PetaPocoRepository.Instance.BulkInsertAsync(spotifyPlaylists, ignoreExisting: true);
            }
        }

        public async Task AddOrUpdateSpotifyPlaylistsForTrackingAsync(IEnumerable<SpotifyPlaylistTrackingReference> spotifyPlaylists)
        {
            if (spotifyPlaylists == null || spotifyPlaylists.Any() == false) { return; }

            Func<string, string> getStringOrNull = input => String.IsNullOrWhiteSpace(input) ? "null" : $"{MySqlHelper.EscapeString(input)}";

            Action<IDbCommand, object> addCommandParameter = (cmd, value) =>
            {
                var p = cmd.CreateParameter();
                p.ParameterName = $"@{cmd.Parameters.Count.ToString()}";
                p.Value = value;

                cmd.Parameters.Add(p);
            };

            Func<int, int, string> createSQLInsertValues = (totalCount, batchSize) =>
            {
                return
                    String.Join(",",
                        Enumerable.Range(0, totalCount).Batch(batchSize)
                            .Select(values => $"({String.Join(",", values.Select(v => $"@{v}"))})")
                            .ToList()
                    );

            };

            Func<MySqlConnection, MySqlCommand> createPlaylistsInsertCommand = (connection) =>
            {
                var command = connection.CreateCommand();
                command.Connection = connection;

                Func<SpotifyPlaylistTrackingReference, object>[] playlistValuesGetters = new Func<SpotifyPlaylistTrackingReference, object>[] {
                    p => p.PlaylistId,
                    p => p.PlaylistUri,
                    p => getStringOrNull(p.Name),
                    p => getStringOrNull(p.User),
                    p => p.SaveTracklist,
                    p => "new playlist" // to ensure next sync job run will fully synchronize playlist. Since Snapshot id has changed
                };

                foreach (var playlist in spotifyPlaylists)
                {
                    foreach (var getter in playlistValuesGetters)
                    {
                        addCommandParameter(command, getter(playlist));
                    }
                }

                var sql = $@"
    INSERT IGNORE INTO tblSpotifyPlaylist (PlaylistId, PlaylistUri, Name, User, SaveTracklist, SnapshotId) 
    VALUES {createSQLInsertValues(command.Parameters.Count, playlistValuesGetters.Length)};";

                command.CommandText = sql;

                return command;
            };

            var dtNow = DateTime.Now;

            try
            {
                await FaultHandlingPolicy.MySqlRetryPolicyAsync.ExecuteAsync(async () =>
                {
                    using (MySqlConnection connection = await DatabaseHandler.GetOpenConnectionAsync())
                    {
                        using (var insertPlaylistCommand = createPlaylistsInsertCommand(connection))
                        {
                            await insertPlaylistCommand.ExecuteNonQueryAsync();
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                ErrorLoggingManager.Instance.LogError(ex, $"Could not insert spotify playlists. count: {spotifyPlaylists.Count()}. Retry count: {FaultHandlingPolicy.RetryCount}");
                throw;
            }
        }

        public void AddPlaylist(SpotifyPlaylist spotifyPlaylist)
        {
            SetPlaylistId(spotifyPlaylist);

            PetaPocoRepository.Instance.Db.Insert(spotifyPlaylist);
        }

        public void AddOrUpdateSpotifyPlaylist(SpotifyPlaylist spotifyPlaylist)
        {
            var existingPlaylist = GetPlaylistById(spotifyPlaylist.PlaylistId);
            if (existingPlaylist == null)
                AddPlaylist(spotifyPlaylist);
            else
                UpdatePlaylist(spotifyPlaylist);
        }

        public void UpdatePlaylist(SpotifyPlaylist spotifyPlaylist)
        {
            SetPlaylistId(spotifyPlaylist);

            FaultHandlingPolicy.MySqlRetryPolicy.Execute(() => PetaPocoRepository.Instance.Update(spotifyPlaylist));
        }

        public async Task UpdatePlaylistsDateAsync(IEnumerable<string> playlistIds)
        {
            await Task.WhenAll(this.UpdatePlaylistsUpdateDateAsync(playlistIds), this.UpdateCurrentTracklistForTodayAsync(playlistIds));
        }

        public async Task UpdateCurrentTracklistForTodayAsync(string playlistId)
        {
            await UpdateCurrentTracklistForTodayAsync(new string[] { playlistId });
        }

        public async Task UpdateCurrentTracklistForTodayAsync(IEnumerable<string> playlistIds)
        {
            const string sql = "UPDATE tblSpotifyPlaylistTrackList2 SET Timestamp = NOW() WHERE PlaylistId = '{0}';";

            StringBuilder sqlBuilder = new StringBuilder();
            playlistIds.Where(id => !String.IsNullOrWhiteSpace(id)).ForEachMy(id =>
            {
                sqlBuilder.AppendLine(String.Format(sql, id));
            });

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var cmd = new MySqlCommand(sqlBuilder.ToString(), conn))
                {
                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task UpdatePlaylistsUpdateDateAsync(IEnumerable<string> playlistIds)
        {
            const string sql = "UPDATE tblSpotifyPlaylist SET UpdateDate = NOW() WHERE PlaylistId = '{0}';";

            StringBuilder sqlBuilder = new StringBuilder();
            playlistIds.ForEachMy(id =>
            {
                sqlBuilder.AppendLine(String.Format(sql, id));
            });

            try
            {
                await FaultHandlingPolicy.MySqlRetryPolicyAsync.ExecuteAsync(async () =>
                {
                    using (MySqlConnection connection = await DatabaseHandler.GetOpenConnectionAsync())
                    {
                        using (MySqlTransaction transaction = await connection.BeginTransactionAsync())
                        {
                            try
                            {
                                var command = connection.CreateCommand();
                                command.Transaction = transaction;
                                command.CommandText = sqlBuilder.ToString();

                                command.ExecuteNonQuery();

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

                                throw;
                            }
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                ErrorLoggingManager.Instance.LogError(ex, $"Could not update spotify playlists. count: {playlistIds.Count()}. Retry count: {FaultHandlingPolicy.RetryCount}");
                throw;
            }
        }

        public async Task UpdatePlaylistsAsync(IEnumerable<SpotifyPlaylist> playlists)
        {
            const string snapshotChangedSqlTemplate = "UPDATE tblSpotifyPlaylist SET Name = '{0}', Description = '{1}', User = '{2}', CountryCode = '{3}', Duration = {4}, TrackCount = {5}, Followers = {6}, BuzzCategoryId = {7}, TrackLatestAdded = '{8}', UpdateDate = NOW(), SnapshotId = '{9}', Public = {11} WHERE PlaylistId = '{10}';";

            StringBuilder sqlBuilder = new StringBuilder();
            playlists.ForEachMy(p =>
            {
                sqlBuilder.AppendLine(String.Format(snapshotChangedSqlTemplate, MySqlHelper.EscapeString(p.Name), MySqlHelper.EscapeString(p.Description), MySqlHelper.EscapeString(p.User), p.CountryCode, p.Duration, p.TrackCount, p.Followers, p.BuzzCategoryId.HasValue ? p.BuzzCategoryId.Value.ToString() : "NULL", p.TrackLatestAdded.HasValue ? p.TrackLatestAdded.Value.ToString("yyyy-MM-dd hh-mm-ss") : "NULL", p.SnapshotId, p.PlaylistId, p.Public ? 1 : 0));
            });

            try
            {
                await FaultHandlingPolicy.MySqlRetryPolicyAsync.ExecuteAsync(async () =>
                {
                    using (MySqlConnection connection = await DatabaseHandler.GetOpenConnectionAsync())
                    {
                        using (MySqlTransaction transaction = await connection.BeginTransactionAsync())
                        {
                            try
                            {
                                var command = connection.CreateCommand();
                                command.Transaction = transaction;
                                command.CommandText = sqlBuilder.ToString();

                                command.ExecuteNonQuery();

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

                                throw;
                            }
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                ErrorLoggingManager.Instance.LogError(ex, $"Could not update spotify playlists. count: {playlists.Count()}. Retry count: {FaultHandlingPolicy.RetryCount}");
                throw;
            }
        }

        public async Task UpdatePlaylistTracksInfo(IEnumerable<SpotifyPlaylist> playlists)
        {
            const string sql = "UPDATE tblSpotifyPlaylist SET Duration = {0}, TrackCount = {1}, TrackLatestAdded = '{2}', UpdateDate = NOW() WHERE PlaylistId = '{3}';";

            StringBuilder sqlBuilder = new StringBuilder();
            playlists.ForEachMy(p =>
            {
                sqlBuilder.AppendLine(String.Format(sql, p.Duration, p.TrackCount, p.TrackLatestAdded.HasValue ? p.TrackLatestAdded.Value.ToString("yyyy-MM-dd hh-mm-ss") : "NULL", p.PlaylistId));
            });

            try
            {
                await FaultHandlingPolicy.MySqlRetryPolicyAsync.ExecuteAsync(async () =>
                {
                    using (MySqlConnection connection = await DatabaseHandler.GetOpenConnectionAsync())
                    {
                        using (MySqlTransaction transaction = await connection.BeginTransactionAsync())
                        {
                            try
                            {
                                var command = connection.CreateCommand();
                                command.Transaction = transaction;
                                command.CommandText = sqlBuilder.ToString();

                                command.ExecuteNonQuery();

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

                                throw;
                            }
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                ErrorLoggingManager.Instance.LogError(ex, $"Could not update spotify playlists. count: {playlists.Count()}. Retry count: {FaultHandlingPolicy.RetryCount}");
                throw;
            }
        }

        public async Task DeletePlaylistByIdAsync(string playlistId)
        {
            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var delCmd = new MySqlCommand("UPDATE tblSpotifyPlaylist SET Removed=1 WHERE playlistId=@playlistId", conn);
                delCmd.Parameters.Add(new MySqlParameter("@playlistId", playlistId));
                await delCmd.ExecuteNonQueryAsync();
            }
        }

        public SpotifyPlaylist GetPlaylist(string playlistUri)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefault<SpotifyPlaylist>((object)playlistUri);
        }

        public async Task<SpotifyPlaylist> GetPlaylistByIdAsync(string playlistId)
        {
            const string sql = @"
SELECT 
	PlaylistUri,
    PlaylistId,
    Name,
    Description,
    Image,
    User,
    CountryCode,
    Duration,
    TrackCount,
    Followers,
    BuzzCategoryId,
    TrackLatestAdded,
    Public,
    Removed,
    UpdateDate,
    SnapshotId
FROM tblSpotifyPlaylist
WHERE PlaylistId = @playlistId;";

            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@playlistId", playlistId);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        var pl = new SpotifyPlaylist();

                        pl.PlaylistId = playlistId;
                        pl.PlaylistUri = reader.GetString("PlaylistUri");
                        pl.Name = reader.GetString("Name");
                        pl.Description = reader.GetString("Description");
                        pl.Image = reader.GetString("Image");
                        pl.User = reader.GetString("User");
                        pl.CountryCode = reader.GetString("CountryCode");
                        pl.Duration = reader.GetLongOrFallback("Duration", 0);
                        pl.TrackCount = reader.GetIntOrFallback("TrackCount", 0);
                        pl.Followers = reader.GetIntOrFallback("Followers", 0);
                        pl.BuzzCategoryId = reader.GetInt32("BuzzCategoryId");
                        pl.TrackLatestAdded = reader.GetDateTimeOrDefault("TrackLatestAdded");
                        pl.Public = reader.GetBoolean("Public");
                        pl.Removed = reader.GetBoolean("Removed");
                        pl.UpdateDate = reader.GetDateTime("UpdateDate");
                        pl.SnapshotId = reader.GetString("SnapshotId");

                        return pl;
                    }

                    return null;
                }
            }
        }

        public SpotifyPlaylist GetPlaylistById(string playlistId)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefaultWithSql<SpotifyPlaylist>("WHERE PlaylistId=@0", playlistId);
        }

        public List<SpotifyPlaylist> GetPlaylistsByIds(IEnumerable<string> playlistIds)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyPlaylist>("WHERE PlaylistId IN (@PlaylistIds)", new { PlaylistIds = playlistIds });
        }

        public List<SpotifyPlaylist> GetPlaylists(IEnumerable<string> playlistIds)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyPlaylist>("WHERE PlaylistId IN (@playlistIds)", new { playlistIds });
        }

        public List<SpotifyPlaylist> GetPlaylistsForUser(string spotifyUsername)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyPlaylist>("WHERE User=@0", spotifyUsername);
        }

        public List<SpotifyPlaylist> GetPlaylistsForUsers(List<string> spotifyUsernames)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyPlaylist>("WHERE User IN (@usernames)", new { usernames = spotifyUsernames });
        }

        public List<SpotifyPlaylist> GetAllPlaylists()
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyPlaylist>("SELECT p.* FROM tblSpotifyPlaylist AS p " +
                                                                              "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                                                                              "WHERE p.Removed=0 AND ip.playlistId IS NULL", new { spotifyMusicServiceId = (int)MusicService.Spotify });
        }

        private void SetPlaylistId(SpotifyPlaylist spotifyPlaylist)
        {
            var spotifyLink = new SpotifyLink(spotifyPlaylist.PlaylistUri);
            var playlistId = spotifyLink.ExtractPlaylistID();

            spotifyPlaylist.PlaylistId = playlistId;
        }

        private async Task SetSpotifyPlaylistCurrentTrackList(string playlistId, IEnumerable<UpdatePlaylistTrack> tracks)
        {
            await _playlistFactory.SetSpotifyPlaylistCurrentTrackList(playlistId, tracks);
            _distributedCacheHandler.Remove(GetTrackListCacheKey(playlistId));
        }

        public async Task SetSpotifyTrackListAndUpdateStatistics(
            string playlistId,
            IEnumerable<UpdatePlaylistTrack> tracks,
            string market)
        {
            await Task.WhenAll(this.SetSpotifyPlaylistCurrentTrackList(playlistId, tracks), this.SetPlaylistUpdateStatistsic(playlistId, market));
        }

        public async Task SetPlaylistUpdateStatistsic(string playlistId, string market)
        {
            await _playlistFactory.SetPlaylistUpdateStatistsic(playlistId, market);
        }


        public async Task AddOrUpdateSpotifyTracksAsync(List<GeneralSpotifyTrack> tracks)
        {
            if (tracks == null || !tracks.Any())
            {
                return;
            }

            var sql = "INSERT IGNORE INTO tblSpotifyTrack2 (TrackId, Name, Duration, ISRC, Popularity) VALUES {0} " +
                                "ON DUPLICATE KEY UPDATE Popularity = VALUES(Popularity), ISRC = VALUES(isrc) ";

            List<string> paramValues = new List<string>();
            foreach (var track in tracks.Where(t => !string.IsNullOrWhiteSpace(t.Id)).DistinctBy(t => t.Id))
            {
                string isrc = String.IsNullOrWhiteSpace(track.Isrc) ? "null" : $"'{MySqlHelper.EscapeString(track.Isrc)}'";
                string name = String.IsNullOrWhiteSpace(track.Name) ? "null" : $"'{MySqlHelper.EscapeString(track.Name)}'";

                var paramValue = "(" + string.Join(",", $"'{track.Id}'", name, track.Duration, isrc, track.Popularity) + ")";

                paramValues.Add(paramValue);
            }

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

            try
            {
                await FaultHandlingPolicy.MySqlRetryPolicyAsync.ExecuteAsync(async () =>
                {
                    using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
                    {
                        using (var cmd = new MySqlCommand(sql, conn))
                        {
                            await cmd.ExecuteNonQueryAsync();
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                ErrorLoggingManager.Instance.LogError(ex, $"Could not insert spotify tracks. Tracks count: {tracks.Count}. Retry count: {FaultHandlingPolicy.RetryCount}");
                throw;
            }
        }

        public async Task AddSpotifyTracksWithConnectionsAsync(List<GeneralSpotifyTrack> tracks)
        {
            if (!tracks.Any()) { return; }

            await AddOrUpdateSpotifyTracksAsync(tracks);
            await AddSpotifyTrackArtistConnectionsAsync(tracks);

            var existingTrackArtistConnections = await GetSpotifyTrackArtistConnectionsAsync(tracks);
            var trackArtistConnectionsToSave = GetChangedTrackArtistConnections(tracks, existingTrackArtistConnections).ToList();
            if (trackArtistConnectionsToSave.Any())
            {
                await AddOrUpdateSpotifyTrackArtistConnectionsAsync(trackArtistConnectionsToSave);
            }

            await AddSpotifyTrackAlbumConnectionsAsync(tracks);
        }

        private IEnumerable<GeneralSpotifyTrack> GetChangedTrackArtistConnections(List<GeneralSpotifyTrack> tracks, Dictionary<string, List<string>> existingTrackArtistConnections)
        {
            return tracks
                .Where(t => AreArtistsDifferent(t.Artists, existingTrackArtistConnections.GetValueOrDefault(t.Id)))
                .ToList();
        }

        private bool AreArtistsDifferent(List<UpdatePlaylistArtist> trackArtists, List<string> existingArtistOrder)
        {
            if (trackArtists == null || existingArtistOrder == null) { return true; }

            if (trackArtists.Count != existingArtistOrder.Count) { return true; }

            for (int i = 0; i < trackArtists.Count; i++)
            {
                if (trackArtists[i].Id != existingArtistOrder[i])
                    return true;
            }

            return false;
        }

        private async Task<Dictionary<string, List<string>>> GetSpotifyTrackArtistConnectionsAsync(List<GeneralSpotifyTrack> tracks)
        {
            Dictionary<string, List<string>> trackArtists = new Dictionary<string, List<string>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                var trackIdsValues = string.Join(",", tracks.Select(t => $"'{MySqlHelper.EscapeString(t.Id)}'"));

                cmd.CommandText = "SELECT TrackId, ArtistId " +
                                  "FROM tblSpotifyTrackArtist " +
                                  $"WHERE TrackId IN ({trackIdsValues}) " +
                                  $"ORDER BY TrackId, ArtistOrder ";

                using (var sqlReader = await cmd.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        var trackId = sqlReader.GetString(0);
                        var artistId = sqlReader.GetString(1);
                        var existingEntry = trackArtists.GetValueOrDefault(trackId);
                        if (existingEntry != null)
                        {
                            existingEntry.Add(artistId);
                        }
                        else
                        {
                            trackArtists.Add(trackId, new List<string>() { artistId });
                        }
                    }
                }
            }

            return trackArtists;
        }

        public async Task<Dictionary<string, string>> GetIsrcFromTrackIdAsync(IEnumerable<string> trackIds)
        {
            Dictionary<string, string> isrcTrackIdMapping = new Dictionary<string, string>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $"SELECT trackId, ISRC FROM tblSpotifyTrack2 WHERE TrackId IN ({Maybe.ToCommaSeparated(trackIds)}) GROUP BY TrackId";

                var cmd = new MySqlCommand(sql, conn);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var trackId = reader.GetString("trackId");
                        var isrc = reader.GetString("ISRC");

                        isrcTrackIdMapping.Add(trackId, isrc);
                    }
                }

            }
            return isrcTrackIdMapping;
        }

        public async Task SaveAudioFeaturesForTracksAsync(IEnumerable<TrackAudioFeature> audioFeatures)
        {
            await _playlistFactory.SaveAudioFeaturesForTracksAsync(audioFeatures);
        }

        public async Task<IEnumerable<string>> GetAllAlbumIdsAsync()
        {
            List<string> ids = new List<string>();
            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                string sql = @"SELECT AlbumId FROM tblSpotifyAlbum";

                var command = new MySqlCommand(sql, connection);

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

            return ids;
        }

        public async Task<IEnumerable<string>> GetAllArtistIdsAsync()
        {
            List<string> ids = new List<string>();
            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                string sql = @"SELECT ArtistId FROM tblSpotifyArtist";

                var command = new MySqlCommand(sql, connection);

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

            return ids;
        }

        public async Task AddSpotifyArtistsAsync(List<UpdatePlaylistArtist> artists)
        {
            if (artists == null || !artists.Any())
            {
                return;
            }

            var sql = "INSERT IGNORE INTO tblSpotifyArtist (ArtistId, Name) VALUES ";

            var paramValues = artists.Select(a => "(" + string.Join(",", "'" + MySqlHelper.EscapeString(a.Id) + "'", "'" + MySqlHelper.EscapeString(a.Name) + "'") + ")").ToList();

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

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }

        private async Task AddSpotifyTrackArtistConnectionsAsync(List<GeneralSpotifyTrack> trackItems)
        {
            var tracks = trackItems.Where(t => t.Artists?.Any() ?? false).DistinctBy(t => t.Id).ToList();

            if (!tracks.Any())
            {
                return;
            }

            var sql = "INSERT IGNORE INTO tblSpotifyTrackArtist (TrackId, ArtistId, ArtistOrder) VALUES ";

            List<string> paramValues = new List<string>();
            foreach (var track in tracks)
            {
                paramValues.AddRange(track.Artists.ItemIndex().Select(a => "(" + string.Join(",", "'" + MySqlHelper.EscapeString(track.Id) + "'", "'" + MySqlHelper.EscapeString(a.Item.Id) + "'", a.Index) + ")"));
            }

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

            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }

        private async Task AddOrUpdateSpotifyTrackArtistConnectionsAsync(List<GeneralSpotifyTrack> trackItems)
        {
            var tracks = trackItems.Where(t => t.Artists?.Any() ?? false).DistinctBy(t => t.Id).ToList();

            if (!tracks.Any())
            {
                return;
            }

            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                foreach (var trackItem in tracks)
                {
                    using (var transaction = await conn.BeginTransactionAsync())
                    {
                        using (var delCmd = new MySqlCommand("DELETE FROM tblSpotifyTrackArtist WHERE TrackId=@trackId", conn, transaction))
                        {
                            delCmd.Parameters.AddWithValue("@trackId", trackItem.Id);
                            await delCmd.ExecuteNonQueryAsync();
                        }


                        var insertSql = "INSERT IGNORE INTO tblSpotifyTrackArtist (TrackId, ArtistId, ArtistOrder) VALUES ";

                        List<string> paramValues = new List<string>();
                        paramValues.AddRange(trackItem.Artists.ItemIndex().Select(artist => "(" + string.Join(",", "'" + MySqlHelper.EscapeString(trackItem.Id) + "'", "'" + MySqlHelper.EscapeString(artist.Item.Id) + "'", artist.Index) + ")"));

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

                        using (var cmd = new MySqlCommand(insertSql, conn, transaction))
                        {
                            await cmd.ExecuteNonQueryAsync();
                        }

                        transaction.Commit();
                    }
                }
            }
        }

        public async Task AddSpotifyAlbumsAsync(List<UpdatePlaylistAlbum> albums)
        {
            if (albums == null || !albums.Any())
            {
                return;
            }

            string sql = "INSERT IGNORE INTO tblSpotifyAlbum (AlbumId, Name, AlbumType) VALUES ";
            var paramValues = albums.Select(a => "(" + string.Join(",", "'" + MySqlHelper.EscapeString(a.Id) + "'", "'" + MySqlHelper.EscapeString(a.Name) + "'", "'" + MySqlHelper.EscapeString(a.AlbumType) + "'") + ")").ToList();

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

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    await cmd.ExecuteNonQueryAsync();

                }
            }
        }

        private async Task AddSpotifyTrackAlbumConnectionsAsync(List<GeneralSpotifyTrack> trackItems)
        {
            var tracks = trackItems.Where(t => !string.IsNullOrWhiteSpace(t.Album?.Id)).DistinctBy(t => t.Id).ToList();

            if (!tracks.Any())
            {
                return;
            }

            var sql = "INSERT IGNORE INTO tblSpotifyTrackAlbum (TrackId, AlbumId, TrackPosition) VALUES ";

            var paramValues = tracks.Select(t => "(" + string.Join(",", "'" + MySqlHelper.EscapeString(t.Id) + "'", "'" + MySqlHelper.EscapeString(t.Album.Id) + "'", t.AlbumIndex) + ")").ToList();

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

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    await cmd.ExecuteNonQueryAsync();

                }
            }
        }

        public async Task<List<PlaylistUpdateReference>> GetUpdatedPlaylistsAsync(DateTime date)
        {
            var updatedUris = new List<PlaylistUpdateReference>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT p.PlaylistId, p.SaveTracklist " +
                                  "FROM tblSpotifyPlaylist AS p " +
                                  "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                                  "WHERE p.removed = 0 " +
                                  "AND ip.playlistId IS NULL " +
                                  "AND Date(p.UpdateDate) = @date";

                cmd.Parameters.AddWithValue("@date", date.Date);
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using (var sqlReader = await cmd.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        updatedUris.Add(new PlaylistUpdateReference()
                        {
                            PlaylistId = sqlReader.GetString(0),
                            SaveTracklist = sqlReader.GetBoolean(1),
                        });
                    }
                }
            }
            return updatedUris;
        }

        public async Task<PlaylistUpdateReference[]> GetHotHitsPlaylistsToUpdate()
        {
            var list = new List<PlaylistUpdateReference>();

            const string sql = @"
SELECT p.PlaylistId, p.SaveTracklist, p.SnapshotId, p.CountryCode 
FROM tblSpotifyPlaylist AS p
INNER JOIN tblSpotifyHotHitsPlaylist hotP ON p.PlaylistId = hotP.PlaylistId;";

            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = new MySqlCommand(sql, connection);

                using (var sqlReader = await command.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        list.Add(new PlaylistUpdateReference()
                        {
                            PlaylistId = sqlReader.GetString("PlaylistId"),
                            SaveTracklist = sqlReader.GetBoolean("SaveTracklist"),
                            SnapshotId = sqlReader.GetString("SnapshotId"),
                            CountryCode = sqlReader.GetString("CountryCode")
                        });
                    }
                }
            }

            return list.ToArray();
        }

        public async Task<PlaylistUpdateReference[]> GetPlaylistsFor(IEnumerable<string> playlistIds)
        {
            var list = new List<PlaylistUpdateReference>();

            string sql = $@"
SELECT p.PlaylistId, p.SaveTracklist, p.SnapshotId, p.CountryCode 
FROM tblSpotifyPlaylist p
WHERE p.PlaylistId in ({Maybe.ToCommaSeparated(playlistIds)});";

            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = new MySqlCommand(sql, connection);

                using (var sqlReader = await command.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        list.Add(new PlaylistUpdateReference()
                        {
                            PlaylistId = sqlReader.GetString("PlaylistId"),
                            SaveTracklist = sqlReader.GetBoolean("SaveTracklist"),
                            SnapshotId = sqlReader.GetString("SnapshotId"),
                            CountryCode = sqlReader.GetString("CountryCode")
                        });
                    }
                }
            }

            return list.ToArray();
        }

        public async Task<PlaylistUpdateReference[]> GetNewlyInsertedPlaylists()
        {
            var list = new List<PlaylistUpdateReference>();

            const string sql = @"
SELECT p.PlaylistId, p.SaveTracklist, p.SnapshotId, p.CountryCode 
FROM tblSpotifyPlaylist p
WHERE SnapshotId = 'new playlist';";

            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = new MySqlCommand(sql, connection);

                using (var sqlReader = await command.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        list.Add(new PlaylistUpdateReference()
                        {
                            PlaylistId = sqlReader.GetString("PlaylistId"),
                            SaveTracklist = sqlReader.GetBoolean("SaveTracklist"),
                            SnapshotId = sqlReader.GetString("SnapshotId"),
                            CountryCode = sqlReader.GetString("CountryCode")
                        });
                    }
                }
            }

            return list.ToArray();
        }

        public async Task<List<PlaylistUpdateReference>> GetPlaylistsToUpdateAsync(IEnumerable<string> playlistId)
        {
            var updatedUris = new List<PlaylistUpdateReference>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = $@"
SELECT p.PlaylistId, p.SaveTracklist, p.SnapshotId, p.CountryCode 
FROM tblSpotifyPlaylist AS p
WHERE p.PlaylistId IN ({Maybe.ToCommaSeparated(playlistId)})";

                using (var sqlReader = await cmd.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        updatedUris.Add(new PlaylistUpdateReference()
                        {
                            PlaylistId = sqlReader.GetString(0),
                            SaveTracklist = sqlReader.GetBoolean(1),
                            SnapshotId = sqlReader.GetString("SnapshotId"),
                            CountryCode = sqlReader.GetString("CountryCode")
                        });
                    }
                }
            }
            return updatedUris;
        }

        public async Task<List<PlaylistUpdateReference>> GetNotUpdatedPlaylistsAsync(DateTime date, DateTime trackLatestAddedDatePivot, bool isTrackLatestAddedGreaterThenProvided)
        {
            var updatedUris = new List<PlaylistUpdateReference>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = @"
SELECT p.PlaylistId, p.SaveTracklist, p.SnapshotId, p.CountryCode 
FROM tblSpotifyPlaylist AS p
LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId
WHERE p.removed = 0 AND ip.playlistId IS NULL AND Date(p.UpdateDate) < @date";

                if (isTrackLatestAddedGreaterThenProvided)
                {
                    cmd.CommandText += @" AND TrackLatestAdded > @trackLatestAddedDate";
                }
                else
                {
                    cmd.CommandText += @" AND (TrackLatestAdded <= @trackLatestAddedDate OR TrackLatestAdded IS NULL OR TrackLatestAdded = '0000-00-00')";
                }

                cmd.CommandText += " ORDER BY p.TrackLatestAdded DESC;";

                cmd.Parameters.AddWithValue("@trackLatestAddedDate", trackLatestAddedDatePivot);
                cmd.Parameters.AddWithValue("@date", date.Date);
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using (var sqlReader = await cmd.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        updatedUris.Add(new PlaylistUpdateReference()
                        {
                            PlaylistId = sqlReader.GetString(0),
                            SaveTracklist = sqlReader.GetBoolean(1),
                            SnapshotId = sqlReader.GetString("SnapshotId"),
                            CountryCode = sqlReader.GetString("CountryCode")
                        });
                    }
                }
            }
            return updatedUris;
        }

        public class PlaylistUpdateReference
        {
            public string PlaylistId { get; set; }

            public bool SaveTracklist { get; set; }

            public string SnapshotId { get; set; }

            public string CountryCode { get; set; }
        }

        public async Task<List<string>> GetAllPlaylistIdsAsync(bool includeRemoved = false)
        {
            List<string> playlistIds = new List<string>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = $@"
SELECT p.PlaylistId
FROM tblSpotifyPlaylist AS p
LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId
WHERE (@includeRemoved OR p.Removed=0) AND ip.playlistId IS NULL;";

                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);
                cmd.Parameters.AddWithValue("@includeRemoved", includeRemoved);

                using (var sqlReader = await cmd.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        playlistIds.Add(sqlReader.GetString(0));
                    }
                }
            }
            return playlistIds;
        }

        public async Task<List<UpdatePlaylistTrack>> GetTrackListAsync(string playlistId)
        {
            return await _playlistFactory.GetTrackListAsync(playlistId);
        }

        private string GetTrackListCacheKey(string playlistId)
        {
            return $"SpotifyPlaylistManager:{playlistId}";
        }

        public async Task<IEnumerable<TracklistItem>> GetTrackListWithDataByIdAsync(string playlistId)
        {
            var tracklistItems = new Dictionary<int, TracklistItem>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT tl.PlaylistIndex, tl.Added, tl.EarliestAdded, t.TrackId, t.Name As trackName, a.Name as ArtistName, a.ArtistId, " +
                                   " t.ISRC, alb.AlbumId, alb.Name AS albumName, alb.upc, t.Popularity, t.Danceability, t.Energy, t.`Key`, t.Loudness, t.Mode, t.Speechiness, t.Acousticness, t.Instrumentalness, t.Liveness, t.Valence, t.Tempo " +
                                   "FROM tblSpotifyPlaylistTrackList2 tl " +
                                   "LEFT JOIN tblSpotifyTrack2 t ON tl.TrackId = t.TrackId " +
                                   "LEFT JOIN tblSpotifyTrackArtist ta ON t.TrackId = ta.TrackId " +
                                   "LEFT JOIN tblSpotifyArtist a ON ta.ArtistId = a.ArtistId " +
                                   "LEFT JOIN tblSpotifyTrackAlbum talb ON t.TrackId = talb.TrackId " +
                                   "LEFT JOIN tblSpotifyAlbum alb ON talb.AlbumId = alb.AlbumId " +
                                   "WHERE tl.PlaylistId = @playlistId " +
                                   "GROUP BY tl.PlaylistIndex, tl.Added, tl.EarliestAdded, t.TrackId, t.Name, a.Name, a.ArtistId, " +
                                   " t.ISRC, alb.AlbumId, alb.Name, t.Popularity, t.Danceability, t.Energy, t.`Key`, t.Loudness, t.Mode, t.Speechiness, t.Acousticness, t.Instrumentalness, t.Liveness, t.Valence, t.Tempo " +
                                   "ORDER BY PlaylistIndex ASC, ta.ArtistOrder ASC";

                var cmd = new MySqlCommand(sql, conn);

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

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var trackPosition = reader.GetInt32(0);
                        var item = tracklistItems.GetValueOrDefault(trackPosition);
                        if (item == null)
                        {
                            item = new TracklistItem();
                            item.Position = trackPosition;

                            item.Added = reader.GetUtcDateTimeOrDefault("Added");
                            item.EarliestAdded = reader.GetUtcDateTimeOrDefault("EarliestAdded");
                            item.TrackId = reader.GetString("TrackId");
                            item.TrackName = reader.GetString("TrackName");
                            item.ArtistName = reader.GetString("ArtistName");
                            var artistId = reader.GetString("ArtistId");

                            item.ISRC = reader.GetString("ISRC");
                            item.AlbumId = reader.GetString("AlbumId");
                            item.AlbumUpc = reader.GetString("upc");
                            item.AlbumName = reader.GetString("AlbumName");
                            item.Popularity = reader.GetIntOrDefault("Popularity");

                            item.Danceability = reader.GetFloatOrDefault("Danceability");
                            item.Energy = reader.GetFloatOrDefault("Energy");
                            item.AudioKey = reader.GetIntOrDefault("Key");
                            item.Loudness = reader.GetFloatOrDefault("Loudness");
                            item.AudioMode = reader.GetIntOrDefault("Mode");
                            item.Speechiness = reader.GetFloatOrDefault("Speechiness");
                            item.Acousticness = reader.GetFloatOrDefault("Acousticness");
                            item.Instrumentalness = reader.GetFloatOrDefault("Instrumentalness");
                            item.Liveness = reader.GetFloatOrDefault("Liveness");
                            item.Valence = reader.GetFloatOrDefault("Valence");
                            item.Tempo = reader.GetFloatOrDefault("Tempo");

                            item.Artists.Add(new SpotifyArtistReferenceViewModel()
                            {
                                Name = item.ArtistName,
                                Uri = SpotifyLink.FromArtistId(artistId).Uri,
                            });
                            tracklistItems.Add(trackPosition, item);
                        }
                        else
                        {
                            var artistName = reader.GetString("ArtistName");
                            var artistId = reader.GetString("ArtistId");

                            item.Artists.Add(new SpotifyArtistReferenceViewModel()
                            {
                                Name = artistName,
                                Uri = SpotifyLink.FromArtistId(artistId).Uri,
                            });
                        }
                    }
                }
            }

            return tracklistItems.Select(p => p.Value).ToArray();
        }

        public async Task<IEnumerable<SpotifyPlaylistWithStreams>> GetPlaylistsForUserAsync(string user, string market, int? offset = null, int? limit = null)
        {
            var playlists = new List<SpotifyPlaylistWithStreams>();

            var applicationRegions = new List<string>();

            if (!string.IsNullOrWhiteSpace(market))
            {
                applicationRegions = _applicationInstanceManager.GetApplicationRegions(market);
            }

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var playlistFields = GetPlaylistWithStreamsDatabaseFields();

                var sql = $@"
SELECT {playlistFields} 
FROM tblSpotifyPlaylistFollowers AS f
RIGHT JOIN tblSpotifyPlaylist AS p ON p.PlaylistId = f.PlaylistId
LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistId = p.PlaylistId
LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.playlistUri AND ip.musicServiceId = @spotifyMusicServiceId
WHERE p.User = @user AND p.removed = 0 AND ip.playlistId IS NULL
GROUP BY p.PlaylistUri
ORDER BY Streams28Days DESC";

                if (applicationRegions.Any())
                {
                    sql = $@"
SELECT {playlistFields}
FROM tblSpotifyPlaylistFollowers AS f
RIGHT JOIN tblSpotifyPlaylist AS p ON p.PlaylistId = f.PlaylistId
LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistId = p.PlaylistId  AND s.Market IN ({string.Join(", ", applicationRegions.Select(p => "'" + MySqlHelper.EscapeString(p) + "'"))})
LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.playlistUri AND ip.musicServiceId = @spotifyMusicServiceId
WHERE p.User = @user AND p.removed = 0 AND ip.playlistId IS NULL
GROUP BY p.PlaylistUri
ORDER BY Streams28Days DESC";
                }

                sql = Maybe.AddLimitIfNeeded(sql, offset, limit);

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);
                cmd.Parameters.AddWithValue("@user", user);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var playlist = BuildSpotifyPlaylistWithStreams(reader);
                        playlists.Add(playlist);
                    }
                }
            }

            return playlists;
        }

        public async Task<PaginatedContent<SpotifyPlaylistWithTrackData>> GetPlaylistsWithTrackAsync(IEnumerable<string> isrcs, string market, SqlLimitOffset? limitOffset = null)
        {
            var playlists = new PaginatedContent<SpotifyPlaylistWithTrackData>();

            var applicationRegions = new List<string>();

            if (!string.IsNullOrWhiteSpace(market))
            {
                applicationRegions = _applicationInstanceManager.GetApplicationRegions(market);
            }

            List<(string PlaylistId, string Isrc, DateTime EarliestAdded)> isrcEarliestAdded = new List<(string PlaylistId, string Isrc, DateTime EarliestAdded)>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var playlistFields = GetPlaylistWithStreamsDatabaseFields();

                if (applicationRegions.Any())
                {
                    playlistFields = $"{playlistFields}, {GetPlaylistWithMarketSpecificStreamsDatabaseFields(applicationRegions)}";
                }

                var sql = $@"
SELECT SQL_CALC_FOUND_ROWS {playlistFields}, MIN(tl.PlaylistIndex) as PlaylistIndex, tl.Added, tl.EarliestAdded, t.ISRC, t.TrackId,
bu.username as buzzUsername, bu.displayname as buzzDisplayName, bu.CategoryID as buzzCategoryId, bu.CountryCode as buzzCountryCode, bc.name as buzzCategoryName
FROM tblSpotifyPlaylist AS p
INNER JOIN tblSpotifyPlaylistTrackList2 as tl ON tl.PlaylistId = p.PlaylistId
INNER JOIN tblSpotifyTrack2 AS t ON tl.TrackId=t.TrackId
LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistId = p.PlaylistId
LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = p.PlaylistId
LEFT JOIN BuzzUser AS bu 
		FORCE INDEX (BuzzUser_Username_IDX)	
		ON p.User = bu.Username AND bu.ServiceType = @buzzSpotifyServiceType
LEFT JOIN BuzzCategory AS bc ON bu.CategoryID = bc.Id
LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.PlaylistUri AND ip.musicServiceId=@spotifyMusicServiceId
WHERE t.Isrc IN ({Maybe.ToCommaSeparated(isrcs)})
	AND p.Removed = 0 AND ip.PlaylistId IS NULL
GROUP BY p.PlaylistUri, t.Isrc
ORDER BY Streams28Days DESC, p.PlaylistId 
";

                if (limitOffset.HasValue)
                {
                    sql += $"LIMIT @offset, @limit";
                }

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);
                    cmd.Parameters.AddWithValue("@buzzSpotifyServiceType", ServiceType.Spotify);

                    if (limitOffset.HasValue)
                    {
                        cmd.Parameters.AddWithValue("@limit", limitOffset.Value.Limit);
                        cmd.Parameters.AddWithValue("@offset", limitOffset.Value.Offset);
                    }

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var playlistTrackListItem = new SpotifyPlaylistWithTrackData()
                            {
                                Playlist = BuildSpotifyPlaylistWithStreams(reader)
                            };

                            playlistTrackListItem.TracklistItem = new PlaylistTrackListItemSummary()
                            {
                                Position = reader.GetInt32("playlistIndex"),
                                Added = reader.GetUtcDateTimeOrDefault("added"),
                                EarliestAdded = reader.GetUtcDateTimeOrDefault("earliestAdded"),
                                ISRC = reader.GetString("ISRC"),
                                TrackId = reader.GetString("TrackId")
                            };

                            playlistTrackListItem.BuzzUser = new SpotifyPlaylistBuzzItem()
                            {
                                BuzzUsername = reader.GetString("buzzUsername"),
                                BuzzDisplayName = reader.GetString("buzzDisplayName"),
                                BuzzCategoryId = reader.GetIntOrDefault("buzzCategoryId"),
                                BuzzCategoryName = reader.GetString("buzzCategoryName"),
                                BuzzCountryCode = reader.GetString("buzzCountryCode"),
                            };

                            if (applicationRegions.Any())
                            {
                                playlistTrackListItem.MarketPlaylistStreams = BuildMarketStreamInfo(reader);
                            }

                            playlists.Items.Add(playlistTrackListItem);
                        }
                    }
                }

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

                    playlists.Pagination = new Pagination()
                    {
                        Limit = limitOffset.HasValue ? limitOffset.Value.Limit : (int?)null,
                        Offset = limitOffset.HasValue ? limitOffset.Value.Offset : (int?)null,
                        Total = (int)totalCount
                    };
                }

                string earliestAddedDateSql = $@"
SELECT
    MIN(history.date) AS 'EarliestAddedDate',
    t.ISRC as isrc,
    history.PlaylistId
FROM
    tblSpotifyPlaylistTrackListHistory2 history
INNER JOIN tblSpotifyTrack2 t ON t.TrackId = history.TrackId
INNER JOIN tblSpotifyPlaylist p ON history.PlaylistId = p.PlaylistId
WHERE history.PlaylistId IN ({Maybe.ToCommaSeparated(playlists.Items.Select(i => i.Playlist.PlaylistId))}) AND t.ISRC IN ({Maybe.ToCommaSeparated(isrcs)})
GROUP BY t.Isrc, history.PlaylistId;";

                using (var earliestAddedCommand = new MySqlCommand(earliestAddedDateSql, conn))
                {
                    using (var reader = await earliestAddedCommand.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            string playlistId = reader.GetString("PlaylistId");
                            string isrc = reader.GetString("isrc");
                            DateTime earliestAdded = reader.GetDateTime("EarliestAddedDate");

                            isrcEarliestAdded.Add((PlaylistId: playlistId, Isrc: isrc, EarliestAdded: earliestAdded));
                        }
                    }
                }
            }

            playlists.Items = await FillStreamsAndSummaryAsync(market, playlists.Items, isrcEarliestAdded);

            return playlists;
        }

        public async Task<List<SpotifyPlaylistWithoutTrackData>> GetPlaylistsAsync(List<string> playlistIds, string market)
        {
            var playlists = new List<SpotifyPlaylistWithoutTrackData>();
            if (!playlistIds.Any())
            {
                return playlists;
            }

            var applicationRegions = new List<string>();

            if (!string.IsNullOrWhiteSpace(market))
            {
                applicationRegions = _applicationInstanceManager.GetApplicationRegions(market);
            }

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var playlistFields = GetPlaylistWithStreamsDatabaseFields();

                if (applicationRegions.Any())
                {
                    playlistFields = $"{playlistFields}, {GetPlaylistWithMarketSpecificStreamsDatabaseFields(applicationRegions)}";
                }

                var playlistIdsCondition = string.Join(",", playlistIds.Select(p => "'" + MySqlHelper.EscapeString(p) + "'"));
                var sql = $"SELECT {playlistFields}, bu.username as buzzUsername, bu.DisplayName as buzzDisplayName, bu.CategoryID as buzzCategoryId, bu.CountryCode as buzzCountryCode, bc.name as buzzCategoryName  " +
                          "FROM tblSpotifyPlaylist AS p " +
                          "LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistId = p.PlaylistId " +
                          "LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = p.PlaylistId " +
                          "LEFT JOIN BuzzUser AS bu ON p.User = bu.Username AND bu.ServiceType = @buzzSpotifyServiceType " +
                          "LEFT JOIN BuzzCategory AS bc ON bu.CategoryID = bc.Id " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                          $"WHERE p.playlistId IN ({playlistIdsCondition}) AND p.Removed = 0 AND ip.PlaylistId IS NULL " +
                          "GROUP BY p.PlaylistUri " +
                          "ORDER BY Streams28Days DESC, p.playlistUri";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);
                    cmd.Parameters.AddWithValue("@buzzSpotifyServiceType", ServiceType.Spotify);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var playlistTrackListItem = new SpotifyPlaylistWithoutTrackData()
                            {
                                Playlist = BuildSpotifyPlaylistWithStreams(reader)
                            };


                            playlistTrackListItem.BuzzUser = new SpotifyPlaylistBuzzItem()
                            {
                                BuzzUsername = reader.GetString("buzzUsername"),
                                BuzzDisplayName = reader.GetString("buzzDisplayName"),
                                BuzzCategoryId = reader.GetIntOrDefault("buzzCategoryId"),
                                BuzzCategoryName = reader.GetString("buzzCategoryName"),
                                BuzzCountryCode = reader.GetString("buzzCountryCode"),
                            };

                            if (applicationRegions.Any())
                            {
                                playlistTrackListItem.MarketPlaylistStreams = BuildMarketStreamInfo(reader);
                            }

                            playlists.Add(playlistTrackListItem);
                        }
                    }
                }
            }

            playlists = await FillStreamsAndSummaryAsync(market, playlists);

            return playlists;
        }


        private SpotifyPlaylistMarketStreamsItem BuildMarketStreamInfo(DbDataReader reader)
        {
            var marketStreamsItem = new SpotifyPlaylistMarketStreamsItem();
            marketStreamsItem.MarketStreams56Days = reader.GetIntOrDefault("MarketStreams56Days") ?? 0;
            marketStreamsItem.MarketStreams28Days = reader.GetIntOrDefault("MarketStreams28Days") ?? 0;
            marketStreamsItem.MarketStreams14Days = reader.GetIntOrDefault("MarketStreams14Days") ?? 0;
            marketStreamsItem.MarketStreams7Days = reader.GetIntOrDefault("MarketStreams7Days") ?? 0;
            marketStreamsItem.MarketStreamsLatest = reader.GetIntOrDefault("MarketStreamsLatest") ?? 0;

            marketStreamsItem.MarketUsers56Days = reader.GetIntOrDefault("MarketListeners56Days") ?? 0;
            marketStreamsItem.MarketUsers28Days = reader.GetIntOrDefault("MarketListeners28Days") ?? 0;
            marketStreamsItem.MarketUsers14Days = reader.GetIntOrDefault("MarketListeners14Days") ?? 0;
            marketStreamsItem.MarketUsers7Days = reader.GetIntOrDefault("MarketListeners7Days") ?? 0;
            marketStreamsItem.MarketUsersLatest = reader.GetIntOrDefault("MarketListenersLatests") ?? 0;
            return marketStreamsItem;
        }

        private string GetPlaylistWithStreamsDatabaseFields()
        {
            return "p.PlaylistUri, p.Name, p.Description, p.Image, p.User, p.CountryCode, p.Duration, p.TrackCount, p.UpdateDate, p.BuzzCategoryId, p.TrackLatestAdded, p.Public, " +
                   "f.Followers, f.Followers1dayAgo, f.Followers7daysAgo, f.Followers14daysAgo, f.Followers28daysAgo, f.Followers56daysAgo, " +
                   "SUM(s.Streams56Days) AS Streams56Days, SUM(s.Streams28Days) AS Streams28Days, SUM(s.Streams14Days) AS Streams14Days, SUM(s.Streams7Days) AS Streams7Days, " +
                   "SUM(s.Listeners56Days) AS Listeners56Days, SUM(s.Listeners28Days) AS Listeners28Days, SUM(s.Listeners14Days) AS Listeners14Days, SUM(s.Listeners7Days) AS Listeners7Days, " +
                   "SUM(s.StreamsPerListener56days) AS StreamsPerListener56days, SUM(s.StreamsPerListener28days) AS StreamsPerListener28days, SUM(s.StreamsPerListener14days) AS StreamsPerListener14days, SUM(s.StreamsPerListener7days) AS StreamsPerListener7days, " +
                   "SUM(s.StreamsLatest) AS StreamsLatest, SUM(s.ListenersLatest) AS ListenersLatest, SUM(s.StreamsPerListenerLatest) AS StreamsPerListenerLatest, " +
                   "MAX(s.StreamDays56days) as StreamDays56days, MAX(s.StreamDays28days) as StreamDays28days, MAX(s.StreamDays14days) as StreamDays14days, MAX(s.StreamDays7days) as StreamDays7days ";
        }

        private string GetPlaylistWithMarketSpecificStreamsDatabaseFields(List<string> markets)
        {
            var marketSelection = $"({string.Join(", ", markets.Select(p => "'" + MySqlHelper.EscapeString(p) + "'"))})";

            return $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.Streams56Days ELSE 0 END) AS MarketStreams56Days, " +
                   $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.Streams28Days ELSE 0 END) AS MarketStreams28Days," +
                   $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.Streams14Days ELSE 0 END) AS MarketStreams14Days," +
                   $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.Streams7Days ELSE 0 END) AS MarketStreams7Days," +
                   $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.Listeners56Days ELSE 0 END) AS MarketListeners56Days," +
                   $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.Listeners28Days ELSE 0 END) AS MarketListeners28Days," +
                   $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.Listeners14Days ELSE 0 END) AS MarketListeners14Days," +
                   $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.Listeners7Days ELSE 0 END) AS MarketListeners7Days," +
                   $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.StreamsLatest ELSE 0 END) AS MarketStreamsLatest," +
                   $"SUM(CASE WHEN s.Market IN {marketSelection} THEN s.ListenersLatest ELSE 0 END) AS MarketListenersLatests";
        }

        //public async Task<List<SpotifyPlaylistInMarket>> GetPlaylistsForMarketAsync(string market, int buzzCategory)
        //{
        //    var playlists = new List<SpotifyPlaylist>();

        //    using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
        //    {
        //        var applicationRegions = _applicationInstanceManager.GetApplicationRegions(market);
        //        var applicationRegionString = string.Join(",", applicationRegions.Select(a => "'" + MySqlHelper.EscapeString(a) + "'"));

        //        string sql;
        //        if (market == "_gl")
        //        {
        //            sql = $"SELECT p.PlaylistUri, p.Name, p.Description, p.Image, p.User, p.CountryCode, p.Duration, p.TrackCount, p.UpdateDate, p.BuzzCategoryId, p.TrackLatestAdded, p.Public, " +
        //                      "f.Followers " +
        //                      "FROM tblSpotifyPlaylist AS p " +
        //                      $"LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistId = p.PlaylistId " +
        //                      "LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = p.PlaylistId " +
        //                      "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
        //                      "WHERE BuzzCategoryId = @buzzCategory AND (p.CountryCode = @market or s.streams28days > 0) " +
        //                      "AND p.removed = 0 AND ip.playlistId IS NULL " +
        //                      "GROUP BY p.PlaylistUri " +
        //                      "ORDER BY Streams28Days DESC";
        //        }
        //        else
        //        {
        //            sql = $"SELECT p.PlaylistUri, p.Name, p.Description, p.Image, p.User, p.CountryCode, p.Duration, p.TrackCount, p.UpdateDate, p.BuzzCategoryId, p.TrackLatestAdded, p.Public, " +
        //                      "f.Followers " +
        //                      "FROM tblSpotifyPlaylist AS p " +
        //                      $"LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistId = p.PlaylistId AND s.Market IN ({applicationRegionString}) " +
        //                      "LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = p.PlaylistId " +
        //                      "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
        //                      "WHERE BuzzCategoryId = @buzzCategory AND (p.CountryCode = @market or s.streams28days > 0) " +
        //                      "AND p.removed = 0 AND ip.playlistId IS NULL " +
        //                      "GROUP BY p.PlaylistUri " +
        //                      "ORDER BY Streams28Days DESC";
        //        }


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

        //        cmd.Parameters.AddWithValue("@market", market);
        //        cmd.Parameters.AddWithValue("@buzzCategory", buzzCategory);
        //        cmd.Parameters.AddWithValue("@spotifyMusicServiceId", (int)MusicService.Spotify);

        //        using(var reader = await cmd.ExecuteReaderAsync())
        //        {
        //            while (await reader.ReadAsync())
        //            {
        //                var playlist = BuildSpotifyPlaylist(reader);

        //                playlists.Add(playlist);
        //            }
        //        }
        //    }

        //    var filledPlaylists = await FillStreamsAndSummaryAsync(market, playlists);

        //    return filledPlaylists;
        //}

        public async Task<IEnumerable<SpotifyPlaylistInMarket>> GetPlaylistsForMarketAsync(string market, int buzzCategory)
        {
            ConcurrentBag<SpotifyPlaylistInMarket> playlistsInMarket = new ConcurrentBag<SpotifyPlaylistInMarket>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var applicationRegions = _applicationInstanceManager.GetApplicationRegions(market);

                string sql;
                if (market == "_gl")
                {
                    sql = $@"
SELECT a.*, f.Followers, f.Followers1dayAgo, f.Followers7daysAgo, f.Followers14daysAgo, f.Followers28daysAgo, f.Followers56daysAgo, f.UpdateDate,  personPlaylist.PlaylistId IS NOT NULL AS IsPersonalized
FROM (
	SELECT 
	p.playlistId, concat('spotify:playlist:', p.playlistId) AS PlaylistUri, p.Name, p.description, p.image, p.user, p.countryCode, p.trackCount, p.buzzCategoryId, p.trackLatestAdded, p.public, p.removed, p.updateDate, p.saveTracklist,
	s.Streams56Days as              Streams56Days,
	s.Streams28Days as              Streams28Days,
	s.Streams14Days as              Streams14Days,
	s.Streams7Days as               Streams7Days,
	s.Listeners56Days as            Users56Days,
	s.Listeners28Days as            Users28Days,
	s.Listeners14Days as            Users14Days,
	s.Listeners7Days as             Users7Days,
	s.StreamsLatest as              StreamsLatest, 
	s.ListenersLatest as            UsersLatest,
	StreamDays56Days as             StreamsDays56days,
	StreamDays28Days as             StreamsDays28Days,
	StreamDays14Days as             StreamsDays14Days,
	StreamDays7Days as              StreamsDays7Days,
	StreamsPerListenerLatest as     StreamsPerUserLatest,
	StreamsPerListener7days as      StreamsPerUser7days,
	StreamsPerListener14days as     StreamsPerUser14days,
	StreamsPerListener28days as     StreamsPerUser28days,
	StreamsPerListener56days as     StreamsPerUser56days,
	AvgStreams56 as                 AverageStreams56Days,
	AvgStreams28 as                 AverageStreams28Days,
	AvgStreams14 as                 AverageStreams14Days,
	AvgStreams7 as                  AverageStreams7Days,
	AvgListeners56 as               AverageListeners56Days,
	AvgListeners28 as               AverageListeners28Days,
	AvgListeners14 as               AverageListeners14Days,
	AvgListeners7 as                AverageListeners7Days
	FROM tblSpotifyPlaylist AS p
	LEFT JOIN tblSpotifyPlaylistStreamSummaryGlobal AS s ON s.PlaylistId = p.PlaylistId
	LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId={(int)MusicService.Spotify}
	WHERE BuzzCategoryId = {buzzCategory} AND (p.CountryCode = '{market}' or s.streams28days > 0)
	AND p.removed = 0 AND ip.playlistId IS NULL
GROUP BY p.PlaylistId
) As a
LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = a.PlaylistId
LEFT JOIN tblSpotifyPersonalizedPlaylist as personPlaylist ON a.PlaylistId = personPlaylist.PlaylistId;
";
                }
                else
                {
                    sql = $@"
SELECT a.*, f.Followers, f.Followers1dayAgo, f.Followers7daysAgo, f.Followers14daysAgo, f.Followers28daysAgo, f.Followers56daysAgo, f.UpdateDate, personPlaylist.PlaylistId IS NOT NULL AS IsPersonalized
FROM (
	SELECT p.playlistId, concat('spotify:playlist:', p.playlistId) AS PlaylistUri, p.Name, p.description, p.image, p.user, p.countryCode, p.trackCount, p.buzzCategoryId, p.trackLatestAdded, p.public, p.removed, p.updateDate, p.saveTracklist,
	SUM(s.Streams56Days) AS                         Streams56Days,
	SUM(s.Streams28Days) AS                         Streams28Days,
	SUM(s.Streams14Days) AS                         Streams14Days,
	SUM(s.Streams7Days) AS                          Streams7Days,
	SUM(s.Listeners56Days) AS                       Users56Days,
	SUM(s.Listeners28Days) AS                       Users28Days,
	SUM(s.Listeners14Days) AS                       Users14Days,
	SUM(s.Listeners7Days) AS                        Users7Days,
	SUM(s.StreamsLatest) AS                         StreamsLatest,
	SUM(s.ListenersLatest) AS                       UsersLatest,
	MAX(StreamDays56Days) as                        StreamsDays56days,
	MAX(StreamDays28Days) as                        StreamsDays28Days,
	MAX(StreamDays14Days) as                        StreamsDays14Days,
	MAX(StreamDays7Days) as                         StreamsDays7Days,
	SUM(StreamsPerListenerLatest) as                StreamsPerUserLatest,
	SUM(StreamsPerListener7days) as                 StreamsPerUser7days,
	SUM(StreamsPerListener14days) as                StreamsPerUser14days,
	SUM(StreamsPerListener28days) as                StreamsPerUser28days,
	SUM(StreamsPerListener56days) as                StreamsPerUser56days,
	SUM(s.Streams56Days / s.StreamDays56Days) as    AverageStreams56Days,
	SUM(s.Streams28Days / s.StreamDays28Days) as    AverageStreams28Days,
	SUM(s.Streams14Days / s.StreamDays14Days) as    AverageStreams14Days,
	SUM(s.Streams7Days / s.StreamDays7Days) as      AverageStreams7Days,
	SUM(s.Listeners56Days / s.StreamDays56Days) as  AverageListeners56Days,
	SUM(s.Listeners28Days / s.StreamDays28Days) as  AverageListeners28Days,
	SUM(s.Listeners14Days / s.StreamDays14Days) as  AverageListeners14Days,
	SUM(s.Listeners7Days / s.StreamDays7Days) as    AverageListeners7Days
	FROM tblSpotifyPlaylist AS p
	LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistId = p.PlaylistId AND s.Market IN ({Maybe.ToCommaSeparated(applicationRegions)})
	LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId={(int)MusicService.Spotify}
	WHERE BuzzCategoryId = {buzzCategory} AND (p.CountryCode = '{market}' or s.streams28days > 0) 
	AND p.removed = 0 AND ip.playlistId IS NULL 
	GROUP BY p.PlaylistId) As a 
LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = a.PlaylistId
LEFT JOIN tblSpotifyPersonalizedPlaylist as personPlaylist ON a.PlaylistId = personPlaylist.PlaylistId;";
                }

                var cmd = new MySqlCommand(sql, conn);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    var playlistReader = reader.GetRowParser<SpotifyPlaylist>();
                    var followerSummaryReader = reader.GetRowParser<PlaylistFollowerSummary>();
                    var streamSummaryReader = reader.GetRowParser<PlaylistStreamSummary>();

                    var data = reader.Parse();
                    data.AsParallel().ForEach((x) =>
                    {
                        var playlistInMarket = new SpotifyPlaylistInMarket()
                        {
                            Playlist = playlistReader(reader),
                            FollowerSummary = followerSummaryReader(reader),
                            StreamSummary = streamSummaryReader(reader),
                            IsPersonalized = reader.GetBoolean("IsPersonalized")
                        };
                        playlistsInMarket.Add(playlistInMarket);
                    });
                }
            }

            return playlistsInMarket;
        }

        private PlaylistStreamSummary CalculateAverages(PlaylistStreamSummary streamSummary)
        {
            const int decimals = 4;
            streamSummary.AverageStreams7Days = CalculatePercentage(streamSummary.Streams7Days, streamSummary.StreamsDays7days, decimals);
            streamSummary.AverageStreams14Days = CalculatePercentage(streamSummary.Streams14Days, streamSummary.StreamsDays14days, decimals);
            streamSummary.AverageStreams28Days = CalculatePercentage(streamSummary.Streams28Days, streamSummary.StreamsDays28days, decimals);
            streamSummary.AverageStreams56Days = CalculatePercentage(streamSummary.Streams56Days, streamSummary.StreamsDays56days, decimals);

            streamSummary.AverageListeners7Days = CalculatePercentage(streamSummary.Users7Days, streamSummary.StreamsDays7days, decimals);
            streamSummary.AverageListeners14Days = CalculatePercentage(streamSummary.Users14Days, streamSummary.StreamsDays14days, decimals);
            streamSummary.AverageListeners28Days = CalculatePercentage(streamSummary.Users28Days, streamSummary.StreamsDays28days, decimals);
            streamSummary.AverageListeners56Days = CalculatePercentage(streamSummary.Users56Days, streamSummary.StreamsDays56days, decimals);

            //const int streamsPerUserDecimals = 14;
            //streamSummary.StreamsPerUser7Days = CalculatePercentage(streamSummary.Streams7Days, streamSummary.Users7Days, streamsPerUserDecimals);
            //streamSummary.StreamsPerUser14Days = CalculatePercentage(streamSummary.Streams14Days, streamSummary.Users14Days, streamsPerUserDecimals);
            //streamSummary.StreamsPerUser28Days = CalculatePercentage(streamSummary.Streams28Days, streamSummary.Users28Days, streamsPerUserDecimals);
            //streamSummary.StreamsPerUser56Days = CalculatePercentage(streamSummary.Streams56Days, streamSummary.Users56Days, streamsPerUserDecimals);
            //streamSummary.StreamsPerUserLatest = CalculatePercentage(streamSummary.StreamsLatest, streamSummary.UsersLatest, streamsPerUserDecimals);

            return streamSummary;
        }

        public async Task<IEnumerable<SpotifyPlaylistInMarket>> GetCustomPlaylistsForMarketAsync(string market)
        {
            ConcurrentBag<SpotifyPlaylistInMarket> playlistsInMarket = new ConcurrentBag<SpotifyPlaylistInMarket>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var applicationRegions = _applicationInstanceManager.GetApplicationRegions(market);

                string sql;
                if (market == "_gl")
                {
                    sql = $@"
SELECT a.*, f.Followers, f.Followers1dayAgo, f.Followers7daysAgo, f.Followers14daysAgo, f.Followers28daysAgo, f.Followers56daysAgo, f.UpdateDate,  personPlaylist.PlaylistId IS NOT NULL AS IsPersonalized
FROM (
	SELECT 
	p.playlistId, concat('spotify:playlist:', p.playlistId) AS PlaylistUri, p.Name, p.description, p.image, p.user, p.countryCode, p.trackCount, customPlaylist.buzzCategoryId, p.trackLatestAdded, p.public, p.removed, p.updateDate, p.saveTracklist,
	s.Streams56Days as              Streams56Days,
	s.Streams28Days as              Streams28Days,
	s.Streams14Days as              Streams14Days,
	s.Streams7Days as               Streams7Days,
	s.Listeners56Days as            Users56Days,
	s.Listeners28Days as            Users28Days,
	s.Listeners14Days as            Users14Days,
	s.Listeners7Days as             Users7Days,
	s.StreamsLatest as              StreamsLatest, 
	s.ListenersLatest as            UsersLatest,
	StreamDays56Days as             StreamsDays56days,
	StreamDays28Days as             StreamsDays28Days,
	StreamDays14Days as             StreamsDays14Days,
	StreamDays7Days as              StreamsDays7Days,
	StreamsPerListenerLatest as     StreamsPerUserLatest,
	StreamsPerListener7days as      StreamsPerUser7days,
	StreamsPerListener14days as     StreamsPerUser14days,
	StreamsPerListener28days as     StreamsPerUser28days,
	StreamsPerListener56days as     StreamsPerUser56days,
	AvgStreams56 as                 AverageStreams56Days,
	AvgStreams28 as                 AverageStreams28Days,
	AvgStreams14 as                 AverageStreams14Days,
	AvgStreams7 as                  AverageStreams7Days,
	AvgListeners56 as               AverageListeners56Days,
	AvgListeners28 as               AverageListeners28Days,
	AvgListeners14 as               AverageListeners14Days,
	AvgListeners7 as                AverageListeners7Days
	FROM tblSpotifyPlaylist AS p
    INNER JOIN tblSpotifyCustomPlaylistUser customPlaylist ON customPlaylist.PlaylistId = p.PlaylistId
	LEFT JOIN tblSpotifyPlaylistStreamSummaryGlobal AS s ON s.PlaylistId = p.PlaylistId
	LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId={(int)MusicService.Spotify}
	WHERE (p.CountryCode = '{market}' or s.streams28days > 0)
	AND p.removed = 0 AND ip.playlistId IS NULL
GROUP BY p.PlaylistId
) As a
LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = a.PlaylistId
LEFT JOIN tblSpotifyPersonalizedPlaylist as personPlaylist ON a.PlaylistId = personPlaylist.PlaylistId;
";
                }
                else
                {
                    sql = $@"
SELECT a.*, f.Followers, f.Followers1dayAgo, f.Followers7daysAgo, f.Followers14daysAgo, f.Followers28daysAgo, f.Followers56daysAgo, f.UpdateDate, personPlaylist.PlaylistId IS NOT NULL AS IsPersonalized
FROM (
	SELECT p.playlistId, concat('spotify:playlist:', p.playlistId) AS PlaylistUri, p.Name, p.description, p.image, p.user, p.countryCode, p.trackCount, customPlaylist.buzzCategoryId, p.trackLatestAdded, p.public, p.removed, p.updateDate, p.saveTracklist,
	SUM(s.Streams56Days) AS                         Streams56Days,
	SUM(s.Streams28Days) AS                         Streams28Days,
	SUM(s.Streams14Days) AS                         Streams14Days,
	SUM(s.Streams7Days) AS                          Streams7Days,
	SUM(s.Listeners56Days) AS                       Users56Days,
	SUM(s.Listeners28Days) AS                       Users28Days,
	SUM(s.Listeners14Days) AS                       Users14Days,
	SUM(s.Listeners7Days) AS                        Users7Days,
	SUM(s.StreamsLatest) AS                         StreamsLatest,
	SUM(s.ListenersLatest) AS                       UsersLatest,
	MAX(StreamDays56Days) as                        StreamsDays56days,
	MAX(StreamDays28Days) as                        StreamsDays28Days,
	MAX(StreamDays14Days) as                        StreamsDays14Days,
	MAX(StreamDays7Days) as                         StreamsDays7Days,
	SUM(StreamsPerListenerLatest) as                StreamsPerUserLatest,
	SUM(StreamsPerListener7days) as                 StreamsPerUser7days,
	SUM(StreamsPerListener14days) as                StreamsPerUser14days,
	SUM(StreamsPerListener28days) as                StreamsPerUser28days,
	SUM(StreamsPerListener56days) as                StreamsPerUser56days,
	SUM(s.Streams56Days / s.StreamDays56Days) as    AverageStreams56Days,
	SUM(s.Streams28Days / s.StreamDays28Days) as    AverageStreams28Days,
	SUM(s.Streams14Days / s.StreamDays14Days) as    AverageStreams14Days,
	SUM(s.Streams7Days / s.StreamDays7Days) as      AverageStreams7Days,
	SUM(s.Listeners56Days / s.StreamDays56Days) as  AverageListeners56Days,
	SUM(s.Listeners28Days / s.StreamDays28Days) as  AverageListeners28Days,
	SUM(s.Listeners14Days / s.StreamDays14Days) as  AverageListeners14Days,
	SUM(s.Listeners7Days / s.StreamDays7Days) as    AverageListeners7Days
	FROM tblSpotifyPlaylist AS p
    INNER JOIN tblSpotifyCustomPlaylistUser customPlaylist ON customPlaylist.PlaylistId = p.PlaylistId
	LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistId = p.PlaylistId AND s.Market IN ({Maybe.ToCommaSeparated(applicationRegions)})
	LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId={(int)MusicService.Spotify}
	WHERE (p.CountryCode = '{market}' or s.streams28days > 0) 
	AND p.removed = 0 AND ip.playlistId IS NULL 
	GROUP BY p.PlaylistId) As a 
LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = a.PlaylistId
LEFT JOIN tblSpotifyPersonalizedPlaylist as personPlaylist ON a.PlaylistId = personPlaylist.PlaylistId;";
                }

                var cmd = new MySqlCommand(sql, conn);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    var playlistReader = reader.GetRowParser<SpotifyPlaylist>();
                    var followerSummaryReader = reader.GetRowParser<PlaylistFollowerSummary>();
                    var streamSummaryReader = reader.GetRowParser<PlaylistStreamSummary>();

                    var data = reader.Parse();
                    data.AsParallel().ForEach((x) =>
                    {
                        var playlistInMarket = new SpotifyPlaylistInMarket()
                        {
                            Playlist = playlistReader(reader),
                            FollowerSummary = followerSummaryReader(reader),
                            StreamSummary = streamSummaryReader(reader),
                            IsPersonalized = reader.GetBoolean("IsPersonalized")
                        };
                        playlistsInMarket.Add(playlistInMarket);
                    });
                }
            }

            return playlistsInMarket;
        }

        private double CalculatePercentage(int a, int b, int? decimals = null)
        {
            if (b == 0)
                return 0;

            var percentage = 1.0 * a / b;
            if (decimals.HasValue)
            {
                return Math.Round(percentage, decimals.Value);
            }

            return percentage;
        }

        private async Task<List<SpotifyPlaylistInMarket>> FillStreamsAndSummaryAsync(string market, List<SpotifyPlaylist> playlists)
        {
            var playlistsIds = playlists.Select(p => p.PlaylistId).ToList();
            var playlistStreams = await GetPlaylistStreamSummaryAsync(playlistsIds, market);
            var playlistFollowers = await GetPlaylistFollowerSummaryAsync(playlistsIds);

            List<SpotifyPlaylistInMarket> filledPlaylists = new List<SpotifyPlaylistInMarket>();
            foreach (var playlist in playlists)
            {
                filledPlaylists.Add(new SpotifyPlaylistInMarket()
                {
                    Playlist = playlist,
                    StreamSummary = playlistStreams.GetValueOrDefault(playlist.PlaylistId),
                    FollowerSummary = playlistFollowers.GetValueOrDefault(playlist.PlaylistId),
                });
            }
            return filledPlaylists;
        }

        private async Task<List<SpotifyPlaylistWithoutTrackData>> FillStreamsAndSummaryAsync(string market, List<SpotifyPlaylistWithoutTrackData> playlists)
        {
            var playlistsIds = playlists.Select(p => p.Playlist.PlaylistId).ToList();
            var playlistStreams = await GetPlaylistStreamSummaryAsync(playlistsIds, market);
            var playlistFollowers = await GetPlaylistFollowerSummaryAsync(playlistsIds);

            foreach (var playlist in playlists)
            {
                playlist.PlaylistFollowerSummary = playlistFollowers.GetValueOrDefault(playlist.Playlist.PlaylistId);
                playlist.PlaylistStreamSummary = playlistStreams.GetValueOrDefault(playlist.Playlist.PlaylistId);
            }
            return playlists;
        }

        private async Task<List<SpotifyPlaylistWithTrackData>> FillStreamsAndSummaryAsync(
            string market,
            List<SpotifyPlaylistWithTrackData> playlists,
            List<(string PlaylistId, string Isrc, DateTime EarliestAdded)> earliestAddedList)
        {
            var playlistsIds = playlists.Select(p => p.Playlist.PlaylistId).ToList();
            var playlistStreams = await GetPlaylistStreamSummaryAsync(playlistsIds, market);
            var playlistFollowers = await GetPlaylistFollowerSummaryAsync(playlistsIds);

            List<SpotifyPlaylistWithTrackData> filledPlaylists = new List<SpotifyPlaylistWithTrackData>();
            foreach (var playlist in playlists)
            {
                playlist.PlaylistStreamSummary = playlistStreams.GetValueOrDefault(playlist.Playlist.PlaylistId);
                playlist.PlaylistFollowerSummary = playlistFollowers.GetValueOrDefault(playlist.Playlist.PlaylistId);
                filledPlaylists.Add(playlist);

                if (earliestAddedList.TryFirst(i => i.PlaylistId == playlist.Playlist.PlaylistId && i.Isrc == playlist.TracklistItem.ISRC, out var result))
                {
                    playlist.TracklistItem.EarliestAdded = result.EarliestAdded;
                }
            }

            return filledPlaylists;
        }

        public async Task<Dictionary<string, PlaylistFollowerSummary>> GetPlaylistFollowerSummaryAsync(List<string> playlistIds)
        {
            Dictionary<string, PlaylistFollowerSummary> playlistFollowerSummaries = new Dictionary<string, PlaylistFollowerSummary>();
            if (!playlistIds.Any())
                return playlistFollowerSummaries;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var playlistUriList = $"{string.Join(", ", playlistIds.Select(p => "'" + MySqlHelper.EscapeString(p) + "'"))}";

                var sql = "SELECT f.playlistId, f.Followers1dayAgo, f.Followers7daysAgo, f.Followers14daysAgo, f.Followers28daysAgo, f.Followers56daysAgo " +
                          "FROM tblSpotifyPlaylistFollowers AS f " +
                          $"WHERE f.playlistId IN ({playlistUriList}) " +
                          "GROUP BY f.PlaylistId";

                var cmd = new MySqlCommand(sql, conn);

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

                        var followers1dayAgo = reader.GetIntOrDefault("Followers1dayAgo");
                        var followers7daysAgo = reader.GetIntOrDefault("Followers7daysAgo");
                        var followers14daysAgo = reader.GetIntOrDefault("followers14daysAgo");
                        var followers28daysAgo = reader.GetIntOrDefault("Followers28daysAgo");
                        var followers56daysAgo = reader.GetIntOrDefault("Followers56daysAgo");

                        playlistFollowerSummaries.Add(playlistId, new PlaylistFollowerSummary()
                        {
                            Followers56daysAgo = followers56daysAgo,
                            Followers28daysAgo = followers28daysAgo,
                            Followers14daysAgo = followers14daysAgo,
                            Followers7daysAgo = followers7daysAgo,
                            Followers1dayAgo = followers1dayAgo,
                        });
                    }
                }
            }

            return playlistFollowerSummaries;
        }

        public async Task<Dictionary<string, PlaylistStreamSummary>> GetPlaylistStreamSummaryAsync(List<string> playlistIds, string market)
        {
            Dictionary<string, PlaylistStreamSummary> playlistStreamSummaries = new Dictionary<string, PlaylistStreamSummary>();
            if (!playlistIds.Any())
                return playlistStreamSummaries;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                string marketSql = string.Empty;
                if (market != null && market != "_gl")
                {
                    var applicationRegions = _applicationInstanceManager.GetApplicationRegions(market);
                    var applicationRegionString = string.Join(", ", applicationRegions.Select(a => "'" + MySqlHelper.EscapeString(a) + "'"));
                    marketSql = $" AND (@market = '_gl' OR @market IS NULL OR s.Market IN ({applicationRegionString})) ";
                }
                var playlistIdList = string.Join(", ", playlistIds.Select(p => "'" + MySqlHelper.EscapeString(p) + "'"));

                var sql = "SELECT s.playlistId, " +
                          "SUM(s.Streams56Days) AS Streams56Days, " +
                          "SUM(s.Streams28Days) AS Streams28Days, " +
                          "SUM(s.Streams14Days) AS Streams14Days, " +
                          "SUM(s.Streams7Days) AS Streams7Days, " +
                          "SUM(s.Streams56Days / s.StreamDays56Days) as avgStreams56, " +
                          "SUM(s.Streams28Days / s.StreamDays28Days) as avgStreams28, " +
                          "SUM(s.Streams14Days / s.StreamDays14Days) as avgStreams14, " +
                          "SUM(s.Streams7Days / s.StreamDays7Days) as avgStreams7, " +
                          "SUM(s.Streams28Days) AS Streams28Days, " +
                          "SUM(s.Streams14Days) AS Streams14Days, " +
                          "SUM(s.Streams7Days) AS Streams7Days, " +
                          "SUM(s.Listeners56Days) AS Listeners56Days, " +
                          "SUM(s.Listeners28Days) AS Listeners28Days, " +
                          "SUM(s.Listeners14Days) AS Listeners14Days, " +
                          "SUM(s.Listeners7Days) AS Listeners7Days, " +
                          "SUM(s.StreamsPerListener56days) AS StreamsPerListener56days, " +
                          "SUM(s.StreamsPerListener28days) AS StreamsPerListener28days, " +
                          "SUM(s.StreamsPerListener14days) AS StreamsPerListener14days, " +
                          "SUM(s.StreamsPerListener7days) AS StreamsPerListener7days, " +
                          "SUM(s.StreamsLatest) AS StreamsLatest, " +
                          "SUM(s.ListenersLatest) AS ListenersLatest, " +
                          "SUM(s.StreamsPerListenerLatest) AS StreamsPerListenerLatest, " +
                          "SUM(s.Listeners56Days / s.StreamDays56Days) as avgListeners56, " +
                          "SUM(s.Listeners28Days / s.StreamDays28Days) as avgListeners28, " +
                          "SUM(s.Listeners14Days / s.StreamDays14Days) as avgListeners14, " +
                          "SUM(s.Listeners7Days / s.StreamDays7Days) as avgListeners7, " +
                          "MAX(StreamDays56Days) as StreamDays56Days," +
                          "MAX(StreamDays28Days) as StreamDays28Days," +
                          "MAX(StreamDays14Days) as StreamDays14Days," +
                          "MAX(StreamDays7Days) as StreamDays7Days " +
                          "FROM tblSpotifyPlaylistStreamSummary AS s " +
                          $"WHERE playlistId IN ({playlistIdList}) {marketSql} " +
                          "GROUP BY playlistId";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@market", market);

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

                        var streams56days = reader.GetIntOrFallback("streams56Days", 0);
                        var streams28days = reader.GetIntOrFallback("streams28Days", 0);
                        var streams14days = reader.GetIntOrFallback("streams14Days", 0);
                        var streams7days = reader.GetIntOrFallback("streams7days", 0);

                        var streamsDays56days = reader.GetIntOrFallback("StreamDays56days", 0);
                        var streamsDays28days = reader.GetIntOrFallback("StreamDays28days", 0);
                        var streamsDays14days = reader.GetIntOrFallback("StreamDays14days", 0);
                        var streamsDays7days = reader.GetIntOrFallback("StreamDays7days", 0);

                        var listeners56days = reader.GetIntOrFallback("Listeners56Days", 0);
                        var listeners28days = reader.GetIntOrFallback("listeners28Days", 0);
                        var listeners14days = reader.GetIntOrFallback("listeners14Days", 0);
                        var listeners7days = reader.GetIntOrFallback("listeners7Days", 0);

                        var streamsPerListener56days = reader.GetDoubleOrFallback("streamsPerListener56days", 0);
                        var streamsPerListener28days = reader.GetDoubleOrFallback("streamsPerListener28days", 0);
                        var streamsPerListener14days = reader.GetDoubleOrFallback("streamsPerListener14days", 0);
                        var streamsPerListener7days = reader.GetDoubleOrFallback("streamsPerListener7days", 0);

                        var streamsLatest = reader.GetIntOrFallback("streamsLatest", 0);
                        var listenersLatest = reader.GetIntOrDefault("listenersLatest") ?? 0;
                        var streamsPerListenerLatest = reader.GetDoubleOrFallback("streamsPerListenerLatest", 0);

                        var averageStreams56 = reader.GetDoubleOrFallback("avgStreams56", 0);
                        var averageStreams28 = reader.GetDoubleOrFallback("avgStreams28", 0);
                        var averageStreams14 = reader.GetDoubleOrFallback("avgStreams14", 0);
                        var averageStreams7 = reader.GetDoubleOrFallback("avgStreams7", 0);

                        var averageListeners56 = reader.GetDoubleOrFallback("avgListeners56", 0);
                        var averageListeners28 = reader.GetDoubleOrFallback("avgListeners28", 0);
                        var averageListeners14 = reader.GetDoubleOrFallback("avgListeners14", 0);
                        var averageListeners7 = reader.GetDoubleOrFallback("avgListeners7", 0);


                        playlistStreamSummaries.Add(playlistId, new PlaylistStreamSummary()
                        {
                            Streams56Days = streams56days,
                            Streams28Days = streams28days,
                            Streams14Days = streams14days,
                            Streams7Days = streams7days,
                            StreamsLatest = streamsLatest,

                            StreamsDays56days = streamsDays56days,
                            StreamsDays28days = streamsDays28days,
                            StreamsDays14days = streamsDays14days,
                            StreamsDays7days = streamsDays7days,

                            Users56Days = listeners56days,
                            Users28Days = listeners28days,
                            Users14Days = listeners14days,
                            Users7Days = listeners7days,
                            UsersLatest = listenersLatest,

                            AverageStreams56Days = averageStreams56,
                            AverageStreams28Days = averageStreams28,
                            AverageStreams14Days = averageStreams14,
                            AverageStreams7Days = averageStreams7,

                            AverageListeners56Days = averageListeners56,
                            AverageListeners28Days = averageListeners28,
                            AverageListeners14Days = averageListeners14,
                            AverageListeners7Days = averageListeners7,

                            StreamsPerUser56Days = streamsPerListener56days,
                            StreamsPerUser28Days = streamsPerListener28days,
                            StreamsPerUser14Days = streamsPerListener14days,
                            StreamsPerUser7Days = streamsPerListener7days,
                            StreamsPerUserLatest = streamsPerListenerLatest,
                        });
                    }
                }
            }
            return playlistStreamSummaries;
        }

        private static SpotifyPlaylist BuildSpotifyPlaylist(DbDataReader reader)
        {
            var playlistUri = reader.GetString("PlaylistUri");
            var playlistName = reader.GetString("Name");
            var description = reader.GetString("description");
            var image = reader.GetString("image");
            var user = reader.GetString("user");
            var countryCode = reader.GetString("countryCode");
            var duration = reader.GetIntOrDefault("duration") ?? 0;
            var trackCount = reader.GetIntOrDefault("trackCount") ?? 0;
            var updateDate = reader.GetDateTimeOrDefault("updateDate");
            var buzzCategoryId = reader.GetIntOrDefault("buzzCategoryId");
            var @public = reader.GetBoolean("public");
            var followers = reader.GetIntOrDefault("followers") ?? 0;
            var trackLatestAdded = reader.GetUtcDateTimeOrDefault("TrackLatestAdded");
            var playlistId = new SpotifyLink(playlistUri).ExtractPlaylistID();

            var playlist = new SpotifyPlaylist()
            {
                PlaylistId = playlistId,
                PlaylistUri = playlistUri,
                Name = playlistName,
                Description = description,
                Image = image,
                User = user,
                CountryCode = countryCode,
                Duration = duration,
                TrackCount = trackCount,
                BuzzCategoryId = buzzCategoryId,
                UpdateDate = updateDate,
                Public = @public,
                Followers = followers,
                TrackLatestAdded = trackLatestAdded
            };
            return playlist;
        }

        private static SpotifyPlaylistWithStreams BuildSpotifyPlaylistWithStreams(DbDataReader reader)
        {
            var playlistUri = reader.GetString("PlaylistUri");
            var playlistName = reader.GetString("Name");
            var description = reader.GetString("description");
            var image = reader.GetString("image");
            var user = reader.GetString("user");
            var countryCode = reader.GetString("countryCode");
            var duration = reader.GetIntOrDefault("duration") ?? 0;
            var trackCount = reader.GetIntOrDefault("trackCount") ?? 0;
            var updateDate = reader.GetDateTimeOrDefault("updateDate");
            var buzzCategoryId = reader.GetIntOrDefault("buzzCategoryId");
            var @public = reader.GetBoolean("public");

            var followers = reader.GetIntOrDefault("followers") ?? 0;
            var followers1dayAgo = reader.GetIntOrDefault("Followers1dayAgo");
            var followers7daysAgo = reader.GetIntOrDefault("Followers7daysAgo");
            var followers14daysAgo = reader.GetIntOrDefault("followers14daysAgo");
            var followers28daysAgo = reader.GetIntOrDefault("Followers28daysAgo");
            var followers56daysAgo = reader.GetIntOrDefault("Followers56daysAgo");

            var streams56days = reader.GetIntOrDefault("streams56Days") ?? 0;
            var streams28days = reader.GetIntOrDefault("streams28Days") ?? 0;
            var streams14days = reader.GetIntOrDefault("streams14Days") ?? 0;
            var streams7days = reader.GetIntOrDefault("streams7days") ?? 0;

            var listeners56days = reader.GetIntOrDefault("Listeners56Days") ?? 0;
            var listeners28days = reader.GetIntOrDefault("listeners28Days") ?? 0;
            var listeners14days = reader.GetIntOrDefault("listeners14Days") ?? 0;
            var listeners7days = reader.GetIntOrDefault("listeners7Days") ?? 0;

            var streamsPerListener56days = reader.GetDoubleOrFallback("streamsPerListener56days", 0);
            var streamsPerListener28days = reader.GetDoubleOrFallback("streamsPerListener28days", 0);
            var streamsPerListener14days = reader.GetDoubleOrFallback("streamsPerListener14days", 0);
            var streamsPerListener7days = reader.GetDoubleOrFallback("streamsPerListener7days", 0);

            var streamDays56days = reader.GetIntOrDefault("StreamDays56days") ?? 0;
            var streamDays28days = reader.GetIntOrDefault("StreamDays28days") ?? 0;
            var streamDays14days = reader.GetIntOrDefault("StreamDays14days") ?? 0;
            var streamDays7days = reader.GetIntOrDefault("StreamDays7days") ?? 0;

            var streamsLatest = reader.GetIntOrDefault("streamsLatest") ?? 0;
            var listenersLatest = reader.GetIntOrDefault("listenersLatest") ?? 0;
            var streamsPerListenerLatest = reader.GetDoubleOrFallback("streamsPerListenerLatest", 0);

            var trackLatestAdded = reader.GetUtcDateTimeOrDefault("TrackLatestAdded");

            var playlist = new SpotifyPlaylistWithStreams()
            {
                PlaylistId = new SpotifyLink(playlistUri).ExtractPlaylistID(),
                PlaylistUri = playlistUri,
                Name = playlistName,
                Description = description,
                Image = image,
                User = user,
                CountryCode = countryCode,
                Duration = duration,
                TrackCount = trackCount,
                Followers = followers,
                BuzzCategoryId = buzzCategoryId,
                UpdateDate = updateDate,
                TrackLatestAdded = trackLatestAdded,
                Public = @public,

                Followers1dayAgo = followers1dayAgo,
                Followers7daysAgo = followers7daysAgo,
                Followers14daysAgo = followers14daysAgo,
                Followers28daysAgo = followers28daysAgo,
                Followers56daysAgo = followers56daysAgo,

                Streams56Days = streams56days,
                Streams28Days = streams28days,
                Streams14Days = streams14days,
                Streams7Days = streams7days,
                StreamsLatest = streamsLatest,

                Users56Days = listeners56days,
                Users28Days = listeners28days,
                Users14Days = listeners14days,
                Users7Days = listeners7days,
                UsersLatest = listenersLatest,

                StreamsPerUser56Days = streamsPerListener56days,
                StreamsPerUser28Days = streamsPerListener28days,
                StreamsPerUser14Days = streamsPerListener14days,
                StreamsPerUser7Days = streamsPerListener7days,
                StreamsPerUserLatest = streamsPerListenerLatest,

                StreamDaysInPeriod56Days = streamDays56days,
                StreamDaysInPeriod28Days = streamDays28days,
                StreamDaysInPeriod14Days = streamDays14days,
                StreamDaysInPeriod7Days = streamDays7days,

            };
            return playlist;
        }

        public async Task<SpotifyPlaylistFollowerSummary> GetPlaylistFollowerSummaryAsync(int? buzzCategoryId = null, string playlistCountryCode = null, string username = null, string streamsRegion = null)
        {
            SpotifyPlaylistFollowerSummary summary = null;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT SUM(f.followers) as followers " +
                        "FROM tblSpotifyPlaylist AS p " +
                        "LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = p.PlaylistId " +
                        "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                        "WHERE p.Removed = 0 AND ip.playlistId IS NULL " +
                        "AND (@buzzCategoryId IS NULL OR p.BuzzCategoryId = @buzzCategoryId) " +
                        "AND (@playlistCountryCode IS NULL OR p.CountryCode = @playlistCountryCode) " +
                        "AND (@user IS NULL OR p.User = @user) ";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);
                cmd.Parameters.AddWithValue("@buzzCategoryId", buzzCategoryId);
                cmd.Parameters.AddWithValue("@playlistCountryCode", playlistCountryCode);
                cmd.Parameters.AddWithValue("@user", username);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        summary = new SpotifyPlaylistFollowerSummary()
                        {
                            Followers = reader.GetLongOrFallback("followers", 0)
                        };
                    }
                }
            }
            return summary;
        }

        public async Task<SpotifyPlaylistStreamSummary> GetPlaylistStreamSummaryAsync(int? buzzCategoryId = null, string playlistCountryCode = null, string username = null, string streamsRegion = null)
        {
            SpotifyPlaylistStreamSummary summary = null;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT SUM(s.Streams28Days) AS Streams28DaysTotal, SUM(s.Streams7Days) AS Streams7Days, SUM(s.Listeners28Days) AS Listeners28Days, " +
                        " SUM(s.Listeners7Days) AS Listeners7Days, SUM(s.StreamsLatest) AS StreamLatest, SUM(s.ListenersLatest) AS ListenersLatest " +
                        "FROM tblSpotifyPlaylist AS p " +
                        "LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistId = p.PlaylistId " +
                        "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                        "WHERE p.Removed = 0 AND ip.playlistId IS NULL " +
                        "AND (@buzzCategoryId IS NULL OR p.BuzzCategoryId = @buzzCategoryId) " +
                        "AND (@playlistCountryCode IS NULL OR p.CountryCode = @playlistCountryCode) " +
                        "AND (@streamsRegion IS NULL OR s.Market = @streamsRegion) " +
                        "AND (@user IS NULL OR p.User = @user) ";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);
                cmd.Parameters.AddWithValue("@buzzCategoryId", buzzCategoryId);
                cmd.Parameters.AddWithValue("@playlistCountryCode", playlistCountryCode);
                cmd.Parameters.AddWithValue("@user", username);
                cmd.Parameters.AddWithValue("@streamsRegion", streamsRegion);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        summary = new SpotifyPlaylistStreamSummary()
                        {
                            TotalStreams28day = reader.GetLongOrFallback("Streams28DaysTotal", 0),
                            TotalStreams7day = reader.GetLongOrFallback("Streams7Days", 0),
                            TotalListeners28day = reader.GetLongOrFallback("Listeners28Days", 0),
                            TotalListeners7day = reader.GetLongOrFallback("Listeners7Days", 0),
                            TotalStreamsLatest = reader.GetLongOrFallback("StreamLatest", 0),
                            TotalListenersLatest = reader.GetLongOrFallback("ListenersLatest", 0),
                        };
                    }
                }
            }
            return summary;
        }

        public class SpotifyPlaylistStreamSummary
        {
            public long TotalStreams28day { get; set; }
            public long TotalStreams7day { get; set; }

            public long TotalListeners28day { get; set; }
            public long TotalListeners7day { get; set; }

            public long TotalStreamsLatest { get; set; }
            public long TotalListenersLatest { get; set; }
        }

        public class SpotifyPlaylistFollowerSummary
        {
            public long Followers { get; set; }
        }

        public async Task SetFollowersAsync(IEnumerable<KeyValuePair<string, uint>> playlistToFollowersPairs)
        {
            if (!playlistToFollowersPairs.Any()) { return; }

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                const string upsertSqlTemplate = @"
INSERT IGNORE INTO tblSpotifyPlaylistFollowers (PlaylistUri, PlaylistId, Followers) 
VALUES {0}
ON DUPLICATE KEY UPDATE Followers = VALUES(Followers)";

                StringBuilder valuesBuilder = new StringBuilder(playlistToFollowersPairs.Count() * 100);
                foreach (var item in playlistToFollowersPairs)
                {
                    valuesBuilder.Append($"('{SpotifyLink.FromPlaylistId(item.Key).Uri}','{item.Key}',{item.Value}),");
                }

                valuesBuilder.Remove(valuesBuilder.Length - 1, 1);

                var cmd = new MySqlCommand(String.Format(upsertSqlTemplate, valuesBuilder.ToString()), conn);

                await cmd.ExecuteNonQueryAsync();
            }
        }

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

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT p.countrycode, COUNT(*) as playlists, COUNT(CASE WHEN p.public=1 THEN p.public ELSE null END) as publicPlaylists, SUM(f.Followers) as followers, " +
                                   "SUM(CASE WHEN p.public=1 THEN f.followers ELSE 0 END) as publicFollowers " +
                                   "FROM tblSpotifyPlaylist AS p " +
                                   "LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistId = p.PlaylistId " +
                                   "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                                   "WHERE p.removed = 0 AND ip.playlistId IS NULL AND (@buzzCategoryId IS NULL OR p.BuzzCategoryId = @buzzCategoryId) " +
                                   "GROUP BY p.countryCode ";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);
                cmd.Parameters.AddWithValue("@buzzCategoryId", (int)buzzCategoryId);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var countryOverview = new CountryOverview()
                        {
                            CountryCode = reader.GetString("countryCode"),
                            Playlists = reader.GetIntOrFallback("playlists", 0),
                            PublicPlaylists = reader.GetIntOrFallback("publicPlaylists", 0),
                            PlaylistFollowers = reader.GetIntOrDefault("followers"),
                            PublicPlaylistFollowers = reader.GetIntOrDefault("publicFollowers"),
                        };

                        countryOverviews.Add(countryOverview);
                    }
                }
            }
            return countryOverviews;
        }

        public async Task<List<PlaylistTrackListDistribution>> GetTrackListKpiAsync(List<string> playlistIds, string region)
        {
            var playlistKpis = new List<PlaylistTrackListDistribution>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT p.playlistUri, COUNT(ti.trackId) as trackCount, SUM(ti.isrc IS NOT NULL) as trackWithISRCCount, " +
                          "SUM(ti.trackRegion = @region) as localTrackCount, SUM(ti.albumReleaseDate>=@frontlineCutoff) as frontlineTrackCount, SUM(ti.sonyUpc IS NOT NULL) as sonyTrackCount " +
                          "FROM " +
                          " (SELECT tl.playlistId, tl.trackId, t.isrc, coalesce(isrc.realCountryCode, LEFT(t.isrc, 2)) as trackRegion, CAST(tal.ReleaseDate as datetime) as albumReleaseDate, upcRegion.upc as sonyUpc " +
                          " FROM tblSpotifyPlaylistTrackList2 AS tl " +
                          " INNER JOIN tblSpotifyTrack2 AS t ON tl.trackId = t.trackId " +
                          " INNER JOIN tblSpotifyTrackAlbum AS tat ON tat.trackId = t.trackId " +
                          " INNER JOIN tblSpotifyAlbum AS tal ON tal.albumId = tat.albumId " +
                          " LEFT JOIN tblISRCRegion AS isrc ON isrc.isrcCountryCode = LEFT(t.isrc, 2) " +
                          " LEFT JOIN tblSonyUPCRegion as upcRegion ON upcRegion.upc = tal.upc AND upcRegion.Region = @region AND upcRegion.releaseTypeId = @releaseTypeId " +
                          " WHERE tl.playlistId IN (@playlistIds) " +
                          " GROUP BY tl.playlistId, tl.trackId ) AS ti " +
                          "INNER JOIN tblSpotifyPlaylist AS p ON ti.playlistId = p.playlistId " +
                          "GROUP BY p.playlistUri ";

                sql = sql.Replace("@playlistIds", string.Join(",", playlistIds.Select(p => "'" + MySqlHelper.EscapeString(p) + "'")));

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@frontlineCutoff", DateTime.UtcNow.AddMonths(-18));
                cmd.Parameters.AddWithValue("@region", region);
                cmd.Parameters.AddWithValue("@ReleaseTypeId", SpotifyAnalyticsAccount.Sony);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var countryOverview = new PlaylistTrackListDistribution()
                        {
                            PlaylistUri = reader.GetString("playlistUri"),
                            TrackCount = reader.GetIntOrFallback("trackCount", 0),
                            LocalTrackCount = reader.GetIntOrFallback("localTrackCount", 0),
                            FrontlineTrackCount = reader.GetIntOrFallback("frontlineTrackCount", 0),
                            SonyTrackCount = reader.GetIntOrFallback("sonyTrackCount", 0),
                        };

                        playlistKpis.Add(countryOverview);
                    }
                }
            }
            return playlistKpis;
        }

        public async Task SavePlaylistChangeAsync(IEnumerable<PlaylistChangeHistoryEntry> changes)
        {
            var sql = new StringBuilder(@"INSERT IGNORE INTO tblSpotifyPlaylistChangeHistory (PlaylistId, Date, ChangeType, OldValue, NewValue) VALUES ");

            sql.Append(String.Join(", ", changes.Select(c =>
            {
                string oldValue = String.IsNullOrWhiteSpace(c.OldValue) ? "NULL" : MySqlHelper.EscapeString(c.OldValue);
                string newValue = String.IsNullOrWhiteSpace(c.NewValue) ? "NULL" : MySqlHelper.EscapeString(c.NewValue);

                return $"('{MySqlHelper.EscapeString(c.PlaylistId)}', '{c.Date.ToString("yyyy-MM-dd")}', {(int)c.ChangeType}, '{oldValue}', '{newValue}')";
            })));

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var cmd = new MySqlCommand(sql.ToString(), conn);

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public void SavePlaylistChangeAsync(string playlistId, DateTime date, PlaylistChangeType changeType, string oldValue, string newValue)
        {
            PetaPocoRepository.Instance.Insert(new PlaylistChangeHistoryEntry() { PlaylistId = playlistId, ChangeType = changeType, Date = date, OldValue = oldValue, NewValue = newValue });
        }

        public async Task<List<PlaylistChangeHistoryEntry>> GetPlaylistChangesAsync(string playlistUri)
        {
            //Explicit selection of columns to get resultcolums as well.
            return PetaPocoRepository.ReadOnlyInstance.Fetch<PlaylistChangeHistoryEntry>("SELECT * FROM tblSpotifyPlaylistChangeHistory WHERE playlistUri = @0", playlistUri);
        }

        public async Task<string> GetLegacyUriAsync(string playlistId)
        {
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = @"
SELECT PlaylistUri
FROM tblSpotifyPlaylistLegacyUri
WHERE playlistId = @playlistId";

                var cmd = new MySqlCommand(sql, conn);

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

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        return reader.GetString(0);
                    }
                }
            }

            return null;
        }

        public async Task<string[]> GetAllPersonalizedPlaylistIdsAsync()
        {
            const string sql = @"SELECT PlaylistId FROM tblSpotifyPersonalizedPlaylist;";

            var ids = new List<string>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = new MySqlCommand(sql, conn);

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

            return ids.ToArray();
        }

        public IList<SpotifyPersonalizedPlaylist> GetPersonalizedPlaylists()
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyPersonalizedPlaylist>();
        }

        public SpotifyPersonalizedPlaylist GetPersonalizedPlaylist(string playlistId)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefault<SpotifyPersonalizedPlaylist>(playlistId);
        }

        public async Task UpdatePlaylistPersonalizedStatusAsync(string playlistId, bool isCurrentlyPersonalized, bool? wasPersonalized = null)
        {
            if (!wasPersonalized.HasValue)
            {
                wasPersonalized = GetPersonalizedPlaylist(playlistId) != null;
            }

            await UpdatePlaylistPersonalizedStatusAsync(playlistId, isCurrentlyPersonalized, wasPersonalized.Value);
        }

        private async Task UpdatePlaylistPersonalizedStatusAsync(string playlistId, bool isCurrentlyPersonalized, bool wasPersonalized)
        {
            if (isCurrentlyPersonalized && !wasPersonalized)
            {
                await Task.WhenAll(
                    this.MarkPlaylistAsPersonalizedAsync(playlistId),
                    this.AddSpotifyPersonalizedPlaylistHistoryEntryAsync(new SpotifyPersonalizedPlaylistHistoryEntry() { PlaylistId = playlistId, StatusChange = PersonalizationStatusChange.SetAsPersonalized, Timestamp = DateTime.UtcNow }));
            }
            else if (!isCurrentlyPersonalized && wasPersonalized)
            {
                await Task.WhenAll(
                    this.UnMarkPlaylistAsPersonalizedAsync(playlistId),
                    this.AddSpotifyPersonalizedPlaylistHistoryEntryAsync(new SpotifyPersonalizedPlaylistHistoryEntry() { PlaylistId = playlistId, StatusChange = PersonalizationStatusChange.SetAsNotPersonalized, Timestamp = DateTime.UtcNow }));
            }
        }

        public async Task AddSpotifyPersonalizedPlaylistHistoryEntryAsync(SpotifyPersonalizedPlaylistHistoryEntry entry)
        {
            if (entry == null || String.IsNullOrWhiteSpace(entry.PlaylistId)) { return; }

            const string sql = "INSERT INTO tblSpotifyPersonalizedPlaylistHistory(PlaylistId, StatusChange, Timestamp) VALUES (@playlistId, @statusChange, @timeStamp)";

            using (var connection = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@playlistId", entry.PlaylistId);
                command.Parameters.AddWithValue("@statusChange", entry.StatusChange);
                command.Parameters.AddWithValue("@timeStamp", entry.Timestamp);

                await command.ExecuteNonQueryAsync();
            }
        }

        public async Task MarkPlaylistAsPersonalizedAsync(string playlistId)
        {
            const string sql = "INSERT IGNORE INTO tblSpotifyPersonalizedPlaylist(PlaylistId) VALUES (@playlistId)";

            using (var connection = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@playlistId", playlistId);

                await command.ExecuteNonQueryAsync();
            }
        }

        public async Task UnMarkPlaylistAsPersonalizedAsync(string playlistId)
        {
            const string sql = "DELETE FROM tblSpotifyPersonalizedPlaylist WHERE PlaylistId = (@playlistId)";

            using (var connection = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@playlistId", playlistId);

                await command.ExecuteNonQueryAsync();
            }
        }

        public async Task<long> CountGeneralAmountOfPlaylistsForUser(string user)
        {
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $@"
SELECT COUNT(PlaylistCounter.Playlist) as Count FROM
	(
		SELECT distinct(p.PlaylistUri) as Playlist FROM tblSpotifyPlaylist AS p
		LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.playlistUri AND ip.musicServiceId = 'Spotify'
		WHERE p.User = '{MySqlHelper.EscapeString(user)}'
			AND p.removed = 0
			AND ip.playlistId IS NULL
    ) 
as PlaylistCounter";

                var cmd = new MySqlCommand(sql, conn);
                var result = Convert.ToInt64(await cmd.ExecuteScalarAsync());

                return result;
            }
        }

        public async Task SetSavetracklistForPlaylistsAsync(IEnumerable<string> ids, bool saveTracklist)
        {
            Func<IEnumerable<string>, MySqlConnection, MySqlTransaction, MySqlCommand> createUpdateCommand = (b, connection, transaction) => {
                var command = new MySqlCommand() { 
                    Connection = connection,
                    Transaction = transaction
                };
                
                for (int i = 0; i < b.Count(); ++i)
                {
                    command.CommandText += $"UPDATE tblSpotifyPlaylist SET SaveTracklist = @saveTracklist WHERE PlaylistId = @playlistId{i};";
                    command.Parameters.AddWithValue($"playlistId{i}", b.ElementAt(i));
                }

                command.Parameters.AddWithValue("@saveTracklist", saveTracklist);

                return command;
            };

            using (var con = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var trans = await con.BeginTransactionAsync();

                foreach (var batch in ids.Batch(500))
                {
                    var command = createUpdateCommand(batch, con, trans);

                    await command.ExecuteNonQueryAsync();
                }

                trans.Commit();
            }
        }
    }

    public class PlaylistTrackListDistribution
    {
        public string PlaylistUri { get; set; }

        public int TrackCount { get; set; }

        public int SonyTrackCount { get; set; }

        public int FrontlineTrackCount { get; set; }

        public int LocalTrackCount { get; set; }
    }
    public class CountryOverview
    {
        public string CountryCode { get; set; }

        public int Playlists { get; set; }

        public int PublicPlaylists { get; set; }

        public int? PlaylistFollowers { get; set; }
        public int? PublicPlaylistFollowers { get; set; }
    }

    public class HistoricPlaylistsTrackSummary
    {
        public SpotifyPlaylist SpotifyPlaylist { get; set; }

        public DateTime EarliestTrackDate { get; set; }

        public DateTime LatestTrackDate { get; set; }
        public string ISRC { get; set; }
        public string AccountId { get; set; }
        public string AccountName { get; set; }
    }

    public class SpotifyPlaylistTrackCount
    {
        public int TotalTracks { get; set; }

        public int SonyTracks { get; set; }
        public DateTime Date { get; set; }
    }

    public class SpotifyPlaylistMarketTrackCount
    {
        public string Market { get; set; }

        public List<SpotifyPlaylistTrackCount> TrackCounts { get; set; }
    }

    public class SpotifyPlaylistHistory
    {
        public string PlaylistId { get; set; }

        public List<TrackWithEarliestDate> Tracks { get; set; }
    }

    public class SpotifyPlaylistHistoryToday
    {
        public string PlaylistId { get; set; }

        public TrackWithEarliestDate Track { get; set; }
    }

    public class TrackWithEarliestDate
    {
        public string TrackId { get; set; }

        public DateTime EarliestDate { get; set; }

        public int? PlaylistIndex { get; set; }

        public string ISRC { get; set; }

        public string ArtistsName { get; set; }

        public string TrackName { get; set; }
    }

    [Serializable]
    public class SpotifyPlaylistWithStreams : SpotifyPlaylist
    {
        public int Streams56Days { get; set; }
        public int Streams28Days { get; set; }
        public int Streams14Days { get; set; }
        public int Streams7Days { get; set; }
        public int StreamsLatest { get; set; }

        public int Users56Days { get; set; }
        public int Users28Days { get; set; }
        public int Users14Days { get; set; }
        public int Users7Days { get; set; }
        public int UsersLatest { get; set; }

        public int? Followers1dayAgo { get; set; }
        public int? Followers7daysAgo { get; set; }
        public int? Followers14daysAgo { get; set; }
        public int? Followers28daysAgo { get; set; }
        public int? Followers56daysAgo { get; set; }

        public double? StreamsPerUser56Days { get; set; }
        public double? StreamsPerUser28Days { get; set; }
        public double? StreamsPerUser14Days { get; set; }
        public double? StreamsPerUser7Days { get; set; }
        public double? StreamsPerUserLatest { get; set; }

        public int StreamDaysInPeriod7Days { get; set; }
        public int StreamDaysInPeriod56Days { get; set; }
        public int StreamDaysInPeriod28Days { get; set; }
        public int StreamDaysInPeriod14Days { get; set; }

        public TrackStreamsInPlaylists Streams { get; set; }
    }

    public class SpotifyPlaylistInMarket
    {
        public SpotifyPlaylist Playlist { get; set; }

        public PlaylistStreamSummary StreamSummary { get; set; }

        public PlaylistFollowerSummary FollowerSummary { get; set; }

        public bool IsPersonalized { get; set; }
    }

    public class SpotifyPlaylistWithoutTrackData
    {
        public SpotifyPlaylistWithStreams Playlist { get; set; }
        public PlaylistStreamSummary PlaylistStreamSummary { get; set; }
        public SpotifyPlaylistMarketStreamsItem MarketPlaylistStreams { get; set; }
        public SpotifyPlaylistBuzzItem BuzzUser { get; set; }
        public PlaylistFollowerSummary PlaylistFollowerSummary { get; set; }
    }

    [Serializable]
    public class PlaylistTrackListItemSummary
    {
        public int Position { get; set; }
        public DateTime? Added { get; set; }
        public DateTime? EarliestAdded { get; set; }
        public string ISRC { get; set; }
        public string TrackId { get; set; }
    }

    [Serializable]
    public class PlaylistStreamSummary
    {
        public int Streams56Days { get; set; }
        public int Streams28Days { get; set; }
        public int Streams14Days { get; set; }
        public int Streams7Days { get; set; }
        public int StreamsLatest { get; set; }

        public int Users56Days { get; set; }
        public int Users28Days { get; set; }
        public int Users14Days { get; set; }
        public int Users7Days { get; set; }
        public int UsersLatest { get; set; }

        public double? StreamsPerUser56Days { get; set; }
        public double? StreamsPerUser28Days { get; set; }
        public double? StreamsPerUser14Days { get; set; }
        public double? StreamsPerUser7Days { get; set; }
        public double? StreamsPerUserLatest { get; set; }

        public int StreamsDays56days { get; set; }
        public int StreamsDays28days { get; set; }
        public int StreamsDays14days { get; set; }
        public int StreamsDays7days { get; set; }

        public double AverageStreams56Days { get; set; }
        public double AverageStreams28Days { get; set; }
        public double AverageStreams14Days { get; set; }
        public double AverageStreams7Days { get; set; }

        public double AverageListeners56Days { get; set; }
        public double AverageListeners28Days { get; set; }
        public double AverageListeners14Days { get; set; }
        public double AverageListeners7Days { get; set; }
    }

    [Serializable]
    public class PlaylistFollowerSummary
    {
        public int? Followers1dayAgo { get; set; }
        public int? Followers7daysAgo { get; set; }
        public int? Followers14daysAgo { get; set; }
        public int? Followers28daysAgo { get; set; }
        public int? Followers56daysAgo { get; set; }
    }

    [Serializable]
    public class SpotifyPlaylistMarketStreamsItem
    {
        public int? MarketStreams56Days { get; set; }
        public int? MarketStreams28Days { get; set; }
        public int? MarketStreams14Days { get; set; }
        public int? MarketStreams7Days { get; set; }
        public int? MarketStreamsLatest { get; set; }

        public int? MarketUsers56Days { get; set; }
        public int? MarketUsers28Days { get; set; }
        public int? MarketUsers14Days { get; set; }
        public int? MarketUsers7Days { get; set; }
        public int? MarketUsersLatest { get; set; }
    }

    [Serializable]
    public class SpotifyPlaylistBuzzItem
    {
        public string BuzzUsername { get; set; }

        public string BuzzDisplayName { get; set; }

        public int? BuzzCategoryId { get; set; }

        public string BuzzCategoryName { get; set; }

        public string BuzzCountryCode { get; set; }
    }

    [Serializable]
    public class SpotifyPlaylistWithTrackData
    {
        public SpotifyPlaylistWithStreams Playlist { get; set; }
        public PlaylistTrackListItemSummary TracklistItem { get; set; }
        public PlaylistStreamSummary PlaylistStreamSummary { get; set; }
        public SpotifyPlaylistMarketStreamsItem MarketPlaylistStreams { get; set; }
        public SpotifyPlaylistBuzzItem BuzzUser { get; set; }
        public PlaylistFollowerSummary PlaylistFollowerSummary { get; set; }
    }

    [Serializable]
    public class TrackStreamsInPlaylists
    {
        public int Local1Day { get; set; }
        public int Local7Days { get; set; }
        public int Global7Days { get; set; }
        public int Global1Day { get; set; }
    }

    public struct SqlLimitOffset
    {
        public readonly int Offset;
        public readonly int Limit;

        public SqlLimitOffset(int offset, int limit)
        {
            this.Offset = offset;
            this.Limit = limit;
        }
    }
}