using MySql.Data.MySqlClient;
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(int spotifyAnalyticsAccountId, string playlistId, string playlistUri, DateTime latestDate)
        {
            var playlistSummaries = new List<PlaylistStreamsSummary>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = @"
SELECT 
	a.*, latest.TotalStreams as latestStreams, latest.UniqueUsers as latestUsers
FROM (
	SELECT 
		total.Market, 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.accountId = @accountId AND total.Date > @daysAgo56
		AND (total.PlaylistUri = @playlistUri OR total.playlistUri = @playlistId)
	GROUP BY Market) AS a
INNER JOIN tblSpotifyAnalyticsAccountPlaylistStreamInfo AS latest ON 
	latest.accountId = @accountID 
	AND latest.market = a.market 
	AND latest.date = a.latestDate 
	AND (latest.PlaylistUri = @playlistUri OR latest.playlistUri = @playlistId)
GROUP BY Market";

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

                    cmd.Parameters.AddWithValue("@accountId", spotifyAnalyticsAccountId);
                    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.GetString("market"),
                                PlaylistId = playlistId,
                                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;
        }

    }
}