using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;

namespace Sony.Filtr.Playlists.Spotify
{
    public class SpotifyTrackAddManager
    {
        public async Task<IEnumerable<TrackPlaylistAdd>> GetArtistTrackSummaryAsync(SpotifyLink spotifyLink, string region)
        {
            var viewModels = new List<TrackPlaylistAdd>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $@"
SELECT pl.ISRC as TrackIsrc, COUNT(*) as Playlists, SUM(psl.Followers) as TotalPlaylistSubscribers
FROM (
    SELECT t.isrc, tl.playlistId
    FROM tblSpotifyPlaylistTrackList2 AS tl
    INNER JOIN tblSpotifyTrack2 AS t ON tl.TrackId = t.TrackId
    INNER JOIN tblSpotifyPlaylist AS p ON p.playlistId = tl.playlistId
    INNER JOIN tblSpotifyTrackArtist AS ta ON ta.TrackId = t.TrackId
    LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.playlistUri AND ip.musicServiceId = {(int)MusicService.Spotify}
    WHERE ta.ArtistId = '{spotifyLink.ExtractArtistID()}' AND p.removed = 0 AND ip.playlistId IS NULL
    GROUP BY t.isrc, tl.playlistId ) AS pl
INNER JOIN tblSpotifyPlaylistFollowers AS psl ON psl.PlaylistId = pl.playlistId
WHERE pl.ISRC IS NOT NULL
GROUP BY pl.ISRC
ORDER BY COUNT(*) DESC";
                
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            TrackPlaylistAdd item = ToTrackPlaylistAdd(reader);
                            viewModels.Add(item);
                        }
                    }
                }
            }

            return viewModels;
        }

        public async Task<IEnumerable<TrackPlaylistAdd>> GetAlbumTrackSummaryAsync(SpotifyLink spotifyLink)
        {
            List<TrackPlaylistAdd> viewModels = new List<TrackPlaylistAdd>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                string sql = $@"
SELECT pl.TrackId as TrackIsrc, COUNT(*) as Playlists, SUM(psl.Followers) as TotalPlaylistSubscribers
FROM (
    SELECT t.trackId, tl.PlaylistId
    FROM tblSpotifyPlaylistTrackList2 AS tl
    INNER JOIN tblSpotifyTrack2 AS t ON tl.TrackId = t.TrackId
    INNER JOIN tblSpotifyPlaylist AS p ON p.playlistId = tl.playlistId 
    INNER JOIN tblSpotifyTrackAlbum AS ta ON ta.TrackId = t.TrackId 
    LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.playlistUri AND ip.musicServiceId = {(int)MusicService.Spotify}
    WHERE ta.AlbumId = '{spotifyLink.ExtractAlbumID()}' AND p.removed = 0 AND ip.playlistId IS NULL 
    GROUP BY t.isrc, tl.playlistId) AS pl 
INNER JOIN tblSpotifyPlaylistFollowers AS psl ON psl.PlaylistId = pl.playlistId
GROUP BY pl.TrackId
ORDER BY COUNT(*) DESC";

                var cmd = new MySqlCommand(sql, conn);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        TrackPlaylistAdd item = ToTrackPlaylistAdd(reader);
                        viewModels.Add(item);
                    }
                }
            }
            return viewModels;
        }

        private static TrackPlaylistAdd ToTrackPlaylistAdd(IDataReader reader)
        {
            return new TrackPlaylistAdd
            {
                TrackIsrc = reader.GetString(0),
                Playlists = reader.GetInt32(1),
                TotalPlaylistSubscribers = !reader.IsDBNull(2) ? reader.GetInt64(2) : (long?)null
            };
        }
    }

    public class TrackPlaylistAdd
    {
        public string TrackIsrc { get; set; }
        public string TrackId { get; set; }

        public int Playlists { get; set; }

        public long? TotalPlaylistSubscribers { get; set; }
    }



}