using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using PetaPoco.Business;
using Sony.Filtr.AppleMusic.Data.Streams;
using Sony.Filtr.AppleMusic.Streams;
using Sony.Filtr.Contracts.Definitions;

namespace Sony.Filtr.AppleMusic
{
    public class AppleMusicTrackStreamsManager
    {
        public async Task<List<AppleMusicTrackDemographic>> GetTrackStreamDemographicsAsync(List<string> isrcs, DateTime startDate, DateTime endDate, string countryCode, SpotifyAnalyticsAccount? licensor = null)
        {
            return PetaPocoRepository.Instance.Fetch<AppleMusicTrackDemographic>("SELECT DistributerId, Date, CountryCode, ReportVendorId, ISRC, ReportDate, " +
                                                                                 "SUM(StreamsGenderFemaleAge0_17) as StreamsGenderFemaleAge0_17, " +
                                                                                 "SUM(StreamsGenderMaleAge0_17) as StreamsGenderMaleAge0_17, " +
                                                                                 "SUM(StreamsGenderUnknownAge0_17) as StreamsGenderUnknownAge0_17, " +
                                                                                 "SUM(StreamsGenderFemaleAge18_24) as StreamsGenderFemaleAge18_24, " +
                                                                                 "SUM(StreamsGenderMaleAge18_24) as StreamsGenderMaleAge18_24, " +
                                                                                 "SUM(StreamsGenderUnknownAge18_24) as StreamsGenderUnknownAge18_24, " +
                                                                                 "SUM(StreamsGenderFemaleAge25_34) as StreamsGenderFemaleAge25_34, " +
                                                                                 "SUM(StreamsGenderMaleAge25_34) as StreamsGenderMaleAge25_34, " +
                                                                                 "SUM(StreamsGenderUnknownAge_25_34) as StreamsGenderUnknownAge_25_34, " +
                                                                                 "SUM(StreamsGenderFemaleAge35_44) as StreamsGenderFemaleAge35_44, " +
                                                                                 "SUM(StreamsGenderMaleAge35_44) as StreamsGenderMaleAge35_44, " +
                                                                                 "SUM(StreamsGenderUnknownAge35_44) as StreamsGenderUnknownAge35_44, " +
                                                                                 "SUM(StreamsGenderFemaleAge45_54) as StreamsGenderFemaleAge45_54, " +
                                                                                 "SUM(StreamsGenderMaleAge45_54) as StreamsGenderMaleAge45_54, " +
                                                                                 "SUM(StreamsGenderUnknownAge45_54) as StreamsGenderUnknownAge45_54, " +
                                                                                 "SUM(StreamsGenderFemaleAge55_64) as StreamsGenderFemaleAge55_64, " +
                                                                                 "SUM(StreamsGenderMaleAge55_64) as StreamsGenderMaleAge55_64, " +
                                                                                 "SUM(StreamsGenderUnknownAge55_64) as StreamsGenderUnknownAge55_64, " +
                                                                                 "SUM(StreamsGenderFemaleAge60_plus) as StreamsGenderFemaleAge60_plus, " +
                                                                                 "SUM(StreamsGenderMaleAge60_plus) as StreamsGenderMaleAge60_plus, " +
                                                                                 "SUM(StreamsGenderUnknownAge60_plus) as StreamsGenderUnknownAge60_plus, " +
                                                                                 "SUM(StreamsGenderFemaleAgeUnknown) as StreamsGenderFemaleAgeUnknown, " +
                                                                                 "SUM(StreamsGenderMaleAgeUnknown) as StreamsGenderMaleAgeUnknown, " +
                                                                                 "SUM(StreamsGenderUnknownAgeUnknown) as StreamsGenderUnknownAgeUnknown " +
                                                                                 "FROM tblAppleMusicSongStreamDemographics " +
                                                                                 "WHERE (@countryCode IS NULL OR CountryCode = @countryCode) " +
                                                                                 "AND isrc IN (@isrcs) " +
                                                                                 "AND (@licensorId IS NULL OR DistributerId = @licensorId) " +
                                                                                 "AND Date >= @startDate AND Date <= @endDate " +
                                                                                 "GROUP BY Date, CountryCode, ISRC ", new { countryCode = countryCode, isrcs = isrcs, licensorId = (int?)licensor, startDate = startDate, endDate = endDate });
        }

        public async Task<List<AppleMusicTrackStream>> GetTrackStreamAsync(List<string> isrcs, DateTime startDate, DateTime endDate, string countryCode, SpotifyAnalyticsAccount? licensor = null)
        {
            return PetaPocoRepository.Instance.Fetch<AppleMusicTrackStream>("SELECT DistributerId, ReportDate, ReportVendorId, CountryCode, Date, ISRC, " +
                                                                                 "SUM(ActionType1) AS actionType1, " +
                                                                                 "SUM(ContainerSubtype0) AS ContainerSubtype0, " +
                                                                                 "SUM(ContainerSubtype1) AS ContainerSubtype1, " +
                                                                                 "SUM(ContainerSubtype2) AS ContainerSubtype2, " +
                                                                                 "SUM(ContainerSubtype3) AS ContainerSubtype3, " +
                                                                                 "SUM(ContainerSubtype4) AS ContainerSubtype4, " +
                                                                                 "SUM(ContainerSubtype5) AS ContainerSubtype5,  " +
                                                                                 "SUM(ContainerSubtype6) AS ContainerSubtype6, " +
                                                                                 "SUM(ContainerSubtype7) AS ContainerSubtype7, " +
                                                                                 "SUM(ContainerSubtype8) AS ContainerSubtype8,  " +
                                                                                 "SUM(ContainerSubtype9) AS ContainerSubtype9, " +
                                                                                 "SUM(ContainerSubtype10) AS ContainerSubtype10, " +
                                                                                 "SUM(ContainerSubtype11) AS ContainerSubtype11, " +
                                                                                 "SUM(ContainerType0) AS ContainerType0,  " +
                                                                                 "SUM(ContainerType1) AS ContainerType1,  " +
                                                                                 "SUM(ContainerType2) AS ContainerType2,  " +
                                                                                 "SUM(ContainerType3) AS ContainerType3,  " +
                                                                                 "SUM(ContainerType4) AS ContainerType4, " +
                                                                                 "SUM(DeviceOs0) AS DeviceOs0,  " +
                                                                                 "SUM(DeviceOs1) AS DeviceOs1,  " +
                                                                                 "SUM(DeviceOs2) AS DeviceOs2,  " +
                                                                                 "SUM(DeviceOs3) AS DeviceOs3,  " +
                                                                                 "SUM(DeviceOs4) AS DeviceOs4,  " +
                                                                                 "SUM(DeviceOs5) AS DeviceOs5,  " +
                                                                                 "SUM(DeviceOs6) AS DeviceOs6, " +
                                                                                 "SUM(DeviceType0) AS DeviceType0,  " +
                                                                                 "SUM(DeviceType1) AS DeviceType1,  " +
                                                                                 "SUM(DeviceType2) AS DeviceType2,  " +
                                                                                 "SUM(EndReason0) AS EndReason0,  " +
                                                                                 "SUM(EndReason1) AS EndReason1,  " +
                                                                                 "SUM(EndReason2) AS EndReason2,  " +
                                                                                 "SUM(Offline0) AS Offline0,  " +
                                                                                 "SUM(Offline1) AS Offline1,  " +
                                                                                 "SUM(Source0) AS Source0,   " +
                                                                                 "SUM(Source1) AS Source1,  " +
                                                                                 "SUM(Source2) AS Source2,  " +
                                                                                 "SUM(Source3) AS Source3,  " +
                                                                                 "SUM(Source4) AS Source4,  " +
                                                                                 "SUM(Streams) AS Streams  " +
                                                                                 "FROM tblAppleMusicSongStreams " +
                                                                                 "WHERE (@countryCode IS NULL OR CountryCode = @countryCode) " +
                                                                                 "AND isrc IN (@isrcs) " +
                                                                                 "AND (@licensorId IS NULL OR DistributerId = @licensorId) " +
                                                                                 "AND Date >= @startDate AND Date <= @endDate " +
                                                                                 "GROUP BY Date, CountryCode, ISRC ", new { countryCode = countryCode, isrcs = isrcs, licensorId = (int?)licensor, startDate = startDate, endDate = endDate });
        }

        public async Task<List<AppleMusicTrackContainerStream>> GetTrackContainerStreamAsync(string isrc, string containerId, DateTime startDate, DateTime endDate, string countryCode, int? distributerId = null)
        {
            
            return PetaPocoRepository.Instance.Fetch<AppleMusicTrackContainerStream>("SELECT DistributerId, Date, CountryCode, ReportVendorId, ISRC, ContainerId," +
                                                                                     "ContainerName, ContainerSubType, ReportDate, " +
                                                                            "SUM(ActionType1) AS actionType1, " +
                                                                            "SUM(ContainerType0) AS ContainerType0,  " +
                                                                            "SUM(ContainerType1) AS ContainerType1,  " +
                                                                            "SUM(ContainerType2) AS ContainerType2,  " +
                                                                            "SUM(ContainerType3) AS ContainerType3,  " +
                                                                            "SUM(ContainerType4) AS ContainerType4, " +
                                                                            "SUM(DeviceOs0) AS DeviceOs0,  " +
                                                                            "SUM(DeviceOs1) AS DeviceOs1,  " +
                                                                            "SUM(DeviceOs2) AS DeviceOs2,  " +
                                                                            "SUM(DeviceOs3) AS DeviceOs3,  " +
                                                                            "SUM(DeviceOs4) AS DeviceOs4,  " +
                                                                            "SUM(DeviceOs5) AS DeviceOs5,  " +
                                                                            "SUM(DeviceOs6) AS DeviceOs6, " +
                                                                            "SUM(DeviceType0) AS DeviceType0,  " +
                                                                            "SUM(DeviceType1) AS DeviceType1,  " +
                                                                            "SUM(DeviceType2) AS DeviceType2,  " +
                                                                            "SUM(EndReason0) AS EndReason0,  " +
                                                                            "SUM(EndReason1) AS EndReason1,  " +
                                                                            "SUM(EndReason2) AS EndReason2,  " +
                                                                            "SUM(Offline0) AS Offline0,  " +
                                                                            "SUM(Offline1) AS Offline1,  " +
                                                                            "SUM(Source0) AS Source0,   " +
                                                                            "SUM(Source1) AS Source1,  " +
                                                                            "SUM(Source2) AS Source2,  " +
                                                                            "SUM(Source3) AS Source3,  " +
                                                                            "SUM(Source4) AS Source4,  " +
                                                                            "SUM(Streams) AS Streams  " +
                                                                            "FROM tblAppleMusicSongContainerStreams " +
                                                                            "WHERE (@countryCode IS NULL OR CountryCode = @countryCode) " +
                                                                            "AND isrc = @isrc " +
                                                                            "AND ContainerId = @containerId " +
                                                                            "AND (@licensorId IS NULL OR DistributerId = @licensorId) " +
                                                                            "AND Date >= @startDate AND Date <= @endDate " +
                                                                            "GROUP BY Date, CountryCode, ISRC ", new { countryCode = countryCode, isrc = isrc, containerId = containerId, licensorId = (int?)distributerId, startDate = startDate, endDate = endDate });
        }

        //TODO: Move from TrackStreams-manager?
        public async Task<List<AppleMusicContainerStream>> GetContainerStreamsAsync(string containerId, DateTime startDate, DateTime endDate, string countryCode, int? distributerId = null)
        {
            return PetaPocoRepository.Instance.Fetch<AppleMusicContainerStream>("SELECT DistributerId, Date, CountryCode, ReportVendorId, ContainerId," +
                                                                                     "ContainerName, ContainerSubType, ReportDate, " +
                                                                            "SUM(ActionType1) AS actionType1, " +
                                                                            "SUM(ContainerType0) AS ContainerType0,  " +
                                                                            "SUM(ContainerType1) AS ContainerType1,  " +
                                                                            "SUM(ContainerType2) AS ContainerType2,  " +
                                                                            "SUM(ContainerType3) AS ContainerType3,  " +
                                                                            "SUM(ContainerType4) AS ContainerType4, " +
                                                                            "SUM(DeviceOs0) AS DeviceOs0,  " +
                                                                            "SUM(DeviceOs1) AS DeviceOs1,  " +
                                                                            "SUM(DeviceOs2) AS DeviceOs2,  " +
                                                                            "SUM(DeviceOs3) AS DeviceOs3,  " +
                                                                            "SUM(DeviceOs4) AS DeviceOs4,  " +
                                                                            "SUM(DeviceOs5) AS DeviceOs5,  " +
                                                                            "SUM(DeviceOs6) AS DeviceOs6, " +
                                                                            "SUM(DeviceType0) AS DeviceType0,  " +
                                                                            "SUM(DeviceType1) AS DeviceType1,  " +
                                                                            "SUM(DeviceType2) AS DeviceType2,  " +
                                                                            "SUM(EndReason0) AS EndReason0,  " +
                                                                            "SUM(EndReason1) AS EndReason1,  " +
                                                                            "SUM(EndReason2) AS EndReason2,  " +
                                                                            "SUM(Offline0) AS Offline0,  " +
                                                                            "SUM(Offline1) AS Offline1,  " +
                                                                            "SUM(Source0) AS Source0,   " +
                                                                            "SUM(Source1) AS Source1,  " +
                                                                            "SUM(Source2) AS Source2,  " +
                                                                            "SUM(Source3) AS Source3,  " +
                                                                            "SUM(Source4) AS Source4,  " +
                                                                            "SUM(Streams) AS Streams  " +
                                                                            "FROM tblAppleMusicSongContainerStreams " +
                                                                            "WHERE (@countryCode IS NULL OR CountryCode = @countryCode) " +
                                                                            "AND ContainerId = @containerId " +
                                                                            "AND (@licensorId IS NULL OR DistributerId = @licensorId) " +
                                                                            "AND Date >= @startDate AND Date <= @endDate " +
                                                                            "GROUP BY Date, CountryCode, ISRC ", new { countryCode = countryCode, containerId = containerId, licensorId = (int?)distributerId, startDate = startDate, endDate = endDate });
        }

        public List<AppleMusicAnalyticsAggregatedDate> GetDates(AppleS3FileType fileType, DateTime startDate, DateTime endDate)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicAnalyticsAggregatedDate>("WHERE Date > @startDate AND Date < @endDate AND FileType = @fileType", new { startDate, endDate, fileType = (int)fileType });
        }

        public DateTime GetLatestDates(AppleS3FileType fileType)
        {
            var date = PetaPocoRepository.ReadOnlyInstance.FirstOrDefaultWithSql<AppleMusicAnalyticsAggregatedDate>("WHERE FileType = @fileType ORDER BY Date DESC", new { fileType = (int)fileType });
            return date.Date;
        }
    }
}