using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.Database;
using Sony.Filtr.Playlists.Spotify.Model;
using Sony.Filtr.SpotifyWebAPI.Model;

namespace Sony.Filtr.Playlists.Spotify
{
    public class SpotifyAlbumManager
    {
        public async Task<List<LocalSpotifyAlbum>> GetAlbumsAsync(List<string> albumIds)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<LocalSpotifyAlbum>("WHERE albumId IN (@albumIds)", new { @albumIds = albumIds.ToArray() });
        }

        public async Task UpdateAlbumPropertiesAsync(FullAlbumItem album)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "UPDATE tblSpotifyAlbum SET Popularity=@popularity, Copyright=@copyright, Label=@label, ReleaseDate=@releaseDate, ReleaseDatePrecision=@releaseDatePrecision, Upc=@upc WHERE AlbumId=@albumId";

                var cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@albumId", album.id);
                cmd.Parameters.AddWithValue("@popularity", album.popularity);
                cmd.Parameters.AddWithValue("@copyright", album.copyrights?.FirstOrDefault()?.text);
                cmd.Parameters.AddWithValue("@label", album.label);
                cmd.Parameters.AddWithValue("@releaseDate", album.release_date);
                cmd.Parameters.AddWithValue("@releaseDatePrecision", album.release_date_precision);
                cmd.Parameters.AddWithValue("@upc", album.external_ids?.upc);

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task UpdateAlbumImageAsync(string albumId, string imageFilename)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "UPDATE tblSpotifyAlbum SET SmallImageFilename=@imageFilename WHERE AlbumId=@albumId";

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

                await cmd.ExecuteNonQueryAsync();
                
            }
        }
    }
}