using MySqlConnector;
using PetaPoco;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.SpotifyAnalytics.Data;
using Sony.Filtr.Utility.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Sony.Filtr.SpotifyAnalytics
{
    public class SpotifyAnalyticsFactory
    {
        public async Task<List<PlaylistStreamDataSummary>> GetPlaylistStreamDataAsync(string playlistId, string legacyPlaylistUri, int? accountId, DateTime startDate, DateTime endDate)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<PlaylistStreamDataSummary>("WHERE (PlaylistUri=@playlistUri OR PlaylistUri = @playlistId) AND Date >= @startDate AND Date <= @endDate AND (@accountId IS NULL OR accountId=@accountId)",
                new
                {
                    playlistUri = legacyPlaylistUri,
                    playlistId = playlistId,
                    startDate = startDate,
                    endDate = endDate,
                    accountId = accountId
                });
        }

        public async Task<List<PlaylistStreamsSummary>> GetPlaylistsStreamSummaryAsync(List<string> playlistIds)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<PlaylistStreamsSummary>("WHERE PlaylistId IN (@playlistIds)", new { @playlistIds = playlistIds });
        }

        public async Task<List<MarketPlaylistCategoryStreamSummary>> GetMarketStreamDataAsync(DateTime date, int buzzCategoryId)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<MarketPlaylistCategoryStreamSummary>("WHERE Date = @0 AND buzzCategoryId=@1 ", date, buzzCategoryId);
        }
        public async Task<List<MarketPlaylistCategoryStreamSummary>> GetMarketStreamDataAsync(int spotifyAnalyticsAccountId, string market, DateTime startDate, DateTime endDate, int buzzCategoryId)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<MarketPlaylistCategoryStreamSummary>("WHERE accountId = @4 AND (@0 IS NULL OR Market = @0) AND BuzzCategoryId = @1 " +
                                                                                          "AND Date >= @2 && Date <= @3", market, buzzCategoryId, startDate, endDate, spotifyAnalyticsAccountId);
        }
        public async Task<List<MarketPlaylistCategoryStreamSummary>> GetMarketStreamDataAsync(string market, DateTime startDate, DateTime endDate, int buzzCategoryId)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<MarketPlaylistCategoryStreamSummary>("WHERE (@0 IS NULL OR Market = @0) AND BuzzCategoryId = @1 " +
                                                                                          "AND Date >= @2 && Date <= @3", market, buzzCategoryId, startDate, endDate);
        }

        public List<StreamDataSummary> GetStreamSummary(int accountId, DateTime startDate, DateTime endDate)
        {
            var streamSummaries = PetaPocoRepository.ReadOnlyInstance.Fetch<StreamDataSummary>(new Sql("WHERE AccountId=@0 AND Date >= @1 && Date <= @2", accountId, startDate, endDate));
            return streamSummaries;
        }

        public List<StreamDataSummary> GetStreamSummary(DateTime startDate, DateTime endDate)
        {
            var streamSummaries = PetaPocoRepository.ReadOnlyInstance.Fetch<StreamDataSummary>(new Sql("WHERE Date >= @0 && Date <= @1", startDate, endDate));
            return streamSummaries;
        }

        public async Task<List<PlaylistStreamsSummary>> CalculatePlaylistStreamSummaryAsync(IEnumerable<string> playlistUris, DateTime latestDate)
        {
            string playlistUrisString = string.Join(",", playlistUris.Select(x => string.Format("'{0}'", x)));

            var playlistSummaries = new List<PlaylistStreamsSummary>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                string sql = @"SELECT a.*,
                    latest.TotalStreams AS latestStreams,
                    latest.UniqueUsers AS latestUsers
                    FROM tblSpotifyAnalyticsAccountPlaylistStreamInfo AS latest
                    JOIN
                    (SELECT total.Market,
                    total.AccountId,
                    total.playlistUri,
                    MAX(total.Date) AS LatestDate,
                    SUM(IF(total.Date > @daysAgo56, total.TotalStreams, 0)) AS streams56Days,
                    SUM(IF(total.Date > @daysAgo56, total.UniqueUsers, 0)) AS users56Days,
                    SUM(IF(total.Date > @daysAgo28, total.TotalStreams, 0)) AS streams28Days,
                    SUM(IF(total.Date > @daysAgo28, total.UniqueUsers, 0)) AS users28Days,
                    SUM(IF(total.Date > @daysAgo14, total.TotalStreams, 0)) AS streams14Days,
                    SUM(IF(total.Date > @daysAgo14, total.UniqueUsers, 0)) AS users14Days,
                    SUM(IF(total.Date > @daysAgo7, total.TotalStreams, 0)) AS streams7Days,
                    SUM(IF(total.Date > @daysAgo7, total.UniqueUsers, 0)) AS users7Days,
                    SUM(IF(total.Date > @daysAgo56, 1, 0)) AS streamDays56Days,
                    SUM(IF(total.Date > @daysAgo28, 1, 0)) AS streamDays28Days,
                    SUM(IF(total.Date > @daysAgo14, 1, 0)) AS streamDays14Days,
                    SUM(IF(total.Date > @daysAgo7, 1, 0)) AS streamDays7Days
                    FROM tblSpotifyAnalyticsAccountPlaylistStreamInfo AS total
                    WHERE total.Date > @daysAgo56
                      AND total.PlaylistUri IN ({0})
                    GROUP BY Market,
                             AccountId,
                             playlistUri) AS a
                    ON latest.market = a.market
                    AND latest.date = a.latestDate
                    AND latest.accountId = a.accountId
                    AND latest.PlaylistUri = a.PlaylistUri
                    GROUP BY Market,
                             AccountId,
                             playlistUri";

                sql = string.Format(sql, playlistUrisString);
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.CommandTimeout = 1000;
                    cmd.Parameters.AddWithValue("@daysAgo56", latestDate.Date.AddDays(-56));
                    cmd.Parameters.AddWithValue("@daysAgo28", latestDate.Date.AddDays(-28));
                    cmd.Parameters.AddWithValue("@daysAgo14", latestDate.Date.AddDays(-14));
                    cmd.Parameters.AddWithValue("@daysAgo7", latestDate.Date.AddDays(-7));

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var summary = new PlaylistStreamsSummary()
                            {
                                Market = reader.GetSafeString("market"),
                                PlaylistId =  reader.GetSafeString("playlistUri"),
                                AccountId = reader.GetInt32("accountId"),
                                LatestDate = reader.GetDateTime("latestDate"),
                                Streams56days = reader.GetInt32("streams56Days"),
                                Listeners56days = reader.GetInt32("users56Days"),
                                Streams28days = reader.GetInt32("streams28Days"),
                                Listeners28days = reader.GetInt32("users28Days"),
                                Streams14days = reader.GetInt32("streams14Days"),
                                Listeners14days = reader.GetInt32("users14Days"),
                                Streams7days = reader.GetInt32("streams7Days"),
                                Listeners7days = reader.GetInt32("users7Days"),
                                StreamsLatest = reader.GetInt32("latestStreams"),
                                ListenersLatest = reader.GetInt32("latestUsers"),
                                TotalStreamDays = 0,
                                StreamDays56Days = reader.GetInt32("streamDays56Days"),
                                StreamDays28Days = reader.GetInt32("streamDays28Days"),
                                StreamDays14Days = reader.GetInt32("streamDays14Days"),
                                StreamDays7Days = reader.GetInt32("streamDays7Days"),
                            };
                            playlistSummaries.Add(summary);
                        }
                    }
                }
            }
            return playlistSummaries;
        }

    }
}