using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MoreLinq;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Contracts.SpotifyIntegration;
using Sony.Filtr.Core.EditorialPlaylists;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;
using StructureMap;

namespace Sony.Filtr.Worker.Onetime
{
    public class AnalyzeTrackForManyISRCs
    {
        public async Task ExecuteAsync(IContainer container)
        {
            var editorialPlaylistManager = container.GetInstance<EditorialPlaylistManager>();

            List<string> isrcs = new List<string>();
            using (var connection = DatabaseHandler.GetOpenConnection())
            {
                var cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT ISRC FROM TempAnalyzeISRC";

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

            Console.WriteLine("Done loading {0} isrcs from DB", isrcs.Count);

            await isrcs.ForEachAsync(10, async isrc =>
            {
                Console.WriteLine("Lookup for {0}", isrc);

                IDictionary<EditorialPlaylist, SpotifyTrack> editorialPlaylists;
                if (isrc.StartsWith("spotify:track:"))
                {
                    editorialPlaylists = await editorialPlaylistManager.GetEditorialPlaylistsWithTrackAsync(isrc, null);
                }
                else
                {
                    editorialPlaylists = await editorialPlaylistManager.GetEditorialPlaylistsWithTrackAsync(null, isrc);
                }

;
                if (editorialPlaylists.Any())
                {
                    var playlists = editorialPlaylists.Keys.DistinctBy(p => p.SpotifyLink).ToList();

                    var playlistAttributes = await editorialPlaylistManager.GetPlaylistAttributesAsync(playlists);

                    var playlistsCount = playlists.Count;
                    var editorialPlaylistsCount = editorialPlaylists.GroupBy(k => k.Key.SpotifyLink.Uri).Count(g => g.Any(gi => gi.Key.SonyCreated));
                    var filtrPlaylistsCount = playlists.Count(p => !string.IsNullOrWhiteSpace(p.CountryCode));

                    var sumFollowers = playlistAttributes.Sum(p => p.Attributes.Followers);

                    Console.WriteLine("Saving for {0}", isrc);
                    using (var connection = DatabaseHandler.GetOpenConnection())
                    {
                        var cmd = connection.CreateCommand();
                        cmd.CommandText = "UPDATE TempAnalyzeISRC SET PlaylistsCount = @playlistsCount, EditorialPlaylistsCount = @editorialPlaylistsCount, FiltrPlaylistsCount = @filtrPlaylistsCount, SumFollowers = @sumFollowers " +
                                          "WHERE ISRC=@isrc";
                        cmd.Parameters.AddWithValue("@playlistsCount", playlistsCount);
                        cmd.Parameters.AddWithValue("@editorialPlaylistsCount", editorialPlaylistsCount);
                        cmd.Parameters.AddWithValue("@filtrPlaylistsCount", filtrPlaylistsCount);
                        cmd.Parameters.AddWithValue("@sumFollowers", sumFollowers);
                        cmd.Parameters.AddWithValue("@isrc", isrc);

                        await cmd.ExecuteNonQueryAsync();
                    }
                }
            });
        }
    }
}