using System;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.Core.EditorialPlaylists;
using Sony.Filtr.Core.SpotifyBrowse;
using Sony.Filtr.Core.SpotifyBrowse.Data;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;
using System.Collections.Generic;

namespace Sony.Filtr.Worker.Onetime
{
    public class FixPlaylistUrlEncodedUrl
    {
        private readonly EditorialPlaylistManager _editorialPlaylistManager;
        private readonly SpotifyBrowseManager _spotifyBrowseManager;

        public FixPlaylistUrlEncodedUrl(EditorialPlaylistManager editorialPlaylistManager, SpotifyBrowseManager spotifyBrowseManager)
        {
            _editorialPlaylistManager = editorialPlaylistManager;
            _spotifyBrowseManager = spotifyBrowseManager;
        }

        public async Task ExecuteAsync()
        {
            var allPlaylists = await _editorialPlaylistManager.GetEditorialPlaylistsAsync(null, onlySony: false, onlyActive: false, lightWeight: true);

            var playlistsWithUrlEncodedUris = allPlaylists.Where(p => HttpUtility.UrlDecode(p.SpotifyLinkUri) != p.SpotifyLinkUri).ToList();
            foreach(var playlist in playlistsWithUrlEncodedUris)
            {
                var correctUri = HttpUtility.UrlDecode(playlist.SpotifyLinkUri);
                Console.WriteLine($"Urldecode {playlist.Name} with url {playlist.SpotifyLinkUri} to {correctUri}");
                playlist.SpotifyLinkUri = correctUri;
                //PetaPocoRepository.Instance.Update(playlist, new[] { "strSpotifyUri"});

                //await _editorialPlaylistManager.ClearCacheAsync(playlist);
            }
            Console.WriteLine($"Total {playlistsWithUrlEncodedUris.Count()} playlists");

            //var invalidPlaylists = await GetInvalidPlaylistUriAsync();
            //foreach(var playlistUri in invalidPlaylists.Distinct())
            //{
            //    var fixedUri = HttpUtility.UrlDecode(playlistUri);
            //    if (fixedUri != playlistUri)
            //    {
            //        Console.WriteLine($"{playlistUri} will be renamed {fixedUri}");

            //        await UpdateBrowsePlaylistUriAsync(playlistUri, fixedUri);
            //    }
            //}
        }

        private async Task<List<string>> GetInvalidPlaylistUriAsync()
        {
            List<string> playlistsUris = new List<string>();
            using (var conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                await conn.OpenAsync();

                var sqlString = "SELECT DISTINCT PlaylistUri FROM tblSpotifyBrowseFeaturedPlaylists WHERE PlaylistUri LIKE '%\\%%'";

                var command = new MySqlCommand(sqlString, conn);

                var reader = await command.ExecuteReaderAsync();
                while(await reader.ReadAsync())
                {
                    playlistsUris.Add(reader.GetString(0));
                }
                await conn.CloseAsync();
            }
            return playlistsUris;
        }

        private async Task UpdateBrowsePlaylistUriAsync(string playlistUri, string fixedUri)
        {
            using (var conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                await conn.OpenAsync();

                var sqlString = "UPDATE tblSpotifyBrowseFeaturedPlaylists SET PlaylistUri=@newPlaylistUri WHERE PlaylistUri=@oldPlaylistUri";

                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@oldPlaylistUri", playlistUri);
                command.Parameters.AddWithValue("@newPlaylistUri", fixedUri);

                await command.ExecuteNonQueryAsync();
                await conn.CloseAsync();
            }
        }
    }
}