using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using MySqlConnector;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.Playlists.Spotify.Model;
using Sony.Filtr.Utility;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Playlists.Spotify
{
    public class SpotifyTrackManager
    {
        public async Task<IEnumerable<LocalSpotifyTrack>> GetFullLocalTracksAsync(IEnumerable<string> trackIds)
        {
            trackIds = trackIds.FilterNull().ToArray();
            if (!trackIds.Any())
                return Enumerable.Empty<LocalSpotifyTrack>();

            Dictionary<string, LocalSpotifyTrack> tracks = new Dictionary<string, LocalSpotifyTrack>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT t.TrackId, t.ISRC, t.Name, t.Danceability, t.Energy, t.`Key`, t.Loudness, t.Mode, t.Speechiness, t.Acousticness, t.Instrumentalness, t.Liveness, t.Valence, t.Tempo, t.Popularity, " +
                          "a.Name, a.ArtistId, " +
                          "alb.AlbumId, alb.Name, alb.Popularity, alb.Copyright, alb.Label, alb.Upc, alb.ReleaseDate, alb.ReleaseDatePrecision, alb.SmallImageFilename, " +
                          "talb.TrackPosition " +
                          "FROM tblSpotifyTrack2 AS t " +
                          "INNER JOIN tblSpotifyTrackArtist ta ON t.TrackId = ta.TrackId " +
                          "INNER JOIN tblSpotifyArtist a ON ta.ArtistId = a.ArtistId " +
                          "INNER JOIN tblSpotifyTrackAlbum talb ON t.TrackId = talb.TrackId " +
                          "INNER JOIN tblSpotifyAlbum alb ON talb.AlbumId = alb.AlbumId " +
                          $"WHERE t.TrackId IN ({Maybe.ToCommaSeparated(trackIds)}) ";

                var cmd = new MySqlCommand(sql, conn);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        BuildAndAdd(reader, tracks);
                    }
                }
            }

            return tracks.Values.ToArray();
        }

        public async Task<IEnumerable<LocalSpotifyTrack>> GetFullLocalTracksByIsrcAsync(IEnumerable<string> isrcs)
        {
            isrcs = isrcs.FilterNull().ToArray();
            if (!isrcs.Any())
                return Enumerable.Empty<LocalSpotifyTrack>();

            Dictionary<string, LocalSpotifyTrack> tracks = new Dictionary<string, LocalSpotifyTrack>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT t.TrackId, t.ISRC, t.Name, t.Danceability, t.Energy, t.`Key`, t.Loudness, t.Mode, t.Speechiness, t.Acousticness, t.Instrumentalness, t.Liveness, t.Valence, t.Tempo, t.Popularity, " +
                          "a.Name, a.ArtistId, " +
                          "alb.AlbumId, alb.Name, alb.Popularity, alb.Copyright, alb.Label, alb.Upc, alb.ReleaseDate, alb.ReleaseDatePrecision, alb.SmallImageFilename, " +
                          "talb.TrackPosition " +
                          "FROM " +
                          "(SELECT t.TrackId, t.ISRC, t.Name, t.Danceability, t.Energy, t.`Key`, t.Loudness, t.Mode, t.Speechiness, t.Acousticness, t.Instrumentalness, t.Liveness, t.Valence, t.Tempo, t.Popularity " +
                          " FROM tblSpotifyTrack2 AS t" +
                          $" WHERE t.ISRC IN ({Maybe.ToCommaSeparated(isrcs)}) " +
                          " GROUP BY t.ISRC ) AS t " +
                          "INNER JOIN tblSpotifyTrackArtist ta ON t.TrackId = ta.TrackId " +
                          "INNER JOIN tblSpotifyArtist a ON ta.ArtistId = a.ArtistId " +
                          "INNER JOIN tblSpotifyTrackAlbum talb ON t.TrackId = talb.TrackId " +
                          "INNER JOIN tblSpotifyAlbum alb ON talb.AlbumId = alb.AlbumId ";

                var cmd = new MySqlCommand(sql, conn);

                using(var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        BuildAndAdd(reader, tracks);
                    }
                }
            }

            return tracks.Values.ToArray();
        }
        public bool? IsSonyByUrlAndUpc(
            IEnumerable<SpotifyWebAPI.Model.Track> spotifyTracks, 
            IEnumerable<SpotifyWebAPI.Model.FullAlbumItem> albums, 
            IEnumerable<string> upcs, string url)
        {
            if (!string.IsNullOrEmpty(url))
            {
                var sonyTrackByUrl = spotifyTracks.Where(x => x.id == new SpotifyLink(url).ExtractTrackID()).FirstOrDefault();
                if (sonyTrackByUrl != null)
                {
                    var trackAlbum = albums.Where(x => x.id == sonyTrackByUrl.album.id).FirstOrDefault();
                    if (trackAlbum != null)
                    {
                        var isSonyTrack = upcs.Contains(trackAlbum.external_ids.upc);
                        return isSonyTrack;
                    }
                }
            }
            
            return null;
        }

        private static void BuildAndAdd(DbDataReader reader, Dictionary<string, LocalSpotifyTrack> tracks)
        {
            var trackId = reader.GetString(0);

            LocalSpotifyTrack item;
            if (tracks.TryGetValue(trackId, out item))
            {
                var artist = new LocalSpotifyTrackArtist();
                artist.Name = reader.IsDBNull(15) ? null : reader.GetString(15);
                artist.Uri = reader.IsDBNull(16) ? null : SpotifyLink.FromArtistId(reader.GetString(16)).Uri;

                item.Artists.Add(artist);
            }
            else
            {
                item = new LocalSpotifyTrack();
                item.TrackId = trackId;
                item.ISRC = reader.IsDBNull(1) ? null : reader.GetString(1);
                item.TrackName = reader.IsDBNull(2) ? null : reader.GetString(2);

                item.Danceability = reader.IsDBNull(3) ? 0 : reader.GetFloat(3);
                item.Energy = reader.IsDBNull(4) ? 0 : reader.GetFloat(4);
                item.AudioKey = reader.IsDBNull(5) ? 0 : reader.GetInt32(5);
                item.Loudness = reader.IsDBNull(6) ? 0 : reader.GetFloat(6);
                item.AudioMode = reader.IsDBNull(7) ? 0 : reader.GetInt32(7);
                item.Speechiness = reader.IsDBNull(8) ? 0 : reader.GetFloat(8);
                item.Acousticness = reader.IsDBNull(9) ? 0 : reader.GetFloat(9);
                item.Instrumentalness = reader.IsDBNull(10) ? 0 : reader.GetFloat(10);
                item.Liveness = reader.IsDBNull(11) ? 0 : reader.GetFloat(11);
                item.Valence = reader.IsDBNull(12) ? 0 : reader.GetFloat(12);
                item.Tempo = reader.IsDBNull(13) ? 0 : reader.GetFloat(13);
                item.Popularity = reader.IsDBNull(14) ? 0 : reader.GetInt32(14);

                var artist = new LocalSpotifyTrackArtist();
                artist.Name = reader.IsDBNull(15) ? null : reader.GetString(15);
                artist.Uri = reader.IsDBNull(16) ? null : SpotifyLink.FromArtistId(reader.GetString(16)).Uri;
                item.Artists.Add(artist);

                var album = new LocalSpotifyAlbum();
                album.AlbumId = reader.IsDBNull(17) ? null : reader.GetString(17);
                album.Name = reader.IsDBNull(18) ? null : reader.GetString(18);
                album.Popularity = reader.IsDBNull(19) ? (int?)null : reader.GetInt32(19);
                album.Copyright = reader.IsDBNull(20) ? null : reader.GetString(20);
                album.Label = reader.IsDBNull(21) ? null : reader.GetString(21);
                album.Upc = reader.IsDBNull(22) ? null : reader.GetString(22);
                album.ReleaseDate = reader.IsDBNull(23) ? null : reader.GetString(23);
                album.ReleaseDatePrecision = reader.IsDBNull(24) ? null : reader.GetString(24);
                album.SmallImageFilename = reader.IsDBNull(25) ? null : reader.GetString(25);

                item.AlbumPosition = reader.GetInt32(26);

                item.Album = album;

                tracks.Add(trackId, item);
            }
        }
    }
}