using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MySqlConnector;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Playlists
{
    public class IgnoredPlaylistsManager
    {
        public async Task AddIgnoredPlaylistAsync(MusicServicePlaylistReference playlist)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "INSERT IGNORE INTO tblPlaylistIgnoredPlaylist (PlaylistId, MusicServiceId) VALUES (@playlistId, @musicServiceId)";
                
                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@playlistId", playlist.PlaylistId);
                cmd.Parameters.AddWithValue("@musicServiceId", playlist.MusicServiceId);

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task<List<MusicServicePlaylistReference>> GetIgnoredPlaylistsAsync(MusicService? musicService = null)
        {
            List<MusicServicePlaylistReference> playlists = new List<MusicServicePlaylistReference>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT PlaylistId, MusicServiceId FROM tblPlaylistIgnoredPlaylist " +
                                   "WHERE (@MusicServiceId IS NULL OR MusicServiceId=@MusicServiceId)";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@musicServiceId", musicService);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var playlistId = reader.GetSafeString("playlistId");
                        var musicServiceId = reader.GetInt32("musicServiceId");

                        playlists.Add(new MusicServicePlaylistReference(playlistId, musicServiceId));
                    }
                }
            }
            return playlists;
        }

        public async Task DeleteIgnoredPlaylistAsync(MusicServicePlaylistReference playlist)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "DELETE FROM tblPlaylistIgnoredPlaylist WHERE PlaylistId=@playlistId AND MusicServiceId=@musicServiceId";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@playlistId", playlist.PlaylistId);
                cmd.Parameters.AddWithValue("@musicServiceId", playlist.MusicServiceId);

                await cmd.ExecuteNonQueryAsync();
            }
        }
    }
}