using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Amazon.S3;
using Amazon.S3.Transfer;
using MoreLinq;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using NLog;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Tasks.Tasks
{
    public class UploadMoodagentPlaylistExportTask : IScheduledTask
    {
        private readonly AmazonS3Client _s3Client;
        private readonly Logger _logger;

        public UploadMoodagentPlaylistExportTask(AmazonS3Client s3Client)
        {
            _s3Client = s3Client;

            _logger = NLog.LogManager.GetLogger("UploadMoodagentPlaylistExportTask");
        }

        public async Task<ScheduledTaskLog> ExecuteAsync(Guid scheduledTaskLogId)
        {
            _logger.Debug("Get playlists");
            var playlists = await GetPlaylistsToExportAsync();
            
            playlists.ForEach(p => p.SpotifyUri = new SpotifyLink(p.SpotifyUri, urlDecode: true).Uri);
            
            _logger.Debug("Get tracks");

            var batches = playlists.Batch(100).ToList();
            await batches.ItemIndex().ForEachAsync(5, async playlistBatch =>
            {
                _logger.Debug($"Load tracks-batch {playlistBatch.Index}/{batches.Count}");

                var playlistInBatch = playlistBatch.Item.ToList();

                var playlistBatchTracks = await GetSpotifyTracksAsync(playlistInBatch.Select(p=> p.SpotifyUri).ToList());
                foreach(var moodAgentPlaylistExportItem in playlistInBatch)
                {
                    var tracks = playlistBatchTracks.GetValueOrDefault(moodAgentPlaylistExportItem.SpotifyUri);
                    if (tracks != null)
                    {
                        moodAgentPlaylistExportItem.Tracks = tracks.Distinct().ToList();
                    }
                    
                }
            });

            string dir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
            var fileName = $"FiltrPlaylistsExport-{DateTime.Today.ToString("d", CultureInfo.GetCultureInfo("sv-SE"))}.json";
            var filePath = Path.Combine(dir, fileName);
            _logger.Debug("Filename is {0}", fileName);

            _logger.Info("Playlists to include {0}", playlists.Count);

            
            var file = new FileInfo(filePath);
            if (file.Exists)
            {
                file.Delete();
            }
                
            var jsonSerializer = new JsonSerializer();
            using (var jsonWriter = new JsonTextWriter(file.CreateText()))
            {
                jsonSerializer.Serialize(jsonWriter, playlists);
            }

            const string bucketName = "filtrmoodagent";

            _logger.Info("Starting upload to {0}", bucketName);
            var transferUtility = new TransferUtility(_s3Client);
            await transferUtility.UploadAsync(new TransferUtilityUploadRequest()
            {
                BucketName = bucketName,
                FilePath = file.FullName,
                Key = $"playlistdatainput/FiltrPlaylistsExport-{DateTime.Today.ToString("d", CultureInfo.GetCultureInfo("sv-SE"))}.json",
                CannedACL = S3CannedACL.BucketOwnerFullControl,
            });

            file.Delete();

            _logger.Info("Done!");
            return null;
        }

        private async Task<List<MoodAgentPlaylistExportItem>> GetPlaylistsToExportAsync()
        {
            List<MoodAgentPlaylistExportItem> playlists = new List<MoodAgentPlaylistExportItem>();

            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT p.PlaylistId, p.name, p.description, pf.Followers, ep.strSpotifyUri " +
                                   "FROM tblSpotifyPlaylist AS p " +
                                   "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.playlistUri AND ip.musicServiceId = @spotifyMusicServiceId " +
                                   "LEFT JOIN tblSpotifyPlaylistFollowers AS pf ON p.playlistId = pf.playlistId " +
                                   "LEFT JOIN tblEditorialPlaylist AS ep ON ep.strSpotifyUri = p.playlistUri AND ep.blnSonyCreated = 1 AND ep.blnRemoved = 0 " +
                                   "WHERE p.Removed = 0 " +
                                   "AND ip.playlistId IS NULL " +
                                   "GROUP BY p.playlistId ";

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var playlistUri = reader.GetString("PlaylistId");
                        var name = reader.GetString("name");
                        var description = reader.GetString("description");
                        var followers = reader.GetLongOrFallback("followers", 0);
                        var editorialPlaylistUri = reader.GetString("strSpotifyUri");

                        playlists.Add(new MoodAgentPlaylistExportItem()
                        {
                            SpotifyUri = playlistUri,
                            Name = name,
                            Description = description,
                            Followers = followers,
                            IsFiltr = !string.IsNullOrWhiteSpace(editorialPlaylistUri)
                        });
                    }
                }
            }
            return playlists;
        }
        

        public async Task<Dictionary<string, List<string>>> GetSpotifyTracksAsync(List<string> playlistUris)
        {
            Dictionary<string, List<string>> playlistTracks = new Dictionary<string, List<string>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $"SELECT p.playlistUri, pt.TrackId " +
                          $"FROM tblSpotifyPlaylistTrackList2 AS pt " +
                          $"INNER JOIN tblSpotifyPlaylist AS p ON pt.playlistId = p.playlistId " +
                          $"WHERE p.playlistUri IN ({string.Join(",", playlistUris.Select(p => "'" + MySqlHelper.EscapeString(p) + "'"))})";
                var cmd = new MySqlCommand(sql, conn);

                var reader = await cmd.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var playlistUri = reader.GetString(0);
                    var trackId = reader.GetString(1);
                    var trackUri = SpotifyLink.FromTrackId(trackId).Uri;

                    if (playlistTracks.ContainsKey(playlistUri))
                        playlistTracks[playlistUri].Add(trackUri);
                    else
                        playlistTracks.Add(playlistUri, new List<string>() { trackUri });
                }
                reader.Close();
            }

            return playlistTracks;
        }

        
    }

    public class MoodAgentPlaylistExportItem
    {
        public MoodAgentPlaylistExportItem()
        {
            Tracks = new List<string>();
        }
        public string Name { get; set; }

        public string Description { get; set; }

        public string SpotifyUri { get; set; }

        public long? Followers { get; set; }

        public List<string> Tracks { get; set; }
        public bool IsFiltr { get; set; }

    }
}