using MySqlConnector;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Core.SpotifyBrowse.Data;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using Sony.Filtr.Database;

namespace Sony.Filtr.Core.SpotifyBrowse
{
    public class SpotifyBrowseFactory
    {
        public async Task<List<DateTime>> GetSpotifyBrowseDatesAsync(string region)
        {
            var dates = new List<DateTime>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand command;
                if (region != null)
                {
                    const string sqlString = "SELECT Date " +
                                             "FROM tblSpotifyBrowseDate2 " +
                                             "WHERE Market = @region " +
                                             "ORDER BY Date DESC";
                    command = new MySqlCommand(sqlString, conn);
                    command.Parameters.AddWithValue("@region", region);
                }
                else
                {
                    const string sqlString = "SELECT DISTINCT Date " +
                                             "FROM tblSpotifyBrowseDate2 " +
                                             "ORDER BY Date DESC";
                    command = new MySqlCommand(sqlString, conn);
                }

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

            return dates;
        }

        public async Task<Dictionary<DateTime, DateTime>> GetSpotifyBrowseUtcDatesAsync(string region)
        {
            var dates = new Dictionary<DateTime, DateTime>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sqlString = "SELECT Date, Timestamp FROM tblSpotifyBrowseDate2 WHERE Market = @region ORDER BY Date DESC";

                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@region", region);

                using(var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        dates.Add(reader.GetDateTime(0), reader.GetDateTime(1));
                    }
                }
            }

            return dates;
        }

        public async Task<List<SpotifyBrowseCategory>> GetSpotifyBrowseCategoriesAsync(string region)
        {
            var categories = new List<SpotifyBrowseCategory>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sqlString = "SELECT Region, CategoryId, CategoryName " +
                                         "FROM tblSpotifyBrowseCategoryName " +
                                         "WHERE Region = @region";

                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@region", region);

                var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    categories.Add(new SpotifyBrowseCategory()
                    {
                        Region = reader.GetString(0),
                        CategoryId = reader.GetString(1),
                        CategoryName = reader.GetString(2)
                    });
                }
                reader.Close();
            }

            return categories;
        }

        public async Task<List<string>> GetSpotifyBrowseMarketsAsync()
        {
            var markets = new List<string>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sqlString = "SELECT DISTINCT Market FROM tblSpotifyBrowseFeaturedPlaylist2";

                var command = new MySqlCommand(sqlString, conn);

                var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    if(!reader.IsDBNull(0))
                        markets.Add(reader.GetString(0));
                }

                reader.Close();
            }

            return markets;
        }

        public async Task<List<SpotifyBrowseCategoryStatItem>> GetSpotifyBrowseCategoryStatsAsync(string region, DateTime date)
        {
            var items = new List<SpotifyBrowseCategoryStatItem>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sqlString = "SELECT b.Market, b.Category, b.Date, b.Position, p.PlaylistUri, b.PlaylistName, p.user, p.BuzzCategoryId, bc.Name, pc.CategoryName, COALESCE(bp.PeakPosition, b.Position) " +
                                         "FROM tblSpotifyBrowseFeaturedPlaylist2 b " +
                                         "INNER JOIN tblSpotifyPlaylist AS p ON b.playlistId = p.playlistId " +
                                         "LEFT JOIN tblSpotifyBrowsePeakPosition2 AS bp ON bp.playlistId = b.playlistId AND bp.Market = b.Market AND bp.Category = b.Category " +
                                         "LEFT JOIN tblSpotifyBrowseCategoryName AS pc ON b.Market = pc.Region AND b.Category = pc.CategoryId " +
                                         "LEFT JOIN BuzzUser AS bu ON bu.Username = p.user AND bu.ServiceType = 0 " +
                                         "LEFT JOIN BuzzCategory AS bc ON bc.Id = bu.CategoryId " +
                                         "WHERE b.Date = @date " +
                                         "AND (@region IS NULL OR b.Market = @region)";

                using(var command = new MySqlCommand(sqlString, conn))
                {
                    command.Parameters.AddWithValue("@region", region);
                    command.Parameters.AddWithValue("@date", date);

                    using(var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            items.Add(BuildSpotifyBrowseCategoryStatItem(reader));
                        }
                    }
                }
            }

            return items;
        }
        
        public async Task<List<SpotifyBrowseCategoryStatItem>> GetSpotifyBrowsePlaylistsSummaryAsync(List<string> playlistIds, DateTime startDate, DateTime endDate)
        {
            List<SpotifyBrowseCategoryStatItem> playlistInBrowse = new List<SpotifyBrowseCategoryStatItem>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var playlistIdsParameter = string.Join(",", playlistIds.Select(p => "\"" + MySqlHelper.EscapeString(p) + "\""));
                var sqlString = "SELECT b.Market, b.Category, b.Date, b.Position, p.PlaylistUri, b.PlaylistName, p.user, bu.CategoryId, bc.Name, pc.CategoryName, COALESCE(bp.PeakPosition, b.Position) " +
                                "FROM tblSpotifyBrowseFeaturedPlaylist2 b " +
                                "INNER JOIN tblSpotifyPlaylist AS p ON p.playlistId = b.playlistId " +
                                "LEFT JOIN tblSpotifyBrowsePeakPosition2 AS bp ON bp.playlistId = b.playlistId AND bp.Market = b.Market AND bp.Category = b.Category " +
                                "LEFT JOIN tblSpotifyBrowseCategoryName AS pc ON b.Market = pc.Region AND b.Category = pc.CategoryId " +
                                "LEFT JOIN BuzzUser AS bu ON bu.Username = p.user AND bu.MusicServiceId = @spotifyMusicServiceId " +
                                "LEFT JOIN BuzzCategory AS bc ON bc.Id = bu.CategoryId " +
                                $"WHERE (b.date >= @startDate AND b.date <= @endDate) AND b.playlistId IN ({playlistIdsParameter}) ";

                var command = new MySqlCommand(sqlString, conn);
                command.Parameters.AddWithValue("@startDate", startDate.Date);
                command.Parameters.AddWithValue("@endDate", endDate.Date);
                command.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    playlistInBrowse.Add(BuildSpotifyBrowseCategoryStatItem(reader));
                }
                reader.Close();
            }

            return playlistInBrowse;
        }

        public async Task<List<SpotifyBrowseCategorySimpleStatItem>> GetSpotifyBrowsePlaylistsSimpleItemAsync(string playlistUri, DateTime startDate, DateTime endDate)
        {
            List<SpotifyBrowseCategorySimpleStatItem> playlistInBrowse = new List<SpotifyBrowseCategorySimpleStatItem>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sqlString = "SELECT b.Market, b.Category, b.Date, b.Position " +
                                         " FROM tblSpotifyBrowseFeaturedPlaylist2 b " +
                                         " WHERE (b.date >= @startDate AND b.date <= @endDate) AND b.playlistURI = @playlistUri ";

                var command = new MySqlCommand(sqlString, conn);
                command.Parameters.AddWithValue("@startDate", startDate.Date);
                command.Parameters.AddWithValue("@endDate", endDate.Date);
                command.Parameters.AddWithValue("@playlistUri", playlistUri);

                var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var item = new SpotifyBrowseCategorySimpleStatItem()
                    {
                        Market = reader.GetString(0),
                        CategoryId = reader.GetString(1),
                        Date = reader.GetDateTime(2),
                        Position = reader.GetInt32(3),
                    };

                    playlistInBrowse.Add(item);
                }
                reader.Close();
            }

            return playlistInBrowse;
        }

        private static SpotifyBrowseCategoryStatItem BuildSpotifyBrowseCategoryStatItem(DbDataReader reader)
        {
            return new SpotifyBrowseCategoryStatItem()
            {
                Market = reader.GetString(0),
                CategoryId = reader.GetString(1),
                Date = reader.GetDateTime(2),
                Position = reader.GetInt32(3),
                PlaylistUri = reader.GetString(4),
                PlaylistName = reader.GetString(5),
                OwnerUsername = reader.IsDBNull(6) ? null : reader.GetString(6),
                BuzzCategoryId = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7),
                BuzzCategoryName = reader.IsDBNull(8) ? null : reader.GetString(8),
                CategoryName = reader.IsDBNull(9) ? null : reader.GetString(9),
                PeakPosition = reader.GetInt32(10),
            };
        }

        public async Task<List<SpotifyBrowseTopListItem>> GetSpotifyBrowseTopListAsync(string region, string category, DateTime date)
        {
            var items = new List<SpotifyBrowseTopListItem>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sqlString = "SELECT b.Position, b.playlistId, b.PlaylistName, p.user, COALESCE(a1.TotalStreams,a2.TotalStreams) as totalStreams, COALESCE(a1.UniqueUsers, a2.UniqueUsers) as UniqueUsers, 0 AS IsSony, " +
                                         "p.CountryCode, bu.CategoryId, bz.Name, p.Image, GROUP_CONCAT(DISTINCT b2.Market ORDER BY b2.Market) AS Markets, " +
                                         "pc.CategoryName, COALESCE(bp.PeakPosition, b.Position) " +
                                         "FROM tblSpotifyBrowseFeaturedPlaylist2 AS b " +
                                         "LEFT JOIN tblSpotifyPlaylist AS p ON p.PlaylistId = b.playlistId " +
                                         "LEFT JOIN tblSpotifyBrowsePeakPosition2 AS bp ON bp.playlistId = b.playlistId AND bp.Market = b.Market AND bp.Category = b.Category " +
                                         "LEFT JOIN tblSpotifyAnalyticsAccountPlaylistStreamInfo AS a1 ON a1.PlaylistUri = p.PlaylistUri AND b.Market = a1.Market AND b.Date = a1.Date AND a1.accountId = @analyticsAccountId " +
                                         "LEFT JOIN tblSpotifyAnalyticsAccountPlaylistStreamInfo AS a2 ON a2.PlaylistUri = p.PlaylistId AND b.Market = a2.Market AND b.Date = a2.Date AND a2.accountId = @analyticsAccountId " +
                                         "LEFT JOIN tblSpotifyBrowseFeaturedPlaylist2 AS b2 ON b.PlaylistId = b2.PlaylistId AND b.Date = b2.Date " +
                                         "LEFT JOIN BuzzUser AS bu ON bu.Username = p.user AND bu.MusicServiceId = @musicServiceId " +
                                         "LEFT JOIN BuzzCategory AS bz ON bz.ID = bu.CategoryId " +
                                         "LEFT JOIN tblSpotifyBrowseCategoryName AS pc ON pc.Region = b.Market AND pc.CategoryId = b.Category " +
                                         "WHERE b.Date = @date " +
                                         "AND b.Category = @category " +
                                         "AND b.Market = @region " +
                                         "GROUP BY b.Position " +
                                         "ORDER BY b.Position";
                
                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@region", region);
                command.Parameters.AddWithValue("@category", category);
                command.Parameters.AddWithValue("@date", date);
                command.Parameters.AddWithValue("@analyticsAccountId", SpotifyAnalyticsAccount.Sony);
                command.Parameters.AddWithValue("@musicServiceId", MusicService.Spotify);

                var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var buzzCategoryid = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);
                    var isSony = (buzzCategoryid == (int)StaticBuzzCategory.SonyMusic);
                    string playlistMarket = string.Empty; //We previously only exposed this for SME playlist so we keep that behavior.
                    if (isSony)
                    {
                        playlistMarket = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
                    }

                    var playlistId = reader.GetString(1);
                    items.Add(new SpotifyBrowseTopListItem()
                    {
                        Position = reader.GetInt32(0),
                        PlaylistURI = $"spotify:playlist:{playlistId}",
                        PlaylistName = reader.GetString(2),
                        OwnerUsername = reader.IsDBNull(3) ? string.Empty : reader.GetString(3),
                        TotalStreams = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4),
                        UniqueUserStreams = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5),
                        IsSony = isSony,
                        PlaylistMarket = playlistMarket,
                        BuzzCategoryId = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8),
                        BuzzCategoryName = reader.IsDBNull(9) ? string.Empty : reader.GetString(9),
                        SpotifyImageUrl = reader.IsDBNull(10) ? string.Empty : reader.GetString(10),
                        BrowseMarketString = reader.IsDBNull(11) ? string.Empty : reader.GetString(11),
                        CategoryName = reader.IsDBNull(12) ? null : reader.GetString(12),
                        Peak = reader.GetInt32(13),
                        CategoryId = category,
                    });
                }
                reader.Close();
            }

            return items;
        }

        public async Task<List<SpotifyBrowseTopListItem>> GetSpotifyBrowseForExportAsync(string market, DateTime date)
        {
            var items = new List<SpotifyBrowseTopListItem>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sqlString = "SELECT b.Position, b.playlistId, b.PlaylistName, p.User, COALESCE(a1.TotalStreams, a2.TotalStreams) as totalStreams, COALESCE(a1.UniqueUsers, a2.UniqueUsers) as UniqueUsers, " +
                                         "p.CountryCode, bu.CategoryId, bz.Name, p.Image, b.Market, b.Category, pc.CategoryName, COALESCE(bp.PeakPosition, b.Position), pf.Followers " +
                                         "FROM tblSpotifyBrowseFeaturedPlaylist2 AS b " +
                                         "LEFT JOIN tblSpotifyPlaylist AS p ON p.playlistId = b.playlistId " +
                                         "LEFT JOIN tblSpotifyBrowsePeakPosition2 AS bp ON bp.playlistId = p.playlistId AND bp.Market = b.Market AND bp.Category = b.Category " +
                                         "LEFT JOIN tblSpotifyPlaylistLegacyUri AS legacy ON legacy.playlistId = b.playlistId " +
                                         "LEFT JOIN tblSpotifyAnalyticsAccountPlaylistStreamInfo AS a1 ON a1.PlaylistUri = legacy.PlaylistUri AND a1.Market = b.Market AND a1.Date = b.Date AND a1.accountId = @accountId " +
                                         "LEFT JOIN tblSpotifyAnalyticsAccountPlaylistStreamInfo AS a2 ON a2.PlaylistUri = b.PlaylistId AND a2.Market = b.Market AND a2.Date = b.Date AND a2.accountId = @accountId " +
                                         "LEFT JOIN BuzzUser AS bu ON bu.Username = p.User AND bu.ServiceType = 0 " +
                                         "LEFT JOIN BuzzCategory AS bz ON bz.ID = bu.CategoryId " +
                                         "LEFT JOIN tblSpotifyBrowseCategoryName AS pc ON pc.Region = b.Market AND pc.CategoryId = b.Category " +
                                         "LEFT JOIN tblSpotifyPlaylistFollowers AS pf ON pf.playlistId = b.playlistId " +
                                         "WHERE b.Date = @date " +
                                         "AND (@region IS NULL OR b.Market = @region)" +
                                         "ORDER BY b.Market, b.Category, b.Position";

                var command = new MySqlCommand(sqlString, conn);
                command.Parameters.AddWithValue("@region", market);
                command.Parameters.AddWithValue("@date", date);
                command.Parameters.AddWithValue("@accountId", SpotifyAnalyticsAccount.Sony);

                var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var buzzCategoryId = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7);
                    var isSony = (buzzCategoryId == (int)StaticBuzzCategory.SonyMusic);
                    string playlistMarket = string.Empty; //We previously only exposed this for SME playlist so we keep that behavior.
                    if (isSony)
                    {
                        playlistMarket = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
                    }

                    var playlistId = reader.GetString(1);
                    items.Add(new SpotifyBrowseTopListItem()
                    {
                        Position = reader.GetInt32(0),
                        PlaylistURI = $"spotify:playlist:{playlistId}",
                        PlaylistName = reader.GetString(2),
                        OwnerUsername = reader.IsDBNull(3) ? string.Empty : reader.GetString(3),
                        TotalStreams = reader.IsDBNull(4) ? null : (int?) reader.GetInt32(4),
                        UniqueUserStreams = reader.IsDBNull(5) ? null : (int?) reader.GetInt32(5),
                        IsSony = (buzzCategoryId == (int)StaticBuzzCategory.SonyMusic),
                        PlaylistMarket = playlistMarket,
                        BuzzCategoryId = buzzCategoryId,
                        BuzzCategoryName = reader.IsDBNull(8) ? string.Empty : reader.GetString(8),
                        SpotifyImageUrl = reader.IsDBNull(9) ? string.Empty : reader.GetString(9),
                        BrowseMarket = reader.GetString(10),
                        CategoryId = reader.GetString(11),
                        CategoryName = reader.IsDBNull(12) ? null : reader.GetString(12),
                        Peak = reader.GetInt32(13),
                        Followers = reader.IsDBNull(14) ? (int?)null : reader.GetInt32(14)
                    });
                }
            
                reader.Close();
                await conn.CloseAsync();
            }

            return items;
        }

        public async Task SetSpotifyBrowseDateAsync(string region, DateTime date)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                const string sqlString = "INSERT INTO tblSpotifyBrowseDate2 (Date, Market) VALUES(@date, @region) ON DUPLICATE KEY UPDATE Timestamp = CURRENT_TIMESTAMP";

                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@date", date);
                command.Parameters.AddWithValue("@region", region);

                await command.ExecuteNonQueryAsync();
            }
        }

        public async Task<HashSet<string>> GetPlaylistUrisAsync(string market, DateTime date)
        {
            var playlistUris = new HashSet<string>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sqlString = "SELECT PlaylistUri FROM tblSpotifyBrowseFeaturedPlaylist2 WHERE Market = @region AND Date = @date";

                var command = new MySqlCommand(sqlString, conn);

                command.Parameters.AddWithValue("@date", date.Date);
                command.Parameters.AddWithValue("@region", market);
                
                var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    playlistUris.Add(reader.GetString(0));
                }

                reader.Close();
            }

            return playlistUris;
        }
    }
}