using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.Database;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace Sony.Filtr.Core.SpotifyWeekly
{
    public class SpotifyWeeklyManager
    {
        public List<SpotifyTopPlaylist> GetSpotifyPlaylistsWithNullId()
        {
            return PetaPocoRepository.Instance.Fetch<SpotifyTopPlaylist>("WHERE PlaylistId IS NULL");
        }

        public async Task SetSpotifyPlaylistsId(string cmd)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var command = new MySqlCommand(cmd, conn);
                await command.ExecuteNonQueryAsync();
            }
        }

        public async Task<string[]> GetLatestTopPlaylistIds()
        {
            string sql = @"
SELECT DISTINCT(weekly.PlaylistId) 
FROM tblSpotifyWeeklyTopPlaylist weekly
INNER JOIN tblSpotifyPlaylist p ON p.PlaylistId = weekly.PlaylistID
WHERE p.SaveTracklist = 0 AND  weekly.Date = (SELECT Max(Date) FROM tblSpotifyWeeklyTopPlaylist);
";

            List<string> ids = new List<string>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var command = new MySqlCommand(sql, conn);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        ids.Add(reader.GetString(0));
                    }
                }
            }

            return ids.ToArray();
        }
    }
}