using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Amazon.SimpleWorkflow.Model;
using MySql.Data.MySqlClient;
using NLog;
using Sony.Filtr.Buzz;
using Sony.Filtr.Contracts.Abstractions;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Database;
using Sony.Filtr.Tasks.Tasks.Spotify;
using Sony.Filtr.Utility.Extensions;
using SpreadsheetLight;

namespace Sony.Filtr.Worker.Onetime
{
    public class MostPlaylistedArtists
    {
        private readonly BuzzAccountManager _buzzAccountManager;
        private Logger _logger;

        public MostPlaylistedArtists(BuzzAccountManager buzzAccountManager)
        {
            _buzzAccountManager = buzzAccountManager;
            _logger = LogManager.GetLogger("MostPlaylistedArtists");
        }

        public async Task ExecuteAsync()
        {
            var artists = await GetAllPlaylistedArtistsAsync();
            var firstArtistToplist = artists.GroupBy(p => p.ArtistId).Select(p => new
            {
                ArtistId = p.Key,
                ArtistName = p.First().Name,
                FirstArtistPlaylistAdds = p.Count(a => a.ArtistOrder == 0),
                SecondaryArtistPlaylistAdds = p.Count(a => a.ArtistOrder != 0)
            });

            var buzzCategories = _buzzAccountManager.GetBuzzCategories().ToDictionary(k=> k.ID, v=> v.Name);

            using (var fileStream = File.OpenWrite("MostPlaylistedArtists.xlsx"))
            {
                var excelDocument = new SpreadsheetLight.SLDocument();
                excelDocument.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Total");

                excelDocument.SetCellValue(0, 1, "ArtistId");
                excelDocument.SetCellValue(0, 2, "Artist Name");
                excelDocument.SetCellValue(0, 3, "Playlist adds as first artist");
                excelDocument.SetCellValue(0, 4, "Playlist adds as featured artist");

                foreach (var artist in firstArtistToplist.ItemIndex())
                {
                    var index = artist.Index + 1;

                    excelDocument.SetCellValue(index, 1, artist.Item.ArtistId);
                    excelDocument.SetCellValue(index, 1, artist.Item.ArtistName);
                    excelDocument.SetCellValue(index, 2, artist.Item.FirstArtistPlaylistAdds);
                    excelDocument.SetCellValue(index, 3, artist.Item.SecondaryArtistPlaylistAdds);
                }

                foreach (var buzzCategoryId in artists.GroupBy(p => p.BuzzCategoryId))
                {
                    string buzzCategoryName = "Uncategorized";
                    if (buzzCategoryId.Key.HasValue)
                    {
                        buzzCategoryName = buzzCategories[buzzCategoryId.Key.Value] ?? buzzCategoryId.Key.ToString();
                    }
                    
                    excelDocument.AddWorksheet(buzzCategoryName);
                    excelDocument.SelectWorksheet(buzzCategoryName);

                    var artistsInCategory = buzzCategoryId.ToList().GroupBy(p => p.ArtistId).Select(p => new
                    {
                        ArtistId = p.Key,
                        ArtistName = p.First().Name,
                        FirstArtistPlaylistAdds = p.Count(a => a.ArtistOrder == 0),
                        SecondaryArtistPlaylistAdds = p.Count(a => a.ArtistOrder != 0)
                    });

                    foreach (var artist in artistsInCategory.ItemIndex())
                    {
                        var index = artist.Index + 1;
                        
                        excelDocument.SetCellValue(index, 1, artist.Item.ArtistId);
                        excelDocument.SetCellValue(index, 1, artist.Item.ArtistName);
                        excelDocument.SetCellValue(index, 2, artist.Item.FirstArtistPlaylistAdds);
                        excelDocument.SetCellValue(index, 3, artist.Item.SecondaryArtistPlaylistAdds);
                    }
                }
                
                excelDocument.SaveAs(fileStream);
            }
            
        }

        private async Task<List<TempMostPlaylistedArtistData>> GetAllPlaylistedArtistsAsync()
        {
            List<TempMostPlaylistedArtistData> playlistedArtists = new List<TempMostPlaylistedArtistData>();

            int readProgress = 0;
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT a.artistId, a.name, ta.artistOrder, p.buzzCategoryId " +
                                   "FROM tblSpotifyPlaylistTrackList2 AS tl " +
                                   "INNER JOIN tblSpotifyPlaylist AS p ON tl.PlaylistId = p.playlistId " +
                                   "INNER JOIN tblSpotifyTrack2 AS t ON tl.trackId = t.trackId " +
                                   "INNER JOIN tblSpotifyTrackArtist AS ta ON ta.trackId = t.trackId " +
                                   "INNER JOIN tblSpotifyArtist AS a ON a.artistId = ta.artistId " +
                                   "WHERE p.removed = 0 AND EarliestAdded > @date ";

                using(var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@date", DateTime.Today.AddDays(-28));

                    _logger.Debug("Begin read");

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var playlistedTrack = new TempMostPlaylistedArtistData()
                            {
                                ArtistId = reader.GetString(0),
                                Name = reader.GetString(1),
                                ArtistOrder = reader.GetInt32(2),
                                BuzzCategoryId = reader.GetIntOrDefault("buzzCategoryId"),
                            };

                            playlistedArtists.Add(playlistedTrack);

                            readProgress++;
                            if (readProgress % 10000 == 0)
                            {
                                _logger.Debug($"Progress: {readProgress}");
                            }
                        }
                    }
                }
            }

            return playlistedArtists;
        }

        private class TempMostPlaylistedArtistData
        {
            public string ArtistId { get; set; }
            public int ArtistOrder { get; set; }

            public string Name { get; set; }

            public int? BuzzCategoryId { get; set; }
        }
    }
}