using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Sony.Filtr.Database;
using MySqlConnector;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.PlaylistContainer.Models;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.PlaylistContainer
{
    public class PlaylistContainerFactory
    {
        internal async Task<int> AddPlaylistContainerAsyncInDb(string playlistId, int musicServiceId, MySqlConnection conn, string playlistContainerName = null)
        {
            Models.PlaylistContainer container = null;

            var cmd = new MySqlCommand("INSERT INTO tblPlaylistContainer (PlaylistName) VALUES (@name)", conn);

            cmd.Parameters.AddWithValue("@name", playlistContainerName);

            await cmd.ExecuteNonQueryAsync();

            int id = (int)cmd.LastInsertedId;

            return id;
        }

        private async Task<Models.PlaylistContainer> GetExistingPlaylistContainerFromDatabaseAsync(string playlistId, int musicServiceId, MySqlConnection conn)
        {
            var cmd = new MySqlCommand("SELECT pc.Id, pc.PlaylistName, pc.Market FROM tblPlaylistContainerPlaylist AS p " +
                                       "INNER JOIN tblPlaylistContainer AS pc ON p.PlaylistContainerId = pc.Id " +
                                       "WHERE MusicServiceId = @musicServiceId AND PlaylistId = @playlistId", conn);

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

            Models.PlaylistContainer container = null;

            using (var reader = await cmd.ExecuteReaderAsync())
            {
                while(await reader.ReadAsync())
                {
                    container = new Models.PlaylistContainer()
                    {
                        Id = reader.GetInt32("Id"),
                        Name = reader.GetSafeString("PlaylistName"),
                        Market = reader.GetSafeString("Market")
                    };
                }

                reader.Close();
            }

            return container;
        }

        private async Task<Models.PlaylistContainer> GetExistingPlaylistContainerFromDatabaseAsync(int playlistContainerId, MySqlConnection conn)
        {
            var cmd = new MySqlCommand("SELECT p.Id, p.PlaylistName, p.Market FROM tblPlaylistContainer AS p WHERE p.Id = @containerId", conn);

            cmd.Parameters.AddWithValue("@containerId", playlistContainerId);

            Models.PlaylistContainer container = null;

            using (var reader = await cmd.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    container = new Models.PlaylistContainer()
                    {
                        Id = reader.GetInt32("Id"),
                        Name = reader.GetSafeString("PlaylistName"),
                        Market = reader.GetSafeString("Market")
                    };
                }

                reader.Close();
            }

            return container;
        }

        internal async Task<Models.PlaylistContainer> GetOrCreatePlaylistContainerAsync(string playlistId, int musicServiceId)
        {
            using(var conn = DatabaseHandler.GetOpenConnection())
            {
                using(var transaction = await conn.BeginTransactionAsync())
                {
                    var container = await GetExistingPlaylistContainerFromDatabaseAsync(playlistId, musicServiceId, conn);

                    if (container == null)
                    {
                        var containerId = await AddPlaylistContainerAsyncInDb(playlistId, musicServiceId, conn);

                        container = await GetExistingPlaylistContainerFromDatabaseAsync(containerId, conn);
                    }

                    
                    transaction.Commit();

                    return container;
                }
            }
        }

        internal async Task<Models.PlaylistContainer> GetPlaylistContainerAsync(string playlistId, int musicServiceId)
        {
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var container = await GetExistingPlaylistContainerFromDatabaseAsync(playlistId, musicServiceId, conn);

                return container;
            }
        }

        private async Task AddPlaylistLinkToDatabaseAsync(int playlistContainerId, string playlistId, int musicServiceId, MySqlConnection conn)
        {
            var cmd = new MySqlCommand("INSERT INTO tblPlaylistContainerPlaylist (PlaylistContainerId, MusicServiceId, PlaylistId) VALUES (@containerId, @serviceId, @playlistId)", conn);

            cmd.Parameters.AddWithValue("@containerId", playlistContainerId);
            cmd.Parameters.AddWithValue("@serviceId", musicServiceId);
            cmd.Parameters.AddWithValue("@playlistId", playlistId);

            await cmd.ExecuteNonQueryAsync();
        }

        internal async Task AddPlaylistLinkAsync(string sourcePlaylistId, int sourceMusicServiceId, string linkedPlaylistId, int linkedMusicServiceId, string playlistName = null)
        {
            using(var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                using(var transaction = await conn.BeginTransactionAsync())
                {
                    int? containerId = null;

                    var sourcePlaylistContainer = await GetExistingPlaylistContainerFromDatabaseAsync(sourcePlaylistId, sourceMusicServiceId, conn);
                    var linkedPlaylistContainer = await GetExistingPlaylistContainerFromDatabaseAsync(linkedPlaylistId, linkedMusicServiceId, conn);

                    // If both playlists already have containers, that means they potentially have information connected to them.
                    if (sourcePlaylistContainer != null && linkedPlaylistContainer != null && sourcePlaylistContainer.Id != linkedPlaylistContainer.Id)
                    {
                        throw new Exception("Both playlists already have (different) playlist containers");
                    }
                    // If they both have values and they are not different, that must mean they are already connected...
                    if (sourcePlaylistContainer != null && linkedPlaylistContainer != null)
                    {
                        throw new Exception("Playlists are already connected.");
                    }

                    // This will only be done if there is only one existing container, or none.
                    if (sourcePlaylistContainer != null)
                    {
                        containerId = sourcePlaylistContainer.Id;
                    }
                    else if (linkedPlaylistContainer != null)
                    {
                        containerId = linkedPlaylistContainer.Id;
                    }
                    
                    // No containers: add a new one!
                    if (!containerId.HasValue)
                    {
                        var cmd = new MySqlCommand("INSERT INTO tblPlaylistContainer (PlaylistName) VALUES (@name)", conn);

                        cmd.Parameters.AddWithValue("@name", playlistName);

                        await cmd.ExecuteNonQueryAsync();

                        containerId = (int)cmd.LastInsertedId;
                    }

                    // Linked list has a container, so add the source playlist to that one.
                    if (linkedPlaylistContainer != null)
                    {
                        await AddPlaylistLinkToDatabaseAsync(containerId.Value, sourcePlaylistId, sourceMusicServiceId, conn);
                    }

                    // Source list has a container, so add the linked playlist to that one.
                    if (sourcePlaylistContainer != null)
                    {
                        await AddPlaylistLinkToDatabaseAsync(containerId.Value, linkedPlaylistId, linkedMusicServiceId, conn);
                    }

                    transaction.Commit();
                }
            }
        }

        internal async Task<List<PlaylistContainerMusicService>> GetLinkedPlaylistsAsync(string playlistId, int musicServiceId)
        {
            List<PlaylistContainerMusicService> linkedPlaylists;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                linkedPlaylists = await GetPlaylistContainerMusicServicesFromDbAsync(playlistId, musicServiceId, conn);
            }

            return linkedPlaylists;
        }

        private async Task<List<PlaylistContainerMusicService>> GetPlaylistContainerMusicServicesFromDbAsync(string playlistId, int musicServiceId, MySqlConnection conn)
        {
            List<PlaylistContainerMusicService> linkedPlaylists;
            var sql = "SELECT p.PlaylistContainerId, pcp.PlaylistId, pcp.MusicServiceId FROM tblPlaylistContainerPlaylist p " +
                      "INNER JOIN tblPlaylistContainerPlaylist pcp ON p.PlaylistContainerId = pcp.PlaylistContainerId " +
                      "WHERE p.PlaylistId = @playlistId AND p.musicServiceId = @musicServiceId";

            var cmd = new MySqlCommand(sql, conn);

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

            using(var reader = await cmd.ExecuteReaderAsync())
            {
                linkedPlaylists = await BuildPlaylistContainerMusicServicesAsync(reader);

                reader.Close();
            }

            return linkedPlaylists;
        }

        internal async Task<List<PlaylistContainerMusicService>> GetLinkedPlaylistsAsync(int playlistContainerId)
        {
            List<PlaylistContainerMusicService> linkedPlaylists;

            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT PlaylistContainerId, PlaylistId, MusicServiceId FROM tblPlaylistContainerPlaylist " +
                          "WHERE PlaylistContainerId = @playlistContainerId";

                var cmd = new MySqlCommand(sql, conn);

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

                var reader = await cmd.ExecuteReaderAsync();

                linkedPlaylists = await BuildPlaylistContainerMusicServicesAsync(reader);
            }

            return linkedPlaylists;
        }

        internal async Task DeleteLinkedPlaylistAsync(int playlistContainerId, string playlistId, int musicServiceId)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "DELETE FROM tblPlaylistContainerPlaylist " +
                          "WHERE PlaylistContainerId = @playlistContainerId AND PlaylistId = @playlistId AND MusicServiceId = @musicServiceId";

                var cmd = new MySqlCommand(sql, conn);

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

                await cmd.ExecuteNonQueryAsync();
            }
        }
        

        private async Task<List<PlaylistContainerMusicService>> BuildPlaylistContainerMusicServicesAsync(MySqlDataReader reader)
        {
            var linkedPlaylists = new List<PlaylistContainerMusicService>();

            while (await reader.ReadAsync())
            {
                var service = new PlaylistContainerMusicService()
                {
                    PlaylistContainerId = reader.GetInt32("PlaylistContainerId"),
                    PlaylistId = reader.GetSafeString("PlaylistId"),
                    MusicServiceId = reader.GetInt32("MusicServiceId")
                };

                linkedPlaylists.Add(service);
            }

            return linkedPlaylists;
        }



        internal async Task UpdatePlaylistContainerMetaDataAsync(Models.PlaylistContainer container)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                var sql = "UPDATE tblPlaylistContainer SET (Name = @name, @market = market) " +
                          "WHERE Id = @containerId";

                var cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@name", container.Name);
                cmd.Parameters.AddWithValue("@market", container.Market);
                cmd.Parameters.AddWithValue("@containerId", container.Id);

                await cmd.ExecuteNonQueryAsync();
            }
        }

        public async Task<string> GetPlaylistMarketAsync(string playlistId, MusicService musicService)
        {
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT Market FROM tblPlaylistMarket WHERE PlaylistId = @playlistId AND MusicServiceId = @musicServiceId";

                using(var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@playlistId", playlistId);
                    cmd.Parameters.AddWithValue("@musicServiceId", musicService);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        if (reader.Read())
                        {
                            return reader.GetSafeString("Market");
                        }
                    }
                }
                
            }
            return null;
        }

        public async Task<Dictionary<string, string>> GetPlaylistMarketMappingsAsync(MusicService musicService)
        {
            Dictionary<string, string> playlistMarketMappings = new Dictionary<string, string>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var sql = "SELECT PlaylistId, Market FROM tblPlaylistMarket WHERE MusicServiceId = @musicServiceId";

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

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while(reader.Read())
                        {
                            playlistMarketMappings.Add(reader.GetString(0), reader.GetString(1));
                        }
                    }
                }
                
            }
            return playlistMarketMappings;
        }

        internal async Task SetPlaylistMarketAsync(string playlistId, MusicService musicService, string market)
        {
            using (var conn = await DatabaseHandler.GetOpenConnectionAsync())
            {
                const string sql = "INSERT INTO tblPlaylistMarket (PlaylistId, MusicServiceId, Market) " +
                                   "VALUES (@playlistId, @musicServiceId, @market) " +
                                   "ON DUPLICATE KEY UPDATE Market=@market";

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@playlistId", playlistId);
                    cmd.Parameters.AddWithValue("@musicServiceId", musicService);
                    cmd.Parameters.AddWithValue("@market", market);

                    await cmd.ExecuteNonQueryAsync();
                }
            }

        }


    }
}