using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using NLog;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.SpotifyWebAPI;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Worker.Onetime
{
    public class ReactivateRemovedPlaylists
    {
        private readonly SpotifyWebApi _spotifyWebApi;
        private Logger _logger;

        public ReactivateRemovedPlaylists(SpotifyWebApi spotifyWebApi)
        {
            _spotifyWebApi = spotifyWebApi;
            _logger = LogManager.GetLogger("ReactivateRemovedPlaylists");

        }

        public async Task ExecuteAsync()
        {
            var playlists = await GetRemovedPlaylistsAsync();

            await playlists.ForEachAsync(5, async playlistUri =>
            {
                try
                {
                    Console.WriteLine($"Fetching {playlistUri}");
                    var playlistLink = new SpotifyLink(playlistUri, urlDecode:true);

                    var playlistId = playlistLink.ExtractPlaylistID();
                    var spotifyPlaylist = await _spotifyWebApi.GetPlaylistByIdAsync(playlistId);

                    if (spotifyPlaylist != null)
                    {
                        await ReactivatePlaylistAsync(playlistUri);

                        _logger.Info($"Reactivated {playlistUri}");
                    }
                    else
                    {
                        _logger.Info($"Still removed: {playlistUri}");
                    }
                }
                catch (Exception e)
                {
                    _logger.Info($"Error: {e}");
                }
            });
        }

        private async Task ReactivatePlaylistAsync(string playlistUri)
        {
            const string sql = "UPDATE tblSpotifyPlaylist SET Removed = 0 WHERE playlistUri=@playlistUri";

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using(var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@playlistUri", playlistUri);
                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }
        
        private async Task<List<string>> GetRemovedPlaylistsAsync()
        {
            List<string> playlists = new List<string>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT PlaylistUri " +
                                   "FROM tblSpotifyPlaylist AS p " +
                                   "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.playlistUri " +
                                   "WHERE p.Removed = 1 " +
                                   "AND ip.playlistId IS NULL " +
                                   "AND user != 'thesoundsofspotify' "
                                   ;

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var playlistUri = reader.GetString(0);

                            playlists.Add(playlistUri);
                        }
                    }
                }
            }

            return playlists;
        }
    }
}