using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using MoreLinq;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Contracts.Entities.Buzz;
using Sony.Filtr.Core.EditorialPlaylists;
using Sony.Filtr.Database;
using Sony.Filtr.Playlists.Spotify;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Worker.Onetime
{
    public class SpotifyPlaylistAverageChangeExport
    {
        private readonly SpotifyPlaylistManager _spotifyPlaylistManager;
        private readonly EditorialPlaylistStatsManager _editorialPlaylistStatsManager;

        public SpotifyPlaylistAverageChangeExport(SpotifyPlaylistManager spotifyPlaylistManager, EditorialPlaylistStatsManager editorialPlaylistStatsManager)
        {
            _spotifyPlaylistManager = spotifyPlaylistManager;
            _editorialPlaylistStatsManager = editorialPlaylistStatsManager;
        }

        public async Task ExecuteAsync()
        {
            var playlists = await GetPlaylistsAsync();
            List<string> rows = new List<string>();

            await playlists.GroupBy(p => p.PlaylistUri).Batch(100).ToList().ForEachAsync(2, async spotifyPlaylistBatch =>
            {
                var batch = spotifyPlaylistBatch.ToList();
                var allPlaylistStats = await _editorialPlaylistStatsManager.GetPlaylistStatsAsync(batch.Select(p => new SpotifyLink(p.Key)).ToList(), DateTime.Today.AddDays(-27), DateTime.Today);
                var statsPerPlaylist = allPlaylistStats.GroupBy(k => k.SpotifyUri).ToDictionary(k => k.Key, v => v);

                foreach(var playlist in batch)
                {
                    Console.WriteLine($"Playlist: {playlist.Key}");

                    var playlistStats = statsPerPlaylist.GetValueOrDefault(playlist.Key)?.OrderBy(p=> p.Date);
                    //if (playlistStats.Any())
                    //{
                    //    Console.WriteLine($"Earliest stat: {playlistStats.First().Date}");
                    //    Console.WriteLine($"Latest stat: {playlistStats.Last().Date}");
                    //}

                    double? averageChangePerDay = null;
                    var firstFollowers = playlistStats?.FirstOrDefault();
                    var lastFollowers = playlistStats?.LastOrDefault();
                        
                    if (firstFollowers != null && lastFollowers != null && firstFollowers.Date != lastFollowers.Date)
                    {
                        var followerDays = (lastFollowers.Date - firstFollowers.Date).TotalDays;
                        averageChangePerDay = 1.0*((int)lastFollowers.Subscribers - (int)firstFollowers.Subscribers) / followerDays;
                    }
                    
                    var name = PrepareString(playlist.First().Name);
                    var description = PrepareString(playlist.First().Description);
                    foreach (var p in playlist)
                    {
                        var csvRow = string.Join(";", "\"" + p.PlaylistUri + "\"", "\"" + name + "\"", "\"" + description + "\"", p.User, p.CountryCode, p.Followers, firstFollowers?.Date, firstFollowers?.Subscribers, lastFollowers?.Date, lastFollowers?.Subscribers, averageChangePerDay, p.Market, p.Streams28Days);

                        rows.Add(csvRow);

                    }
                }
            });

            using(FileStream playlistExportFile = File.Open("SpotifyPlaylistFollowerChangeExport.csv", FileMode.OpenOrCreate))
            {
                playlistExportFile.Write(Encoding.UTF8.GetPreamble(), 0, Encoding.UTF8.GetPreamble().Length);

                using (StreamWriter fileWriter = new StreamWriter(playlistExportFile))
                {
                    var headerRow = string.Join(";", "Playlist Uri", "Playlist Name", "Playlist Description", "Playlist owner user", "Playlist owner market", "Followers", "Date used for first follower", "Follower count for first date", "Date used for latest follower", "Follower count for latest date", "Average follower change per day of time span", "Spotify region for streams", "Sum of streams last 28 days in region");
                    await fileWriter.WriteLineAsync(headerRow);
                    foreach (var row in rows)
                    {
                        await fileWriter.WriteLineAsync(row);
                    }
                }
            }
        }

        private string PrepareString(string name)
        {
            if (name == null)
                return null;
            name = name.Replace(";", "");
            return Regex.Replace(name, @"\r\n?|\n", string.Empty);
        }

        private
            async Task<List<PlaylistTempViewModel>> GetPlaylistsAsync()
        {
            var list = new List<PlaylistTempViewModel>();
            using (var connection = DatabaseHandler.GetOpenConnection())
            {
                var cmd = connection.CreateCommand();
                var sql = "SELECT p.PlaylistUri, p.Name, p.Description, p.User, p.CountryCode, f.Followers, s.market, s.Streams28days" +
                      " FROM tblSpotifyPlaylist AS p " +
                      "LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.PlaylistUri = p.PlaylistUri " +
                      "LEFT JOIN tblSpotifyPlaylistStreamSummary AS s ON s.PlaylistUri = p.PlaylistUri " +
                      "WHERE BuzzCategoryId = 5 AND Removed=0";
                cmd.CommandText = sql;

                var reader = await cmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    list.Add(new PlaylistTempViewModel()
                    {
                        PlaylistUri = reader.GetString("playlistUri"),
                        Name = reader.GetString("name"),
                        Description = reader.GetString("Description"),
                        User = reader.GetString("User"),
                        CountryCode = reader.GetString("CountryCode"),
                        Followers = reader.GetIntOrDefault("Followers"),
                        Market = reader.GetString("market"),
                        Streams28Days = reader.GetIntOrDefault("Streams28Days"),
                    });
                }
            }
            return list;
        }

        //public async Task<Te>
        private class PlaylistTempViewModel
        {
            public string PlaylistUri { get; set; }

            public string Name{ get; set; }

            public string Description { get; set; }

            public string User { get; set; }

            public string CountryCode { get; set; }

            public int? Followers { get; set; }

            public string Market { get; set; }

            public int? Streams28Days { get; set; }

            
        }
    }

    
}