using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using NLog;
using PetaPoco.Business;
using Sony.Filtr.ApolloAPI.Models.Apple;
using Sony.Filtr.AppleMusic.Curators;
using Sony.Filtr.AppleMusic.Data;
using Sony.Filtr.AppleMusic.Data.Internal;
using Sony.Filtr.AppleMusic.Models;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.AppleMusic
{
    public class AppleMusicChartManager
    {
        private readonly Logger _logger;
        public AppleMusicChartManager()
        {
            _logger = LogManager.GetLogger("ImportAppleMusicData");
        }
        public async Task<List<AvailableSongChartOverview>> GetAvailableChartsOverviewAsync(ChartType chartType, string chartName = null)
        {
            var availableCharts = new List<AvailableSongChartOverview>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT c2.storefront, sc1.date as latestDate, sc1.timestamp as latestTimestamp, sc2.date as prevDate, sc2.timestamp as prevTimestamp " +
                                   "FROM ( " +
                                   " SELECT c1.storefront, Max(c1.id) as maxChartId, " +
                                   "  (SELECT Max(c2.Id) FROM tblAppleMusicChart AS c2 WHERE c1.Storefront = c2.storefront AND c1.chartName = c2.chartName AND c1.chartType = c2.ChartType AND c2.genreId IS NULL AND c2.id < c1.id ) as secondMaxChartId " +
                                   " FROM tblAppleMusicChart AS c1 " +
                                   " WHERE c1.chartType = @chartType AND c1.chartName = @chartName AND GenreId IS NULL " +
                                   " GROUP BY c1.storefront) As c2 " +
                                   "LEFT JOIN tblAppleMusicChart AS sc1 ON sc1.id = c2.maxChartId  " +
                                   "LEFT JOIN tblAppleMusicChart AS sc2 ON sc2.id = c2.secondMaxChartId";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@chartType", chartType);
                    cmd.Parameters.AddWithValue("@chartName", chartName);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var availableChart = new AvailableSongChartOverview()
                            {
                                Storefront = reader.GetString("storefront"),
                                LatestDate = reader.GetDateTime("latestDate"),
                                LatestTimestamp = reader.GetDateTime("latestTimestamp"),
                                PreviousDate = reader.GetDateTimeOrDefault("prevDate"),
                                PreviousTimestamp = reader.GetDateTimeOrDefault("prevTimestamp"),
                            };

                            availableCharts.Add(availableChart);
                        }
                    }
                }
            }

            return availableCharts;
        }

        internal async Task<List<TrackSummaryLatestAndEntryPosition>> GetEntryAndLatestPositionAsync(string isrc)
        {
            List<TrackSummaryLatestAndEntryPosition> positions = new List<TrackSummaryLatestAndEntryPosition>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT entryLatest.storefront, " +
                          "maxC.date as maxDate, maxE.Position as maxDatePosition, " +
                          "minC.date as minDate, minE.Position as minDatePosition " +
                          "FROM ( " +
                          " SELECT c.storefront, e.songId, Max(c.id) as maxChartId, Min(c.id) AS minChartId " +
                          " FROM tblAppleMusicChart AS c " +
                          " INNER JOIN tblAppleMusicChartSongEntry AS e ON c.id = e.chartId " +
                          " INNER JOIN tblAppleMusicSong AS s ON s.Id = e.songId AND s.storefront = c.storefront " +
                          " WHERE s.isrc = @isrc AND ChartType = 1 AND chartname = 'most-played' AND GenreId IS NULL " +
                          " GROUP BY c.storefront, c.ChartType, c.ChartName, c.genreId) AS entryLatest " +
                          "INNER JOIN tblAppleMusicChart as maxC ON maxC.id = entryLatest.maxChartId " +
                          "INNER JOIN tblAppleMusicChartSongEntry AS maxE ON maxE.chartId = maxC.id AND maxE.SongId = entryLatest.songId " +
                          "INNER JOIN tblAppleMusicChart as minC ON minC.id = entryLatest.minChartId " +
                          "INNER JOIN tblAppleMusicChartSongEntry AS minE ON minE.chartId = minC.id AND minE.SongId = entryLatest.songId " +
                          "GROUP BY entryLatest.storefront";

                using (var command = new MySqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@isrc", isrc);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var storefront = reader.GetString("storefront");

                            var latestDate = reader.GetDateTime("maxDate");
                            var latestPosition = reader.GetInt32("maxDatePosition");
                            var earliestDate = reader.GetDateTime("minDate");
                            var earliestPosition = reader.GetInt32("minDatePosition");

                            positions.Add(new TrackSummaryLatestAndEntryPosition()
                            {
                                Storefront = storefront,

                                LatestDate = latestDate,
                                LatestPosition = latestPosition,
                                EarliestDate = earliestDate,
                                EarliestPosition = earliestPosition,
                            });
                        }
                    }
                }
            }
            return positions;
        }

        internal async Task<List<TrackSummaryPeakPosition>> GetPeakPositionsAsync(string isrc)
        {
            List<TrackSummaryPeakPosition> peakPositions = new List<TrackSummaryPeakPosition>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT ct.storefront, ct.PeakPosition, ct.genreId, Max(c.date) as latestPeakDate, Min(c.date) as earliestPeakDate " +
                          "FROM( " +
                          " SELECT c.storefront, c.chartType, c.chartName, c.genreId, Min(e.Position) as peakPosition " +
                          " FROM tblAppleMusicChart AS c " +
                          " INNER JOIN tblAppleMusicChartSongEntry AS e ON c.id = e.chartId " +
                          " INNER JOIN tblAppleMusicSong AS s ON s.id = e.songId AND s.storefront = c.storefront " +
                          " WHERE s.isrc = @isrc " +
                          " AND ChartType = 1 AND chartname = 'most-played' AND GenreId IS NULL " +
                          " GROUP BY c.storefront) as ct " +
                          "INNER JOIN tblAppleMusicChart as c ON c.storefront = ct.storefront AND c.chartType = ct.chartType AND c.chartName = ct.chartName AND c.genreId IS NULL " +
                          "INNER JOIN tblAppleMusicChartSongEntry AS e ON e.chartId = c.id AND e.Position = ct.PeakPosition " +
                          "INNER JOIN tblAppleMusicSong AS s ON s.id = e.songId AND s.storefront = c.storefront " +
                          "WHERE s.isrc = @isrc " +
                          "GROUP BY ct.storefront, ct.chartType, ct.chartName; ";

                using (var command = new MySqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@isrc", isrc);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var storefront = reader.GetString("storefront");

                            var peakPosition = reader.GetInt32("peakPosition");
                            var latestPeakDate = reader.GetDateTime("latestPeakDate");
                            var earliestPeakDate = reader.GetDateTime("earliestPeakDate");

                            peakPositions.Add(new TrackSummaryPeakPosition()
                            {
                                Storefront = storefront,

                                PeakPosition = peakPosition,
                                LatestPeakDate = latestPeakDate,
                                EarliestPeakDate = earliestPeakDate,
                            });
                        }
                    }
                }
            }

            return peakPositions;
        }

        public async Task<List<AppleMusicTrackChartSummary>> GetTrackChartSummaryAsync(string isrc)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicTrackChartSummary>("WHERE isrc = @0", isrc);
        }

        internal async Task<List<TrackSummaryCurrentAndPreviousPosition>> GetCurrentAndPreviousPositionAsync(string isrc)
        {
            List<TrackSummaryCurrentAndPreviousPosition> positions = new List<TrackSummaryCurrentAndPreviousPosition>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT c2.storefront, sc1.date as currentDate, scs1.position as currentDatePosition, " +
                                   "sc2.date as prevDate, scs2.position as prevDatePosition " +
                                   "FROM ( " +
                                   " SELECT c1.storefront, c2.maxChartId, Max(c1.id) as secondMaxChartId FROM tblAppleMusicChart AS c1 " +
                                   " INNER JOIN( SELECT storefront, Max(id) as maxChartId FROM tblAppleMusicChart " +
                                   "  WHERE charttype = 1 AND chartname = 'most-played' AND GenreId IS NULL " +
                                   "  GROUP BY storefront) AS c2 ON c2.storefront = c1.storefront " +
                                   " WHERE c1.id < c2.maxChartId AND c1.charttype = 1 AND c1.chartname = 'most-played' AND c1.GenreId IS NULL " +
                                   "GROUP BY c1.storefront) as c2 " +
                                   "LEFT JOIN tblAppleMusicChart AS sc1 ON sc1.id = c2.maxChartId " +
                                   "LEFT JOIN tblAppleMusicChartSongEntry AS scs1 ON scs1.chartId = sc1.id " +
                                   "LEFT JOIN tblAppleMusicSong AS scsi1 ON scsi1.id = scs1.songid AND scsi1.storefront = sc1.storefront " +
                                   "LEFT JOIN tblAppleMusicChart AS sc2 ON sc2.id = c2.secondMaxChartId " +
                                   "LEFT JOIN tblAppleMusicChartSongEntry AS scs2 ON scs2.chartId = sc2.id " +
                                   "LEFT JOIN tblAppleMusicSong AS scsi2 ON scsi2.id = scs2.songid AND scsi2.storefront = sc2.storefront " +
                                   "WHERE scsi1.isrc = @isrc AND scsi2.isrc = @isrc AND scs1.position IS NOT NULL AND scs2.position IS NOT NULL " +
                                   "GROUP BY c2.storefront";

                using (var command = new MySqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@isrc", isrc);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var storefront = reader.GetString("storefront");

                            var currentDate = reader.GetDateTimeOrDefault("currentDate");
                            var currentDatePosition = reader.GetIntOrDefault("currentDatePosition");
                            var prevDate = reader.GetDateTimeOrDefault("prevDate");
                            var prevDatePosition = reader.GetIntOrDefault("prevDatePosition");

                            positions.Add(new TrackSummaryCurrentAndPreviousPosition()
                            {
                                Storefront = storefront,
                                CurrentDate = currentDate,
                                CurrentPosition = currentDatePosition,
                                PreviousDate = prevDate,
                                PreviousPosition = prevDatePosition,
                            });
                        }
                    }
                }
            }
            return positions;
        }

        public List<AppleMusicChart> GetCharts(DateTime today)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicChart>("WHERE date=@0", today);
        }

        public List<AppleMusicChart> GetCharts(ChartsFilter chartsFilter)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicChart>("WHERE (@Storefront IS NULL OR storefront=@Storefront) " +
                                                                      "AND (@ChartType IS NULL OR ChartType=@ChartType) " +
                                                                      "AND (@ChartName IS NULL OR ChartName=@ChartName) " +
                                                                      "AND (@Date IS NULL OR Date=@Date) " +
                                                                      "AND (@GenreId IS NULL OR GenreId=@GenreId) " +
                                                                      "AND (@GenreIsNull=0 OR GenreId IS NULL)", chartsFilter);
        }

        public List<AppleMusicChart> GetLatestCharts(ChartsFilter chartsFilter)
        {
            var result = PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicChart>(@"
SELECT t1.Id, t1.Storefront, t1.ChartType, t1.ChartName, t1.GenreId, t1.Date, t1.Timestamp
FROM tblAppleMusicChart AS t1 
INNER JOIN 
(
	SELECT Storefront, ChartType, ChartName, GenreId, MAX(Date) AS TopDate FROM `tblAppleMusicChart`
    WHERE (@Storefront IS NULL OR storefront=@Storefront)
    AND (@ChartType IS NULL OR ChartType=@ChartType)
    AND (@ChartName IS NULL OR ChartName=@ChartName)
    AND (@GenreId IS NULL OR GenreId=@GenreId)
    AND (@GenreIsNull=0 OR GenreId IS NULL)
    GROUP BY Storefront, ChartType, ChartName, GenreId
) AS  t2 ON 
t1.Storefront = t2.Storefront 
AND t1.ChartType = t2.ChartType 
AND t1.ChartName = t2.ChartName 
AND t1.Date  = t2.TopDate 
AND (t2.GenreId IS NULL OR t1.GenreId = t2.GenreId)", chartsFilter);
            
            return result;
        }

        public AppleMusicChart GetChart(int id)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefault<AppleMusicChart>(id);
        }

        public List<AppleMusicChartMusicVideoEntry> GetMusicVideoChartEntries(int id)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicChartMusicVideoEntry>("WHERE ChartId = @0", id);
        }
        public List<AppleMusicChartAlbumEntry> GetAlbumChartEntries(int id)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicChartAlbumEntry>("WHERE ChartId = @0", id);
        }

        public List<AppleMusicChartSongEntry> GetSongChartEntries(int id)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicChartSongEntry>("SELECT se.*, s.isrc " +
                                                                               "FROM tblAppleMusicChartSongEntry AS se " +
                                                                               "INNER JOIN tblAppleMusicChart AS c ON se.chartId = c.id " +
                                                                               "LEFT JOIN tblAppleMusicSong AS s ON se.songId = s.id AND c.storefront = s.storefront " +
                                                                               "WHERE ChartId = @0", id);
        }

        public async Task SaveSongChartAsync(MySqlConnection conn, MySqlTransaction tran, long chartId, IEnumerable<AppleChartSongData> songs)
        {
            var songValues = string.Join(",", songs.ItemIndex().Select(p => $"({chartId}, {p.Index}, {p.Item.id},'{SafeGetString(p.Item.attributes?.artistName)}','{SafeGetString(p.Item.attributes?.name)}' )"));
            var chartEntriesSql = $"INSERT INTO tblAppleMusicChartSongEntry (ChartId, Position, SongId, ArtistName, name) VALUES {songValues}";
            
            using (var cmd = new MySqlCommand(chartEntriesSql, conn, tran))
            {
                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task SaveMusicVideoChartAsync(MySqlConnection conn, MySqlTransaction tran, long chartId, List<AppleChartMusicVideoData> musicVideos)
        {
            var musicVideoValues = string.Join(",", musicVideos.ItemIndex().Select(p => $"({chartId}, {p.Index}, {p.Item.id},'{SafeGetString(p.Item.attributes?.artistName)}','{SafeGetString(p.Item.attributes?.name)}' )"));
            var chartEntriesSql = $"INSERT INTO tblAppleMusicChartMusicVideoEntry (ChartId, Position, MusicVideoId, ArtistName, name) VALUES {musicVideoValues}";
            using (var cmd = new MySqlCommand(chartEntriesSql, conn, tran))
            {
                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task SaveAlbumChartAsync(MySqlConnection conn, MySqlTransaction tran, long chartId, List<AppleChartAlbumData> albums)
        {
            var albumValues = string.Join(",", albums.ItemIndex().Select(p => $"({chartId}, {p.Index}, {p.Item.id},'{SafeGetString(p.Item.attributes?.artistName)}','{SafeGetString(p.Item.attributes?.name)}' )"));
            var chartEntriesSql = $"INSERT INTO tblAppleMusicChartAlbumEntry (ChartId, Position, AlbumId, ArtistName, name) VALUES {albumValues}";
            using (var cmd = new MySqlCommand(chartEntriesSql, conn, tran))
            {
                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task SavePlaylistChartAsync(MySqlConnection conn, MySqlTransaction tran, long chartId, List<AppleChartPlaylistData> albums, string storefront)
        {
            var playlistValues = string.Join(",", albums.ItemIndex().Select(p => $"({chartId}, {p.Index}, '{p.Item.id}','{SafeGetString(storefront)}' )"));
            var chartEntriesSql = $"INSERT INTO tblAppleMusicChartPlaylistMostPlayed (ChartId, Position, PlaylistId, Storefront) VALUES {playlistValues}";
            using (var cmd = new MySqlCommand(chartEntriesSql, conn, tran))
            {
                await cmd.ExecuteNonQueryAsync();
            }
        }

        private static string SafeGetString(string p)
        {
            if (p != null)
            {
                return MySqlHelper.EscapeString(p);
            }
            return string.Empty;
        }

        public List<AppleMusicCurator> GetCuratorByName(string curatorName)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicCurator>("WHERE Name = @0", curatorName);
        }

        public async Task<long> SaveChartAsync(MySqlConnection conn, string storefront, long? genreId, ChartType chartType, string chartName, DateTime chartUTCDateTime)
        {
            long chartId;
            var chartSql = "INSERT INTO tblAppleMusicChart (StoreFront, ChartType, ChartName, GenreId, Date, Timestamp) VALUES (@storefront, @chartType, @chartName, @genreId, @date, @timestamp)";
            using (var cmd = new MySqlCommand(chartSql, conn))
            {
                cmd.Parameters.AddWithValue("@storefront", storefront);
                cmd.Parameters.AddWithValue("@ChartType", chartType);
                cmd.Parameters.AddWithValue("@chartName", chartName);
                cmd.Parameters.AddWithValue("@genreId", genreId);

                cmd.Parameters.AddWithValue("@date", chartUTCDateTime.Date);
                cmd.Parameters.AddWithValue("@timestamp", chartUTCDateTime);

                await cmd.ExecuteNonQueryAsync();

                chartId = cmd.LastInsertedId;
            }

            return chartId;
        }

        public async Task<long> SaveChartAsync(MySqlConnection conn, string storefront, long? genreId, ChartType chartType, string chartName)
        {
            return await this.SaveChartAsync(conn, storefront, genreId, chartType, chartName, DateTime.Now);
        }

        public async Task SaveGenresAsync(string storefront, List<AppleGenreData> chartGenres)
        {

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var tran = await conn.BeginTransactionAsync())
                {
                    var deleteSql = "DELETE FROM tblAppleMusicGenre WHERE Storefront=@storefront";
                    var values = string.Join(",", chartGenres.Select(p => $"('{SafeGetString(storefront)}',{p.id},'{SafeGetString(p.attributes?.name)}')"));
                    var insertSql = $"INSERT INTO tblAppleMusicGenre (Storefront, GenreId, Name) VALUES {values}";
                    try
                    {
                        using (var cmd = new MySqlCommand(deleteSql, conn, tran))
                        {
                            cmd.Parameters.AddWithValue("@storefront", storefront);
                            await cmd.ExecuteNonQueryAsync();
                        }

                        using (var cmd = new MySqlCommand(insertSql, conn, tran))
                        {
                            await cmd.ExecuteNonQueryAsync();
                        }

                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        var logStr = $"Error in Saving Genre delete query {deleteSql}, insert sql: {insertSql}";
                        _logger.Error(ex, logStr);
                    }
                }
            }
        }

        public async Task SaveCurrentMostPlayedSongChartAsync(string storefront, DateTime date, IEnumerable<AppleChartSongData> songs)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var tran = await conn.BeginTransactionAsync())
                {
                    var deleteSql = $"DELETE FROM tblAppleMusicChartSongMostPlayed WHERE Storefront=@storefront";
                    using (var cmd = new MySqlCommand(deleteSql, conn, tran))
                    {
                        cmd.Parameters.AddWithValue("@storefront", storefront);

                        await cmd.ExecuteNonQueryAsync();
                    }

                    var defaultCulture = CultureInfo.GetCultureInfo("sv-SE");
                    var songValues = string.Join(",", songs.ItemIndex().Select(p => $"('{storefront}', {p.Index}, {p.Item.id}, '{SafeGetString(p.Item.attributes?.artistName)}','{SafeGetString(p.Item.attributes?.name)}', '{date.ToString("d", defaultCulture)}')"));
                    var chartEntriesSql = $"INSERT INTO tblAppleMusicChartSongMostPlayed (Storefront, Position, SongId, ArtistName, Name, Date) VALUES {songValues}";
                    using (var cmd = new MySqlCommand(chartEntriesSql, conn, tran))
                    {
                        await cmd.ExecuteNonQueryAsync();
                    }

                    tran.Commit();
                }
            }
        }

        public List<AppleMusicChartGenre> GetChartGenres(string storefront)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicChartGenre>("WHERE @0 IS NULL OR storefront=@0", storefront);
        }

        public List<AppleMusicChartSongMostPlayedSongEntry> GetMostPlayedSongChartEntries(string storefront)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicChartSongMostPlayedSongEntry>("WHERE @0 IS NULL OR storefront=@0", storefront);
        }

        public async Task<Dictionary<string, List<long>>> GetSongIdsWithoutInformationByStorefrontAsync(DateTime chartDate)
        {
            var songIdsByStorefront = new Dictionary<string, List<long>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT cs.songId, cs.storefront FROM ( " +
                          " SELECT DISTINCT cs.SongId, c.Storefront " +
                          " FROM tblAppleMusicChartSongEntry AS cs " +
                          " LEFT JOIN tblAppleMusicChart AS c ON cs.chartId = c.id " +
                          " WHERE c.date = @date) As cs " +
                          "LEFT JOIN tblAppleMusicSong AS s ON s.Id = cs.SongId AND s.storefront = cs.storefront " +
                          "WHERE s.id IS NULL";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@date", chartDate);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var songId = reader.GetInt64(0);
                            var storefront = reader.GetString(1);

                            if (string.IsNullOrWhiteSpace(storefront))
                                continue;

                            storefront = storefront.ToLowerInvariant();

                            if (!songIdsByStorefront.ContainsKey(storefront))
                                songIdsByStorefront.Add(storefront, new List<long>());

                            songIdsByStorefront[storefront].Add(songId);
                        }
                    }
                }
            }

            return songIdsByStorefront;
        }

        public AppleMusicChart GetChart(string chartStorefront, ChartType chartChartType, string chartChartname, int? chartGenreId, DateTime chartDate)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefaultWithSql<AppleMusicChart>("WHERE storefront=@Storefront AND ChartType=@ChartType AND ChartName=@ChartName AND ((@GenreId IS NULL AND GenreId IS NULL) OR GenreId = @GenreId) AND Date=@Date ", new { Storefront = chartStorefront, ChartType = chartChartType, ChartName = chartChartname, GenreId = chartGenreId, Date = chartDate });
        }

        public async Task<DateTime?> GetLatestChartDateAsync()
        {
            DateTime? latestDate = null;
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT Max(Date) as latestDate FROM tblAppleMusicChart;";
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            latestDate = reader.GetDateTimeOrDefault("latestDate");
                        }
                    }
                }
            }
            return latestDate;
        }

        public async Task<List<ChartPlaylistMapping>> GetChartPlaylistIdsPerStorefrontAsync()
        {
            var playlistPerStorefront = new List<ChartPlaylistMapping>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT DisplayStorefront, PlaylistId, TracklistStorefront FROM tblAppleMusicChartsPlaylistMapping";
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            playlistPerStorefront.Add(new ChartPlaylistMapping()
                            {
                                DisplayStorefront = reader.GetString("DisplayStorefront"),
                                PlaylistId = reader.GetString("PlaylistId"),
                                TracklistStorefront = reader.GetString("TracklistStorefront"),
                            });
                        }
                    }
                }
            }

            return playlistPerStorefront;
        }
    }

    public class ChartPlaylistMapping
    {
        public string DisplayStorefront { get; set; }

        public string PlaylistId { get; set; }
        public string TracklistStorefront { get; set; }
    }
    public class ChartsFilter
    {
        public string Storefront { get; set; }

        public int? GenreId { get; set; }
        public bool GenreIsNull { get; set; }

        public ChartType? ChartType { get; set; }

        public string ChartName { get; set; }

        public DateTime? Date { get; set; }
    }
}