using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using System.Transactions;
using MySql.Data.MySqlClient;
using Sony.Filtr.Database;
using Sony.Filtr.Playlists.Models;
using Sony.Filtr.SpotifyWebAPI.Model;
using Sony.Filtr.Utility;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.Playlists.Spotify
{
    public class SpotifyPlaylistFactory
    {
        private readonly IFormatProvider _defaultCulture = CultureInfo.GetCultureInfo("sv-SE");

        private string SafeGetValue(DateTime? nullable)
        {
            if (nullable.HasValue)
                return "'" + nullable.Value.ToString("O", _defaultCulture) + "'";
            return "null";
        }

        private string SafeGetValue(double? nullable)
        {
            var nfi = new NumberFormatInfo();
            nfi.NumberDecimalSeparator = ".";

            if (nullable.HasValue)
                return nullable.Value.ToString(nfi);

            return "null";
        }

        public async Task<List<UpdatePlaylistTrack>> GetTrackListAsync(string playlistId)
        {
            var tracklistItems = new List<UpdatePlaylistTrack>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = @"
SELECT 
	tl.PlaylistIndex, 
    tl.TrackId, 
    tl.Added, 
    tl.EarliestAdded,
    track.Duration,
    track.ISRC,
    track.Name,
    track.Popularity AS Popularity
FROM tblSpotifyPlaylistTrackList2 tl
INNER JOIN tblSpotifyTrack2 track ON track.TrackId = tl.TrackId
WHERE tl.PlaylistId = @playlistId
ORDER BY PlaylistIndex;";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@playlistId", playlistId);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var item = new UpdatePlaylistTrack
                        {
                            Track = new GeneralSpotifyTrack()
                            {
                                Id = reader.GetString("trackId"),
                                Popularity = reader.GetInt32("Popularity"),
                                Isrc = reader.GetString("ISRC"),
                                Name = reader.GetString("Name"),
                                Duration = reader.GetInt32("Duration")
                            },
                            PlaylistIndex = reader.GetInt32("playlistIndex"),
                            Added = reader.GetDateTimeOrDefault("added"),
                            EarliestAdded = reader.GetDateTimeOrDefault("earliestAdded")
                        };

                        tracklistItems.Add(item);
                    }
                }
            }

            return tracklistItems;
        }

        public async Task SetPlaylistUpdateStatistsic(string playlistId, string market)
        {
            using (var connection = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var insertCommand = connection.CreateCommand();

                insertCommand.CommandText = "INSERT INTO tblSpotifyPlaylistStatistics (PlaylistId, Market, TracklistLastUpdate) VALUES (@playlistId, @market, NOW());";
                insertCommand.Parameters.AddWithValue("@playlistId", playlistId);
                insertCommand.Parameters.AddWithValue("@market", market);

                await insertCommand.ExecuteNonQueryAsync();
            }
        }

        public async Task SetSpotifyPlaylistCurrentTrackList(string playlistId, IEnumerable<UpdatePlaylistTrack> tracks)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var transaction = await conn.BeginTransactionAsync())
                {
                    var delCmd = new MySqlCommand("DELETE FROM tblSpotifyPlaylistTrackList2 WHERE playlistId=@playlistId", conn, transaction);
                    delCmd.Parameters.Add(new MySqlParameter("@playlistId", playlistId));
                    await delCmd.ExecuteNonQueryAsync();

                    if (tracks != null && tracks.Any())
                    {
                        var sql = "INSERT INTO tblSpotifyPlaylistTrackList2 (PlaylistId, TrackId, PlaylistIndex, EarliestAdded, Added) VALUES ";

                        List<string> paramValues = new List<string>();
                        foreach (var track in tracks)
                        {
                            var paramValue = "(" + string.Join(",", "'" + MySqlHelper.EscapeString(playlistId) + "'", "'" + MySqlHelper.EscapeString(track.Track.Id) + "'", track.PlaylistIndex,
                                "" + SafeGetValue(track.EarliestAdded) + "", "" + SafeGetValue(track.Added) + "") + ")";
                            paramValues.Add(paramValue);
                        }
                        sql += string.Join(",", paramValues);

                        var cmd = new MySqlCommand(sql, conn, transaction);
                        await cmd.ExecuteNonQueryAsync();
                    }

                    transaction.Commit();
                }
            }
        }

        public async Task SaveAudioFeaturesForTracksAsync(IEnumerable<TrackAudioFeature> audioFeatures)
        {
            audioFeatures = audioFeatures.Where(a => a != null).ToArray();

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "INSERT IGNORE INTO tblSpotifyTrack2 (TrackId, Danceability, Energy, `Key`, Loudness, Mode, Speechiness, Acousticness, Instrumentalness, Liveness, Valence, Tempo) VALUES {0} " +
                          "ON DUPLICATE KEY UPDATE Danceability = VALUES(Danceability), Energy = VALUES(Energy), `Key` = VALUES(`Key`), Loudness = VALUES(Loudness), Mode = VALUES(Mode), " +
                          "Speechiness = VALUES(Speechiness), Acousticness = VALUES(Acousticness), Instrumentalness = VALUES(Instrumentalness), Liveness = VALUES(Liveness), Valence = VALUES(Valence), " +
                          "Tempo = VALUES(Tempo)";

                var paramValues = audioFeatures.Select(a => "(" + string.Join(",", "'" + MySqlHelper.EscapeString(a.id) + "'", SafeGetValue(a.danceability), SafeGetValue(a.energy), SafeGetValue(a.key),
                                                                SafeGetValue(a.loudness), SafeGetValue(a.mode), SafeGetValue(a.speechiness), SafeGetValue(a.acousticness), SafeGetValue(a.instrumentalness), SafeGetValue(a.liveness),
                                                                SafeGetValue(a.valence), SafeGetValue(a.tempo)) + ")").ToList();

                sql = string.Format(sql, string.Join(",", paramValues));

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }
    }
}