using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NLog;
using PetaPoco;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.PlaylistSynchronization.Data;
using Sony.Filtr.PlaylistSynchronization.Synchronizer;
using Sony.Filtr.SpotifyWebAPI;
using MySqlConnector;
using Sony.Filtr.Database;
using Sony.Filtr.ErrorLogging;
using Sony.Filtr.SpotifyImages;
using Sony.Filtr.Utility.Extensions;
using Sony.Filtr.ApolloAPI;
using Sony.Filtr.DeezerAPI;
using System.Net.Http;
using Sony.Filtr.YouTube.Api;
using Sony.Filtr.Functional;

namespace Sony.Filtr.PlaylistSynchronization
{
    public class PlaylistSynchronizationManager
    {
        private readonly IApolloWebApi _vendorApi;
        private readonly SpotifySynchronizer _spotifySynchronizer;
        private readonly DeezerSynchronizer _deezerSynchronizer;
        private readonly YoutubeSynchronizer _youtubeSynchronizer;
        private readonly SpotifyImageDownloader _spotifyImageHandler;

        private Logger _logger;

        public PlaylistSynchronizationManager(
            IApolloWebApi vendorApi,
            SpotifySynchronizer spotifySynchronizer,
            DeezerSynchronizer deezerSynchronizer,
            YoutubeSynchronizer youtubeSynchronizer,
            SpotifyImageDownloader spotifyImageHandler)
        {
            _vendorApi = vendorApi;
            _spotifySynchronizer = spotifySynchronizer;
            _deezerSynchronizer = deezerSynchronizer;
            _youtubeSynchronizer = youtubeSynchronizer;
            _spotifyImageHandler = spotifyImageHandler;

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

        public Data.PlaylistSynchronization AddPlaylistSynchronization(Data.PlaylistSynchronization playlistSynchronization)
        {
            PetaPocoRepository.Instance.Insert(playlistSynchronization);

            var newPlaylistSync = GetPlaylistSynchronization(playlistSynchronization.Id);

            SetInsertMedia(playlistSynchronization.InsertMedia, newPlaylistSync);

            newPlaylistSync.InsertMedia = GetInsertMedia(newPlaylistSync);

            return newPlaylistSync;
        }

        private async Task UpdatePlaylistSynchronizationResultAsync(Data.PlaylistSynchronization playlistSynchronization)
        {
            string successUpdateSql = @"
UPDATE tblPlaylistSynchronization 
SET 
    SourceTitle = @sourceTitle, 
    SourceImage = @sourceImage, 
    SourceTrackCount = @sourceTrackCount, 
    SynchronizedTrackCount = @synchronizedTrackCount,
    Description = @description,
    LastUpdated = @lastUpdate,
    Error = 0,
    SourceServiceAccountId  = @sourceServiceAccountId,
    SourceServiceAccountName = @sourceServiceAccountName,
    ToPlaylistTitle = @toPlaylistTitle,
    ToPlaylistImage = @toPlaylistImage
WHERE Id = @id;";

            string failureUpdateSql = @"
UPDATE tblPlaylistSynchronization 
SET 
    LastUpdated = @lastUpdate,
    Error = 1
WHERE Id = @id;";

            using (var connection = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var command = new MySqlCommand(playlistSynchronization.Error ? failureUpdateSql : successUpdateSql, connection);

                command.Parameters.AddWithValue("@sourceTitle", playlistSynchronization.SourceTitle);
                command.Parameters.AddWithValue("@sourceImage", playlistSynchronization.SourceImage);
                command.Parameters.AddWithValue("@sourceTrackCount", playlistSynchronization.SourceTrackCount);
                command.Parameters.AddWithValue("@synchronizedTrackCount", playlistSynchronization.SynchronizedTrackCount);
                command.Parameters.AddWithValue("@description", playlistSynchronization.Description);
                command.Parameters.AddWithValue("@lastUpdate", playlistSynchronization.LastUpdated);
                command.Parameters.AddWithValue("@sourceServiceAccountId", playlistSynchronization.SourceServiceAccountId);
                command.Parameters.AddWithValue("@sourceServiceAccountName", playlistSynchronization.SourceServiceAccountName);
                command.Parameters.AddWithValue("@toPlaylistTitle", playlistSynchronization.ToPlaylistTitle);
                command.Parameters.AddWithValue("@toPlaylistImage", playlistSynchronization.ToPlaylistImage);
                command.Parameters.AddWithValue("@id", playlistSynchronization.Id);

                await command.ExecuteNonQueryAsync();
            }
        }

        public void UpdatePlaylistSynchronization(Data.PlaylistSynchronization playlistSynchronization)
        {
            var savedSync = GetPlaylistSynchronization(playlistSynchronization.Id);

            if (savedSync == null)
            {
                throw new PlaylistSynchronizationException("Playlist not saved, can't update", SyncError.DB);
            }

            PetaPocoRepository.Instance.Update(playlistSynchronization, new List<string>() { "FromPlaylistId", "ToPlaylistId", "ToServiceAccountId", "Title", "Description", "Active", "TitleCopyMode", "DescriptionCopyMode" });

            SetInsertMedia(playlistSynchronization.InsertMedia, playlistSynchronization);
        }

        public Data.PlaylistSynchronization GetPlaylistSynchronization(int playlistSynchronizationId)
        {
            var sync = PetaPocoRepository.ReadOnlyInstance.SingleOrDefault<Data.PlaylistSynchronization>(playlistSynchronizationId);

            if (sync != null)
            {
                sync.InsertMedia = GetInsertMedia(sync);
            }

            return sync;
        }

        public async Task<PlaylistSyncWithLog> GetPlaylistSynchronizationWithLatestLogAsync(int playlistSynchronizationId)
        {
            var results = await this.GetPlaylistSynchronizationsWithLogsInternal(null, null, playlistSynchronizationId);

            var sync = results.FirstOrDefault();

            if (sync != null)
            {
                sync.Synchronization.InsertMedia = GetInsertMedia(sync.Synchronization);
            }

            return sync;
        }

        public async Task<List<Data.PlaylistSynchronization>> GetPlaylistSyncsForceAllSourcesAsync(Data.PlaylistSynchronization sync, bool forceAllSources)
        {
            var syncs = forceAllSources ?
                new List<Data.PlaylistSynchronization>(await this.GetPlaylistSyncsHavingFromPlaylistIdAsSource(sync)) :
                new List<Data.PlaylistSynchronization>() { sync };

            ConnectInsertMediaToSyncList(syncs);

            return syncs;
        }

        private async Task<Data.PlaylistSynchronization[]> GetPlaylistSyncsHavingFromPlaylistIdAsSource(Data.PlaylistSynchronization sync, bool onlyActive = true)
        {
            if (sync == null) { return Array.Empty<Data.PlaylistSynchronization>(); }

            string sql = @"

SELECT 
    ID,
    ApplicationId,
    FromPlaylistId,
    FromServiceType,
    FromMusicServiceId,
    ToPlaylistId,
    ToServiceAccountId,
    Title,
    Description,
    LastUpdated,
    Active,
    TitleCopyMode,
    DescriptionCopyMode,
    CreatedAt,
    SourceTitle,
    SourceImage,
    SourceTrackCount,
    SynchronizedTrackCount,
    AppendTrackList,
    Error as HasError,
    SourceServiceAccountId,
    SourceServiceAccountName,
    ToPlaylistTitle,
    ToPlaylistImage
FROM tblPlaylistSynchronization 
WHERE 
	FromPlaylistId = (SELECT FromPlaylistId FROM tblPlaylistSynchronization WHERE ID = @syncId )
	AND (@onlyActive = 0 OR @onlyActive = Active);
";

            List<Data.PlaylistSynchronization> result = new List<Data.PlaylistSynchronization>();

            using (var connection = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@syncId", sync.Id);
                command.Parameters.AddWithValue("onlyActive", onlyActive);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        result.Add(ReadSynchronization(reader));
                    }
                }
            }

            return result.ToArray();
        }

        public async Task<List<PlaylistSyncWithLog>> GetPlaylistSynchronizationsWithLogsAsync(Application application = null, Nullable<bool> active = null)
        {
            var syncs = await this.GetPlaylistSynchronizationsWithLogsInternal(application, active, null);

            ConnectInsertMediaToSyncList(syncs.Select(s => s.Synchronization).ToList());

            return syncs;
        }

        private async Task<List<PlaylistSyncWithLog>> GetPlaylistSynchronizationsWithLogsInternal(Application application = null, Nullable<bool> active = null, Nullable<int> syncId = null)
        {
            var results = new List<PlaylistSyncWithLog>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sqlString = @"
SELECT  
	sync.Id, sync.ApplicationId, sync.FromPlaylistId, sync.FromServiceType, sync.FromMusicServiceId, 
    sync.ToPlaylistId, sync.ToServiceAccountId, sync.Title, sync.Description, sync.LastUpdated, sync.Active, 
    sync.TitleCopyMode, sync.DescriptionCopyMode, sync.CreatedAt, sync.SourceTitle, sync.SourceImage, sync.SourceTrackCount, 
    sync.SynchronizedTrackCount, sync.AppendTrackList, sync.Error AS HasError,
    sync.SourceServiceAccountId, sync.SourceServiceAccountName, sync.ToPlaylistTitle, sync.ToPlaylistImage,

    log.Time, log.MadeChanges, log.AddedTracks, log.DeletedDuplicates, log.DeletedTracks, log.SourceTracks,
    log.TargetTrackCount, log.ErrorMessage, log.TriggeredManually, log.Error AS ErrorType,

    toServiceAccount.ServiceType as ToServiceType
FROM tblPlaylistSynchronization sync
INNER JOIN tblServiceAccount toServiceAccount ON sync.ToServiceAccountId = toServiceAccount.Id
LEFT JOIN 
    (
		select log.* from  tblPlaylistSynchronizationLog log 
		INNER JOIN
			(
				SELECT PlaylistSynchronizationId, MAX(TIME) maxDate
				FROM tblPlaylistSynchronizationLog
				GROUP BY PlaylistSynchronizationId
			) maxTime ON maxTime.PlaylistSynchronizationId = log.PlaylistSynchronizationId AND maxTime.maxDate = log.Time
) log on log.PlaylistSynchronizationId = sync.Id
WHERE 
    ((@applicationId IS NULL) OR (sync.ApplicationId = @applicationId)) AND 
    ((@isActive IS NULL) OR (sync.Active = @isActive)) AND 
    ((@syncId is NULL) OR (sync.Id = @syncId));";

                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@applicationId", application == null ? null : (object)application.ID);
                command.Parameters.AddWithValue("@isActive", active.HasValue ? active.Value : (object)null);
                command.Parameters.AddWithValue("@syncId", syncId.HasValue ? syncId.Value : (object)null);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        results.Add(new PlaylistSyncWithLog(ReadSynchronization(reader), ReadSyncResult(reader), reader.GetInt32("ToServiceType")));
                    }
                }
            }

            return results;
        }

        public List<Data.PlaylistSynchronization> GetPlaylistSynchronizations(Application application = null, bool onlyActive = false)
        {
            var synchronizations = PetaPocoRepository.ReadOnlyInstance.Fetch<Data.PlaylistSynchronization>();

            if (onlyActive)
                synchronizations = synchronizations.Where(s => s.Active).ToList();

            if (application != null)
                synchronizations = synchronizations.Where(s => s.ApplicationId == application.ID).ToList();

            synchronizations = ConnectInsertMediaToSyncList(synchronizations);

            return synchronizations;
        }

        public Data.PlaylistSynchronization GetPlaylistSynchronization(string toPlaylistId)
        {
            var synchronization = PetaPocoRepository.ReadOnlyInstance.Fetch<Data.PlaylistSynchronization>(new Sql("WHERE ToPlaylistId = @0", toPlaylistId)).FirstOrDefault();

            if (synchronization != null)
                synchronization.InsertMedia = GetInsertMedia(synchronization);

            return synchronization;
        }

        public void DeletePlaylistSynchronization(Data.PlaylistSynchronization playlistSynchronization)
        {
            DeleteInsertMedia(playlistSynchronization);
            PetaPocoRepository.Instance.Delete<Data.PlaylistSynchronization>(playlistSynchronization);
        }

        private async Task<Result<PlaylistSynchronizationResult>> Sync(Data.PlaylistSynchronization playlistSynchronization, ServiceAccount toAccount)
        {
            if (toAccount == null)
            {
                return Result<PlaylistSynchronizationResult>.FromException(new PlaylistSynchronizationException($"Could not find service account ({playlistSynchronization.ToServiceAccountId}) for playlistSync with id {playlistSynchronization.Id}", SyncError.NoServiceAccount));
            }

            if (playlistSynchronization.ApplicationId != toAccount.ApplicationId)
            {
                return Result<PlaylistSynchronizationResult>.FromException(new PlaylistSynchronizationException($"Service account application ({toAccount.ApplicationId}) does not match application id of synchronization ({playlistSynchronization.ApplicationId}).", SyncError.ApplicationMismatch));
            }

            Result<PlaylistSynchronizationResult> result = await Result<Data.PlaylistSynchronization>.Success(playlistSynchronization)
                .BindAsync(GetGenericPlaylist)
                .BindAndTupleAsync(async genericPlaylist => {
                    switch (toAccount.MusicServiceId)
                    {
                        case (int)MusicService.Deezer: return await _deezerSynchronizer.CopyToPlaylistAsync(genericPlaylist, playlistSynchronization, toAccount);
                        case (int)MusicService.YouTube: return await _youtubeSynchronizer.CopyToPlaylistAsync(genericPlaylist, playlistSynchronization, toAccount);
                        case (int)MusicService.Spotify: return await _spotifySynchronizer.CopyToPlaylistAsync(genericPlaylist, playlistSynchronization, toAccount);
                        default:
                            throw new PlaylistSynchronizationException($"Playlist sync with id {playlistSynchronization.Id} has service account with music service {toAccount.MusicServiceId} which is not supported", SyncError.NotSupported);
                    }
                })
                .OnSuccess(tuple => {
                    GenericPlaylist genericPlaylist = tuple.Item1;
                    playlistSynchronization.SourceTitle = genericPlaylist.Name;
                    playlistSynchronization.SourceImage = genericPlaylist.Image;
                    playlistSynchronization.SourceTrackCount = genericPlaylist.Tracks.Count;
                    playlistSynchronization.SourceServiceAccountId = genericPlaylist.OwnerId;
                    playlistSynchronization.SourceServiceAccountName = genericPlaylist.OwnerName;

                    (PlaylistSynchronizationResult, string, string) syncResult = tuple.Item2;
                    playlistSynchronization.SynchronizedTrackCount = syncResult.Item1.SyncedTrackCount;
                    playlistSynchronization.Error = syncResult.Item1.Error.HasValue;
                    playlistSynchronization.ToPlaylistTitle = syncResult.Item2;
                    playlistSynchronization.ToPlaylistImage = syncResult.Item3;
                })
                .Bind(tuple => tuple.Item2.Item1);

            playlistSynchronization.LastUpdated = DateTime.UtcNow;

            return result.IsOk ? result : Result<PlaylistSynchronizationResult>.FromException(result.Exception);
        }

        public async Task<PlaylistSynchronizationResult> ExecutePlaylistSyncAsync(Data.PlaylistSynchronization playlistSynchronization, ServiceAccount toAccount, bool triggeredManually = false, bool forceAllSources = false)
        {
            List<(Data.PlaylistSynchronization, PlaylistSynchronizationResult)> syncResults = new List<(Data.PlaylistSynchronization, PlaylistSynchronizationResult)>();
            foreach (var playlistSync in await this.GetPlaylistSyncsForceAllSourcesAsync(playlistSynchronization, forceAllSources))
            {
                PlaylistSynchronizationResult syncResult =
                    await Result<Data.PlaylistSynchronization>.Success(playlistSync)
                    .BindAsync(sync => Sync(sync, toAccount))
                    .OnSuccess(r => playlistSync.Error = false)
                    .OnFailure(exception =>
                    {
                        _logger.Error(exception, $"Playlist synchronization id: {playlistSync.Id} Exception: '{exception.GetFullMessage(ExceptionData.Message)}'");
                        ErrorLoggingManager.Instance.LogError(exception);
                        playlistSync.Error = true;
                    })
                    .Flatten(PlaylistSynchronizationResult.FromException);

                syncResult.TriggeredManually = triggeredManually;

                await UpdatePlaylistSynchronizationResultAsync(playlistSync);

                await AddSyncLog(playlistSync, syncResult);

                _logger.Info(() => $"Synchronized {playlistSync.Id} Made changes: {syncResult.MadeChange} Error: {syncResult.Error} Text: {syncResult.ErrorText}");

                syncResults.Add((playlistSync, syncResult));
            }

            if (forceAllSources)
            {
                _logger.Info(() => $"ForceAllSource TRUE. Initial sync id: {playlistSynchronization.Id} Found syncs having same source playlist: {syncResults.Count} ({String.Join(", ", syncResults.Select(t => $"Id: {t.Item1.Id} : HasError: {t.Item2.Error.HasValue}"))})");
            }

            return syncResults.First(r => r.Item1.Id == playlistSynchronization.Id).Item2;
        }

        private async Task<Result<GenericPlaylist>> GetGenericPlaylist(Data.PlaylistSynchronization playlistSynchronization)
        {
            string playlistId = new SpotifyLink(playlistSynchronization.FromPlaylistId).ExtractPlaylistID();

            var spotifyPlaylist = await this._vendorApi.GetPlaylistByIdWithAllTracksAsync(playlistId, cacheExpiration: TimeSpan.FromSeconds(0));
            if (spotifyPlaylist == null)
            {
                return Result<GenericPlaylist>.FromException(new PlaylistSynchronizationException($"Source playlist could not be loaded from Spotify. id: '{playlistId}'", SyncError.NoSourcePlaylist));
            }

            if (spotifyPlaylist.Tracks == null)
            {
                return Result<GenericPlaylist>.FromException(new PlaylistSynchronizationException($"Source playlist loaded from Spotify contains empty Track object. id: '{playlistId}'", SyncError.VendorSpecific));
            }

            var image = await _spotifyImageHandler.StoreLargestPlaylistImageAsync(spotifyPlaylist.id, spotifyPlaylist.images?.FirstOrDefault()?.url);

            var userData = await this.GetPlaylistOwnerData(spotifyPlaylist);

            return new GenericPlaylist()
            {
                Name = spotifyPlaylist.name,
                Description = spotifyPlaylist.description,
                Image = image,
                Tracks = spotifyPlaylist.Tracks.items.Where(i => i != null && i.track != null && i.track.artists != null && !i.is_local).Select(t => new GenericTrack()
                {
                    Artists = t.track.artists.Select(a => a.name).ToList(),
                    ISRC = t.track.external_ids.isrc,
                    Name = t.track.name,
                    SpotifyUri = t.track.uri,
                    TrackAddedDate = t.added_at,
                    TrackId = t.track.id
                }).ToList(),
                OwnerId = userData.id,
                OwnerName = userData.name
            };
        }

        private async Task<(string id, string name)> GetPlaylistOwnerData(Filtr.ApolloAPI.Models.SpotifyPlaylist playlist)
        {
            if (playlist == null || playlist.owner == null || String.IsNullOrWhiteSpace(playlist.owner.id)) { return (null, null); }

            var user = await this._vendorApi.GetUserAsync(playlist.owner.id);

            return user == null ? (null, null) : (user.id, user.display_name);
        }

        public async Task AddSyncLog(Data.PlaylistSynchronization playlistSync, PlaylistSynchronizationResult result)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                const string sqlString = @"
INSERT INTO tblPlaylistSynchronizationLog 
    (PlaylistSynchronizationId, MadeChanges, AddedTracks, DeletedTracks, DeletedDuplicates, SourceTracks, TargetTrackCount, ErrorMessage, TriggeredManually, Error) 
    VALUES (@syncId, @changes, @addedTracks, @deletedTracks, @deletedDuplicates, @sourceTracks, @targetTrackCount, @errorMessage, @triggeredManually, @error)";

                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@syncId", playlistSync.Id);
                command.Parameters.AddWithValue("@changes", result.MadeChange);
                command.Parameters.AddWithValue("@addedTracks", result.AddedTracks);
                command.Parameters.AddWithValue("@deletedTracks", result.DeletedTracks);
                command.Parameters.AddWithValue("@deletedDuplicates", result.DeletedDuplicates);
                command.Parameters.AddWithValue("@sourceTracks", result.SourceTrackCount);
                command.Parameters.AddWithValue("@targetTrackCount", result.SyncedTrackCount);
                command.Parameters.AddWithValue("@errorMessage", result.ErrorText);
                command.Parameters.AddWithValue("@triggeredManually", result.TriggeredManually);
                command.Parameters.AddWithValue("@error", result.Error.HasValue ? result.Error.ToString() : null);

                await command.ExecuteNonQueryAsync();
            }
        }

        public async Task<List<PlaylistSynchronizationResult>> GetSyncLogAsync(Data.PlaylistSynchronization playlistSync)
        {
            List<PlaylistSynchronizationResult> results = new List<PlaylistSynchronizationResult>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sqlString = @"
SELECT Time, MadeChanges, AddedTracks, DeletedTracks, DeletedDuplicates, SourceTracks, TargetTrackCount, ErrorMessage, TriggeredManually, Error as ErrorType
FROM tblPlaylistSynchronizationLog
WHERE PlaylistSynchronizationId=@syncId
ORDER BY Time DESC;";

                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@syncId", playlistSync.Id);
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        results.Add(ReadSyncResult(reader));
                    }
                }
            }

            return results;
        }

        private static Data.PlaylistSynchronization ReadSynchronization(MySqlDataReader reader)
        {
            return new Data.PlaylistSynchronization()
            {
                Active = reader.GetBoolean("Active"),
                AppendTrackList = reader.GetBoolean("AppendTrackList"),
                ApplicationId = reader.GetInt32("ApplicationId"),
                CreatedAt = reader.GetDateTime("CreatedAt"),
                Description = reader.GetSafeString("Description"),
                DescriptionCopyMode = (FieldCopyMode)reader.GetInt32("DescriptionCopyMode"),
                Error = reader.GetBoolean("HasError"),
                FromMusicServiceId = reader.GetInt32("FromMusicServiceId"),
                FromPlaylistId = reader.GetSafeString("FromPlaylistId"),
                FromServiceType = (ServiceType)reader.GetInt32("FromServiceType"),
                Id = reader.GetInt32("Id"),
                LastUpdated = reader.GetDateTimeOrDefault("LastUpdated"),
                SourceImage = reader.GetSafeString("SourceImage"),
                SourceTitle = reader.GetSafeString("SourceTitle"),
                SourceTrackCount = reader.GetInt32("SourceTrackCount"),
                SynchronizedTrackCount = reader.GetInt32("SynchronizedTrackCount"),
                Title = reader.GetSafeString("Title"),
                TitleCopyMode = (FieldCopyMode)reader.GetInt32("TitleCopyMode"),
                ToPlaylistId = reader.GetSafeString("ToPlaylistId"),
                ToServiceAccountId = reader.GetInt32("ToServiceAccountId"),
                SourceServiceAccountId = reader.GetSafeString("SourceServiceAccountId"),
                SourceServiceAccountName = reader.GetSafeString("SourceServiceAccountName"),
                ToPlaylistTitle = reader.GetSafeString("ToPlaylistTitle"),
                ToPlaylistImage = reader.GetSafeString("ToPlaylistImage")
            };
        }

        private static PlaylistSynchronizationResult ReadSyncResult(MySqlDataReader reader)
        {
            Nullable<DateTime> time = reader.GetDateTimeOrDefault("Time");

            return time.HasValue ?
                new PlaylistSynchronizationResult()
                {
                    Time = time,
                    MadeChange = reader.GetBoolean("MadeChanges"),
                    AddedTracks = reader.GetInt32("AddedTracks"),
                    DeletedTracks = reader.GetInt32("DeletedTracks"),
                    DeletedDuplicates = reader.GetInt32("DeletedDuplicates"),
                    SourceTrackCount = reader.GetInt32("SourceTracks"),
                    SyncedTrackCount = reader.GetInt32("TargetTrackCount"),
                    ErrorText = reader.GetSafeString("ErrorMessage"),
                    TriggeredManually = reader.GetBoolean("TriggeredManually"),
                    Error = reader.GetSafeString("ErrorType").ParseAsEnumNullable<SyncError>()
                } :
                new PlaylistSynchronizationResult();
        }

        private List<Data.PlaylistSynchronization> ConnectInsertMediaToSyncList(List<Data.PlaylistSynchronization> playlistSynchronizations)
        {
            var mediaItems = GetAllInsertMedia();

            playlistSynchronizations.ForEach(p => p.InsertMedia = mediaItems.Where(m => m.PlaylistSynchronizationId == p.Id).ToList());

            return playlistSynchronizations;
        }

        public List<InsertMedia> GetInsertMedia(Data.PlaylistSynchronization playlistSync)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<InsertMedia>(new Sql("WHERE PlaylistSynchronizationId = @0", playlistSync.Id)).OrderBy(m => m.InsertPosition).ToList();
        }

        public List<InsertMedia> GetAllInsertMedia()
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<InsertMedia>().OrderBy(m => m.InsertPosition).ToList();
        }

        public void SetInsertMedia(List<InsertMedia> mediaToInsert, Data.PlaylistSynchronization playlistSync)
        {
            PetaPocoRepository.Instance.Delete<InsertMedia>(new Sql("WHERE PlaylistSynchronizationId = @0", playlistSync.Id));

            if (mediaToInsert == null || !mediaToInsert.Any())
                return;

            foreach (var media in mediaToInsert)
            {
                media.PlaylistSynchronizationId = playlistSync.Id;
                PetaPocoRepository.Instance.Insert(media);
            }
        }

        public void DeleteInsertMedia(Data.PlaylistSynchronization playlistSync)
        {
            PetaPocoRepository.Instance.Delete<InsertMedia>(new Sql("WHERE PlaylistSynchronizationId = @0", playlistSync.Id));
        }
    }

    public class PlaylistSynchronizationResult
    {
        public DateTime? Time { get; set; }

        public int SyncedTrackCount { get; set; }
        public int SourceTrackCount { get; set; }

        public int AddedTracks { get; set; }
        public int DeletedTracks { get; set; }
        public int DeletedDuplicates { get; set; }

        public bool MadeChange { get; set; }

        public string ErrorText { get; set; }

        public bool TriggeredManually { get; set; }

        public Nullable<SyncError> Error { get; set; }

        public static PlaylistSynchronizationResult FromSuccess(SyncCounts counts, int syncedTrackCount)
        {
            return new PlaylistSynchronizationResult()
            {
                Time = DateTime.Now,
                SyncedTrackCount = syncedTrackCount,
                AddedTracks = counts.Added,
                DeletedDuplicates = 0,
                DeletedTracks = counts.Removed,
                MadeChange = counts.Added > 0 || counts.ReorderedTracks || counts.Removed > 0
            };
        }

        public static PlaylistSynchronizationResult FromError(SyncError result, string message)
        {
            return new PlaylistSynchronizationResult()
            {
                Time = DateTime.Now,
                Error = result,
                ErrorText = message
            };
        }

        public static PlaylistSynchronizationResult FromException(Exception ex)
        {
            var result = new PlaylistSynchronizationResult()
            {
                Time = DateTime.Now,
                ErrorText = ex.GetFullMessage(ExceptionData.Message)
            };

            switch (ex)
            {
                case SpotifyWebAPIException spotifyEx:
                    switch (spotifyEx.StatusCode)
                    {
                        case System.Net.HttpStatusCode.BadRequest: result.Error = SyncError.Unauthorized; break;
                        case System.Net.HttpStatusCode.Unauthorized: result.Error = SyncError.Unauthorized; break;
                        default: result.Error = SyncError.VendorSpecific; break;
                    }

                    break;
                case DeezerException deezerEx:
                    switch (deezerEx.Error.code)
                    {
                        case (int)System.Net.HttpStatusCode.BadRequest: result.Error = SyncError.Unauthorized; break;
                        case (int)System.Net.HttpStatusCode.Unauthorized: result.Error = SyncError.Unauthorized; break;
                        default: result.Error = SyncError.VendorSpecific; break;
                    }

                    break;
                case YoutubeApiException youtubeEx:
                    switch (youtubeEx.StatusCode)
                    {
                        case System.Net.HttpStatusCode.BadRequest: result.Error = SyncError.Unauthorized; break;
                        case System.Net.HttpStatusCode.Unauthorized: result.Error = SyncError.Unauthorized; break;
                        default: result.Error = SyncError.VendorSpecific; break;
                    }

                    break;
                case HttpRequestException httpRequestEx:
                    result.Error = SyncError.BadRequest;

                    break;
                case PlaylistSynchronizationException syncEx:
                    result.Error = syncEx.Error;

                    break;
                default:
                    result.Error = SyncError.Unknown;

                    break;
            }

            return result;
        }
    }

    public struct SyncCounts
    {
        public readonly int Added;
        public readonly bool ReorderedTracks;
        public readonly int Removed;

        public SyncCounts(int added, bool reorderedTracks, int removed)
        {
            this.Added = added;
            this.ReorderedTracks = reorderedTracks;
            this.Removed = removed;
        }
    }

    public enum SyncError
    {
        Unknown,
        NoSourcePlaylist,
        NoTargetPlaylist,
        BadRequest,
        Unauthorized,
        NoServiceAccount,
        ApplicationMismatch,
        DB,
        NotSupported,
        VendorSpecific
    }
}