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

namespace Sony.Filtr.AppleMusic.BestOfTheWeek
{
    public class BestOfTheWeekFactory
    {
        public IList<AppleMusicBestOfTheWeekPlaylist> GetPlaylists()
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicBestOfTheWeekPlaylist>();
        }

        public IList<AppleMusicBestOfTheWeekPlaylistTrack> GetPlaylistTracksForFriday(AppleMusicBestOfTheWeekPlaylist playlist, DateTime utcFriday)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<AppleMusicBestOfTheWeekPlaylistTrack>("WHERE PlaylistId=@0 AND Storefront=@1 AND Date=@2", playlist.PlaylistId, playlist.Storefront, utcFriday.Date);
        }

        public async Task AddPlaylists(IEnumerable<AppleMusicBestOfTheWeekPlaylist> playlists)
        {
            await PetaPocoRepository.Instance.BulkInsertAsync(playlists);
        }

        public async Task SetPlaylistTracksForFriday(IList<AppleMusicBestOfTheWeekPlaylistTrack> tracks, AppleMusicBestOfTheWeekPlaylist playlist, DateTime utcFriday, Logger logger = null)
        {
            if (tracks.Any(x => x.PlaylistId != playlist.PlaylistId || x.Storefront != playlist.Storefront || x.Date.Date != utcFriday.Date))
                throw new InvalidOperationException("Not all BotW-tracks are for the same playlist and/or date as input");

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

                        using (var command = new MySqlCommand(deleteSql, conn, transaction))
                        {
                            command.Parameters.AddWithValue("@playlistId", playlist.PlaylistId);
                            command.Parameters.AddWithValue("@storefront", playlist.Storefront);
                            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 tblAppleMusicBestOfTheWeekPlaylistTrack (PlaylistId, Date, PlaylistIndex, SongId, Storefront) VALUES ");

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

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

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

                        transaction.Commit();
                    }
                    catch (Exception e)
                    {
                        logger?.Error(e, $"Error writing { tracks.Count } tracks to database for BotW playlist { playlist?.FullIdentifier }");

                        transaction.Rollback();
                    }
                }
            });
        }

        public async Task<List<BestOfTheWeekPlaylistTrackCacheModel>> GetPlaylistTracksAsync(BestOfTheWeekPlaylistReference playlist, DateTime date)
        {
            const string sql = "SELECT tl.Date, tl.PlaylistId, tl.Storefront, tl.PlaylistIndex, tl.SongId, t.Isrc, t.Name AS SongName, alb.ArtworkUrl AS AlbumImage, a.Id AS ArtistId, a.Name AS ArtistName, a.Storefront AS ArtistStorefront " +
                               "FROM tblAppleMusicBestOfTheWeekPlaylistTrack AS tl " +
                               "INNER JOIN tblAppleMusicSong AS t ON t.Id = tl.SongId AND t.Storefront = tl.Storefront " +
                               "INNER JOIN tblAppleMusicAlbum AS alb ON alb.Id = t.AlbumId AND alb.Storefront = t.Storefront " +
                               "INNER JOIN tblAppleMusicSongArtist AS sa ON sa.SongId = t.Id AND t.Storefront = sa.Storefront " +
                               "INNER JOIN tblAppleMusicArtist AS a ON sa.ArtistId = a.Id AND sa.Storefront = a.Storefront " +
                               "WHERE tl.Date = @date AND tl.PlaylistId = @playlistId AND tl.Storefront = @storefront " +
                               "ORDER BY tl.PlaylistIndex ASC, sa.ArtistOrder ASC;";

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

            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", playlist.PlaylistId);
                command.Parameters.AddWithValue("@storefront", playlist.Storefront);

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

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

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

            const string sql = "SELECT DISTINCT tl.PlaylistId, tl.Storefront, pls.Name, pls.Artwork, MAX(t.Date) AS LastDate " +
                               "FROM tblAppleMusicBestOfTheWeekPlaylist AS tl " +
                               "INNER JOIN tblAppleMusicBestOfTheWeekPlaylistTrack AS t ON t.PlaylistId = tl.PlaylistId AND t.Storefront = tl.Storefront " +
                               "INNER JOIN tblAppleMusicPlaylist AS pl ON pl.Id = tl.PlaylistId " +
                               "LEFT JOIN tblAppleMusicPlaylistStorefrontData AS pls ON pls.PlaylistId = tl.PlaylistId AND pls.Storefront = tl.Storefront " +
                               "GROUP BY tl.Storefront";

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            using (var command = new MySqlCommand(sql, conn))
            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    playlist.Add(new BestOfTheWeekPlaylistData()
                    {
                        PlaylistId = reader.GetString("PlaylistId"),
                        Storefront = reader.GetString("Storefront"),
                        Name = reader.GetString("Name"),
                        FridayLastUpdatedDate = reader.GetUtcDateTime("LastDate")
                    });
                }
            }

            return playlist;
        }
    }
}