using MoreLinq;
using MySqlConnector;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Contracts.Entities.Genre;
using Sony.Filtr.Database;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Sony.Filtr.Core.EditorialPlaylists
{
    public class EditorialPlaylistFactory : IEditorialPlaylistFactory
    {
        public EditorialPlaylist AddEditorialPlaylist(EditorialPlaylist pl)
        {
            var playlistId = PetaPocoRepository.Instance.Insert(pl);
            pl.ID = playlistId;

            return pl;
        }

        public void UpdateEditorialPlaylist(EditorialPlaylist pl)
        {
            PetaPocoRepository.Instance.Update(pl);
        }

        public async Task DeleteEditorialPlaylistAsync(EditorialPlaylist playlist)
        {
            //We don't remove EditorialPlaylists anymore because we risk having IDs be reused so we mark them as removed and filter when fetching
            //PetaPocoRepository.Instance.Delete<EditorialPlaylist>(playlist);

            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                MySqlCommand delCmd = new MySqlCommand("UPDATE tblEditorialPlaylist SET blnRemoved=1 WHERE intID=@intPlaylistID", conn);
                delCmd.Parameters.Add(new MySqlParameter("@intPlaylistID", playlist.ID));
                await delCmd.ExecuteNonQueryAsync();
            }
        }

        public async Task SetEditorialPlaylistGenresAsync(EditorialPlaylist playlist, List<GenreMatch> genreMatches)
        {
            using (MySqlConnection conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using(var tran = await conn.BeginTransactionAsync())
                {
                    MySqlCommand delCmd = new MySqlCommand("DELETE FROM tblEditorialPlaylistGenre WHERE intPlaylistID=@intPlaylistID", conn);
                    delCmd.Parameters.Add(new MySqlParameter("@intPlaylistID", playlist.ID));
                    delCmd.Transaction = tran;
                    await delCmd.ExecuteNonQueryAsync();

                    if (genreMatches.Any())
                    {
                        var sql = "INSERT INTO tblEditorialPlaylistGenre VALUES ";
                        var paramValues = genreMatches.Select(g => "(" + playlist.ID + ",'" + MySqlHelper.EscapeString(g.Genre) + "'," + g.Score + ")");
                        sql += string.Join(",", paramValues);

                        MySqlCommand cmd = new MySqlCommand(sql, conn);
                        cmd.Transaction = tran;
                        await cmd.ExecuteNonQueryAsync();
                    }

                    tran.Commit();
                }
            }
        }
        
        public async Task<List<EditorialPlaylist>> GetEditorialPlaylistsAsync(Application application, bool onlyActive, bool lightWeight = false)
        {
            var sql = PetaPoco.Sql.Builder.Append("SELECT p.* FROM tblEditorialPlaylist AS p " +
                                                  " LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON p.strSpotifyUri=ip.playlistId AND ip.MusicServiceId=1 " +
                                                  " WHERE blnRemoved=0 AND ip.playlistId IS NULL" +
                                                  " AND blnSonyCreated = 1 ");
            if (application != null)
                sql.Append(" AND intApplicationInstanceID=@0 ", application.ID);
            if (onlyActive)
                sql.Append(" AND blnActive=1 ");

            var editorialPlaylists = PetaPocoRepository.ReadOnlyInstance.Fetch<EditorialPlaylist>(sql);

            if (!lightWeight && editorialPlaylists.Any())
            {
                editorialPlaylists = await SetGenreInfoAsync(editorialPlaylists);
                editorialPlaylists = await SetArtistInfoAsync(editorialPlaylists);
                editorialPlaylists = await SetTrackInfoAsync(editorialPlaylists);
            }

            return editorialPlaylists;
        }

        public async Task<Dictionary<string, string>> GetUniqueSonySpotifyPlaylistsAsync()
        {
            var sonyPlaylists = new Dictionary<string, string>();
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                MySqlCommand cmd = new MySqlCommand("SELECT DISTINCT p.strSpotifyUri, p.strCountryCode FROM tblEditorialPlaylist AS p " +
                                                    "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON p.strSpotifyUri = ip.playlistId AND ip.musicserviceId=@spotifyMusicServiceId " +
                                                    "WHERE blnRemoved=0 " +
                                                    "AND blnSonyCreated=1 " +
                                                    "AND strCountryCode IS NOT NULL " +
                                                    "AND ip.playlistId IS NULL", conn);

                cmd.Parameters.AddWithValue("spotifyMusicServiceId", MusicService.Spotify);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        sonyPlaylists.Add(reader.GetString(0), reader.GetString(1));
                    }
                }
            }
            return sonyPlaylists;
        }

        private async Task<List<EditorialPlaylist>> SetArtistInfoAsync(List<EditorialPlaylist> editorialPlaylists)
        {
            var spotifyPlaylistIds = editorialPlaylists.Select(p => p.SpotifyLink.ExtractPlaylistID()).Distinct().ToList();
            var playlistArtistNames = await GetArtistsAsync(spotifyPlaylistIds);

            foreach (var playlist in editorialPlaylists)
            {
                var playlistId = playlist.SpotifyLink.ExtractPlaylistID();
                if (!playlistArtistNames.ContainsKey(playlistId))
                    continue;

                playlist.Artists = playlistArtistNames[playlistId];
            }

            return editorialPlaylists;
        }

        public async Task<Dictionary<string, List<SimilarArtistMatch>>> GetArtistsAsync(List<string> spotifyPlaylistIds)
        {
            Dictionary<string, List<SimilarArtistMatch>> playlistArtistNames = new Dictionary<string, List<SimilarArtistMatch>>();

            foreach (var playlistIdBatch in spotifyPlaylistIds.Batch(10))
            {
                using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
                {
                    var sql = "SELECT DISTINCT tl.playlistId, a.name, MIN(ta.artistOrder) " + 
                              "FROM tblSpotifyPlaylist AS p " + 
                              "INNER JOIN tblSpotifyPlaylistTrackList2 AS tl ON tl.PlaylistId = p.playlistId " + 
                              "INNER JOIN tblSpotifyTrack2 AS t ON tl.trackId = t.trackId " + 
                              "INNER JOIN tblSpotifyTrackArtist AS ta ON ta.trackId = tl.trackId " + 
                              "INNER JOIN tblSpotifyArtist AS a ON a.artistId = ta.artistId " + 
                              $"WHERE p.playlistId IN ({string.Join(",", playlistIdBatch.Select(p => "'" + MySqlHelper.EscapeString(p) + "'"))}) " +
                              "GROUP BY tl.playlistId, ta.ArtistId " +
                              "ORDER BY tl.PlaylistId, COUNT(a.name) DESC, MIN(tl.playlistIndex), MIN(ta.artistOrder)";

                    MySqlCommand cmd = new MySqlCommand(sql, conn);

                    using(var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var playlistId = reader.GetString(0);
                            var artistName = reader.GetString(1);
                            var order = reader.GetInt32(2);

                            order = order + 1;

                            var similarArtistMatch = new SimilarArtistMatch(new Artist(artistName), order);

                            if (playlistArtistNames.ContainsKey(playlistId))
                                playlistArtistNames[playlistId].Add(similarArtistMatch);
                            else
                                playlistArtistNames.Add(playlistId, new List<SimilarArtistMatch>() { similarArtistMatch });
                        }
                    }
                }
            }
            
            return playlistArtistNames;
        }

        private async Task<List<EditorialPlaylist>> SetGenreInfoAsync(List<EditorialPlaylist> editorialPlaylists)
        {
            var playlistGenres = await GetPlaylistGenresAsync(editorialPlaylists);

            foreach (var playlist in editorialPlaylists)
            {
                if (playlistGenres.ContainsKey(playlist.ID))
                    playlist.Genres = playlistGenres[playlist.ID];
            }

            return editorialPlaylists;
        }

        public async Task<Dictionary<int, List<GenreMatch>>> GetPlaylistGenresAsync(List<EditorialPlaylist> editorialPlaylists)
        {
            var playlistGenres = new Dictionary<int, List<GenreMatch>>();
            foreach (var playlistBatch in editorialPlaylists.Batch(10000))
            {
                using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
                {
                    var playlistIds = string.Join(",", playlistBatch.Select(p => p.ID));
                    string sql = $"SELECT intPlaylistID, strGenreName, intScore FROM tblEditorialPlaylistGenre WHERE intPlaylistID IN ({playlistIds})";
                    using(MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        using(var reader = await cmd.ExecuteReaderAsync())
                        {
                            while(await reader.ReadAsync())
                            {
                                int playlistID = reader.GetInt32(0);
                                var genreName = reader.GetString(1);
                                var score = reader.GetInt32(2);

                                if (playlistGenres.ContainsKey(playlistID))
                                    playlistGenres[playlistID].Add(new GenreMatch(genreName, score));
                                else
                                    playlistGenres.Add(playlistID, new List<GenreMatch>() { new GenreMatch(genreName, score) });
                            }
                        }
                    }
                }    
            }
            
            return playlistGenres;
        }

        private async Task<List<EditorialPlaylist>> SetTrackInfoAsync(List<EditorialPlaylist> editorialPlaylists)
        {
            var playlistIds = editorialPlaylists.Select(p => p.SpotifyLink.ExtractPlaylistID()).Distinct().ToList();

            var playlistTracks = await GetTracksAsync(playlistIds);
            foreach (var playlist in editorialPlaylists)
            {
                var playlistId = playlist.SpotifyLink.ExtractPlaylistID();
                if (playlistTracks.ContainsKey(playlistId))
                    playlist.Tracks = playlistTracks[playlistId];
            }

            return editorialPlaylists;
        }

        private async Task<Dictionary<string, List<Track>>> GetTracksAsync(List<string> playlistIds)
        {
            var playlistTracks = new Dictionary<string, List<Track>>();
            using (MySqlConnection conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                foreach (var playlistIdBatch in playlistIds.Batch(100))
                {
                    string sql = "SELECT tl.playlistId, t.trackId,  a.name, t.name, t.isrc, t.duration " +
                                 "FROM tblSpotifyPlaylistTrackList2 AS tl " +
                                 "INNER JOIN tblSpotifyTrack2 AS t ON tl.trackId = t.trackId " +
                                 "INNER JOIN tblSpotifyTrackArtist AS ta ON ta.trackId = tl.trackId AND ta.ArtistOrder=0 " + //We keep same behaviour and only include first artist.
                                 "INNER JOIN tblSpotifyArtist AS a ON a.artistId = ta.artistId " +
                                 $"WHERE tl.PlaylistId IN ({string.Join(",", playlistIdBatch.Select(p => "'" + MySqlHelper.EscapeString(p) + "'"))}) " +
                                 "GROUP BY tl.playlistId, tl.trackId, tl.playlistIndex " +
                                 "ORDER BY tl.playlistId, tl.playlistIndex, ta.artistOrder DESC";

                    using(MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                string playlistId = reader.GetString(0);
                                string trackId = reader.GetString(1);
                                string artist = reader.GetString(2);
                                string name = reader.GetString(3);

                                string isrc = null;
                                if (!reader.IsDBNull(4))
                                    isrc = reader.GetString(4);

                                int duration = 0;
                                if (!reader.IsDBNull(5))
                                {
                                    duration = reader.GetInt32(5);
                                }

                                var track = new Track()
                                {
                                    ArtistName = artist,
                                    Name = name,
                                    SpotifyLink = SpotifyLink.FromTrackId(trackId),
                                    ISRC = isrc,
                                    Duration = duration,
                                };

                                if (playlistTracks.ContainsKey(playlistId))
                                    playlistTracks[playlistId].Add(track);
                                else
                                    playlistTracks.Add(playlistId, new List<Track>() { track });
                            }
                        }
                    }
                }
            }
            
            return playlistTracks;
        }


        public async Task<EditorialPlaylist> GetEditorialPlaylistAsync(int editorialPlayListId)
        {
            var playlist = PetaPocoRepository.ReadOnlyInstance.SingleOrDefault<EditorialPlaylist>(editorialPlayListId);
            if (playlist != null)
            {
                var tempPlaylists = await SetGenreInfoAsync(new List<EditorialPlaylist>() { playlist});
                tempPlaylists = await SetArtistInfoAsync(tempPlaylists);
                tempPlaylists = await SetTrackInfoAsync(tempPlaylists);
                return tempPlaylists.First();
            }
            return null;
        }

        public async Task<List<EditorialPlaylist>> GetEditorialPlaylistsAsync(string spotifyUsername, int applicationId)
        {
            List<EditorialPlaylist> editorialPlaylists = new List<EditorialPlaylist>();
            if (spotifyUsername == null)
                return editorialPlaylists;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();

                const string sql = "SELECT p.intID, p.strName, p.strSpotifyUri, p.intPriority, f.followers, p.datLastUpdated, p.strImageFileName, p.blnActive, p.intApplicationInstanceID, p.blnImageOverride, p.strCountryCode, p.blnSonyCreated, f.Followers1dayAgo, p.strSpotifyImageFileName, p.strDisplayName, p.intBuzzCategoryId, p.strOwnerUsername " +
                                   "FROM tblEditorialPlaylist as p " +
                                   "INNER JOIN tblSpotifyPlaylist AS sp ON sp.playlistUri = p.strSpotifyUri " +
                                   "LEFT JOIN tblSpotifyPlaylistFollowers AS f ON f.playlistId = sp.playlistId " +
                                   "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON p.strSpotifyUri = ip.playlistId AND ip.musicServiceId = @spotifyMusicServiceId " +
                                   "WHERE blnRemoved=0 " +
                                   "AND blnSonyCreated=0 " +
                                   "AND intApplicationInstanceID=@intApplicationInstanceID " +
                                   "AND sp.user = @username " +
                                   "AND ip.playlistId IS NULL " +
                                   "GROUP BY strSpotifyUri";

                cmd.CommandText = sql;

                cmd.Parameters.AddWithValue("@username", spotifyUsername);
                cmd.Parameters.AddWithValue("@intApplicationInstanceID", applicationId);
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using(var sqlReader = await cmd.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        editorialPlaylists.Add(new EditorialPlaylist(sqlReader));
                    }
                }
            }

            return editorialPlaylists;
        }
        
        public void UpdatePlaylistImportFields(EditorialPlaylist playlist)
        {
            PetaPocoRepository.Instance.Update(playlist, new[] { "strName", "strSpotifyDescription", "strSpotifyImageFileName", "strCountryCode", "strDisplayName", "intBuzzCategoryId" });
        }
        
        public async Task<List<EditorialPlaylist>> GetEditorialPlaylistsAsync(List<string> playlistUris)
        {
            var playlists = new List<EditorialPlaylist>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                var sql = "SELECT p.intID, p.strName, p.strSpotifyUri, p.intPriority, p.intSubscribers, p.datLastUpdated, p.strImageFileName, p.blnActive, p.intApplicationInstanceID, p.blnImageOverride, p.strCountryCode, p.blnSonyCreated, p.intPrevSubscribers, p.strSpotifyImageFileName, p.strDisplayName, p.intBuzzCategoryId, p.strOwnerUsername " +
                          "FROM tblEditorialPlaylist AS p " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = p.strSpotifyUri AND ip.musicServiceId = @spotifyMusicServiceId " +
                          "WHERE blnRemoved = 0 AND strSpotifyUri IN ({0}) AND ip.playlistID IS NULL " +
                          "GROUP BY strSpotifyUri";
                sql = string.Format(sql, string.Join(",", playlistUris.Select(p => "\"" + MySqlHelper.EscapeString(p) + "\"")));
                
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using(var sqlReader = await cmd.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        playlists.Add(new EditorialPlaylist(sqlReader));
                    }
                }
            }
            return playlists;
        }
        public async Task<List<EditorialPlaylist>> GetEditorialPlaylistsByIdAsync(List<string> playlistIds)
        {
            var playlists = new List<EditorialPlaylist>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                var sql = "SELECT ep.intID, ep.strName, ep.strSpotifyUri, ep.intPriority, ep.intSubscribers, ep.datLastUpdated, ep.strImageFileName, ep.blnActive, ep.intApplicationInstanceID, ep.blnImageOverride, ep.strCountryCode, ep.blnSonyCreated, ep.intPrevSubscribers, ep.strSpotifyImageFileName, ep.strDisplayName, ep.intBuzzCategoryId, ep.strOwnerUsername " +
                          "FROM tblEditorialPlaylist AS ep " +
                          "INNER JOIN tblSpotifyPlaylist AS p ON ep.strSpotifyUri = p.playlistUri " +
                          "LEFT JOIN tblPlaylistIgnoredPlaylist AS ip ON ip.playlistId = ep.strSpotifyUri AND ip.musicServiceId = @spotifyMusicServiceId " +
                          "WHERE ep.blnRemoved = 0 AND p.playlistId IN ({0}) AND ip.playlistID IS NULL "
                    ;
                sql = string.Format(sql, string.Join(",", playlistIds.Select(p => "\"" + MySqlHelper.EscapeString(p) + "\"")));
                
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@spotifyMusicServiceId", MusicService.Spotify);

                using(var sqlReader = await cmd.ExecuteReaderAsync())
                {
                    while (await sqlReader.ReadAsync())
                    {
                        playlists.Add(new EditorialPlaylist(sqlReader));
                    }
                }
            }
            return playlists;
        }
    }    
}