using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Amazon;
using Amazon.S3;
using Amazon.S3.Transfer;
using MoreLinq;
using NLog;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.Playlists.Spotify;
using Sony.Filtr.Spotify.Artists;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Tasks.Tasks.Spotify
{
    public class ExportSpotifyArtistsTask : IScheduledTask
    {
        private readonly SpotifyPlaylistHistoricTrackListManager _spotifyPlaylistHistoricTrackListManager;
        private readonly S3BucketReference _destinationBucket;
        private readonly Logger _logger;
        private const string _FilenameBase = "ExportSpotifyArtists-";

        public ExportSpotifyArtistsTask(SpotifyPlaylistHistoricTrackListManager spotifyPlaylistHistoricTrackListManager, S3BucketReference destinationBucket)
        {
            _spotifyPlaylistHistoricTrackListManager = spotifyPlaylistHistoricTrackListManager;
            _destinationBucket = destinationBucket;

            _logger = LogManager.GetLogger("ExportSpotifyArtistsTask");
        }

        public async Task<ScheduledTaskLog> ExecuteAsync(Guid scheduledTaskLogId)
        {
            var spotifyArtistsExport = await GetSpotifyArtistsToExportAsync();
            
            var filename = await WriteToDiskAsync(DateTime.Now.Date, spotifyArtistsExport);
            
            await UploadFileAsync(filename);

            File.Delete(filename);
            _logger.Debug($"Done processing");
            
            return null;
        }

        private async Task UploadFileAsync(string filepath)
        {
            _logger.Debug("Uploading to S3");

            var s3Client = new AmazonS3Client(_destinationBucket.AccessKey, _destinationBucket.SecretKey, RegionEndpoint.USEast1);

            var transferUtility = new TransferUtility(s3Client);

            var filename = Path.GetFileName(filepath);

            await transferUtility.UploadAsync(new TransferUtilityUploadRequest()
            {
                BucketName = _destinationBucket.Bucket,
                FilePath = filepath,
                Key = "SpotifyArtists/" + filename,
                CannedACL = S3CannedACL.BucketOwnerFullControl,
            });

            _logger.Debug("Done uploading to S3");
        }

        private async Task<string> WriteToDiskAsync(DateTime date, List<SpotifyArtistsExport> spotifyArtists)
        {
            var datestring = date.ToString("yyyy-MM-ddTHH.mm.ss");
            var dateStringInFile = date.ToString("yyyy-MM-dd");
            var path = GetOrCreateTempDirectory();
            var filename = $"{_FilenameBase}{datestring}.csv";

            var filePath = Path.Combine(path, filename);
            FileInfo file = new FileInfo(filePath);

            _logger.Debug("Writing to disk");
            using (var fileStream = file.OpenWrite())
            {
                fileStream.Write(Encoding.UTF8.GetPreamble(), 0, Encoding.UTF8.GetPreamble().Length);

                using (var fileWriter = new StreamWriter(fileStream))
                {

                    fileWriter.WriteLine(string.Join("\t", "artist_id", "artist_name", "most_recent_album_copyright", "most_recent_album_release_date", "most_recent_track_id", "most_recent_track_isrc"));

                    foreach (var spotifyArtist in spotifyArtists)
                    {
                        var artist_id = spotifyArtist.artist_id;
                        var artist_name = spotifyArtist.artist_name;
                        var most_recent_album_copyright = spotifyArtist.most_recent_album_copyright;
                        var most_recent_album_release_date = spotifyArtist.most_recent_album_release_date;
                        var most_recent_track_id = spotifyArtist.most_recent_track_id;
                        var most_recent_track_isrc = spotifyArtist.most_recent_track_isrc;


                        var row = string.Join("\t", artist_id, artist_name, most_recent_album_copyright, most_recent_album_release_date, most_recent_track_id, most_recent_track_isrc);

                        await fileWriter.WriteLineAsync(row);
                    }
                }
            }

            var compressedFilename = CompressFileAsync(filePath);
            File.Delete(filePath);

            _logger.Debug("Done writing to disk");
            return compressedFilename;
        }
        
        private string GetOrCreateTempDirectory()
        {
            var dirPath = "ArtistsExport";
            if (Directory.Exists(dirPath))
            {
                return dirPath;
            }

            Directory.CreateDirectory(dirPath);
            return dirPath;
        }

        private string CompressFileAsync(string filepath)
        {
            var originalFilename = Path.GetFileName(filepath);
            var compressedFilename = $"{originalFilename}.gz";
            var directory = GetOrCreateTempDirectory();
            var compressedFilePath = Path.Combine(directory, compressedFilename);

            using (FileStream originalFileStream = File.OpenRead(filepath))
            {
                using (FileStream destinationCompressedFile = File.OpenWrite(compressedFilePath))
                {
                    using (GZipStream compressionStream = new GZipStream(destinationCompressedFile, CompressionMode.Compress))
                    {
                        originalFileStream.CopyTo(compressionStream);
                    }
                }
            }

            _logger.Debug($"Done compressing {filepath} to {compressedFilePath}");
            return compressedFilePath;
        }

        private async Task<List<SpotifyArtistsExport>> GetSpotifyArtistsFromDb(int limit, int offset)
        {
            List<SpotifyArtistsExport> spotifyArtistsData = new List<SpotifyArtistsExport>();
            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT sar.ArtistId AS artist_id, sar.Name AS artist_name, sal.ReleaseDate AS most_recent_album_release_date, sal.Copyright AS most_recent_album_copyright, stalbum.TrackId as most_recent_track_id, st2.ISRC AS most_recent_track_isrc " +
                                  " FROM tblSpotifyArtist as sar" +
                                  " INNER join tblSpotifyTrackArtist as sta on sar.ArtistId = sta.ArtistId" +
                                  " INNER JOIN tblSpotifyTrack2 as st2 on sta.TrackId = st2.TrackId" +
                                  " INNER join tblSpotifyTrackAlbum as stalbum on sta.TrackId = stalbum.TrackId" +
                                  " INNER JOIN tblSpotifyAlbum as sal on stalbum.AlbumId = sal.AlbumId " +
                                  "  WHERE sal.AlbumId = " +
                                  " (SELECT sal2.AlbumId from tblSpotifyAlbum as sal2 INNER JOIN tblSpotifyTrackAlbum as stalbum2 on sal2.AlbumId = stalbum2.AlbumId INNER JOIN tblSpotifyTrackArtist AS star ON stalbum2.TrackId = star.TrackId WHERE star.ArtistId = sar.ArtistId" +
                                  " ORDER BY sal2.ReleaseDate DESC LIMIT 1)" +
                                  " AND stalbum.TrackPosition =" +
                                  " (SELECT stalbum2.TrackPosition from tblSpotifyTrackAlbum as stalbum2 INNER JOIN tblSpotifyTrackArtist AS tsta ON tsta.TrackId = stalbum2.TrackId WHERE stalbum2.AlbumId = sal.AlbumId AND tsta.ArtistId = sar.ArtistId ORDER BY stalbum2.TrackPosition ASC LIMIT 1)" +
                                  " ORDER BY sar.ArtistId LIMIT @limit OFFSET @offset";
                cmd.Parameters.AddWithValue("@limit", limit);
                cmd.Parameters.AddWithValue("@offset", offset);
                cmd.CommandTimeout = 5000;
                var reader = await cmd.ExecuteReaderAsync();
                
                while (await reader.ReadAsync())
                {
                    spotifyArtistsData.Add(new SpotifyArtistsExport(reader));
                }
            }

            return spotifyArtistsData;
        }

        private async Task<List<SpotifyArtistsExport>> GetSpotifyArtistsToExportAsync()
        {
            List<SpotifyArtistsExport> spotifyArtistsDataFull = new List<SpotifyArtistsExport>();
            _logger.Debug($"Start extracting data");
            var itemsCounter = 0;
            var limit = 20000;
            var offset = 0;
            do
            {
                List<SpotifyArtistsExport> spotifyArtistsData = new List<SpotifyArtistsExport>();
                try
                {
                    try
                    {
                        spotifyArtistsData = await GetSpotifyArtistsFromDb(limit, offset);
                    }
                    catch (Exception ex)
                    {
                        spotifyArtistsData = await GetSpotifyArtistsFromDb(limit, offset);
                        _logger.Error(ex, $"Error in getting artists from database Limit : {limit}, offset : {offset}. Retry attempt");
                    }
                }
                catch (Exception ex)
                {
                    _logger.Error(ex, $"Error in getting artists from database Limit : {limit}, offset : {offset}. No Retry");
                }
                
                itemsCounter = spotifyArtistsData.Count;
                offset += limit;
                spotifyArtistsDataFull.AddRange(spotifyArtistsData);
                _logger.Debug($"Items processed: {spotifyArtistsDataFull.Count}");
            }
            while (itemsCounter > 0);
            _logger.Debug($"Finished extracting data");

            return spotifyArtistsDataFull;
        }
    }
}