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

namespace Sony.Filtr.Core.EditorialPlaylists
{
    public class PlaylistCategoryFactory
    {
        internal PlaylistCategory SaveCategory(PlaylistCategory playlistCategory)
        {
            return PetaPocoRepository.Instance.Upsert(playlistCategory);
        }

        internal List<PlaylistCategory> GetPlaylistCategories(Application application)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<PlaylistCategory>("WHERE applicationId=@0", application.ID);
        }

        internal List<PlaylistCategory> GetCategories(EditorialPlaylist playlist)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<PlaylistCategory>("SELECT c.* FROM tblPlaylistCategory AS c INNER JOIN tblPlaylistCategoryPlaylist AS p ON c.Id=p.categoryId WHERE p.playlistId=@0", playlist.ID);
        }

        public async Task<Dictionary<int, List<PlaylistCategory>>> GetCategoriesAsync(IEnumerable<EditorialPlaylist> playlists)
        {
            Dictionary<int, List<PlaylistCategory>> playlistCategories = new Dictionary<int, List<PlaylistCategory>>();
            using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync())
            {
                var cmd = conn.CreateCommand();
                var sql = "SELECT p.playlistId, c.* FROM tblPlaylistCategory AS c INNER JOIN tblPlaylistCategoryPlaylist AS p ON c.Id=p.categoryId WHERE p.playlistId IN ({0})";
                var parameterValue = string.Join(",", playlists.Select(p => p.ID));

                cmd.CommandText = String.Format(sql, parameterValue);
                var reader = await cmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    var playlistId = reader.GetInt32(0);

                    var playlistCategory = MapPlaylistCategory(reader, 1);

                    if (playlistCategories.ContainsKey(playlistId))
                    {
                        playlistCategories[playlistId].Add(playlistCategory);
                    }
                    else
                    {
                        playlistCategories.Add(playlistId, new List<PlaylistCategory>() { playlistCategory });
                    }
                }
            }
            return playlistCategories;
        }

        private PlaylistCategory MapPlaylistCategory(DbDataReader reader, int offset)
        {
            return new PlaylistCategory()
            {
                ID = reader.GetInt32(0 + offset),
                Name = (!reader.IsDBNull(1 + offset)) ? reader.GetString(1 + offset) : null,
                Icon = (!reader.IsDBNull(2 + offset)) ? reader.GetString(2 + offset) : null,
                ApplicationId = reader.GetInt32(3 + offset),
                Active = reader.GetBoolean(4 + offset),
                Order = reader.GetInt32(5 + offset),
                Description = (!reader.IsDBNull(6 + offset)) ? reader.GetString(6 + offset) : null,
                PredefinedIconName = (!reader.IsDBNull(7 + offset)) ? reader.GetString(7 + offset) : null,
                PlaylistCategoryIconId = (!reader.IsDBNull(8 + offset)) ? (int?)reader.GetInt32(8 + offset) : null,
            };
        }


        internal void SetCategories(EditorialPlaylist playlist, List<PlaylistCategory> categories)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var tran = conn.BeginTransaction();

                var delCmd = conn.CreateCommand();
                delCmd.CommandText = "DELETE FROM tblPlaylistCategoryPlaylist WHERE playlistId = @playlistId";
                delCmd.Parameters.AddWithValue("@playlistId", playlist.ID);
                delCmd.ExecuteNonQuery();

                foreach (var category in categories)
                {
                    var cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO tblPlaylistCategoryPlaylist (categoryId, playlistId) VALUES (@categoryId, @playlistId)";
                    cmd.Parameters.AddWithValue("@playlistId", playlist.ID);
                    cmd.Parameters.AddWithValue("@categoryId", category.ID);
                    cmd.ExecuteNonQuery();
                }

                tran.Commit();
            }
        }

        internal PlaylistCategory GetCategory(int id)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefault<PlaylistCategory>(id);
        }

        internal List<EditorialPlaylist> GetPlaylists(PlaylistCategory existingCategory)
        {
            var sql = new Sql("SELECT * FROM tblEditorialPlaylist AS p INNER JOIN tblPlaylistCategoryPlaylist AS cp ON p.intId=cp.playlistId WHERE p.blnRemoved = 0 AND cp.categoryId=@0", existingCategory.ID);
            var editorialPlaylists = PetaPocoRepository.ReadOnlyInstance.Fetch<EditorialPlaylist>(sql);
            return editorialPlaylists;
        }

        internal void DeleteCategory(PlaylistCategory existingCategory)
        {
            PetaPocoRepository.Instance.Delete<PlaylistCategory>(existingCategory);
        }

        internal List<PlaylistCategoryIcon> GetIcons()
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<PlaylistCategoryIcon>();
        }

        internal PlaylistCategoryIcon SaveIcon(PlaylistCategoryIcon icon)
        {
            return PetaPocoRepository.Instance.Upsert(icon);
        }

        internal void DeleteIcon(PlaylistCategoryIcon icon)
        {
            PetaPocoRepository.Instance.Delete<PlaylistCategoryIcon>(icon);
        }


    }
}