using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;
using Sony.Filtr.NewMusicFriday.Caching.Models;
using Sony.Filtr.NewMusicFriday.Models;
using Sony.Filtr.Utility.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sony.Filtr.NewMusicFriday
{
    public class NewMusicFridayFactory
    {
        public SpotifyNewMusicFridayPlaylist GetPlaylist(string playlistId)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefault<SpotifyNewMusicFridayPlaylist>(playlistId);
        }

        public IList<SpotifyNewMusicFridayPlaylist> GetPlaylists()
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyNewMusicFridayPlaylist>();
        }

        public async Task<IList<SpotifyNewMusicFridayPlaylistData>> GetPlaylistsData()
        {
            var playlist = new List<SpotifyNewMusicFridayPlaylistData>();

            const string sql = "SELECT DISTINCT tl.Market, tl.PlaylistId, pl.Name, MAX(pt.Date) AS LastDate, MAX(pt.Added) AS LastAdded, f.Followers " +
                      "FROM tblSpotifyNewMusicFridayPlaylist AS tl " +
                      "INNER JOIN tblSpotifyNewMusicFridayPlaylistTrack AS pt ON pt.PlaylistId = tl.PlaylistId " +
                      "INNER JOIN tblSpotifyPlaylist AS pl ON pl.PlaylistId = tl.PlaylistId " +
                      "LEFT JOIN tblSpotifyPlaylistFollowers AS f ON tl.PlaylistId = f.PlaylistId " +
                      "GROUP BY tl.PlaylistId";

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            using (var command = new MySqlCommand(sql, conn))
            using (var reader = await command.ExecuteReaderAsync())
            {
                while (reader.Read())
                {
                    playlist.Add(new SpotifyNewMusicFridayPlaylistData()
                    {
                        Market = reader.GetString("Market"),
                        PlaylistId = reader.GetString("PlaylistId"),
                        Name = reader.GetString("Name"),
                        FridayLastUpdatedDate = reader.GetUtcDateTime("LastDate"),
                        TrackLastAdded = reader.GetUtcDateTimeOrDefault("LastAdded"),
                        Followers = reader.GetIntOrDefault("Followers")
                    });
                }
            }

            return playlist;
        }

        public IList<SpotifyNewMusicFridayPlaylistTrack> GetTracksForNewMusicFridayPlaylist(string playlistId, DateTime utcFriday)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyNewMusicFridayPlaylistTrack>("WHERE PlaylistId=@0 AND Date=@1", playlistId, utcFriday.Date);
        }

        internal SpotifyNewMusicFridayPlaylist AddPlaylist(SpotifyNewMusicFridayPlaylist playlist)
        {
            if (GetPlaylist(playlist.PlaylistId) == null)
                PetaPocoRepository.Instance.Insert(playlist);

            return playlist;
        }

        public void DeletePlaylist(string playlistId)
        {
            PetaPocoRepository.Instance.Delete<SpotifyNewMusicFridayPlaylist>(playlistId);
        }

        public async Task SetPlaylistTracksForFriday(IList<SpotifyNewMusicFridayPlaylistTrack> tracks, string playlistId, DateTime utcFriday)
        {
            if (tracks.Any(x => x.PlaylistId != playlistId || x.Date.Date != utcFriday.Date))
                throw new InvalidOperationException("Not all NMF-tracks have the same Playlist ID and Date as input");

            await FaultHandlingPolicy.MySqlRetryPolicyAsync.ExecuteAsync(async () =>
            {
                using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
                using (var transaction = await conn.BeginTransactionAsync())
                {
                    try
                    {
                        const string deleteSql = "DELETE FROM tblSpotifyNewMusicFridayPlaylistTrack " +
                                        "WHERE PlaylistId=@playlistId AND Date=@date";

                        using (var command = new MySqlCommand(deleteSql, conn, transaction))
                        {
                            command.Parameters.AddWithValue("@playlistId", playlistId);
                            command.Parameters.Add("@date", MySqlDbType.Date).Value = utcFriday.Date;
                            await command.ExecuteNonQueryAsync();
                        }

                        using (var command = new MySqlCommand(string.Empty, conn, transaction))
                        {
                            var insertSql = new StringBuilder("INSERT INTO tblSpotifyNewMusicFridayPlaylistTrack (PlaylistId, Date, PlaylistIndex, TrackId, Added) VALUES ");

                            for (int i = 0; i < tracks.Count; i++)
                            {
                                command.Parameters.AddWithValue($"@playlistId{i}", tracks[i].PlaylistId);
                                command.Parameters.AddWithValue($"@added{i}", tracks[i].Added);
                                command.Parameters.Add($"@date{i}", MySqlDbType.Date).Value = tracks[i].Date.Date;
                                command.Parameters.AddWithValue($"@playlistIndex{i}", tracks[i].PlaylistIndex);
                                command.Parameters.AddWithValue($"@trackId{i}", tracks[i].TrackId);

                                insertSql.Append($"(@playlistId{i}, @date{i}, @playlistIndex{i}, @trackId{i}, @added{i}), ");
                            }

                            insertSql.Remove(insertSql.Length - 2, 2);
                            command.CommandText = insertSql.ToString();
                            await command.ExecuteNonQueryAsync();
                        }

                        transaction.Commit();
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                    }
                }
            });
        }

        public void DeleteTracksForFriday(DateTime utcFriday)
        {
            PetaPocoRepository.Instance.Delete<SpotifyNewMusicFridayPlaylistTrack>("WHERE Date=@0", utcFriday.Date);
        }

        public IList<SpotifyNewMusicFridayPlaylistTrack> GetTracksForFriday(DateTime utcFriday)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<SpotifyNewMusicFridayPlaylistTrack>("WHERE Date=@0", utcFriday.Date);
        }

        public async Task<List<NewMusicFridayPlaylistTrackCacheModel>> GetPlaylistTracksAsync(string playlistId, DateTime date)
        {
            const string sql = "SELECT tl.Date, tl.PlaylistId, tl.PlaylistIndex, tl.TrackId, tl.Added, t.ISRC, t.Name AS TrackName, alb.Upc AS AlbumUpc, alb.SmallImageFilename AS AlbumImage, a.ArtistId, a.Name AS ArtistName " +
                               "FROM tblSpotifyNewMusicFridayPlaylistTrack AS tl " +
                               "INNER JOIN tblSpotifyTrack2 AS t ON t.TrackId = tl.trackId " +
                               "INNER JOIN tblSpotifyTrackAlbum AS talb ON talb.TrackId = t.TrackId " +
                               "INNER JOIN tblSpotifyTrackArtist AS ta ON ta.TrackId = t.TrackId  " +
                               "INNER JOIN tblSpotifyArtist AS a ON a.ArtistId = ta.ArtistId " +
                               "INNER JOIN tblSpotifyAlbum AS alb ON alb.AlbumId = talb.AlbumId " +
                               "WHERE date = @date AND tl.PlaylistId = @playlistId " +
                               "ORDER BY tl.PlaylistIndex ASC, ta.ArtistOrder ASC";

            var tracks = new Dictionary<string, NewMusicFridayPlaylistTrackCacheModel>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            using (var command = new MySqlCommand(sql, conn))
            {
                command.Parameters.Add("@date", MySqlDbType.Date).Value = date;
                command.Parameters.AddWithValue("@playlistId", playlistId);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (reader.Read())
                    {
                        var trackId = reader.GetString("trackId");
                        if (!tracks.ContainsKey(trackId))
                        {
                            tracks.Add(trackId, new NewMusicFridayPlaylistTrackCacheModel()
                            {
                                TrackId = trackId,
                                AlbumUpc = reader.GetString("AlbumUpc"),
                                AlbumImageUrl = reader.GetString("AlbumImage"),
                                Artists = new List<SpotifyArtistReference>()
                                    {
                                        new SpotifyArtistReference() { Name = reader.GetString("ArtistName"), Id = reader.GetString("ArtistId") }
                                    },
                                Date = reader.GetDateTime("Date"),
                                Isrc = reader.GetString("ISRC"),
                                PlaylistId = reader.GetString("PlaylistId"),
                                PlaylistIndex = reader.GetInt32("PlaylistIndex"),
                                TrackName = reader.GetString("TrackName"),
                                Added = reader.GetUtcDateTimeOrDefault("Added")
                            });
                        }
                        else
                        {
                            tracks[trackId].Artists?.Add(new SpotifyArtistReference()
                            {
                                Name = reader.GetString("ArtistName"),
                                Id = reader.GetString("ArtistId")
                            });
                        }
                    }
                }
            }

            return new List<NewMusicFridayPlaylistTrackCacheModel>(tracks.Values);
        }
    }
}