using System.Collections.Generic;
using System.Linq;
using MySqlConnector;
using PetaPoco.Business;
using Sony.Filtr.ContentManagement.Data;
using Sony.Filtr.Contracts.Definitions;
using Sony.Filtr.Database;
using Sony.Filtr.Utility.Extensions;

namespace Sony.Filtr.ContentManagement
{
    public class ContentFactory
    {
        public List<ContentPage> GetPages(int applicationId, ServiceType service)
        {
            var pages = PetaPocoRepository.ReadOnlyInstance.Fetch<ContentPage>("WHERE ApplicationId = @0 AND Service = @1", applicationId, service);

            pages = LoadPageBlocks(pages);
            
            return pages;
        }

        private List<ContentPage> LoadPageBlocks(List<ContentPage> pages)
        {
            if (!pages.Any())
                return pages;

            var pageIds = pages.Select(p => p.Id).ToList();

            var blockConnections = PetaPocoRepository.ReadOnlyInstance.Fetch<PageBlockConnection>("WHERE PageId IN (@pageIds)", new { @pageIds = pageIds.ToArray() });

            if (!blockConnections.Any())
                return pages;

            var blockIds = blockConnections.Select(connection => connection.BlockId).ToList();

            var blocks = PetaPocoRepository.ReadOnlyInstance.Fetch<ContentBlock>("WHERE Id IN (@ids)", new { @ids = blockIds.ToArray() }); 

            var blocksByConnection = blocks.Join(blockConnections, block => block.Id, connection => connection.BlockId,
                    (block, connection) =>
                        new
                        {
                            BlockId = connection.BlockId,
                            PageId = connection.PageId,
                            Order = connection.Order,
                            Block = block
                        }).GroupBy(b=> b.PageId).ToDictionary(k=> k.Key, v=> v.OrderBy(c => c.Order).Select(b=> b.Block).ToList());


            pages.ForEach(page =>
            {
                List<ContentBlock> contentBlocks;
                if (blocksByConnection.TryGetValue(page.Id, out contentBlocks))
                {
                    page.ContentBlocks = contentBlocks;
                }
            });

            return pages;
        }

        public ContentPage GetPage(int id)
        {
            var page = PetaPocoRepository.ReadOnlyInstance.SingleOrDefault<ContentPage>(id);

            if (page == null)
                return null;

            var pageList = LoadPageBlocks(new List<ContentPage>() { page });

            return pageList.First();
        }

        public ContentPage GetPage(int applicationId, ServiceType service, string pageKey)
        {
            var page = PetaPocoRepository.ReadOnlyInstance.SingleOrDefaultWithSql<ContentPage>("WHERE ApplicationId = @0 AND Service = @1 AND PageKey = @2", applicationId, service, pageKey);

            if (page == null)
                return null;

            var pageList = LoadPageBlocks(new List<ContentPage>() {page});

            return pageList.First();
        }

        public ContentPage SavePage(ContentPage page)
        {
            page = PetaPocoRepository.Instance.Upsert(page);

            //var blockIds = page.ContentBlocks.Select(block => block.Id);
            //SaveBlocksToPage(page, blockIds);

            return page;
        }

        public void RemovePage(ContentPage page)
        {
            PetaPocoRepository.Instance.Delete<ContentPage>(page);
        }

        public List<ContentBlock> GetBlocks(int applicationId)
        {
            return PetaPocoRepository.ReadOnlyInstance.Fetch<ContentBlock>("WHERE ApplicationId = @0", applicationId);
        }

        public ContentBlock GetBlock(int id)
        {
            return PetaPocoRepository.ReadOnlyInstance.SingleOrDefault<ContentBlock>(id);
        }

        public ContentBlock SaveBlock(ContentBlock block)
        {
            return PetaPocoRepository.Instance.Upsert(block);
        }

        public void RemoveBlock(ContentBlock block)
        {
            PetaPocoRepository.Instance.Delete<ContentBlock>(block);
        }

        public void RemoveAllBlockConnectionsForBlock(int blockId)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                var command = new MySqlCommand("DELETE FROM tblContentPageBlockConnection WHERE BlockId = @blockId", conn);
                command.Parameters.AddWithValue("@blockId", blockId);

                command.ExecuteNonQuery();
            }
        }

        public void SaveBlocksToPage(ContentPage contentPage, IEnumerable<int> blockIds)
        {
            using (var conn = DatabaseHandler.GetOpenConnection())
            {
                using (var tran = conn.BeginTransaction())
                {
                    var delCommand = conn.CreateCommand();
                    delCommand.Transaction = tran;
                    delCommand.CommandText = "DELETE FROM tblContentPageBlockConnection WHERE PageId = @pageId";
                    delCommand.Parameters.AddWithValue("@pageId", contentPage.Id);
                    delCommand.ExecuteNonQuery();

                    var valuesList = blockIds.ItemIndex().Select(block => string.Format("({0}, {1}, {2})", contentPage.Id, block.Item, block.Index)).ToList();
                    if (valuesList.Any())
                    {
                        var insertCmd = conn.CreateCommand();
                        insertCmd.Transaction = tran;

                        var allValuesString = string.Join(", ", valuesList);
                        insertCmd.CommandText = string.Format("INSERT INTO tblContentPageBlockConnection (PageId, BlockId, `Order`) VALUES {0}", allValuesString);

                        insertCmd.ExecuteNonQuery();
                    }

                    tran.Commit();
                }
            }
        }

    }
}