using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using PetaPoco.Business;
using Sony.Filtr.Contracts.Entities;
using Sony.Filtr.Database;

namespace Sony.Filtr.Playlists
{
    public class EditorialPlaylistManagerNew
    {

        public async Task<List<EditorialPlaylistNew>> GetPlaylistsAsync(Application application)
        {
            List<EditorialPlaylistNew> playlists = new List<EditorialPlaylistNew>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT p.ID, p.ApplicationID, p.DisplayName, " +
                                   " dp.DeezerID, dp.Title, " +
                                   " psp.PriorityLevel, " +
                                   " sp.SpotifyUri, sp.Name, sp.Description, sp.Image, sp.OwnerId, sp.CountryCode, sp.Duration, sp.TrackCount, " +
                                   " t.id as themeId, t.AccentColor, t.BackgroundColor, t.HeaderBackgroundColor, t.HeaderImageUrl, t.TextColor, ept.Active as ThemeActive  " +
                                   " FROM EditorialPlaylistNew AS p " +
                                   " LEFT JOIN EditorialPlaylist_DeezerPlaylist AS pdp ON p.ID=pdp.EditorialPlaylistID " +
                                   " LEFT JOIN DeezerPlaylist AS dp ON dp.DeezerID=pdp.DeezerPlaylistID" +
                                   " LEFT JOIN EditorialPlaylist_SpotifyPlaylist AS psp ON p.ID=psp.EditorialPlaylistID " +
                                   " LEFT JOIN SpotifyPlaylist AS sp ON sp.SpotifyUri=psp.SpotifyUri " +
                                   " LEFT JOIN EditorialPlaylist_Theme AS ept ON ept.PlaylistID=p.ID" +
                                   " LEFT JOIN EditorialPlaylistTheme AS t ON t.ID=ept.ThemeID " +
                                   " WHERE p.Removed = 0 AND (@applicationID IS NULL OR ApplicationId=@applicationID) ";

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@applicationId", application?.ID);

                var sqlReader = await cmd.ExecuteReaderAsync();
                while (await sqlReader.ReadAsync())
                {
                    var playlist = BuildPlaylist(sqlReader);

                    playlists.Add(playlist);
                }

                conn.Close();
            }
            return playlists;
        }

        private static EditorialPlaylistNew BuildPlaylist(DbDataReader sqlReader)
        {
            var id = (int) sqlReader["id"];
            var applicationId = (int) sqlReader["applicationId"];
            var displayName = sqlReader["DisplayName"] as string;
            var playlist = new EditorialPlaylistNew()
            {
                ID = id,
                ApplicationID = applicationId,
                DisplayName = displayName,
            };

            if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("deezerID")))
            {
                var deezerId = (int) sqlReader["deezerID"];
                var deezerTitle = sqlReader["Title"] as string;
                var deezerPlaylist = new DeezerPlaylist()
                {
                    DeezerId = deezerId,
                    Title = deezerTitle,
                };
                playlist.DeezerPlaylist = deezerPlaylist;
            }

            if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("SpotifyUri")))
            {
                playlist.SpotifyPriorityLevel = (int) sqlReader["PriorityLevel"];
                var spotifyUri = sqlReader["SpotifyUri"] as string;
                var name = sqlReader["name"] as string;
                var spotifyPlaylist = new SpotifyPlaylist()
                {
                    PlaylistUri = spotifyUri,
                    Name = name,
                    Image = sqlReader["Image"] as string,
                    CountryCode = sqlReader["CountryCode"] as string,
                    Description = sqlReader["Description"] as string,
                };
                playlist.SpotifyPlaylist = spotifyPlaylist;
            }

            var themeId = sqlReader["ThemeId"] as int?;
            if (themeId.HasValue)
            {
                var accentColor = sqlReader["AccentColor"] as string;
                var backgroundColor = sqlReader["BackgroundColor"] as string;
                var headerBackgroundColor = sqlReader["HeaderBackgroundColor"] as string;
                var headerImageUrl = sqlReader["HeaderImageUrl"] as string;
                var textColor = sqlReader["TextColor"] as string;
                var themeActive = sqlReader.GetBoolean(sqlReader.GetOrdinal("ThemeActive"));
                playlist.Theme = new EditorialPlaylistTheme()
                {
                    ID = themeId.Value,
                    AccentColor = accentColor,
                    BackgroundColor = backgroundColor,
                    HeaderBackgroundColor = headerBackgroundColor,
                    HeaderImageUrl = headerImageUrl,
                    TextColor = textColor,
                    ThemeActive = themeActive
                };
            }
            return playlist;
        }

        public EditorialPlaylistNew AddPlaylist(EditorialPlaylistNew editorialPlaylist)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT INTO EditorialPlaylistNew (ApplicationID, DisplayName) VALUES (@applicationId, @displayName)";
                cmd.Parameters.AddWithValue("@applicationId", editorialPlaylist.ApplicationID);
                cmd.Parameters.AddWithValue("@displayName", editorialPlaylist.DisplayName);

                cmd.ExecuteNonQuery();

                var id = cmd.LastInsertedId;
                editorialPlaylist.ID = (int)id;
                
                conn.Close();
            }
            return editorialPlaylist;
        }

        public void UpdatePlaylist(EditorialPlaylistNew editorialPlaylist)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "UPDATE EditorialPlaylistNew SET DisplayName = @displayName WHERE ID=@id";
                cmd.Parameters.AddWithValue("@displayName", editorialPlaylist.DisplayName);
                cmd.Parameters.AddWithValue("@Id", editorialPlaylist.ID);

                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }
        public void DeletePlaylist(int id)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "UPDATE EditorialPlaylistNew SET Removed=1 WHERE Id=@Id";
                cmd.Parameters.AddWithValue("@Id", id);

                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }

        public async Task<EditorialPlaylistNew> GetPlaylistAsync(int id)
        {
            EditorialPlaylistNew editorialPlaylist = null;
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                const string sql = "SELECT p.ID, p.ApplicationID, p.DisplayName, " +
                                   " dp.DeezerID, dp.Title, " +
                                   " psp.PriorityLevel, " +
                                   " sp.SpotifyUri, sp.Name, sp.Description, sp.Image, sp.OwnerId, sp.CountryCode, sp.Duration, sp.TrackCount, " +
                                   " t.id as themeId, t.AccentColor, t.BackgroundColor, t.HeaderBackgroundColor, t.HeaderImageUrl, t.TextColor, ept.Active as ThemeActive  " +
                                   " FROM EditorialPlaylistNew AS p " +
                                   " LEFT JOIN EditorialPlaylist_DeezerPlaylist AS pdp ON p.ID=pdp.EditorialPlaylistID " +
                                   " LEFT JOIN DeezerPlaylist AS dp ON dp.DeezerID=pdp.DeezerPlaylistID" +
                                   " LEFT JOIN EditorialPlaylist_SpotifyPlaylist AS psp ON p.ID=psp.EditorialPlaylistID " +
                                   " LEFT JOIN SpotifyPlaylist AS sp ON sp.SpotifyUri=psp.SpotifyUri " +
                                   " LEFT JOIN EditorialPlaylist_Theme AS ept ON ept.PlaylistID=p.ID" +
                                   " LEFT JOIN EditorialPlaylistTheme AS t ON t.ID=ept.ThemeID " +
                                   " WHERE p.Removed = 0 AND p.ID=@playlistId ";

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@playlistId", id);

                var sqlReader = await cmd.ExecuteReaderAsync();
                if (await sqlReader.ReadAsync())
                {
                    editorialPlaylist = BuildPlaylist(sqlReader);
                }

                conn.Close();
            }
            return editorialPlaylist;
        }

        public void ConnectToSpotifyPlaylist(EditorialPlaylistNew editorialPlaylist, string spotifyUri)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();

                //We assume the SpotifyPlaylist is stored.
                cmd.CommandText = "INSERT INTO EditorialPlaylist_SpotifyPlaylist (EditorialPlaylistId, SpotifyUri, PriorityLevel) VALUES (@editorialPlaylistId, @spotifyUri, @priorityLevel)" +
                                  " ON DUPLICATE KEY UPDATE PriorityLevel=@PriorityLevel";

                cmd.Parameters.AddWithValue("@editorialPlaylistId", editorialPlaylist.ID);
                cmd.Parameters.AddWithValue("@spotifyUri", spotifyUri);
                cmd.Parameters.AddWithValue("@priorityLevel", editorialPlaylist.SpotifyPriorityLevel);

                //Get id
                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }

        public void ConnectToDeezerPlaylist(EditorialPlaylistNew editorialPlaylist, int deezerPlaylistId)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();

                //We assume the DeezerPlaylist is stored.
                cmd.CommandText = "INSERT IGNORE INTO EditorialPlaylist_DeezerPlaylist (EditorialPlaylistId, DeezerPlaylistId) VALUES (@editorialPlaylistId, @deezerPlaylistId)";
                cmd.Parameters.AddWithValue("@editorialPlaylistId", editorialPlaylist.ID);
                cmd.Parameters.AddWithValue("@deezerPlaylistId", deezerPlaylistId);

                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }

        public void DisconnectFromDeezerPlaylist(EditorialPlaylistNew editorialPlaylist)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM EditorialPlaylist_DeezerPlaylist WHERE editorialPlaylistId=@editorialPlaylistId";
                cmd.Parameters.AddWithValue("@editorialPlaylistId", editorialPlaylist.ID);

                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }
        public void DisconnectFromSpotifyPlaylist(EditorialPlaylistNew editorialPlaylist)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM EditorialPlaylist_SpotifyPlaylist WHERE editorialPlaylistId=@editorialPlaylistId";
                cmd.Parameters.AddWithValue("@editorialPlaylistId", editorialPlaylist.ID);

                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }

        public void SetEditorialPlaylistTheme(EditorialPlaylistNew playlist, EditorialPlaylistTheme theme)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT INTO EditorialPlaylist_Theme (PlaylistID, ThemeID) VALUES (@playlistId, @themeId)" +
                                  "ON DUPLICATE KEY UPDATE themeId=@themeId";
                cmd.Parameters.AddWithValue("@PlaylistID", playlist.ID);
                cmd.Parameters.AddWithValue("@ThemeID", theme.ID);

                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }

        public EditorialPlaylistTheme GetEditorialPlaylistTheme(EditorialPlaylistNew playlist)
        {
            EditorialPlaylistTheme theme = null;
            using (var conn = DatabaseHandler.GetOpenReadOnlyConnection())
            {
                var cmd = conn.CreateCommand();

                cmd.CommandText = "SELECT t.* FROM EditorialPlaylistTheme AS t INNER JOIN EditorialPlaylist_Theme AS et ON et.ThemeID=t.ID WHERE" +
                                  "et.PlaylistID=@playlistId";
                cmd.Parameters.AddWithValue("@PlaylistID", playlist.ID);

                var reader = cmd.ExecuteReader();
                theme = EditorialPlaylistTheme.FromReader(reader);

                conn.Close();
            }
            return theme;
        }

    }
}