using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MoreLinq;
using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.DistributedCaching;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Core.TopLists
{
    public class TopListManager
    {
        private readonly DistributedCacheHandler _distributedCacheHandler;

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

        public List<PlaylistTrackFilterCombination> GetFilteringCombinations()
        {
            var cacheKey = GetFilteringCombinationCacheKey();
            var filteringCombinations = _distributedCacheHandler.Get(cacheKey) as List<PlaylistTrackFilterCombination>;
            if (filteringCombinations == null)
            {
                filteringCombinations = PetaPocoRepository.ReadOnlyInstance.Fetch<PlaylistTrackFilterCombination>();
                _distributedCacheHandler.Put(cacheKey, filteringCombinations);
            }
            return filteringCombinations;
        }

        public string GetFilteringCombinationCacheKey()
        {
            return "FilteringCombinations";
        }

        private PlaylistTrackFilterCombination GetFilteringCombination(TrackReleaseType trackReleaseType, string countryCode, TopListType topListType, int? buzzCategoryId)
        {
            return  GetFilteringCombinations().SingleOrDefault(f => f.TrackReleaseType == trackReleaseType
                            && f.CountryCode == countryCode
                            && f.TimeSpanType == topListType
                            && f.BuzzCategoryId == buzzCategoryId);
        }

        public PlaylistTrackFilterCombination AddFilteringCombination(PlaylistTrackFilterCombination playlistTrackFilterCombination)
        {
            var id = PetaPocoRepository.Instance.Insert(playlistTrackFilterCombination);

            _distributedCacheHandler.Remove(GetFilteringCombinationCacheKey());

            playlistTrackFilterCombination.Id = id;
            return playlistTrackFilterCombination;
        }

        public async Task SetTopListAsync(PlaylistTrackFilterCombination combination, List<PlaylistTrackTopListEntry> toplist)
        {
            PetaPocoRepository.Instance.Delete<PlaylistTrackTopListEntry>("WHERE CombinationId=@0", combination.Id);
            
            if (toplist != null && toplist.Any())
            { 
                await PetaPocoRepository.Instance.BulkInsertAsync(toplist);
            }
        }

        public async Task SetTrackTotalPlaylistsAsync(Dictionary<string, int> isrcPlaylistCount)
        {
            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                foreach(var isrcBatch in isrcPlaylistCount.Batch(100))
                {
                    var values = isrcBatch.Select(i => $"('{i.Key}', {i.Value})");
                    MySqlCommand cmd = new MySqlCommand($"INSERT INTO tblSpotifyPlaylistTrackToplistTotal (isrc, TotalPlaylists) VALUES {string.Join(",", values)} ON DUPLICATE KEY UPDATE totalPlaylists = VALUES(totalPlaylists); ", conn);
                    
                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task<List<MostPlaylistedTrackViewModel>> GetToplistAsync(TrackReleaseType trackReleaseType, string countryCode, TopListType topListType, int? buzzCategoryId)
        {
            var filteringCombination = GetFilteringCombination(trackReleaseType, countryCode, topListType, buzzCategoryId);

            if (filteringCombination == null)
                return new List<MostPlaylistedTrackViewModel>();

            Dictionary<string, MostPlaylistedTrackViewModel> trackTopListEntries = new Dictionary<string, MostPlaylistedTrackViewModel>();
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand(@"
SELECT tl.isrc, tl.CountInFilter, t.TrackId, t.Name, a.ArtistId, a.Name, ta.ArtistOrder, tlt.TotalPlaylists
FROM tblSpotifyPlaylistTrackTopList as tl 
INNER JOIN tblSpotifyTrack2 AS t ON t.trackId = (SELECT trackId FROM tblSpotifyTrack2 WHERE isrc=tl.isrc LIMIT 1)
INNER JOIN tblSpotifyTrackArtist AS ta ON t.TrackId = ta.TrackId
INNER JOIN tblSpotifyArtist AS a ON a.ArtistId = ta.ArtistId
INNER JOIN tblSpotifyPlaylistTrackToplistTotal AS tlt ON tl.Isrc = tlt.isrc
WHERE CombinationId = @combinationId
GROUP BY tl.isrc, a.artistId
ORDER BY CountInFilter DESC, ArtistOrder ASC ", conn);

                cmd.Parameters.AddWithValue("@combinationId", filteringCombination.Id);

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

                    var entry = trackTopListEntries.GetValueOrDefault(isrc);
                    if (entry == null)
                    {
                        var countInFilter = reader.GetInt32(1);
                        var trackUri = SpotifyLink.FromTrackId(reader.GetString(2)).Uri;
                        var trackName = reader.GetString(3);
                        var artistId = reader.GetString(4);
                        var artistName = reader.GetString(5);
                        var artistOrder = reader.GetInt32(6);
                        var totalPlaylists = reader.GetInt32(7);

                        entry = new MostPlaylistedTrackViewModel()
                        {
                            ISRC = isrc,
                            CountInFilter = countInFilter,
                            TrackUri = trackUri,
                            TrackName = trackName,
                            ArtistName = artistName,
                            TotalPlaylists = totalPlaylists
                        };
                        entry.Artists.Add(new SpotifyArtistReferenceViewModel()
                        {
                            Name = artistName,
                            Uri = SpotifyLink.FromArtistId(artistId).Uri,
                        });

                        trackTopListEntries.Add(isrc, entry);
                    }
                    else
                    {
                        var artistId = reader.GetString(4);
                        var artistName = reader.GetString(5);
                        var artistOrder = reader.GetInt32(6);

                        entry.Artists.Add(new SpotifyArtistReferenceViewModel()
                        {
                            Name = artistName,
                            Uri = SpotifyLink.FromArtistId(artistId).Uri,
                        });
                    }
                }
            }
            return trackTopListEntries.Select(p=> p.Value).ToList();
        }
    }

    public enum TrackReleaseType
    {
        Both = 0,
        Frontline = 1,
        Catalog = 2
    }
}