using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Globalization;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using MoreLinq;
using MySql.Data.MySqlClient;
using Sony.Filtr.AppleMusic.Data;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.AppleMusic
{
    public class AppleMusicSongManager
    {
        public async Task AddOrUpdateSongsAsync(IEnumerable<AppleMusicSong> localAppleMusicSongs)
        {
            var appleMusicSongs = localAppleMusicSongs?.ToList();
            if (appleMusicSongs == null || !appleMusicSongs.Any())
                return;

            var sql = "INSERT IGNORE INTO tblAppleMusicSong (Id, Storefront, Name, ArtistId, ArtistName, AlbumId, ArtworkUrl, ComposerName, TrackNumber, Duration, DiscNumber, ISRC, ReleaseDate) VALUES {0} " +
                      "ON DUPLICATE KEY UPDATE Name = VALUES(Name), ArtistId = VALUES(ArtistId), ArtistName = VALUES(ArtistName), AlbumId = VALUES(AlbumId), ArtworkUrl = VALUES(ArtworkUrl), ComposerName = VALUES(ComposerName), TrackNumber = VALUES(TrackNumber), Duration = VALUES(Duration), DiscNumber = VALUES(DiscNumber), ISRC = VALUES(Isrc), ReleaseDate = Values(ReleaseDate) ";

            var paramValues = new List<string>();

            foreach (var track in appleMusicSongs.DistinctBy(t => t.Id))
            {
                var paramValue = "(" + string.Join(",",
                                     track.Id,
                                     SafeGetStringValueParameter(track.Storefront),
                                     SafeGetStringValueParameter(track.Name),
                                     track.ArtistId,
                                     SafeGetStringValueParameter(track.ArtistName),
                                     track.AlbumId,
                                     SafeGetStringValueParameter(track.ArtworkUrl),
                                     SafeGetStringValueParameter(track.ComposerName),
                                     track.TrackNumber,
                                     track.Duration,
                                     track.DiscNumber,
                                     SafeGetStringValueParameter(track.Isrc),
                                     track.ReleaseDate.HasValue ? SafeGetStringValueParameter(track.ReleaseDate.Value.ToString("d", CultureInfo.GetCultureInfo("sv-SE"))) : "NULL")
                                     + ")";
                paramValues.Add(paramValue);
            }


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

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

        public async Task AddOrUpdateArtistsAsync(string storefront, IEnumerable<AppleMusicArtist> localAppleMusicArtists)
        {
            if (!localAppleMusicArtists.Any()) { return; }
            
            var sql = "INSERT IGNORE INTO tblAppleMusicArtist (Storefront, Id, Name) VALUES ";

            var paramValues = localAppleMusicArtists.Select(a => "(" + string.Join(",", "'" + MySqlHelper.EscapeString(storefront) + "'", a.Id, "'" + MySqlHelper.EscapeString(a.Name) + "'") + ")").ToList();

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

            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var tran = await conn.BeginTransactionAsync())
                {
                    try
                    {
                        using (var cmd = new MySqlCommand(sql, conn, tran))
                        {
                            await cmd.ExecuteNonQueryAsync();

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

                        throw;
                    }
                }
            }
        }

        private static string SafeGetStringValueParameter(string value)
        {
            if (value == null)
                return "NULL";

            return "'" + MySqlHelper.EscapeString(value) + "'";
        }

        public async Task<Dictionary<long, string>> GetISRCAsync(string storefront, List<long> songIds)
        {
            Dictionary<long, string> isrc = new Dictionary<long, string>();
            if (songIds == null || !songIds.Any())
                return isrc;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $"SELECT Id, ISRC FROM tblAppleMusicSong WHERE storefront = @storefront AND id IN ({string.Join(",", songIds)}) AND isrc IS NOT NULL";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("storefront", storefront);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            isrc.Add(reader.GetInt64(0), reader.GetString(1));
                        }
                    }

                }
            }
            return isrc;
        }

        public async Task<string> GetISRCAsync(List<long> songIds)
        {
            string isrc=null;
            if (songIds == null || !songIds.Any())
                return isrc;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = $"SELECT Id, ISRC FROM tblAppleMusicSong WHERE id IN ({string.Join(",", songIds)}) AND isrc IS NOT NULL";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            isrc = reader.GetString(1);
                            return isrc;
                        }
                    }
                }
            }
            return isrc;
        }

        public async Task AddOrUpdateSongArtistConnectionsAsync(string storefront, AppleMusicSong song)
        {
            await FaultHandlingPolicy.MySqlRetryPolicyAsync.ExecuteAsync(async () =>
            {
                using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
                {
                    using (var tran = await conn.BeginTransactionAsync())
                    {
                        try
                        {
                            using (var delCmd = new MySqlCommand("DELETE FROM tblAppleMusicSongArtist WHERE Storefront = @storefront AND SongId=@songId", conn, tran))
                            {
                                delCmd.Parameters.AddWithValue("@songId", song.Id);
                                delCmd.Parameters.AddWithValue("@storefront", storefront);
                                await delCmd.ExecuteNonQueryAsync();
                            }

                            if (song.ArtistIds.Any())
                            {
                                var insertSql = "INSERT IGNORE INTO tblAppleMusicSongArtist (Storefront, SongId, ArtistId, ArtistOrder) VALUES ";

                                List<string> paramValues = new List<string>();
                                paramValues.AddRange(song.ArtistIds.Select((artistId, index) => "(" + string.Join(",", "'" + storefront + "'", song.Id, artistId, index) + ")"));

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

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

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

                            throw;
                        }
                    }
                }
            });
        }

        public async Task MarkSongsAsNotAvailable(IEnumerable<(string storefront, long id)> songs)
        {
            if (!songs.Any()) { return; }

            Func<(string, long), string> createRemoveSql = tuple => $"UPDATE tblAppleMusicSong SET NotAvailable = 1 WHERE Storefront = '{tuple.Item1}' AND Id = {tuple.Item2};";

            using (var connection = await DatabaseHandler.GetOpenConnectionAsync())
            {
                foreach (var batch in songs.Batch(100))
                {
                    string sql = String.Join(Environment.NewLine, batch.Select(b => createRemoveSql(b)).ToList());

                    using (var cmd = new MySqlCommand(sql, connection))
                    {
                        cmd.Transaction = await connection.BeginTransactionAsync();
                        await cmd.ExecuteNonQueryAsync();
                        cmd.Transaction.Commit();
                    }
                }
            }
        }

        public async  Task RecalculateMissingAlbumOrArtistSongsTable()
        {
            var insertSQL = @"
INSERT IGNORE INTO tblAppleMusicSongWithMissingAlbumOrArtist
SELECT s.id, s.storefront
FROM tblAppleMusicSong AS s
LEFT JOIN tblAppleMusicSongArtist AS sa ON s.Id = sa.SongId AND s.storefront = sa.storefront
LEFT JOIN tblAppleMusicArtist AS ar ON sa.ArtistId = ar.id AND ar.storefront = sa.Storefront
LEFT JOIN tblAppleMusicAlbum AS al ON s.albumId = al.id AND al.storefront = s.Storefront
WHERE (ar.id IS NULL OR al.id IS NULL) AND s.NotAvailable = 0;";

            using (var connection = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using (var command = new MySqlCommand("TRUNCATE TABLE tblAppleMusicSongWithMissingAlbumOrArtist;", connection))
                {
                    await command.ExecuteNonQueryAsync();
                }

                using (var command = new MySqlCommand(insertSQL, connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task<Dictionary<string, List<long>>> GetSongsMissingDataAsync(CancellationToken token)
        {
            
            Func<CancellationToken, string, Task<Dictionary<string, List<long>>>> readDataUsingSql = async (t, sql) => {
                var songIdsByStorefront = new Dictionary<string, List<long>>();

                using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
                {
                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        using (var reader = await cmd.ExecuteReaderAsync(t))
                        {
                            while (await reader.ReadAsync())
                            {
                                var songId = reader.GetInt64(0);
                                var storefront = reader.GetString(1);

                                storefront = storefront.ToLowerInvariant();

                                if (!songIdsByStorefront.ContainsKey(storefront))
                                {
                                    songIdsByStorefront.Add(storefront, new List<long>());
                                }

                                songIdsByStorefront[storefront].Add(songId);
                            }
                        }
                    }
                }

                return songIdsByStorefront;
            };

            Func<CancellationToken, Task<Dictionary<string, List<long>>>> usingTable = async t => {
                string sql = "SELECT Id, Storefront FROM tblAppleMusicSongWithMissingAlbumOrArtist;";

                return await readDataUsingSql(t, sql);
            };

            Func<CancellationToken, Task<Dictionary<string, List<long>>>> usingJoinSql = async t => {
                var sql = @"
SELECT s.id, s.storefront
FROM tblAppleMusicSong AS s
LEFT JOIN tblAppleMusicSongArtist AS sa ON s.Id = sa.SongId AND s.storefront = sa.storefront
LEFT JOIN tblAppleMusicArtist AS ar ON sa.ArtistId = ar.id AND ar.storefront = sa.Storefront
LEFT JOIN tblAppleMusicAlbum AS al ON s.albumId = al.id AND al.storefront = s.Storefront
WHERE (ar.id IS NULL OR al.id IS NULL) AND s.NotAvailable = 0;";

                return await readDataUsingSql(t, sql);
            };

            return await usingTable(token);
        }

        public async Task<Dictionary<string, List<long>>> GetSongsWithoutAlbumAsync()
        {
            var songIdsByStorefront = new Dictionary<string, List<long>>();

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT s.id, s.storefront " +
                          "FROM tblAppleMusicSong AS s " +
                          "LEFT JOIN tblAppleMusicAlbum AS a ON s.albumId = a.id AND s.storefront = a.storefront " +
                          "WHERE a.id IS NULL " +
                          "LIMIT 100000";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var songId = reader.GetInt64(0);
                            var storefront = reader.GetString(1);

                            storefront = storefront.ToLowerInvariant();

                            if (!songIdsByStorefront.ContainsKey(storefront))
                                songIdsByStorefront.Add(storefront, new List<long>());

                            songIdsByStorefront[storefront].Add(songId);
                        }
                    }
                }
            }

            return songIdsByStorefront;
        }

        public async Task<List<AppleMusicFullSong>> GetSongsAsync(string storefront, List<long> songIds)
        {
            if (!songIds.Any())
            {
                return new List<AppleMusicFullSong>();
            }
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT s.id, s.storefront, s.name, s.albumId, s.artworkUrl, s.composerName, s.trackNumber, s.duration, s.discNumber, s.isrc, a.id as artistId, COALESCE(a.name, s.artistname) as artistName, sa.artistOrder " +
                          "FROM tblAppleMusicSong AS s " +
                          "LEFT JOIN tblAppleMusicSongArtist AS sa ON sa.songId = s.id AND sa.storefront = s.storefront " +
                          "LEFT JOIN tblAppleMusicArtist AS a ON a.storefront = sa.storefront AND a.Id = sa.artistId " +
                          $"WHERE s.storefront = @storefront AND s.id IN ({string.Join(",", songIds)}) " +
                          "ORDER BY s.id, sa.artistOrder";

                Dictionary<long, AppleMusicFullSong> tempSongs = new Dictionary<long, AppleMusicFullSong>();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("storefront", storefront);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var songId = reader.GetLong("id");

                            var artist = new AppleMusicArtistReference()
                            {
                                ArtistId = 0,
                                Name = string.Empty,
                            };

                            var artistId = reader.GetLongOrDefault("artistId");
                            artist = new AppleMusicArtistReference()
                            {
                                ArtistId = artistId ?? 0,
                                Name = reader.GetString("artistName"),
                            };

                            var song = tempSongs.GetValueOrDefault(songId);
                            if (song != null)
                            {
                                song.Artists.Add(artist);
                            }
                            else
                            {
                                song = new AppleMusicFullSong()
                                {
                                    Id = songId,
                                    Storefront = reader.GetString("storefront"),
                                    Name = reader.GetString("name"),
                                    AlbumId = reader.GetLong("albumId"),
                                    ArtworkUrl = reader.GetString("artworkUrl"),
                                    ComposerName = reader.GetString("composerName"),
                                    TrackNumber = reader.GetInt32("trackNumber"),
                                    Duration = reader.GetInt32("duration"),
                                    DiscNumber = reader.GetInt32("DiscNumber"),
                                    Isrc = reader.GetString("isrc"),
                                    ArtistId = artist.ArtistId,
                                    ArtistName = artist.Name,
                                    Artists = new List<AppleMusicArtistReference>() { artist }
                                };
                                tempSongs.Add(songId, song);
                            }
                        }
                    }
                }
                return tempSongs.Values.ToList();
            }
        }


    }
}