using MySql.Data.MySqlClient;
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.Playlists.Models;
using Sony.Filtr.Playlists.Spotify.Model;
using Sony.Filtr.Utility.Extensions;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using PetaPoco.Business;
using Sony.Filtr.Playlists.Spotify.Model.History;
using Dapper;
using System.Collections.Concurrent;
using Sony.Filtr.Utility;
using TaskExtensions = Sony.Filtr.Utility.Extensions.TaskExtensions;
using System.Transactions;
using System.Text;

namespace Sony.Filtr.Playlists.Spotify
{
    public class SpotifyPlaylistHistoricTrackListManager
    {
        private readonly DistributedCacheHandler _cacheHandler;
        private readonly ISpotifyRegionManager _spotifyRegionManager;
        private readonly ISonyMusicManager _sonyMusicManager;

        public SpotifyPlaylistHistoricTrackListManager(DistributedCacheHandler cacheHandler, ISpotifyRegionManager spotifyRegionManager, ISonyMusicManager sonyMusicManager)
        {
            _cacheHandler = cacheHandler;
            _spotifyRegionManager = spotifyRegionManager;
            _sonyMusicManager = sonyMusicManager;
        }

        public async Task<List<SpotifyPlaylistMarketTrackCount>> GetHistoricPlaylistTrackCount(string playlistId, DateTime startDate, DateTime endDate)
        {
            var cacheKey = GetHistoricPlaylistTrackCountCacheKey(playlistId, startDate, endDate);

            var playlistTrackCount = _cacheHandler.Get(cacheKey) as List<SpotifyPlaylistMarketTrackCount>;

            if (playlistTrackCount != null)
                return playlistTrackCount;

            playlistTrackCount = new List<SpotifyPlaylistMarketTrackCount>();

            var historicIsrcs = await GetPlaylistHistoricIsrcs(playlistId, startDate, endDate);

            var distinctIsrcs = historicIsrcs.SelectMany(d => d.Value).FilterNull().Distinct(StringComparer.InvariantCultureIgnoreCase).ToList();
            var isSonyInfo = await _sonyMusicManager.GetSonyTrackMarketsAsync(distinctIsrcs);
            var isSonyPerMarket = isSonyInfo.SelectMany(info => info.Markets, (info, s) => new { Market = s, isrc = info.ISRC }).GroupBy(p => p.Market).ToDictionary(k => k.Key, v => v.Select(p => p.isrc).ToList());

            var allRegions = await _spotifyRegionManager.GetAvailableRegionsAsync();
            foreach(var market in allRegions)
            {
                var marketTrackCount = new SpotifyPlaylistMarketTrackCount() { Market = market, TrackCounts = new List<SpotifyPlaylistTrackCount>() };

                foreach(var dateWithIsrcs in historicIsrcs)
                {
                    marketTrackCount.TrackCounts.Add(new SpotifyPlaylistTrackCount()
                    {
                        Date = dateWithIsrcs.Key,
                        TotalTracks = dateWithIsrcs.Value?.Count ?? 0,
                        SonyTracks = CalculateSonyTrackCount(dateWithIsrcs.Value, isSonyPerMarket.GetValueOrDefault(market)),
                    });
                }

                playlistTrackCount.Add(marketTrackCount);
            }

            _cacheHandler.Put(cacheKey, playlistTrackCount);

            return playlistTrackCount;

        }

        private int CalculateSonyTrackCount(List<string> playlistIsrcs, List<string> sonyIsrcs)
        {
            if (sonyIsrcs == null || !sonyIsrcs.Any())
                return 0;

            return playlistIsrcs.Intersect(sonyIsrcs).Count();
        }

        private string GetHistoricPlaylistTrackCountCacheKey(string playlistId, DateTime startDate, DateTime endDate)
        {
            return $"SpotifyPlaylistManager_GetHistoricPlaylistTrackCount_{playlistId}_{startDate.ToString("yyyy-MM-dd")}_{endDate.ToString("yyyy-MM-dd")}";
        }

        private async Task<Dictionary<DateTime, List<string>>> GetPlaylistHistoricIsrcs(string playlistId, DateTime startDate, DateTime endDate)
        {
            var historicIsrcs = new Dictionary<DateTime, List<string>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $@"
SELECT Date, track.ISRC as Isrc
FROM tblSpotifyPlaylistTrackListHistory2 AS trackList
LEFT JOIN tblSpotifyTrack2 track ON trackList.TrackId = track.TrackId
WHERE PlaylistId = '{playlistId}' AND Date >= '{startDate.ToNeutralShortDateString()}' AND Date <= '{endDate.ToNeutralShortDateString()}'
ORDER BY Date";

                var cmd = new MySqlCommand(sql, conn);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var date = reader.GetDateTime(0);

                    if (!historicIsrcs.ContainsKey(date))
                    {
                        historicIsrcs.Add(date, new List<string>());
                    }

                    var isrc = reader.IsDBNull(1) ? null : reader.GetString(1);
                    historicIsrcs[date].Add(isrc);
                }
            }

            return historicIsrcs;
        }

        public async Task<List<HistoricPlaylistsTrackSummary>> GetHistoricPlaylistsWithTrackSummaryAsync(IEnumerable<string> isrcs, bool excludeCurrentPlaylists = false)
        {
            List<HistoricPlaylistsTrackSummary> playlistSummaries = new List<HistoricPlaylistsTrackSummary>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = BuildHistoricPlaylistsWithTrackSqlCommand(isrcs, excludeCurrentPlaylists, conn);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        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.GetIntOrFallback("duration", 0);
                        var trackCount = reader.GetInt32("trackCount");
                        var followers = reader.GetIntOrFallback("followers", 0);
                        var buzzCategoryId = reader.GetInt32("buzzCategoryId");

                        var earliestTrackDate = reader.GetDateTime("earliestDate");
                        var latestTrackDate = reader.GetDateTime("latestDate");

                        var isrc = reader.GetString("ISRC");

                        string accountId = reader.GetString("buzzUsername");
                        string accountName = reader.GetString("buzzDisplayName");

                        var playlist = new SpotifyPlaylist()
                        {
                            PlaylistUri = playlistUri,
                            Name = playlistName,
                            Description = description,
                            Image = image,
                            User = user,
                            CountryCode = countryCode,
                            Duration = duration,
                            TrackCount = trackCount,
                            Followers = followers,
                            BuzzCategoryId = buzzCategoryId,
                        };

                        playlistSummaries.Add(new HistoricPlaylistsTrackSummary()
                        {
                            SpotifyPlaylist = playlist,
                            EarliestTrackDate = earliestTrackDate,
                            LatestTrackDate = latestTrackDate,
                            ISRC = isrc,
                            AccountName = accountName,
                            AccountId = accountId
                        });
                    }
                }

                return playlistSummaries;
            }
        }

        private static MySqlCommand BuildHistoricPlaylistsWithTrackSqlCommand(IEnumerable<string> isrcs, bool excludeCurrentPlaylists, MySqlConnection conn)
        {
            MySqlCommand cmd;
            string sql = "";
            if (excludeCurrentPlaylists)
            {
                sql = $@"
SELECT 
	pl.PlaylistUri, pl.Name, pl.Description, pl.Image, pl.User, pl.CountryCode, pl.Duration, pl.TrackCount, plf.Followers, pl.UpdateDate, pl.BuzzCategoryId, 
	hd1.Timestamp as earliestDate, hd2.timestamp as latestDate, hi.ISRC,
    bu.username as buzzUsername, bu.displayname as buzzDisplayName
FROM ( 
	SELECT hi.PlaylistId, MIN(hi.date) as earliestDate, MAX(hi.date) as latestDate, ti.ISRC as ISRC
	FROM tblSpotifyPlaylistTrackListHistory2 AS hi 
	INNER JOIN tblSpotifyTrack2 AS ti ON hi.trackId = ti.TrackId 
	WHERE ti.ISRC IN ({Maybe.ToCommaSeparated(isrcs)})
	GROUP BY hi.playlistId, ti.ISRC) AS hi 
INNER JOIN tblSpotifyPlaylist AS pl ON pl.PlaylistId = hi.PlaylistId 
INNER JOIN tblSpotifyPlaylistFollowers AS plf ON plf.playlistId = hi.playlistId 
INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 hd1 ON hi.playlistId = hd1.playlistId AND hi.earliestDate = hd1.Date 
INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 hd2 ON hi.playlistId = hd2.playlistId AND hi.latestDate = hd2.Date 
LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = pl.playlistId AND ip.musicServiceId = @spotifyMusicServiceId
LEFT JOIN BuzzUser AS bu ON pl.User = bu.Username AND bu.ServiceType = @buzzSpotifyServiceType
WHERE pl.Removed = 0 AND ip.playlistId IS NULL 
AND pl.playlistId NOT IN (
    SELECT tl.playlistId 
    FROM tblSpotifyPlaylistTrackList2 AS tl 
    INNER JOIN tblSpotifyPlaylist AS p ON tl.playlistId = p.playlistId
    INNER JOIN tblSpotifyTrack2 AS t2 ON tl.TrackId=t2.trackid
    WHERE t2.isrc IN ({Maybe.ToCommaSeparated(isrcs)}) AND p.Removed = 0)
GROUP BY pl.PlaylistUri, hi.ISRC;";                
            }
            else
            {
                sql = $@"
SELECT
    pl.PlaylistUri, pl.Name, pl.Description, pl.Image, pl.User, pl.CountryCode, 
    pl.Duration, pl.TrackCount, plf.Followers, pl.UpdateDate, pl.BuzzCategoryId, 
    hd1.Timestamp as earliestDate, hd2.timestamp as latestDate, hi.ISRC,
    bu.username as buzzUsername, bu.displayname as buzzDisplayName
FROM ( 
	SELECT hi.playlistId, MIN(hi.date) as earliestDate, MAX(hi.date) as latestDate, ti.ISRC
	FROM tblSpotifyPlaylistTrackListHistory2 AS hi
	INNER JOIN tblSpotifyTrack2 AS ti ON hi.trackId = ti.TrackId 
	WHERE ti.ISRC IN ({Maybe.ToCommaSeparated(isrcs)})
	GROUP BY hi.playlistId, ti.ISRC) AS hi 
INNER JOIN tblSpotifyPlaylist AS pl ON pl.playlistId = hi.playlistId 
INNER JOIN tblSpotifyPlaylistFollowers AS plf ON plf.playlistId = hi.playlistId 
INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 hd1 ON hi.playlistId = hd1.playlistId AND hi.earliestDate = hd1.Date 
INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 hd2 ON hi.playlistId = hd2.playlistId AND hi.latestDate = hd2.Date
LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = pl.playlistUri AND ip.musicServiceId = @spotifyMusicServiceId
LEFT JOIN BuzzUser AS bu ON pl.User = bu.Username AND bu.ServiceType = @buzzSpotifyServiceType
WHERE pl.Removed = 0 AND ip.playlistID IS NULL;";
            }

            cmd = new MySqlCommand(sql, conn);

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

            return cmd;
        }

        public async Task<List<TracklistItem>> GetHistoricTrackListAsync(string playlistId, string date)
        {
            var tracklistItems = new List<TracklistItem>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = @"
SELECT 
    tl.PlaylistIndex, tl.TrackId, t.Name as TrackName,
    t.ISRC, alb.AlbumId, alb.Name as albumName, alb.upc, tl.Popularity, t.Danceability, t.Energy, t.`Key`, t.Loudness, t.Mode, 
    t.Speechiness, t.Acousticness, t.Instrumentalness, t.Liveness, t.Valence, t.Tempo
FROM tblSpotifyPlaylistTrackListHistory2 tl
LEFT JOIN tblSpotifyTrack2 t ON tl.TrackId = t.TrackId
LEFT JOIN tblSpotifyTrackAlbum talb ON t.TrackId = talb.TrackId
LEFT JOIN tblSpotifyAlbum alb ON talb.AlbumId = alb.AlbumId
WHERE tl.playlistId = @playlistId AND tl.Date = @date
GROUP BY 
    tl.PlaylistIndex, tl.TrackId, t.Name, t.ISRC, alb.AlbumId, alb.Name, 
    tl.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";

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

                    using(var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var playlistIndex = reader.GetInt32("PlaylistIndex");

                            var item = BuildTracklistItem(playlistIndex, reader);

                            tracklistItems.Add(item);
                        }
                    }
                }

                if (tracklistItems.Any())
                {
                    tracklistItems = await LoadArtistsAsync(conn, tracklistItems);
                }
            }

            return tracklistItems;
        }

        private async Task<List<TracklistItem>> LoadArtistsAsync(MySqlConnection conn, List<TracklistItem> tracklistItems)
        {
            Dictionary<string, List<SpotifyArtistReferenceViewModel>> allTrackArtists = new Dictionary<string, List<SpotifyArtistReferenceViewModel>>();
            var trackIds = tracklistItems.Select(t => t.TrackId).Distinct();
            using(var cmd = conn.CreateCommand())
            {
                var sql = "SELECT ta.trackId, a.Name as ArtistName, a.ArtistId " +
                          "FROM tblSpotifyArtist a " +
                          "LEFT JOIN tblSpotifyTrackArtist ta ON ta.artistId = a.artistId " +
                          "WHERE ta.TrackId IN ({0}) " +
                          "ORDER BY ta.ArtistOrder ASC";
                sql = string.Format(sql, string.Join(",", trackIds.Select(t => "'" + MySqlHelper.EscapeString(t) + "'")));
                cmd.CommandText = sql;

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

                        SpotifyArtistReferenceViewModel spotifyArtistReference = new SpotifyArtistReferenceViewModel()
                        {
                            Name = artistName,
                            Uri = SpotifyLink.FromArtistId(artistId).Uri,
                        };
                        var trackArtists = allTrackArtists.GetValueOrDefault(trackId);
                        if (trackArtists == null)
                        {
                            allTrackArtists.Add(trackId, new List<SpotifyArtistReferenceViewModel>() { spotifyArtistReference });
                        }
                        else
                        {
                            trackArtists.Add(spotifyArtistReference);
                        }
                    }
                }
            }

            foreach(var tracklistItem in tracklistItems)
            {
                var artists = allTrackArtists.GetValueOrDefault(tracklistItem.TrackId);
                tracklistItem.Artists = artists ?? new List<SpotifyArtistReferenceViewModel>();
                tracklistItem.ArtistName = artists?.FirstOrDefault()?.Name;
            }
            return tracklistItems;
        }

        private static TracklistItem BuildTracklistItem(int playlistIndex, DbDataReader reader)
        {
            var item = new TracklistItem();
            item.Position = playlistIndex;

            item.TrackId = reader.GetString("TrackId");
            item.TrackName = reader.GetString("TrackName");
            
            item.ISRC = reader.GetString("isrc");
            item.AlbumId = reader.GetString("albumId");
            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.AlbumUpc = reader.GetString("upc");

            return item;
        }

        public async Task<bool> DoesPlaylistHaveHistoryForDate(string playlistId, DateTime date)
        {
            return (await GetAvailableHistoricTrackListDatesAsync(playlistId, date.Date)).Any();
        }

        public async Task<Dictionary<DateTime, DateTime>> GetAvailableHistoricTrackListDatesAsync(string playlistId, DateTime? date = null)
        {
            var dates = new Dictionary<DateTime, DateTime>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT DISTINCT(Date), Timestamp " +
                          "FROM tblSpotifyPlaylistTrackListHistoryDates2 " +
                          "WHERE PlaylistId = @playlistId AND (@date IS NULL OR Date = @date) ORDER BY Date DESC";

                var cmd = new MySqlCommand(sql, conn);

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

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        dates.Add(reader.GetDateTime(0), reader.GetDateTime(1));
                    }
                }
            }

            return dates;
        }

        public async Task CopySpotifyTrackListHistoryForTodayAsync(string playlistId)
        {
            Func<string, Task<UpdatePlaylistTrack[]>> getPlaylistLatestHistoryTracksBeforeToday = async (plId) =>
            {
                const string readSql = @"
SELECT tl.TrackId, tl.PlaylistIndex, t.Popularity 
FROM tblSpotifyPlaylistTrackList2 tl
INNER JOIN tblSpotifyTrack2 t on tl.TrackId = t.TrackId
WHERE PlaylistId = @playlistId;";

                List<UpdatePlaylistTrack> latestTracks = new List<UpdatePlaylistTrack>();

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

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            latestTracks.Add(new UpdatePlaylistTrack
                            {
                                Track = new GeneralSpotifyTrack
                                {
                                    Id = reader.GetString("TrackId"),
                                    Popularity = reader.GetInt32("Popularity")
                                },
                                PlaylistIndex = reader.GetInt32("PlaylistIndex")
                            });
                        }
                    }
                }

                return latestTracks.ToArray();
            };

            DateTime now = DateTime.UtcNow;

            if ((await DoesPlaylistHaveHistoryForDate(playlistId, now)) == false)
            {
                await AddSpotifyTrackListHistoryAsync(playlistId, await getPlaylistLatestHistoryTracksBeforeToday(playlistId), now);
            }
        }

        public async Task AddSpotifyTrackListHistoryAsync(string playlistId, IEnumerable<UpdatePlaylistTrack> tracks, DateTime currentDate)
        {
            if (tracks == null || !tracks.Any()) { return; }
            
            Func<MySqlConnection, MySqlTransaction, string , Task> insertHistoryForTable = async (connection, trans, tableName) => {

                var builder = new StringBuilder($"INSERT IGNORE INTO {tableName} (PlaylistId, Date, TrackId, PlaylistIndex, Popularity) VALUES ");

                builder.AppendLine(String.Join(
                    ",", 
                    tracks
                        .Select((track, index) => $"('{MySqlHelper.EscapeString(playlistId)}','{currentDate.ToString("yyyy-MM-dd", CultureInfo.GetCultureInfo("sv-SE"))}','{MySqlHelper.EscapeString(track.Track.Id)}',{index},{track.Track.Popularity})")));

                var command = connection.CreateCommand();
                command.Transaction = trans;
                command.CommandText = builder.ToString();

                await command.ExecuteNonQueryAsync();
            };

            Func<MySqlConnection, MySqlTransaction, Task> insertHistory = (connection, trans) => insertHistoryForTable(connection, trans, "tblSpotifyPlaylistTrackListHistory2");

            Func<MySqlConnection, MySqlTransaction, Task> insertHistoryReduced2 = (connection, trans) => insertHistoryForTable(connection, trans, "tblSpotifyPlaylistTracklistHistory2Reduced2");

            Func <MySqlConnection, MySqlTransaction, Task> insertHistoryDates = async (connection, trans) => {
                var command = connection.CreateCommand();
                command.Transaction = trans;
                command.CommandText =  "INSERT IGNORE INTO tblSpotifyPlaylistTrackListHistoryDates2 (PlaylistId, Date) VALUES (@playlistId, @date)";

                command.Parameters.AddWithValue("@playlistId", playlistId);
                command.Parameters.AddWithValue("@date", currentDate);
                
                await command.ExecuteNonQueryAsync();
            };

            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var transaction = await conn.BeginTransactionAsync())
                {
                    await insertHistory(conn, transaction);

                    await insertHistoryDates(conn, transaction);

                    await insertHistoryReduced2(conn, transaction);
                    transaction.Commit();
                }
            }
        }

        public async Task<IEnumerable<TrackPlaylistSummary>> GetPlaylistSummaryByISRCAsync(IEnumerable<string> isrcs)
        {
            List<TrackPlaylistSummary> results = new List<TrackPlaylistSummary>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = @"
SELECT tl.ISRC, p.buzzCategoryId, COUNT(*) as playlistCount, SUM(pf.followers) as followers
FROM (
     SELECT t.ISRC, tl.PlaylistId
     FROM tblSpotifyPlaylistTrackList2 AS tl
     INNER JOIN tblSpotifyTrack2 AS t ON t.trackId = tl.trackId
     WHERE t.ISRC IN ({0})
     GROUP BY t.isrc, tl.PlaylistId) AS tl
INNER JOIN tblSpotifyPlaylist AS p ON tl.playlistId = p.playlistId
LEFT JOIN tblSpotifyPlaylistFollowers AS pf ON pf.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
GROUP BY tl.ISRC, p.buzzCategoryId";

                sql = string.Format(sql, string.Join(",", isrcs.Select(t => "'" + MySqlHelper.EscapeString(t) + "'")));

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

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var isrc = reader.GetString("ISRC");
                        var buzzCategoryId = reader.GetIntOrDefault("BuzzCategoryId");
                        var playlistCount = reader.GetIntOrFallback("PlaylistCount", 0);
                        var followers = reader.GetIntOrFallback("followers", 0);

                        results.Add(new TrackPlaylistSummary() { Identifier = isrc, BuzzCategoryId = buzzCategoryId, PlaylistCount = playlistCount, Followers = followers });
                    }
                }
            }
            return results;
        }

        public async Task<List<TrackPlaylistSummary>> GetPlaylistSummaryByTrackIdAsync(List<string> trackIds)
        {
            List<TrackPlaylistSummary> results = new List<TrackPlaylistSummary>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = @"
SELECT tl.trackId, p.buzzCategoryId, COUNT(*) as playlistCount, SUM(pf.followers) as followers
FROM (
    SELECT TrackId, PlaylistId
    FROM tblSpotifyPlaylistTrackList2
    WHERE TrackId IN ({0})
    GROUP BY TrackId, PlaylistId) as tl
INNER JOIN tblSpotifyPlaylist AS p ON tl.playlistId = p.playlistId
LEFT JOIN tblSpotifyPlaylistFollowers AS pf ON pf.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
GROUP BY tl.TrackId, p.buzzCategoryId";
                sql = string.Format(sql, string.Join(",", trackIds.Select(t => "'" + MySqlHelper.EscapeString(t) + "'")));

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

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var trackId = reader.GetString("trackId");
                        var buzzCategoryId = reader.GetIntOrDefault("buzzCategoryId");
                        var playlistCount = reader.GetIntOrFallback("playlistCount", 0);
                        var followers = reader.GetIntOrFallback("followers", 0);

                        results.Add(new TrackPlaylistSummary() { Identifier = trackId, BuzzCategoryId = buzzCategoryId, PlaylistCount = playlistCount, Followers = followers });
                    }
                }
            }
            return results;
        }

        public async Task<IEnumerable<TrackPlaylistSummary>> GetPlaylistHistorySummaryByISRCAsync(IEnumerable<string> isrcs, DateTime? date)
        {
            List<TrackPlaylistSummary> results = new List<TrackPlaylistSummary>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT hl.ISRC, p.BuzzCategoryId, COUNT(*) as playlistCount, SUM(ps.followers) as followers " +
                          "FROM " +
                          " (SELECT t.isrc, h.PlaylistId, MAX(date) as date " +
                          "  FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                          "  INNER JOIN tblSpotifyTrack2 AS t ON t.trackId = h.trackId " +
                          " WHERE t.ISRC IN ({0}) AND (@date IS NULL OR h.date=@date) " +
                          " GROUP BY t.isrc, h.playlistId " +
                          " ) AS hl " +
                          "INNER JOIN tblSpotifyPlaylist as p ON p.playlistId = hl.playlistId " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                          "LEFT JOIN tblSpotifyPlaylistFollowerHistory AS ps ON ps.playlistId = p.playlistId AND ps.date = hl.date " +
                          "WHERE p.Removed = 0 AND ip.playlistId IS NULL " +
                          "GROUP BY hl.isrc, p.buzzCategoryId";

                sql = string.Format(sql, string.Join(",", isrcs.Select(t => "'" + MySqlHelper.EscapeString(t) + "'")));

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@date", (object)date ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var trackId = reader.GetString("isrc");
                        var buzzCategoryId = reader.GetIntOrDefault("buzzCategoryId");
                        var playlistCount = reader.GetIntOrFallback("playlistCount", 0);
                        var followers = reader.GetIntOrFallback("followers", 0);

                        results.Add(new TrackPlaylistSummary() { Identifier = trackId, BuzzCategoryId = buzzCategoryId, PlaylistCount = playlistCount, Followers = followers });
                    }
                }
            }
            return results;
        }

        public async Task<List<TrackPlaylistSummary>> GetPlaylistHistorySummaryByTrackIdAsync(List<string> trackIds, DateTime? date)
        {
            List<TrackPlaylistSummary> results = new List<TrackPlaylistSummary>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT hl.trackId, p.BuzzCategoryId, COUNT(*) as playlistCount, SUM(ps.followers) as followers  " +
                          "FROM " +
                          " (SELECT h.trackId, h.PlaylistId, MAX(date) as date " +
                          " FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                          " WHERE h.TrackId IN ({0}) AND (@date IS NULL OR h.date=@date)  " +
                          " GROUP BY h.TrackId, h.playlistId " +
                          " ) AS hl " +
                          "INNER JOIN tblSpotifyPlaylist as p ON p.playlistId = hl.playlistId " +
                          "LEFT JOIN tblSpotifyPlaylistFollowerHistory AS ps ON ps.playlistId = p.playlistId AND ps.date = hl.date " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.playlistUri AND ip.musicServiceId = @spotifyMusicServiceId " +
                          "WHERE p.Removed = 0 AND ip.playlistID IS NULL " +
                          "GROUP BY hl.TrackId, p.buzzCategoryId";


                sql = string.Format(sql, string.Join(",", trackIds.Select(t => "'" + MySqlHelper.EscapeString(t) + "'")));

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@date", (object)date ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var trackId = reader.GetString("trackId");
                        var buzzCategoryId = reader.GetIntOrDefault("buzzCategoryId");
                        var playlistCount = reader.GetIntOrFallback("playlistCount", 0);
                        var followers = reader.GetIntOrFallback("followers", 0);

                        results.Add(new TrackPlaylistSummary() { Identifier = trackId, BuzzCategoryId = buzzCategoryId, PlaylistCount = playlistCount, Followers = followers });
                    }
                }
            }

            return results;
        }

        public async Task<IEnumerable<TrackPlaylistSummary>> GetPlaylistHistoricOnlySummaryByTrackIdAsync(List<string> trackIds, DateTime? date)
        {
            List<TrackPlaylistSummary> results = new List<TrackPlaylistSummary>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT hl.trackId, p.BuzzCategoryId, COUNT(*) as playlistCount, SUM(ps.followers) as followers  " +
                          "FROM " +
                          " (SELECT h.trackId, h.PlaylistId, MAX(date) as date " +
                          " FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                          $" WHERE h.TrackId IN ({Maybe.ToCommaSeparated(trackIds)}) AND (@date IS NULL OR h.date=@date)  " +
                          " GROUP BY h.TrackId, h.playlistId " +
                          " ) AS hl " +
                          "INNER JOIN tblSpotifyPlaylist as p ON p.playlistId = hl.playlistId " +
                          "LEFT JOIN tblSpotifyPlaylistFollowerHistory AS ps ON ps.playlistId = p.playlistId AND ps.date = hl.date " +
                          "LEFT JOIN tblSpotifyPlaylistTrackList2 AS tl ON hl.TrackId = tl.TrackId AND tl.PlaylistId = p.PlaylistId " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                          "WHERE p.Removed = 0 AND tl.PlaylistId IS NULL AND ip.playlistId IS NULL " +
                          "GROUP BY hl.TrackId, p.buzzCategoryId";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@date", (object)date ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var trackId = reader.GetString("trackId");
                        var buzzCategoryId = reader.GetIntOrDefault("buzzCategoryId");
                        var playlistCount = reader.GetIntOrFallback("playlistCount", 0);
                        var followers = reader.GetIntOrFallback("followers", 0);

                        results.Add(new TrackPlaylistSummary() { Identifier = trackId, BuzzCategoryId = buzzCategoryId, PlaylistCount = playlistCount, Followers = followers });
                    }
                }
            }

            return results;
        }

        public async Task<IEnumerable<TrackPlaylistSummary>> GetPlaylistHistoricOnlySummaryByIsrcAsync(IEnumerable<string> isrcs, DateTime? date)
        {
            List<TrackPlaylistSummary> results = new List<TrackPlaylistSummary>();

            string inIsrc = Maybe.ToCommaSeparated(isrcs);

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT hl.isrc, p.BuzzCategoryId, COUNT(*) as playlistCount, SUM(ps.followers) as followers " +
                          "FROM " +
                          " (SELECT t.isrc, h.PlaylistId, MAX(date) as date " +
                          " FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                          " INNER JOIN tblSpotifyTrack2 AS t ON h.TrackId = t.TrackId " +
                          $" WHERE t.isrc IN ({inIsrc}) AND (@date IS NULL OR h.date=@date) " +
                          " GROUP BY t.isrc, h.playlistId) AS hl " +
                          "INNER JOIN tblSpotifyPlaylist as p ON p.playlistId = hl.PlaylistId " +
                          "LEFT JOIN (SELECT t.isrc, tl.PlaylistId " +
                          " FROM tblSpotifyPlaylistTrackList2 AS tl " +
                          " LEFT JOIN tblSpotifyTrack2 AS t ON tl.TrackId = t.trackId WHERE t.isrc " +
                          $" IN ({inIsrc})) AS tl ON hl.isrc = tl.isrc AND hl.PlaylistId = tl.PlaylistId " +
                          "LEFT JOIN tblSpotifyPlaylistFollowerHistory AS ps ON ps.playlistId = p.playlistId AND ps.date = hl.date " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                          "WHERE p.Removed = 0 AND tl.PlaylistId IS NULL AND ip.playlistId IS NULL " +
                          "GROUP BY hl.isrc, p.BuzzCategoryId";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@date", (object)date ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var isrc = reader.GetString("isrc");
                        var buzzCategoryId = reader.GetIntOrDefault("buzzCategoryId");
                        var playlistCount = reader.GetIntOrFallback("playlistCount", 0);

                        results.Add(new TrackPlaylistSummary() { Identifier = isrc, BuzzCategoryId = buzzCategoryId, PlaylistCount = playlistCount });
                    }
                }
            }

            return results;
        }

        public async Task<List<HistoricTrackPosition>> GetHistoricTrackPositionsAsync(string playlistId, string isrc, DateTime startDate, DateTime endDate)
        {
            List<HistoricTrackPosition> results = new List<HistoricTrackPosition>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT h.PlaylistIndex, d.Timestamp, COUNT(h2.TrackId) as trackCount " +
                          "FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                          "INNER JOIN tblSpotifyTrack2 AS t ON h.trackId = t.trackId " +
                          "INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 AS d ON h.playlistId = d.playlistId AND h.date = d.date " +
                          "INNER JOIN tblSpotifyPlaylistTrackListHistory2 AS h2 ON h2.playlistId = h.playlistId AND h2.Date = h.Date " +
                          "WHERE h.playlistId = @playlistId AND t.isrc = @isrc " +
                          "AND h.date BETWEEN @startDate AND @endDate " +
                          "GROUP BY t.isrc, h.date";

                var cmd = new MySqlCommand(sql, conn);

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

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var playlistIndex = reader.GetInt32("playlistIndex");
                        var date = reader.GetUtcDateTime("timestamp");
                        var trackCount = reader.GetInt32("trackCount");

                        results.Add(new HistoricTrackPosition()
                        {
                            Position = playlistIndex,
                            Date = date,
                            TotalTracks = trackCount,
                        });
                    }
                }
            }

            return results;
        }

        public async Task<List<TrackPositionChange>> GetHistoricTrackPositionChangeAsync(string isrc, List<string> playlistIds, DateTime startDate, DateTime endDate)
        {
            List<TrackPositionChange> results = new List<TrackPositionChange>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT h.playlistId, h.PlaylistIndex as startPosition, hd.Timestamp as startTimestamp, h2.PlaylistIndex as endPosition, hd2.Timestamp as endTimestamp " +
                          "FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                          "INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 AS hd ON hd.playlistId = h.playlistId AND hd.date = h.date " +
                          "INNER JOIN tblSpotifyTrack2 AS t ON h.trackId = t.trackId " +
                          "INNER JOIN tblSpotifyPlaylistTrackListHistory2 AS h2 ON h2.playlistId = h.playlistId AND h2.trackId = h.trackId AND h2.date = @endDate " +
                          "INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 AS hd2 ON hd2.playlistId = h2.playlistId AND hd2.date = h2.date " +
                          "WHERE h.PlaylistId IN (@playlistIds) AND t.isrc = @isrc " +
                          "AND h.date = @startdate " +
                          "GROUP BY h.playlistId";

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

                var cmd = new MySqlCommand(sql, conn);

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

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var playlistId = reader.GetString("playlistId");
                        var playlistUri = $"spotify:playlist:{playlistId}";
                        var startPosition = reader.GetInt32("startPosition");
                        var startTimestamp = reader.GetUtcDateTime("startTimestamp");

                        var endPosition = reader.GetInt32("endPosition");
                        var endTimestamp = reader.GetUtcDateTime("endTimestamp");

                        results.Add(new TrackPositionChange()
                        {
                            PlaylistUri = playlistUri,
                            StartPosition = startPosition,
                            StartTimestamp = startTimestamp,
                            EndPosition = endPosition,
                            EndTimestamp = endTimestamp,
                            ISRC = isrc
                        });
                    }
                }
            }

            return results;
        }

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

            public string ISRC { get; set; }

            public int StartPosition { get; set; }

            public DateTime StartTimestamp { get; set; }

            public int EndPosition { get; set; }

            public DateTime EndTimestamp { get; set; }
        }

        public async Task<IEnumerable<HistoricTrackChange>> GetHistoricPlaylistTrackChangeAsync(IEnumerable<string> isrcs, DateTime startDate)
        {
            List<HistoricTrackChange> results = new List<HistoricTrackChange>();

            var currentTrackListsTask = GetCurrentTrackListWorkingDataAsync(isrcs, startDate);

            var historicPlaylistsTask = GetHistoricPlaylistTrackWorkingDataAsync(isrcs, startDate);

            var currentTrackLists = await currentTrackListsTask;
            var historicPlaylists = await historicPlaylistsTask;

            var allIsrcs = historicPlaylists.Select(p => p.ISRC).Union(currentTrackLists.Select(t => t.ISRC)).Distinct();
            foreach(var isrc in allIsrcs)
            {
                var historicPlaylistForTrack = historicPlaylists.Where(p => p.ISRC == isrc).ToList();
                var currentPlaylistsForTrack = currentTrackLists.Where(p => p.ISRC == isrc).ToList();
                var buzzCategories = historicPlaylistForTrack.Select(p => p.BuzzCategoryId).Union(currentTrackLists.Select(p => p.BuzzCategoryId)).Distinct();

                foreach(var category in buzzCategories)
                {
                    var historicPlaylistInCategory = historicPlaylistForTrack.Where(p => p.BuzzCategoryId == category).ToList();
                    var currentPlaylistsInCategory = currentPlaylistsForTrack.Where(p => p.BuzzCategoryId == category).ToList();

                    var playlistCountStart = historicPlaylistInCategory.Count();
                    var followersStart = historicPlaylistInCategory.Sum(p => p.Followers);

                    var playlistCountEnd = currentPlaylistsInCategory.Count();
                    var followersEnd = currentPlaylistsInCategory.Sum(p => p.Followers);

                    var added = currentPlaylistsInCategory.Where(p => p.HasHistoricPlaylistForStartdate).Select(p => p.PlaylistId).Except(historicPlaylistInCategory.Select(p => p.PlaylistId)).Count();
                    var removed = historicPlaylistInCategory.Select(p => p.PlaylistId).Except(currentPlaylistsInCategory.Select(p => p.PlaylistId)).Count();
                    results.Add(new HistoricTrackChange()
                    {
                        ISRC = isrc,
                        BuzzCategoryId = category,
                        Added = added,
                        Removed = removed,
                        FollowersStart = followersStart,
                        FollowersEnd = followersEnd,
                        PlaylistCountEnd = playlistCountEnd,
                        PlaylistCountStart = playlistCountStart
                    });
                }
            }
            return results.OrderBy(p => p.BuzzCategoryId).ToArray();
        }

        private static async Task<List<TempHistoricPlaylist>> GetHistoricPlaylistTrackWorkingDataAsync(IEnumerable<string> isrcs, DateTime date)
        {
            List<TempHistoricPlaylist> historicPlaylists;
            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var historicTracklistSql = "SELECT r.isrc, p.PlaylistId, f.followers, p.buzzCategoryId " +
                                           "FROM ( " +
                                           "    SELECT t.isrc, h.PlaylistId " +
                                           "    FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                                           "    INNER JOIN tblSpotifyTrack2 AS t ON t.trackId = h.trackId " +
                                           "    WHERE t.ISRC IN ({0}) AND h.date = @date " +
                                           "    GROUP BY t.isrc, h.playlistId) AS r " +
                                           "INNER JOIN tblSpotifyPlaylist AS p ON p.playlistId = r.playlistId " +
                                           "LEFT JOIN tblSpotifyPlaylistFollowerHistory AS f ON f.playlistId = p.playlistId AND f.date = @date " +
                                           "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                                           "WHERE p.Removed = 0 AND ip.playlistId IS NULL " +
                                           "ORDER BY r.isrc, p.playlistId";

                historicTracklistSql = string.Format(historicTracklistSql, string.Join(",", isrcs.Select(t => "'" + MySqlHelper.EscapeString(t) + "'")));

                var historicCmd = new MySqlCommand(historicTracklistSql, conn);

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

                var historicReader = await historicCmd.ExecuteReaderAsync();

                historicPlaylists = new List<TempHistoricPlaylist>();
                while(await historicReader.ReadAsync())
                {
                    var isrc = historicReader.GetString("isrc");
                    var playlistId = historicReader.GetString("playlistId");
                    var buzzCategoryId = historicReader.GetInt32("BuzzCategoryId");
                    var followers = historicReader.GetIntOrFallback("followers", 0);

                    historicPlaylists.Add(new TempHistoricPlaylist()
                    {
                        ISRC = isrc,
                        PlaylistId = playlistId,
                        BuzzCategoryId = buzzCategoryId,
                        Followers = followers,
                    });
                }
                historicReader.Close();
            }
            return historicPlaylists;
        }

        private static async Task<List<TempPlaylistCurrentTrackList>> GetCurrentTrackListWorkingDataAsync(IEnumerable<string> isrcs, DateTime startDate)
        {
            List<TempPlaylistCurrentTrackList> currentTrackLists;
            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var currentTracklistSql = "SELECT r.isrc, r.PlaylistId, f.followers, p.buzzCategoryId, hd.date " +
                                          "FROM (" +
                                          " SELECT t.isrc, tl.PlaylistId " +
                                          " FROM tblSpotifyPlaylistTrackList2 AS tl " +
                                          " INNER JOIN tblSpotifyTrack2 AS t ON t.trackId = tl.trackId " +
                                          " WHERE t.ISRC IN ({0}) " +
                                          " GROUP BY t.isrc, tl.playlistId) as r " +
                                          "INNER JOIN tblSpotifyPlaylist AS p ON r.playlistId = p.playlistId " +
                                          "LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.playlistId = p.playlistId " +
                                          "LEFT JOIN tblSpotifyPlaylistTrackListHistoryDates2 AS hd ON hd.playlistId = p.playlistId AND hd.date = @startDate " +
                                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                                          "WHERE p.Removed = 0 AND ip.playlistId IS NULL AND p.BuzzCategoryId IN (1, 5, 7)";

                currentTracklistSql = string.Format(currentTracklistSql, string.Join(",", isrcs.Select(t => "'" + MySqlHelper.EscapeString(t) + "'")));

                var trackListCmd = new MySqlCommand(currentTracklistSql, conn);

                trackListCmd.Parameters.AddWithValue("@startDate", startDate);
                trackListCmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                var reader = await trackListCmd.ExecuteReaderAsync();

                currentTrackLists = new List<TempPlaylistCurrentTrackList>();
                while(await reader.ReadAsync())
                {
                    var isrc = reader.GetString("isrc");
                    var playlistId = reader.GetString("playlistId");
                    var buzzCategoryId = reader.GetInt32("BuzzCategoryId");
                    var followers = reader.GetIntOrFallback("followers", 0);

                    var hasHistoricPlaylist = !reader.IsDBNull("date");

                    currentTrackLists.Add(new TempPlaylistCurrentTrackList()
                    {
                        ISRC = isrc,
                        PlaylistId = playlistId,
                        BuzzCategoryId = buzzCategoryId,
                        Followers = followers,
                        HasHistoricPlaylistForStartdate = hasHistoricPlaylist
                    });
                }
                reader.Close();
            }
            return currentTrackLists;
        }

        public async Task<Dictionary<string, int>> GetTotalPlaylistTrackChangeAsync(IEnumerable<string> isrcs, DateTime startDate)
        {
            Dictionary<string, int> results = new Dictionary<string, int>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT h.isrc, COUNT(*) as playlistCount " +
                          "FROM (" +
                          " SELECT t.isrc, tl.PlaylistId as playlistId " +
                          " FROM tblSpotifyPlaylistTrackList2 AS tl " +
                          " INNER JOIN tblSpotifyTrack2 AS t ON t.trackId = tl.trackId " +
                          " INNER JOIN tblSpotifyPlaylist as p ON tl.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 t.ISRC IN ({0}) AND(tl.earliestAdded > @startDate) " +
                          " GROUP BY t.isrc, tl.playlistId " +
                          "  UNION " +
                          " SELECT t.isrc, tl.PlaylistId as playlistId " +
                          " FROM tblSpotifyPlaylistTrackList2 AS tl " +
                          " INNER JOIN tblSpotifyTrack2 AS t ON t.trackId = tl.trackId " +
                          " INNER JOIN tblSpotifyPlaylist AS p ON tl.PlaylistId = p.playlistId " +
                          " LEFT JOIN tblSpotifyPlaylistTrackListHistoryDates2 AS hdd ON hdd.playlistId = p.playlistId AND hdd.date = @startDate " +
                          " LEFT JOIN tblSpotifyPlaylistTrackListHistory2 AS hd ON hd.playlistId = hdd.playlistId AND hd.date = hdd.date AND hd.trackId = t.trackId " +
                          " LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId=p.playlistUri AND ip.musicServiceId=@spotifyMusicServiceId " +
                          " WHERE p.removed = 0 AND ip.playlistId IS NULL AND t.ISRC IN ({0}) AND (tl.earliestAdded IS NULL OR tl.earliestAdded = '0000-00-00 00:00:00') " +
                          " GROUP BY t.isrc, tl.playlistId) AS h " +
                          "GROUP BY h.isrc";

                sql = string.Format(sql, string.Join(",", isrcs.Select(t => "'" + MySqlHelper.EscapeString(t) + "'")));

                var cmd = new MySqlCommand(sql, conn);

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

                var reader = await cmd.ExecuteReaderAsync();

                while(await reader.ReadAsync())
                {
                    var isrc = reader.GetString("isrc");
                    var playlistCount = reader.GetIntOrFallback("playlistCount", 0);

                    results.Add(isrc, playlistCount);
                }
            }

            return results;
        }

        public async Task<List<string>> GetAllPreviousPlaylistUrisAsync(string isrc, List<string> playlistUris = null)
        {
            var playlistUrisWithTrack = new List<string>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT DISTINCT tlh.PlaylistId " +
                          "FROM tblSpotifyPlaylistTrackListHistory2 tlh " +
                          "INNER JOIN tblSpotifyTrack2 t ON t.TrackId = tlh.TrackId " +
                          "WHERE t.ISRC = @isrc";

                if (playlistUris != null && playlistUris.Any())
                {
                    sql = "SELECT DISTINCT tlh.PlaylistId " +
                          "FROM tblSpotifyPlaylistTrackListHistory2 tlh " +
                          "INNER JOIN tblSpotifyTrack2 t ON t.TrackId = tlh.TrackId " +
                          $"WHERE t.ISRC = @isrc AND tlh.PlaylistId IN ({string.Join(",", playlistUris.Select(p => "'" + MySqlHelper.EscapeString(p) + "'"))})";
                }

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

                var reader = await cmd.ExecuteReaderAsync();

                while(await reader.ReadAsync())
                {
                    var playlistId = reader.GetString(0);

                    playlistUrisWithTrack.Add(playlistId);
                }
            }

            return playlistUrisWithTrack;

        }

        public async Task<PaginatedContent<HistoricTracklistPreviousTrack>> GetPreviousTracksAsync(string playlistId, int limit, int offset)
        {
            var previousTracks = new List<HistoricTracklistPreviousTrack>();
            long totalCount;

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT SQL_CALC_FOUND_ROWS 0 as playlistIndex, h.TrackId, t.Name as TrackName,  " +
                          "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, " +
                          "h.earliestDate, h.latestDate, h.daysInPlaylist, h.peakPosition " +
                          "FROM (" +
                          " SELECT h.trackId, MIN(h.date) as earliestDate, Max(h.date) as latestDate, COUNT(h.date) as daysInPlaylist, MIN(h.playlistIndex) as peakPosition " +
                          " FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                          " LEFT JOIN tblSpotifyPlaylist AS p ON p.playlistId = h.playlistId " +
                          " LEFT JOIN tblSpotifyPlaylistTrackList2 AS tl ON tl.playlistId = p.playlistId AND h.trackId = tl.trackId " +
                          " WHERE h.playlistId = @playlistId AND tl.trackId IS NULL " +
                          " GROUP BY h.trackId " +
                          ") as h " +
                          "LEFT JOIN tblSpotifyTrack2 AS t ON h.trackid = t.trackId " +
                          "LEFT JOIN tblSpotifyTrackAlbum talb ON t.TrackId = talb.TrackId " +
                          "LEFT JOIN tblSpotifyAlbum alb ON talb.AlbumId = alb.AlbumId " +
                          "ORDER BY h.latestDate DESC, h.trackId " +
                          "LIMIT @offset, @limit";

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

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

                            var earliestDate = reader.GetDateTime("earliestDate");
                            var latestDate = reader.GetDateTime("latestDate");
                            var daysInPlaylist = reader.GetInt32("daysInPlaylist");
                            var peakPosition = reader.GetInt32("peakPosition");

                            var track = BuildTracklistItem(0, reader);
                            var previousTrack = new HistoricTracklistPreviousTrack()
                            {
                                Track = track,
                                EarliestDate = earliestDate,
                                LatestDate = latestDate,
                                DaysInPlaylist = daysInPlaylist,
                                PeakPosition = peakPosition
                            };

                            previousTracks.Add(previousTrack);
                        }
                    }
                }

                using(var countCommand = conn.CreateCommand())
                {
                    countCommand.CommandText = "Select FOUND_ROWS()";
                    totalCount = (long)(await countCommand.ExecuteScalarAsync());
                }

                var trackItems = previousTracks.Select(a => a.Track).ToList();

                if (trackItems.Any())
                {
                    await LoadArtistsAsync(conn, trackItems);
                }
            }
           
            return new PaginatedContent<HistoricTracklistPreviousTrack>()
            {
                Items = previousTracks,
                Pagination = new Pagination()
                {
                    Limit = limit,
                    Offset = offset,
                    Total = (int)totalCount
                }
            };
        }

        public async Task<List<TrackPlaylistDateSummary>> GetTrackPlaylistsOverTimeAsync(string isrc, DateTime startDate, DateTime endDate)
        {
            List<TrackPlaylistDateSummary> trackPlaylistCount = new List<TrackPlaylistDateSummary>();

            using(var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT h.date, d.timestamp, COUNT(*) as playlistCount, SUM(s.followers) as playlistFollowers " +
                          "FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                          "INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 AS d ON d.date = h.date AND d.playlistId = h.playlistId " +
                          "INNER JOIN tblSpotifyTrack2 AS t ON h.trackId = t.trackId " +
                          "LEFT JOIN tblSpotifyPlaylistFollowerHistory AS s ON s.playlistId = h.playlistId AND s.date = h.date " +
                          "WHERE t.isrc = @isrc AND h.date BETWEEN @startDate AND @endDate " +
                          "GROUP BY h.date";

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

                var reader = await cmd.ExecuteReaderAsync();

                while(await reader.ReadAsync())
                {
                    //var date = reader.GetDateTime("date");

                    var timestamp = reader.GetUtcDateTime("timestamp");
                    var playlistCount = reader.GetInt32("playlistCount");
                    var playlistFollowers = reader.GetIntOrFallback("playlistFollowers", 0);

                    trackPlaylistCount.Add(new TrackPlaylistDateSummary()
                    {
                        Date = timestamp,
                        PlaylistCount = playlistCount,
                        PlaylistFollowers = playlistFollowers,
                    });
                }
            }

            return trackPlaylistCount;
        }

        public async Task<Dictionary<string, DateTime>>  GetEarliestAddedDateAsync(string playlistId, List<string> isrcs)
        {
            if (!isrcs.Any())
            {
                return new Dictionary<string, DateTime>(StringComparer.InvariantCultureIgnoreCase);
            }

            Dictionary<string, DateTime> earliestAddedDate = new Dictionary<string, DateTime>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var isrcParameter = string.Join(",", isrcs.Select(t => "'" + MySqlHelper.EscapeString(t) + "'"));

                var sql = "SELECT t.isrc, Min(d.timestamp) as earliestTimestamp " +
                          "FROM tblSpotifyPlaylistTrackListHistory2 AS h " +
                          "INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 AS d ON d.date = h.date AND d.playlistId = h.playlistId " +
                          "INNER JOIN tblSpotifyTrack2 AS t ON h.trackId = t.trackId " +
                          $"WHERE h.playlistId = @playlistId AND t.isrc IN ({isrcParameter}) " +
                          "GROUP BY t.isrc";

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

                    using(var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var isrc = reader.GetString("isrc");
                            var timestamp = reader.GetUtcDateTime("earliestTimestamp");

                            earliestAddedDate.Add(isrc, timestamp);
                        }
                    }
                }
            }

            return earliestAddedDate;
        }

        public async Task<Dictionary<string, List<HistoricTrackPosition>>> GetTrackPositionChangesByTrackAsync(
                  IEnumerable<string> isrcs,
                  IEnumerable<string> playlistIds,
                  DateTime datePivot)
        {
            if (!playlistIds.Any())
            {
                return new Dictionary<string, List<HistoricTrackPosition>>();
            }

            string sql = $@"
SELECT 
	history.PlaylistId,
    t.isrc as isrc,
    history.trackId,
    history.playlistIndex AS previous_position,
    MAX(history.date) AS changed_date
FROM 
    tblSpotifyTrack2 t
INNER JOIN 
    tblSpotifyPlaylistTrackListHistory2 history ON t.TrackId = history.trackId
WHERE 
    history.playlistId IN ({Maybe.ToCommaSeparated(playlistIds)}) 
    AND t.ISRC IN ({Maybe.ToCommaSeparated(isrcs)})
    AND history.date > @datePivot
GROUP BY
    history.PlaylistId, history.trackId, history.playlistIndex;";

            Dictionary<string, List<HistoricTrackPosition>> result = new Dictionary<string, List<HistoricTrackPosition>>();

            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@datePivot", datePivot);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            string trackId = reader.GetString("trackId");
                            int previousPosition = reader.GetInt32("previous_position");
                            DateTime changeDate = reader.GetDateTime("changed_date");
                            string isrc = reader.GetString("isrc");
                            string playlistId = reader.GetString("PlaylistId");

                            if (!result.ContainsKey(playlistId))
                            {
                                result.Add(playlistId, new List<HistoricTrackPosition>());
                            }

                            if (!String.IsNullOrWhiteSpace(isrc))
                            {
                                result[playlistId].Add(new HistoricTrackPosition()
                                {
                                    Date = changeDate,
                                    Position = previousPosition,
                                    TrackId = trackId,
                                    Isrc = isrc
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }

        public async Task<Dictionary<string, HistoricTrackDates>> GetPlaylistTrackDates(string playlistId)
        {
string sql = @"
SELECT
    MIN(history.date) AS 'EarliestAddedDate',
    p.UpdateDate AS 'UpdateDate',
    t.Isrc AS 'Isrc'
FROM
    tblSpotifyPlaylistTrackListHistory2 history
INNER JOIN tblSpotifyTrack2 t ON t.TrackId = history.TrackId
INNER JOIN tblSpotifyPlaylist p ON history.PlaylistId = p.PlaylistId
WHERE history.PlaylistId = @playlistId
GROUP BY t.Isrc";

            var result = new Dictionary<string, HistoricTrackDates>();

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

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            DateTime updateDate = reader.GetDateTime("UpdateDate");
                            DateTime earliestAddedDate = reader.GetDateTime("EarliestAddedDate");
                            string isrc = reader.GetString("isrc");

                            if (!String.IsNullOrWhiteSpace(isrc))
                            {
                                result.Add(isrc, new HistoricTrackDates()
                                {
                                    Isrc = isrc,
                                    EarliestAddeDate = earliestAddedDate,
                                    UpdateDate = updateDate
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }

        public async Task<Dictionary<string, List<HistoricTrackPosition>>> GetTrackPositionChangesByPlaylistByIdAsync(string playlistId, DateTime datePivot, string isrc)
        {
            string sql = @"
SELECT 
    t.isrc as isrc,
    history.trackId,
    history.playlistIndex AS previous_position,
    MAX(history.date) AS changed_date
FROM 
    tblSpotifyTrack2 t
INNER JOIN 
    tblSpotifyPlaylistTrackListHistory2 history ON t.TrackId = history.trackId
WHERE 
    history.playlistId = @playlistId
    AND history.date > @datePivot
    AND ((@isrc IS NULL) OR (t.isrc = @isrc))
GROUP BY
    history.trackId, history.playlistIndex;";

            Dictionary<string, List<HistoricTrackPosition>> result = new Dictionary<string, List<HistoricTrackPosition>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@isrc", String.IsNullOrWhiteSpace(isrc) ? null : isrc);
                    cmd.Parameters.AddWithValue("@playlistId", playlistId);
                    cmd.Parameters.AddWithValue("@datePivot", datePivot);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            string trackId = reader.GetString("trackId");
                            int previousPosition = reader.GetInt32("previous_position");
                            DateTime changeDate = reader.GetDateTime("changed_date");
                            string isrcCurrent = reader.GetString("isrc");

                            if (!String.IsNullOrWhiteSpace(isrcCurrent))
                            {
                                if (!result.ContainsKey(isrcCurrent))
                                {
                                    result.Add(isrcCurrent, new List<HistoricTrackPosition>());
                                }

                                result[isrcCurrent].Add(new HistoricTrackPosition()
                                {
                                    Date = changeDate,
                                    Position = previousPosition,
                                    TrackId = trackId,
                                    Isrc = isrcCurrent
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }

        public async Task<Dictionary<string, HistoricTrackPosition>> GetTrackPositionChangesByPlaylistByIdAsync(
            string playlistId,
            Dictionary<string, int> trackPositions,
            DateTime oldestDateCutoff)
        {
            if (!trackPositions.Any())
                return new Dictionary<string, HistoricTrackPosition>();

            int counter = 0;
            /* This value is best fit regarding performance to different number of items in trackPositions.*/
            int groupSize = trackPositions.Count() > 100 ? 100 : 1;
            var groupedPositions = trackPositions.GroupBy(x => counter++ / groupSize);

            var tasks = new List<Task>();
            var sql = "SELECT  t.isrc, hd.timestamp, h1.playlistIndex FROM tblSpotifyPlaylistTrackListHistory2 AS h1 " +
                        " INNER JOIN (  SELECT hi.TrackId, hi.playlistId, Max(hi.date) as latestDate  FROM tblSpotifyPlaylistTrackListHistory2 AS hi " +
                            $" INNER JOIN tblSpotifyTrack2 AS t ON hi.trackid = t.trackId WHERE hi.playlistId = '{MySqlHelper.EscapeString(playlistId)}' AND hi.date > '{oldestDateCutoff.ToNeutralShortDateString()}' " +
                              " AND ( @trackPositionStatements ) " +
                              " GROUP BY hi.TrackId ) AS ho ON h1.playlistId = ho.playlistId AND h1.date = ho.latestDate AND h1.trackid = ho.trackid " +
                              "INNER JOIN tblSpotifyPlaylistTrackListHistoryDates2 As hd ON h1.playlistId = hd.playlistId AND h1.date = hd.date " +
                              "INNER JOIN tblSpotifyTrack2 As t ON t.trackId = h1.trackId " +
                              "GROUP BY t.isrc";

            var results = new ConcurrentDictionary<string, HistoricTrackPosition>();

            foreach (IGrouping<int, KeyValuePair<string, int>> position in groupedPositions)
            {
                tasks.Add(Task.Run(async () => {
                    using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
                    {
                        var trackStatements = position.Select(p => $" (t.isrc = '{MySqlHelper.EscapeString(p.Key)}' AND hi.playlistIndex != {p.Value} ) ");
                        var execSql = sql.Replace("@trackPositionStatements", string.Join(" OR ", trackStatements));

                        IEnumerable<dynamic> historicTrackPositions = await conn.QueryAsync<dynamic>(execSql);
                        foreach (var track in historicTrackPositions)
                        {
                            results.TryAdd((string)track.isrc, new HistoricTrackPosition {
                                Position = track.playlistIndex,
                                Date = DateTime.SpecifyKind(track.timestamp, DateTimeKind.Utc)
                                //TotalTracks = track.trackCount
                            });
                        }
                    }
                }));
            }

            await TaskExtensions.EnsureAllSuccessAsync(tasks);

            return results.ToDictionary(x => x.Key, y => y.Value);
        }

        public async Task<List<string>> GetPlaylistWithHistoricTracklistsAsync(DateTime date)
        {
            List<string> playlists = new List<string>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT PlaylistId " +
                          "FROM tblSpotifyPlaylistTrackListHistoryDates2 " +
                          "WHERE Date = @date";

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

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

                            playlists.Add(playlistId);
                        }
                    }
                }
            }

            return playlists;
        }

        public List<SpotifyPlaylistTracklistHistoryMode> GetCustomTracklistHistoryModes()
        {
            return PetaPocoRepository.Instance.Fetch<SpotifyPlaylistTracklistHistoryMode>();
        }
        public void SetCustomTracklistHistoryMode(SpotifyPlaylistTracklistHistoryMode spotifyPlaylistTracklistHistoryMode)
        {
            PetaPocoRepository.Instance.Upsert(spotifyPlaylistTracklistHistoryMode);
        }
    }

    public class TempHistoricPlaylist
    {
        public string ISRC { get; set; }

        public string PlaylistId { get; set; }

        public int BuzzCategoryId { get; set; }

        public int Followers { get; set; }
    }
}