using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MoreLinq;
using MySql.Data.MySqlClient;
using NLog;
using Sony.Filtr.Database;
using Sony.Filtr.Playlists.Spotify;
using Sony.Filtr.SpotifyWebAPI;
using Sony.Filtr.SpotifyWebAPI.Model;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Worker.Onetime
{
    public class UpdateTrackArtistList
    {
        private readonly SpotifyWebApi _spotifyWebApi;
        private readonly SpotifyPlaylistManager _spotifyPlaylistManager;
        private Logger _logger;

        public UpdateTrackArtistList(SpotifyWebApi spotifyWebApi, SpotifyPlaylistManager spotifyPlaylistManager)
        {
            _spotifyWebApi = spotifyWebApi;
            _spotifyPlaylistManager = spotifyPlaylistManager;

            _logger = NLog.LogManager.GetLogger("UpdateTrackArtistList");
        }

        public async Task ExecuteAsync()
        {
            _logger.Info("Reading tracks from database.");

            var tracks = GetAllTracksToProcess().ToList();

            var batches = tracks.Batch(50).ItemIndex().ToList();
            var batchCount = batches.Count;
            await batches.ForEachAsync(5, async trackBatch =>
            {
                _logger.Info($"{trackBatch.Index} / {batchCount}: Fetching tracks from Spotify...");

                try
                {
                    var spotifyTracks = await _spotifyWebApi.GetAllTracksAsync(trackBatch.Item.ToList());

                    var artists = spotifyTracks.SelectMany(t => t.artists).DistinctBy(a => a.id).ToList();

                    _logger.Info($"{trackBatch.Index} / {batchCount}: Storing data ...");
                    await _spotifyPlaylistManager.AddSpotifyArtistsAsync(artists);

                    await _spotifyPlaylistManager.AddOrUpdateSpotifyTracksAsync(spotifyTracks);
                    await _spotifyPlaylistManager.AddOrUpdateSpotifyTrackArtistConnectionsAsync(spotifyTracks);

                    await SetTracksAsFixedAsync(spotifyTracks);
                    _logger.Info($"{trackBatch.Index} / {batchCount}: Done storing data ...");
                }
                catch(Exception ex)
                {
                    _logger.Error(ex);
                }
            });
        }

        private async Task SetTracksAsFixedAsync(List<Track> spotifyTracks)
        {
            using (var conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                await conn.OpenAsync();

                var sqlString = "INSERT INTO TempTrackArtistFixed VALUES ";

                List<string> paramValues = new List<string>();
                paramValues.AddRange(spotifyTracks.Select(track => $"('{track.id}')"));

                sqlString += string.Join(",", paramValues);

                var command = new MySqlCommand(sqlString, conn);

                await command.ExecuteReaderAsync();

                await conn.CloseAsync();
            }
            
        }

        private IEnumerable<string> GetAllTracksToProcess()
        {
            using (var conn = new MySqlConnection(DatabaseHandler.GetConnectionString()))
            {
                conn.Open();

                var sqlString = "SELECT t.trackId " +
                                "FROM tblSpotifyTrack2 AS t " +
                                "LEFT JOIN TempTrackArtistFixed AS tf ON t.TrackId=tf.TrackId " +
                                "WHERE tf.trackId IS NULL ";

                var command = new MySqlCommand(sqlString, conn);

                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    yield return reader.GetString(0);
                }

                conn.Close();
            }
        }
    }
}