using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MoreLinq;
using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.AppleMusic.Data.Internal;
using Sony.Filtr.AppleMusic.Data.Streams;
using Sony.Filtr.AppleMusic.Streams;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Database;
using Sony.Filtr.Utility;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.AppleMusic
{
    public class AppleMusicStreamsManager
    {
        public List<AppleMusicPlaylistStreams> GetPlaylistStreams()
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicPlaylistStreams>();
        }

        public List<AppleMusicStreamSummary> GetStorefrontStreams(DateTime startDate, DateTime endDate)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicStreamSummary>("WHERE date BETWEEN @0 AND @1", startDate, endDate);
        }

        public async Task<List<AppleMusicMarketPlaylistCategoryStreamSummary>> GetMarketPlaylistStreamsAsync(DateTime startDate, DateTime endDate)
        {
            List<AppleMusicMarketPlaylistCategoryStreamSummary> summaries = new List<AppleMusicMarketPlaylistCategoryStreamSummary>();

            var sql = "SELECT s.date, s.storefront, b.CategoryId, " +
                      "SUM(s.totalStreams) as totalStreams, SUM(IF(s.storefront = b.countryCode, s.totalStreams, 0)) as localStreams, " +
                      "SUM(UniqueUsers) as totalUsers, SUM(IF(s.storefront = b.countryCode, s.UniqueUsers, 0)) as localUsers " +
                      "FROM tblAppleMusicPlaylistStream AS s " +
                      "INNER JOIN tblAppleMusicPlaylist AS p ON s.playlistId = p.id " +
                      "LEFT JOIN tblAppleMusicCurator AS c ON p.curatorId = c.id " +
                      "LEFT JOIN BuzzUser AS b ON b.username = CAST(c.id as char(500)) AND b.MusicServiceId = @musicServiceId " +
                      "WHERE s.date BETWEEN @startDate AND @endDate " +
                      "GROUP BY s.date, s.storefront, b.CategoryID";

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

                cmd.Parameters.AddWithValue("@startDate", startDate);
                cmd.Parameters.AddWithValue("@endDate", endDate);
                cmd.Parameters.AddWithValue("@musicServiceId", (int)MusicService.AppleMusic);

                var reader = await cmd.ExecuteReaderAsync();

                while(await reader.ReadAsync())
                {
                    var summary = new AppleMusicMarketPlaylistCategoryStreamSummary()
                    {
                        Storefront = reader.GetString("storefront"),
                        Date = reader.GetDateTime("date"),
                        BuzzCategoryId = reader.GetIntOrDefault("categoryId"),

                        TotalStreams = reader.GetLong("totalStreams"),
                        LocalStreams = reader.GetLong("localStreams"),
                        TotalUniqueUsers = reader.GetLong("totalUsers"),
                        LocalUniqueUsers = reader.GetLong("localUsers"),
                    };
                    summaries.Add(summary);
                }
            }
            return summaries;
        }

        public async Task<DateTime> GetLatestDayWithAggregatedStreamsDataAsync(AppleS3FileType fileType)
        {
            DateTime latestDate = DateTime.Today;

            using(MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT Max(Date) FROM tblAppleMusicAnalyticsDate WHERE FileType = @fileType", conn);

                cmd.Parameters.AddWithValue("@fileType", (int)fileType);

                var dateTime = await cmd.ExecuteScalarAsync() as DateTime?;

                if (dateTime.HasValue)
                    latestDate = dateTime.Value;
            }
            return latestDate;
        }
        
        public async Task<List<AppleMusicContainerStreamSummary>> CalculateAppleMusicContainerStreamsSummariesAsync(IEnumerable<string> containerIds, DateTime latestDate)
        {
            var playlistSummaries = new List<AppleMusicContainerStreamSummary>();

            const string sql = "WHERE Date > @daysAgo56 " +
                               "AND ContainerId IN " +
                               "(@containerIds)";

            var containerStreams = PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicContainerStream>(sql, new { daysAgo56 = latestDate.Date.AddDays(-56), containerIds = containerIds });

            var groupedContainers = containerStreams.GroupBy(s => s.ContainerId);

            foreach(var container in groupedContainers)
            {
                // Add global stream summary based on all stream summaries from latest 56 days
                var globalSummary = BuildAppleMusicContainerStreamSummary(container.Key, "global", container.ToList(), latestDate);
                SetGlobalStreamDays(globalSummary, container.ToList(), latestDate);
                playlistSummaries.Add(globalSummary);
                

                // Add country-specific stream summaries based on all stream summaries for each country from the latest 56 days
                var groupedCountries = container.Where(c => !string.IsNullOrWhiteSpace(c.CountryCode)).GroupBy(c => c.CountryCode);
                var localSummaries = groupedCountries.Select(streamCountry => BuildLocalAppleContainerStreamSummary(container.Key, streamCountry.Key, streamCountry.ToList(), latestDate,
                    globalSummary.StreamDays56Days, globalSummary.StreamDays28Days, globalSummary.StreamDays14Days, globalSummary.StreamDays7Days));

                playlistSummaries.AddRange(localSummaries);
            }
            var globalList = playlistSummaries.Where(x => x.CountryCode == "global");

            foreach (var global in globalList)
            {
                var containerId = global.ContainerId;
                global.StreamsLatest = playlistSummaries.Where(x => x.CountryCode != "global" && x.ContainerId == containerId).Sum(c => c.StreamsLatest);
            }
            
            return playlistSummaries;
        }

        private void SetGlobalStreamDays(AppleMusicContainerStreamSummary streamSummary, List<AppleMusicContainerStream> containerStreams, DateTime latestDate)
        {
            streamSummary.StreamDays7Days = containerStreams.Where(c => c.Date > latestDate.Date.AddDays(-7)).DistinctBy(s => s.Date).Count();
            streamSummary.StreamDays14Days = containerStreams.Where(c => c.Date > latestDate.Date.AddDays(-14)).DistinctBy(s => s.Date).Count();
            streamSummary.StreamDays28Days = containerStreams.Where(c => c.Date > latestDate.Date.AddDays(-28)).DistinctBy(s => s.Date).Count();
            streamSummary.StreamDays56Days = containerStreams.DistinctBy(s => s.Date).Count();
        }
        private AppleMusicContainerStreamSummary BuildLocalAppleContainerStreamSummary(string containerId, string countryCode, List<AppleMusicContainerStream> containerStreams, DateTime latestDate, int days56, int days28, int days14, int days7)
        {
            var streamSummary = BuildAppleMusicContainerStreamSummary(containerId, countryCode, containerStreams, latestDate);

            streamSummary.StreamDays56Days = days56;
            streamSummary.StreamDays28Days = days28;
            streamSummary.StreamDays14Days = days14;
            streamSummary.StreamDays7Days = days7;

            return streamSummary;
        }

        private AppleMusicContainerStreamSummary BuildAppleMusicContainerStreamSummary(string containerId, string countryCode, List<AppleMusicContainerStream> containerStreams, DateTime latestDate)
        {
            return new AppleMusicContainerStreamSummary
            {
                ContainerId = containerId,
                CountryCode = countryCode,
                LatestDate = latestDate,
                StreamsLatest = containerStreams.MaxBy(c => c.Date).Streams,
                Streams7Days = containerStreams.Where(c => c.Date > latestDate.Date.AddDays(-7)).Sum(s => s.Streams),
                Streams14Days = containerStreams.Where(c => c.Date > latestDate.Date.AddDays(-14)).Sum(s => s.Streams),
                Streams28Days = containerStreams.Where(c => c.Date > latestDate.Date.AddDays(-28)).Sum(s => s.Streams),
                Streams56Days = containerStreams.Sum(s => s.Streams)
            };
        }
        
        public List<AppleMusicContainerStreamSummary> GetAppleMusicContainerStreamsSummaries(IEnumerable<string> containerIds, string countryCode)
        {
            var containerIdList = containerIds.ToList();
            if (!containerIdList.Any())
                return new List<AppleMusicContainerStreamSummary>();

            const string sql = "WHERE ContainerId IN (@ContainerIds) AND CountryCode = @CountryCode";
       
            var containerStreams = PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicContainerStreamSummary>(sql, new { ContainerIds = containerIdList, CountryCode = countryCode});

            return containerStreams;
        }

        /// <summary>
        ///     Returns list of <see cref="AppleMusicContainerStreamSummary"/> from <b>tblAppleMusicContainerStreamSummary</b> table
        ///     where country code is "global".
        /// </summary>
        /// <param name="containerIds">Containers ids to search data. Used by "IN" sql operator.</param>
        public async Task<IEnumerable<AppleMusicContainerStreamSummary>> GetAppleMusicContainerStreamsSummariesGlobalAsync(IEnumerable<string> containerIds)
        {
            if (!containerIds.Any())
                return Enumerable.Empty<AppleMusicContainerStreamSummary>();

            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                // This also prevent from sql-injections
                var containersId = Maybe.ToCommaSeparated(containerIds.Aggregate((a, b) => $"{a},{b}"));

                var sbSql = new StringBuilder();
                // This select statement returns only required fields and do sorting 
                // on SQL server side to prevent memory leaks and CPU consumption.
                sbSql.Append("SELECT " +
                                "ContainerId, " +
                                "CountryCode, " +
                                "Streams7Days, " +
                                "Streams14Days, " +
                                "Streams28Days, " +
                                "Streams56Days, " +
                                "StreamsLatest, " +
                                "StreamDays7Days, " +
                                "StreamDays14Days, " +
                                "StreamDays28Days, " +
                                "StreamDays56Days " +
                                "FROM tblAppleMusicContainerStreamSummary "
                                );

                sbSql.Append($"WHERE CountryCode = ('global') AND ContainerId IN ({containersId}) ORDER BY CountryCode, ContainerId;");

                string sql = sbSql.ToString();

                var data = await conn.QueryAsync<AppleMusicContainerStreamSummary>(sql, commandType: System.Data.CommandType.Text);

                return data;
            }
        }

        /// <summary>
        ///     Returns list of <see cref="AppleMusicContainerStreamSummary"/> objects grouped by <see cref="AppleMusicContainerStreamSummary.CountryCode"/>.
        /// </summary>
        /// <param name="containerCountryCodes">Country codes in lowwer case to search data. Used by "IN" sql operator.</param>
        /// <param name="containerIds">Containers ids to search data. Used by "IN" sql operator.</param>
        public async Task<IDictionary<string, AppleMusicContainerStreamSummary[]>> GetContainerStreamSummariesByCountryCodeAsync(IEnumerable<string> containerCountryCodes, IEnumerable<string> containerIds)
        {
            if (!containerIds.Any())
                return new Dictionary<string, AppleMusicContainerStreamSummary[]>();

            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                // This also prevent from sql-injections
                var countryCodes = Maybe.ToCommaSeparated(containerCountryCodes.Aggregate((a, b) => $"{a},{b}"));
                var containersId = Maybe.ToCommaSeparated(containerIds.Aggregate((a, b) => $"{a},{b}"));

                var sbSql = new StringBuilder();
                // This select statement returns only required fields and do sorting 
                // on SQL server side to prevent memory leaks and CPU consumption.
                sbSql.Append("SELECT " +
                                "ContainerId, " +
                                "CountryCode, " +
                                "Streams7Days, " +
                                "Streams14Days, " +
                                "Streams28Days, " +
                                "Streams56Days, " +
                                "StreamsLatest, " +
                                "StreamDays7Days, " +
                                "StreamDays14Days, " +
                                "StreamDays28Days, " +
                                "StreamDays56Days " +
                                "FROM tblAppleMusicContainerStreamSummary "
                                );

                sbSql.Append($"WHERE CountryCode IN ({countryCodes}) AND ContainerId IN ({containersId}) ORDER BY CountryCode, ContainerId;");

                string sql = sbSql.ToString();

                var data = await conn.QueryAsync<AppleMusicContainerStreamSummary>(sql, commandType: System.Data.CommandType.Text);

                var result = data.GroupBy(s => s.CountryCode).ToDictionary(s => s.Key, s => s.ToArray());

                return result;
            }
        }
    }
}