using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using MoreLinq;
using MySqlConnector;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Contracts.Entities.Genre;
using Sony.Filtr.Database;
using Sony.Filtr.PlaylistGeneration.Data;

namespace Sony.Filtr.PlaylistGeneration.Caching
{
    public class DbCacheProvider : ICacheProvider
    {
        public List<SimilarArtistMatch> FetchSimilarArtistsFromCache(string artistName)
        {
            List<SimilarArtistMatch> artistMatches = new List<SimilarArtistMatch>();

            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT a.strArtistName, a.strImageUrl, a.strLastFMImageID, a.blnHasSonyTracks, a.intListeners, a.intPlaycount, sa.fltScore FROM tblArtist AS a INNER JOIN tblArtistSimilarArtist AS sa ON a.strArtistName=sa.strSimilarArtistName" +
                                                    " WHERE sa.strArtistName=@strArtistName", conn);
                cmd.Parameters.AddWithValue("@strArtistName", artistName);
                using(var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var artist = new Artist(reader);
                        var score = reader.GetInt32(6);
                        var artistMatch = new SimilarArtistMatch(artist, score);
                        artistMatches.Add(artistMatch);
                    }
                }
            }
            return artistMatches;
        }

        public void CacheSimilarArtists(Artist artist, List<SimilarArtistMatch> similarArtists)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var deleteSql = "DELETE FROM tblArtistSimilarArtist WHERE strArtistName = @artistName";
                MySqlCommand delCmd = new MySqlCommand(deleteSql, conn);
                delCmd.Parameters.AddWithValue("@artistName", artist.Name);
                delCmd.ExecuteNonQuery();

                var sql = "INSERT INTO tblArtistSimilarArtist (strArtistName, strSimilarArtistName, fltScore) VALUES ";
                List<string> paramValues = similarArtists.Select(similarArtist => " ('" + MySqlHelper.EscapeString(artist.Name) + "', '" + MySqlHelper.EscapeString(similarArtist.Artist.Name) + "', " + similarArtist.Match + ") ").ToList();
                sql += string.Join(",", paramValues);

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
        }

        public void CacheArtist(Artist artist)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand("INSERT INTO tblArtist (strArtistName, " +
                                                    "strImageUrl, strLastFMImageID, blnHasSonyTracks, " +
                                                    "intListeners, intPlaycount) " +
                                                    "VALUES (@strArtistName, @strImageUrl, @strLastFMImageID, " +
                                                    "@blnHasSonyTracks, @intListeners, @intPlaycount)" +
                                                    " ON DUPLICATE KEY UPDATE strImageUrl=@strImageUrl, " +
                                                    "strLastFMImageID=@strLastFMImageID, blnHasSonyTracks=@blnHasSonyTracks," +
                                                    " intListeners=@intListeners, intPlaycount=@intPlaycount", conn);
                cmd.Parameters.AddWithValue("@strArtistName", artist.Name);
                cmd.Parameters.AddWithValue("@strImageUrl", artist.ImageUrl);
                cmd.Parameters.AddWithValue("@strLastFMImageID", artist.LastFMImageID);
                cmd.Parameters.AddWithValue("@blnHasSonyTracks", artist.IsSonyArtist);
                cmd.Parameters.AddWithValue("@intListeners", artist.Listeners);
                cmd.Parameters.AddWithValue("@intPlaycount", artist.PlayCount);

                cmd.ExecuteNonQuery();
            }
        }

        public Artist FetchArtistFromCache(string artistName)
        {
            Artist artist = null;
            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT strArtistName, strImageUrl, strLastFMImageID, blnHasSonyTracks, intListeners, intPlaycount FROM tblArtist WHERE strArtistName COLLATE utf8_general_ci = @strArtistName LIMIT 1", conn);

                cmd.Parameters.AddWithValue("@strArtistName", artistName);
                var reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    artist = new Artist(reader); 
                }
            }
            return artist;
        }

        public async Task<List<Artist>> FetchArtistsFromCacheAsync(List<string> artistNames)
        {
            List<Artist> artists = new List<Artist>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT strArtistName, strImageUrl, strLastFMImageID, blnHasSonyTracks, intListeners, intPlaycount FROM tblArtist " +
                    "WHERE strArtistName COLLATE utf8_general_ci IN (@artistNames)";
                var artistParameters = string.Join(",", artistNames.Select(a => "'" + MySqlHelper.EscapeString(a) + "'"));
                sql = sql.Replace("@artistNames", artistParameters);
                MySqlCommand cmd = new MySqlCommand(sql, conn);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var artist = new Artist(reader);
                        artists.Add(artist);
                    }
                }
            }
            return artists;
        }

        private void InsertTracks(IEnumerable<Track> tracks, MySqlConnection conn,  uint? artistID)
        {
            List<string> paramValues = new List<string>();
            int order = 1;
            var sql = "INSERT INTO tblArtistTopTrack (intArtistID, strTrackName, strSpotifyUrl, blnIsSonyTrack, strTerritoryCodes, intOrder) VALUES ";
            foreach (var track in tracks)
            {
                paramValues.Add(" (" + artistID + ", '" + MySqlHelper.EscapeString(track.Name) + "', '" + MySqlHelper.EscapeString(track.SpotifyLink.Uri) + "', " + track.IsSonyTrack.ToString() + ", '" + MySqlHelper.EscapeString(string.Join(",", track.Territories)) + "'," + order + ") ");
                order++;
            }
            sql += string.Join(",", paramValues);

            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
        }

        private void DeleteTracks(uint? artistID, MySqlConnection conn)
        {
            MySqlCommand deleteCmd = new MySqlCommand("DELETE FROM tblArtistTopTrack WHERE intArtistID=@intArtistID", conn);
            deleteCmd.Parameters.AddWithValue("@intArtistID", artistID);
            deleteCmd.ExecuteNonQuery();
        }

        public void CacheGenreMatch(Application application, Artist artist, IEnumerable<GenreMatch> genres)
        {
            return;
        }

        public List<GenreMatch> FetchGenreMatchFromCache(Application application, Artist artist)
        {
            return null;
        }



        public void CacheGenreArtists(Genre genre, IEnumerable<SimilarArtistMatch> artists)
        {
            if (artists == null || !artists.Any())
                return;

            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var tran = conn.BeginTransaction();

                MySqlCommand delCmd = new MySqlCommand("DELETE FROM tblGenreArtist WHERE intGenreId=@intGenreID", conn);
                delCmd.Parameters.Add(new MySqlParameter("@intGenreID", genre.ID));
                delCmd.ExecuteNonQuery();

                var sql = "INSERT INTO tblGenreArtist (intGenreID, strArtistName, fltScore) VALUES ";
                List<string> paramValues = new List<string>();
                foreach (var artist in artists)
                {
                    paramValues.Add(" (" + genre.ID + ", '" + MySqlHelper.EscapeString(artist.Artist.Name) + "', " + artist.Match.ToString(CultureInfo.InvariantCulture) + ") ");
                }
                sql += string.Join(",", paramValues);

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();

                tran.Commit();
            }
        }

        public List<SimilarArtistMatch> FetchGenreArtists(Genre genre)
        {
            List<SimilarArtistMatch> artistMatches = new List<SimilarArtistMatch>();

            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT strArtistName, fltScore FROM tblGenreArtist WHERE intGenreID=@intGenreID ORDER BY fltScore DESC", conn);
                cmd.Parameters.AddWithValue("@intGenreID", genre.ID);
                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    var artistName = reader.GetString(0);
                    var score = reader.GetInt32(1);
                    var artistMatch = new SimilarArtistMatch(new Artist(artistName), score);
                    artistMatches.Add(artistMatch);
                }

                reader.Close();
            }
            return artistMatches;
        }

        public async Task<List<RankedItem<Track>>> FetchTopTrackForTagAsync(string tag)
        {
            List<RankedItem<Track>> topTracks = new List<RankedItem<Track>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT strArtistName, strName, fltScore, strMBID FROM tblTagTopTracks WHERE strTag=@strTag", conn);
                cmd.Parameters.Add(new MySqlParameter("@strTag", tag));
                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var track = new Track(reader.GetString(0), reader.GetString(1));
                    var weight = reader.GetDouble(2);
                    if (!reader.IsDBNull(3))
                        track.MBID = reader.GetString(3);

                    topTracks.Add(new RankedItem<Track>(track, (int)weight));
                }

                reader.Close();
            }
            return topTracks;
        }

        public async Task CacheTopTrackForTagAsync(string tag, List<RankedItem<Track>> topTracks)
        {
            if (tag == null || topTracks == null || !topTracks.Any())
                return;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var deleteSql = "DELETE FROM tblTagTopTracks WHERE strTag = @strTag";
                MySqlCommand deleteCommand = new MySqlCommand(deleteSql, conn);
                deleteCommand.Parameters.AddWithValue("@strTag", tag);
                await deleteCommand.ExecuteNonQueryAsync();

                var insertSql = "INSERT IGNORE INTO tblTagTopTracks (strTag, strArtistName, strName, strMBID, fltScore) VALUES ";

                List<string> paramValues = new List<string>();
                foreach (var track in topTracks)
                {
                    paramValues.Add(" ('" + MySqlHelper.EscapeString(tag) + "', '" + MySqlHelper.EscapeString(track.Item.ArtistName) + "', '" + MySqlHelper.EscapeString(track.Item.Name) + "', '" + track.Item.MBID + "', " + track.Weight + ") ");
                }
                insertSql += string.Join(",", paramValues);

                MySqlCommand cmd = new MySqlCommand(insertSql, conn);
                await cmd.ExecuteNonQueryAsync();
            }
        }

        public string GetCacheProviderName()
        {
            return "MusicMatch.PlaylistGeneration.Caching.DbCacheProvider";
        }

        public void CacheArtistTags(string artist, List<Tag> tags)
        {
            if (artist == null || tags == null || !tags.Any())
                return;

            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var sql = "INSERT INTO tblArtistTags (strArtistName, strTagName, fltScore) VALUES ";

                List<string> paramValues = new List<string>();
                foreach (var tag in tags)
                {
                    paramValues.Add(" ('" + MySqlHelper.EscapeString(artist) + "', '" + MySqlHelper.EscapeString(tag.Name) + "', " + tag.Score.ToString(CultureInfo.InvariantCulture) + ") ");
                }
                sql += string.Join(",", paramValues);

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
        }
        
        public async Task<Dictionary<string, List<Tag>>> FetchArtistsTagsFromCacheAsync(List<string> artists)
        {
            Dictionary<string, List<Tag>> artistTags = new Dictionary<string, List<Tag>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT strArtistName, strTagName, fltScore FROM tblArtistTags WHERE strArtistName IN (@strArtistsName)";
                var artistParameters = string.Join(",", artists.Select(a => "'" + MySqlHelper.EscapeString(a) + "'"));
                sql = sql.Replace("@strArtistsName", artistParameters);

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                
                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var artistName = reader.GetString(0);
                    var tagName = reader.GetString(1);
                    var score = reader.GetDouble(2);

                    List<Tag> tagsForArtist;
                    if (!artistTags.TryGetValue(artistName, out tagsForArtist))
                    {
                        tagsForArtist = new List<Tag>();
                        artistTags.Add(artistName, tagsForArtist);
                    }
                    tagsForArtist.Add(new Tag() { Name = tagName, Score = score });
                }

                reader.Close();
            }
            return artistTags;
        }

        public async Task<List<Tag>> FetchArtistTagsFromCacheAsync(string artist)
        {
            List<Tag> artistTags = new List<Tag>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT strTagName, fltScore FROM tblArtistTags WHERE strArtistName=@strArtistName", conn);
                cmd.Parameters.AddWithValue("@strArtistName", artist);
                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var tagName = reader.GetString(0);
                    var score = reader.GetInt32(1);
                    artistTags.Add(new Tag() { Name = tagName, Score =  score});
                }

                reader.Close();
            }
            return artistTags;
        }
        
        public async Task CacheTrackTagsAsync(Track track, List<Tag> tags)
        {
            if (track == null || tags == null || !tags.Any())
                return;

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "INSERT INTO tblTrackTags (strArtistName, strTrackName, strTagName, fltScore) VALUES ";

                List<string> paramValues = new List<string>();
                foreach (var tag in tags)
                {
                    paramValues.Add(" ('" + MySqlHelper.EscapeString(track.ArtistName) + "', '" + MySqlHelper.EscapeString(track.Name) + "', '" + MySqlHelper.EscapeString(tag.Name) + "', " + tag.Score.ToString(CultureInfo.InvariantCulture) + ") ");
                }
                sql += string.Join(",", paramValues);

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task<List<Tag>> FetchTrackTagsFromCacheAsync(Track track)
        {
            List<Tag> artistTags = new List<Tag>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT strTagName, fltScore FROM tblTrackTags WHERE BINARY strArtistName = BINARY @strArtistName AND BINARY strTrackName = BINARY @strTrackName";
                cmd.Parameters.AddWithValue("@strArtistName", track.ArtistName);
                cmd.Parameters.AddWithValue("@strTrackName", track.Name);
                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var tagName = reader.GetString(0);
                    var score = reader.GetInt32(1);
                    artistTags.Add(new Tag() { Name = tagName, Score = score });
                }

                reader.Close();
            }
            return artistTags;
        }

        public async Task<Dictionary<string, List<ExtendedTrack>>> FetchExtendedTopTracksForArtistsAsync(List<string> artists, ServiceType serviceType)
        {
            List<ExtendedTrack> extendedTracks = new List<ExtendedTrack>();
            foreach (var artistBatch in artists.Batch(100))
            {
                var tracks = await FetchExtendedTracksInternalAsync(artistBatch.ToList(), serviceType);
                extendedTracks.AddRange(tracks);
            }
            
            return extendedTracks.GroupBy(k=> k.ArtistName).ToDictionary(k=> k.Key, v=> v.ToList());
        }

        private async Task<List<ExtendedTrack>> FetchExtendedTracksInternalAsync(List<string> artists, ServiceType serviceType)
        {
            List<ExtendedTrack> tracks = new List<ExtendedTrack>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql =
                    "SELECT a.strArtistName, t.strTrackName, t.strSpotifyUrl, t.blnIsSonyTrack, t.strTerritoryCodes, " +
                    "t.intOrder, t.dblDanceability, t.dblEnergy, t.dblHotness, t.dblLoudness, t.intMode, t.dblTempo, " +
                    "t.dblArtistFamiliarity, t.dblArtistHotness, t.intFetchCodeVersion, t.datFetchDate, t.strSongTypes, t.intOrder, t.intDuration, t.intDeezerID, t.strISRC " +
                    "FROM tblArtistExtendedTopTrack AS t " +
                    "INNER JOIN tblArtist AS a ON a.intID=t.intArtistID " +
                    "WHERE t.intServiceType = @intServiceType AND a.strArtistName IN (@strArtistsName) ORDER BY t.intOrder";
                var artistParameters = string.Join(",", artists.Select(a => "'" + MySqlHelper.EscapeString(a) + "'"));
                sql = sql.Replace("@strArtistsName", artistParameters);

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@intServiceType", serviceType);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var track = new ExtendedTrack();
                    var artistName = reader.GetString(0);

                    track.ArtistName = artistName;
                    track.Title = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        track.SpotifyLink = new SpotifyLink(reader.GetString(2));

                    track.IsSonyTrack = reader.GetBoolean(3);
                    track.Territories = reader.GetString(4).Split(',').ToList();
                    track.Danceability = (!reader.IsDBNull(6) ? (double?) reader.GetDouble(6) : null);
                    track.Energy = (!reader.IsDBNull(7) ? (double?) reader.GetDouble(7) : null);
                    track.Hotness = (!reader.IsDBNull(8) ? (double?) reader.GetDouble(8) : null);
                    track.Loudness = (!reader.IsDBNull(9) ? (double?) reader.GetDouble(9) : null);
                    track.Mode = (!reader.IsDBNull(10) ? (int?) reader.GetInt32(10) : null);
                    track.Tempo = (!reader.IsDBNull(11) ? (double?) reader.GetDouble(11) : null);
                    track.ArtistFamiliarity = (!reader.IsDBNull(12) ? (double?) reader.GetDouble(12) : null);
                    track.ArtistHotnesss = (!reader.IsDBNull(13) ? (double?) reader.GetDouble(13) : null);
                    track.FetchCodeVersion = (!reader.IsDBNull(14) ? (int?) reader.GetInt32(14) : null);
                    track.FetchDate = (!reader.IsDBNull(15) ? (DateTime?)DateTime.SpecifyKind(reader.GetDateTime(15), DateTimeKind.Utc) : null);
                    track.SongType = (!reader.IsDBNull(16) ? reader.GetString(16) : string.Empty).Split(',').ToList();
                    track.Order = reader.GetInt32(17);
                    track.Duration = (!reader.IsDBNull(18) ? reader.GetInt32(18) : 0);
                    track.DeezerId = (!reader.IsDBNull(19) ? (long?) reader.GetInt64(19) : null);
                    track.ISRC = (!reader.IsDBNull(20) ? reader.GetString(20) : null);

                    tracks.Add(track);
                }
                reader.Close();
            }
            return tracks;
        }


        public async Task<List<ExtendedTrack>> FetchExtendedTopTracksForArtistAsync(string artistName, ServiceType serviceType)
        {
            List<ExtendedTrack> tracks = new List<ExtendedTrack>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT a.strArtistName, t.strTrackName, t.strSpotifyUrl, t.blnIsSonyTrack, t.strTerritoryCodes, " +
                                                    "t.intOrder, t.dblDanceability, t.dblEnergy, t.dblHotness, t.dblLoudness, t.intMode, t.dblTempo, " +
                                                    "t.dblArtistFamiliarity, t.dblArtistHotness, t.intFetchCodeVersion, t.datFetchDate, t.strSongTypes, t.intOrder, t.intDuration, t.intDeezerID, t.strISRC " +
                                                    "FROM tblArtistExtendedTopTrack AS t " +
                                                    "INNER JOIN tblArtist AS a ON a.intID=t.intArtistID " +
                                                    "WHERE a.strArtistName = @strArtistName AND t.intServiceType = @intServiceType ORDER BY t.intOrder", conn);
                cmd.Parameters.AddWithValue("@strArtistName", artistName);
                cmd.Parameters.AddWithValue("@intServiceType", serviceType);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var track = new ExtendedTrack();
                    track.ArtistName = reader.GetString(0);
                    track.Title = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        track.SpotifyLink = new SpotifyLink(reader.GetString(2));
                    track.IsSonyTrack = reader.GetBoolean(3);
                    track.Territories = reader.GetString(4).Split(',').ToList();
                    track.Danceability = (!reader.IsDBNull(6) ? (double?)reader.GetDouble(6) : null); 
                    track.Energy = (!reader.IsDBNull(7) ? (double?)reader.GetDouble(7) : null);
                    track.Hotness = (!reader.IsDBNull(8) ? (double?) reader.GetDouble(8) : null);
                    track.Loudness = (!reader.IsDBNull(9) ? (double?)reader.GetDouble(9) : null);
                    track.Mode = (!reader.IsDBNull(10) ? (int?)reader.GetInt32(10) : null);
                    track.Tempo = (!reader.IsDBNull(11) ? (double?)reader.GetDouble(11) : null);
                    track.ArtistFamiliarity = (!reader.IsDBNull(12) ? (double?)reader.GetDouble(12) : null);
                    track.ArtistHotnesss = (!reader.IsDBNull(13) ? (double?)reader.GetDouble(13) : null);
                    track.FetchCodeVersion = (!reader.IsDBNull(14) ? (int?)reader.GetInt32(14) : null);
                    track.FetchDate = (!reader.IsDBNull(15) ? (DateTime?)DateTime.SpecifyKind(reader.GetDateTime(15), DateTimeKind.Utc) : null);
                    track.SongType = (!reader.IsDBNull(16) ? reader.GetString(16) : string.Empty).Split(',').ToList();
                    track.Order = reader.GetInt32(17);
                    track.Duration = (!reader.IsDBNull(18) ? reader.GetInt32(18) : 0);
                    track.DeezerId= (!reader.IsDBNull(19) ? (long?)reader.GetInt64(19) : null);
                    track.ISRC = (!reader.IsDBNull(20) ? reader.GetString(20) : null);
                    
                    tracks.Add(track);
                }
                reader.Close();
            }
            return tracks;
        }

        public async Task CacheExtendedTopTracksForArtistAsync(string artistName, List<ExtendedTrack> tracks, ServiceType serviceType)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                MySqlCommand artistIDcmd = new MySqlCommand("SELECT intID FROM tblArtist WHERE strArtistName = @strArtistName", conn);
                artistIDcmd.Parameters.AddWithValue("@strArtistName", artistName);
                uint? artistID = await artistIDcmd.ExecuteScalarAsync() as uint?;
                if (!artistID.HasValue)
                {
                    MySqlCommand insertCmd = new MySqlCommand("INSERT INTO tblArtist (strArtistName) VALUES (@strArtistName)", conn);
                    insertCmd.Parameters.AddWithValue("@strArtistName", artistName);
                    await insertCmd.ExecuteNonQueryAsync();
                    artistID = (uint)insertCmd.LastInsertedId;
                }
                else
                {
                    MySqlCommand updateCmd = new MySqlCommand("UPDATE tblArtist SET datUpdated = @datUpdated WHERE intID=@intArtistID", conn);
                    updateCmd.Parameters.AddWithValue("@datUpdated", DateTime.Now);
                    updateCmd.Parameters.AddWithValue("@intArtistID", artistID.Value);
                    await updateCmd.ExecuteNonQueryAsync();
                }

                await DeleteExtendedTracks(artistID, conn, serviceType);
                if (tracks.Any())
                {
                    await InsertExtendedTracks(tracks, conn, artistID.Value, serviceType);
                }
            }
        }

        private async Task InsertExtendedTracks(List<ExtendedTrack> tracks, MySqlConnection conn, uint artistID, ServiceType serviceType)
        {
            const string sql = "INSERT INTO tblArtistExtendedTopTrack (intArtistID, strTrackName, strSpotifyUrl, blnIsSonyTrack, " + 
                               "strTerritoryCodes, intOrder, dblDanceability, dblEnergy, dblHotness, dblLoudness, intMode, dblTempo," +
                               "dblArtistFamiliarity, dblArtistHotness, intFetchCodeVersion, datFetchDate, strSongTypes, intDuration, intDeezerID, strISRC, intServiceType)" +
                               "VALUES (@intArtistID, @strTrackName, @strSpotifyUrl, @blnIsSonyTrack, @strTerritoryCodes, @intOrder, @dblDanceability, @dblEnergy, @dblHotness, @dblLoudness, @intMode, @dblTempo, @dblArtistFamiliarity, @dblArtistHotness, @intFetchCodeVersion, @datFetchDate, @strSongTypes, @intDuration, @intDeezerID, @strISRC, @intServiceType)";

            int order = 0;
            var insertTasks = new List<Task>();
            foreach (var track in tracks)
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@intArtistID", artistID);
                cmd.Parameters.AddWithValue("@strTrackName", track.Title);
                if (track.SpotifyLink != null)
                {
                    cmd.Parameters.AddWithValue("@strSpotifyUrl", track.SpotifyLink.Uri);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@strSpotifyUrl", null);
                }

                cmd.Parameters.AddWithValue("@blnIsSonyTrack", track.IsSonyTrack);
                cmd.Parameters.AddWithValue("@strTerritoryCodes", string.Join(",", track.Territories));
                cmd.Parameters.AddWithValue("@intOrder", ++order);
                cmd.Parameters.AddWithValue("@dblDanceability", track.Danceability);
                cmd.Parameters.AddWithValue("@dblEnergy", track.Energy);
                cmd.Parameters.AddWithValue("@dblHotness", track.Hotness);
                cmd.Parameters.AddWithValue("@dblLoudness", track.Loudness);
                cmd.Parameters.AddWithValue("@intMode", track.Mode);
                cmd.Parameters.AddWithValue("@dblTempo", track.Tempo);
                cmd.Parameters.AddWithValue("@dblArtistFamiliarity", track.ArtistFamiliarity);
                cmd.Parameters.AddWithValue("@dblArtistHotness", track.ArtistHotnesss);
                cmd.Parameters.AddWithValue("@datFetchDate", track.FetchDate);
                cmd.Parameters.AddWithValue("@intFetchCodeVersion", track.FetchCodeVersion);
                cmd.Parameters.AddWithValue("@strSongTypes", string.Join(",",track.SongType));
                cmd.Parameters.AddWithValue("@intDuration", track.Duration);
                cmd.Parameters.AddWithValue("@intDeezerID", track.DeezerId);
                cmd.Parameters.AddWithValue("@strISRC", track.ISRC);
                cmd.Parameters.AddWithValue("@intServiceType", serviceType);

                var insertTask = cmd.ExecuteNonQueryAsync();
                insertTasks.Add(insertTask);
            }

            await Task.WhenAll(insertTasks);
        }

        private async Task DeleteExtendedTracks(uint? artistID, MySqlConnection conn, ServiceType serviceType)
        {
            MySqlCommand deleteCmd = new MySqlCommand("DELETE FROM tblArtistExtendedTopTrack WHERE intArtistID=@intArtistID AND intServiceType=@intServiceType", conn);
            deleteCmd.Parameters.AddWithValue("@intArtistID", artistID);
            deleteCmd.Parameters.AddWithValue("@intServiceType", serviceType);
            await deleteCmd.ExecuteNonQueryAsync();
        }

        public void CacheExtendedTopTracksForTag(string tag, List<GroupedExtendedTrack> groupedExtendedTracks)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var fetchDate = DateTime.Now;
                foreach (var track in groupedExtendedTracks)
                {
                    var mbid = track.Track.Item.MBID;

                    MySqlCommand insertCmd = new MySqlCommand("INSERT IGNORE INTO tblExtendedTopTracks " +
                                                              "(strMBID, strTrackName, strArtistName, blnIsSonyTrack, dblDanceability, dblEnergy, dblHotness, dblLoudness, intMode, dblTempo, dblArtistFamiliarity, dblArtistHotness, datFetchDate, strSongTypes, intDeezerID) VALUES " +
                                                              "(@strMBID, @strTrackName, @strArtistName, @blnIsSonyTrack, @dblDanceability, @dblEnergy, @dblHotness, @dblLoudness, @intMode, @dblTempo, @dblArtistFamiliarity, @dblArtistHotness, @datFetchDate, @strSongTypes, @intDeezerID)", conn);
                        
                    insertCmd.Parameters.AddWithValue("@strMBID", mbid);
                    insertCmd.Parameters.AddWithValue("@strTrackName", track.ExtendedTrack.Title);
                    insertCmd.Parameters.AddWithValue("@strArtistName", track.ExtendedTrack.ArtistName);
                    insertCmd.Parameters.AddWithValue("@blnIsSonyTrack", 0);
                    insertCmd.Parameters.AddWithValue("@dblDanceability", track.ExtendedTrack.Danceability);
                    insertCmd.Parameters.AddWithValue("@dblEnergy", track.ExtendedTrack.Energy);
                    insertCmd.Parameters.AddWithValue("@dblHotness", track.ExtendedTrack.Hotness);
                    insertCmd.Parameters.AddWithValue("@dblLoudness", track.ExtendedTrack.Loudness);
                    insertCmd.Parameters.AddWithValue("@intMode", track.ExtendedTrack.Mode);
                    insertCmd.Parameters.AddWithValue("@dblTempo", track.ExtendedTrack.Tempo);
                    insertCmd.Parameters.AddWithValue("@dblArtistFamiliarity", track.ExtendedTrack.ArtistFamiliarity);
                    insertCmd.Parameters.AddWithValue("@dblArtistHotness", track.ExtendedTrack.ArtistHotnesss);
                    insertCmd.Parameters.AddWithValue("@datFetchDate", fetchDate);
                    insertCmd.Parameters.AddWithValue("@strSongTypes", string.Join(",", track.ExtendedTrack.SongType));
                    insertCmd.Parameters.AddWithValue("@intDeezerID", track.ExtendedTrack.DeezerId);
                        
                    insertCmd.ExecuteNonQuery();

                    if (track.SpotifyTracks.Any())
                    { 
                        var spotifyTracksSql =
                            "INSERT IGNORE INTO tblSpotifyTrack (strMBID, strSpotifyUri, strTerritoryCodes, strTrackName, strArtistName, intDuration) " +
                            "VALUES ";
                        spotifyTracksSql += string.Join(", ", track.SpotifyTracks.Select(t=> string.Format("('{0}', '{1}', '{2}', '{3}', '{4}', {5})", MySqlHelper.EscapeString(mbid), MySqlHelper.EscapeString(t.uri), MySqlHelper.EscapeString(string.Join(" ", t.available_markets)), MySqlHelper.EscapeString(t.name), MySqlHelper.EscapeString(t.artists.First().name), t.duration_ms/1000)));
                        MySqlCommand spInsertCmd = new MySqlCommand(spotifyTracksSql, conn);
                        spInsertCmd.ExecuteNonQuery();
                    }
                }
            }
        }

        public List<GroupedExtendedTrack> FetchExtendedTopTracksForTag(string tag)
        {
            List<GroupedExtendedTrack> tracks = new List<GroupedExtendedTrack>();

            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT t.strTag, t.fltScore, t.strArtistName, t.strName, st.strSpotifyUri, st.strTerritoryCodes, st.intDuration, " +
                                                    "et.strMBID, et.strTrackName, et.strArtistName, et.blnIsSonyTrack, et.dblDanceability, et.dblEnergy, et.dblHotness, " +
                                                    "et.dblLoudness, et.intMode, et.dblTempo, et.dblArtistFamiliarity, et.dblArtistHotness, et.strSongTypes, et.intDeezerID" +
                                                    " FROM tblTagTopTracks AS t " +
                                                    "INNER JOIN tblExtendedTopTracks AS et ON t.strMBID = et.strMBID " +
                                                    "INNER JOIN tblSpotifyTrack AS st ON t.strMBID = st.strMBID " +
                                                    "WHERE t.strTag=@strTag", conn);
                cmd.Parameters.AddWithValue("@strTag", tag);

                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    var track = new GroupedExtendedTrack();
                    var tagName = reader.GetString(0);
                    var score = reader.GetFloat(1);
                    var artistName = reader.GetString(2);
                    var trackName = reader.GetString(3);
                    var spotifyUri = reader.GetString(4);
                    var territoryCodes = reader.GetString(5);
                    var duration = reader.GetInt32(6);
                    var mbid = reader.GetString(7);
                    //var etTrackName = reader.GetString(8);
                    //var etArtistName = reader.GetString(9);
                    var isSony = reader.GetBoolean(10);
                    var danceability = reader.GetDouble(11);
                    var energy = reader.GetDouble(12);
                    var hotness = reader.GetDouble(13);
                    var loudness = reader.GetDouble(14);
                    var mode = reader.GetInt32(15);
                    var tempo = reader.GetDouble(16);
                    var artistFamiliarity = reader.IsDBNull(17) ? (double?)null :  reader.GetDouble(17);
                    var artistHotness = reader.IsDBNull(18) ? (double?)null : reader.GetDouble(18);
                        
                    //var songTypes = reader.GetString(19);
                    long? deezerId = null;
                    if (!reader.IsDBNull(20))
                        deezerId = reader.GetInt64(20);

                    track.MBID = mbid;
                    track.Track = new RankedItem<Track>(new Track(artistName, trackName), (int)score);
                    track.ExtendedTrack = new ExtendedTrack()
                    {
                        Title = trackName,
                        ArtistName = artistName,
                        Territories = territoryCodes.Split(new[] { " " }, StringSplitOptions.None).ToList(),
                        SpotifyLink = new SpotifyLink(spotifyUri),
                        Duration = duration,
                        IsSonyTrack = isSony,
                        Danceability = danceability,
                        Energy = energy,
                        Hotness = hotness,
                        Loudness = loudness,
                        Mode = mode,
                        Tempo = tempo,
                        ArtistFamiliarity = artistFamiliarity,
                        ArtistHotnesss = artistHotness,
                        DeezerId = deezerId,
                    };

                    tracks.Add(track);
                }
            }
            return tracks;
        }
    }
}