using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Transactions;
using App.Common.Helpers.DB;
using App.Common.Helpers.Instance;
using App.Domain.DataRepository.Base;
using App.Domain.DataRepositoryContracts;
using App.Domain.Entities.Common;
using App.Domain.Entities.Products;
using App.Domain.Entities.Products.Dto;
using App.Domain.Entities.Shopify;
using Dapper;
using Signals.Aspects.DI.Attributes;
using Signals.Core.Processing.Results;
using SqlBulkTools;
using SqlBulkTools.Enumeration;
using SQLinq.Dapper;

namespace App.Domain.DataRepository.Products
{
    [Export(typeof(IProductsRepository))]

    internal class ProductsRepository : BaseDbRepository<Product>, IProductsRepository
    {
        /// <summary>
        /// Get the products for export to shopify by account retailers
        /// </summary>
        /// <param name="clientId"></param>
        /// <returns></returns>
        public ListResult<Product> GetProductsForClient(int clientId,int page, int pageSize)
        {
                return Using(connection =>
           {
               var sql = $@"
                   DECLARE @clientId INT = {clientId}
                   DECLARE @page INT = {page}
                   DECLARE @pageSize INT = {pageSize};
                   WITH
                   CTEResults AS
                   (
                   SELECT
                   p.*,
                   c.NAME AS Category,
                   r.NAME AS RetailerName,
                   ROW_NUMBER() OVER (ORDER BY p.id asc) AS RowNum
                   FROM products p
                   LEFT JOIN categories c ON c.id = p.categoryid
                   LEFT JOIN retailers r ON r.id = p.retailerid
                   LEFT JOIN productexternalmap pem ON pem.productid = p.id AND (pem.clientid = @clientId  OR pem.clientid IS NULL)
                   WHERE
                   p.statusID=1 AND
                   ((pem.productid IS NULL AND p.retailerid IN(SELECT retailerid FROM retailer_clients rc WHERE clientid=@clientId AND enabled=1
                   AND EXISTS(SELECT id FROM retailers WHERE id=rc.retailerId AND statusid=1)) AND p.clientId=0 ))
                   OR (pem.ProductId IS NOT NULL AND p.updated > pem.LastUpdatedOn )

                   )
                   SELECT
                    p.*
                   ,pii.id
                   ,pii.src
                   ,pv.[Id]
                   ,pv.[statusId]
                   ,pv.position
                   ,pv.[updated]
                   ,pv.[type]
                   ,pv.[value]
                   ,pv.[sku]
                   ,pv.[upc]
                   ,pv.[upcLooked]
                   ,pv.[price]
                   ,pv.[compareAtPrice]
                   ,pv.[stock]
                   ,pv.[providerId]
                   ,pv.[options]
                   ,por.optionType
                   ,por.optionKey FROM (
                   SELECT *
                   FROM CTEResults
                   WHERE RowNum > ((@Page - 1) * @PageSize) AND RowNum <= (@Page * @PageSize)
                   ) p
                   LEFT JOIN product_variants pv ON pv.productid = p.id
                   LEFT JOIN product_variant_options pvo ON pv.id=pvo.variantId
                   LEFT JOIN product_options_raw por ON por.id = pvo.rawOptionId
                   LEFT JOIN product_images pii ON pii.productid = p.id
                   WHERE (pv.statusid = 1) AND (pii.src is not null)
                                 ";

               Dictionary<int, Product> lookup = new Dictionary<int, Product>();
               connection.Query<Product, ProductImage, ProductVariant, Product>(sql, (product, image, variant) =>
               {
                   Product mapProduct;
                   if (!lookup.TryGetValue(product.Id, out mapProduct))
                   {
                       lookup.Add(product.Id, mapProduct = product);
                   }

                   mapProduct.Variants.Add(variant);
                   mapProduct.Images.Add(image);

                   return mapProduct;
               },commandTimeout:0);

               return (lookup.Values.ToList());
           });
        }

        /// <summary>
        /// We get the products for all clients
        /// </summary>
        /// <param name="clientId"></param>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<ClientShopifyCredentials> GetClientsRecurringTask()
        {


            return Using(connection => connection.Query<ClientShopifyCredentials>(@"
                                   SELECT * FROM ClientShopifyCredentials
                                 ")).ToList();


        }

        /// <summary>
        /// Function to get the mapped products with all columns
        /// </summary>
        /// <returns></returns>
        public List<ProductExternalMapping> GetMappedProducts(int clientId)
        {
            return Using(connection => connection.Query<ProductExternalMapping>(@"
                                  SELECT
                                  pem.Id
                                 ,pem.ProductId
                                 ,pem.ExternalId
                                 ,pem.CreatedOn
                                 ,pem.LastUpdatedOn
                                 ,pem.AppName
                                 ,pem.Handle
                                 ,pem.ClientId
                                  FROM ProductExternalMap pem
                                  INNER JOIN Products p ON p.id = pem.productid
                                  WHERE
                                  pem.clientid=@clientId
                                 ",
                new
                {
                    clientId = clientId
                }).ToList());
        }

        /// <summary>
        /// Only mapped productid and externalid function
        /// </summary>
        /// <param name="clientId"></param>
        /// <returns></returns>
        public List<ProductExternalAndLocalId> GetMappedProductIds(int clientId)
        {
            return Using(connection => connection.Query<ProductExternalAndLocalId>(@"
                                  SELECT
                                  ProductId,
                                  ExternalId
                                  FROM ProductExternalMap
                                  WHERE
                                  clientid=@clientId
                                 ",
                new
                {
                    clientId = clientId
                }).ToList());
        }

        /// <summary>
        /// Function to get ExternalIds from productexternalmap
        /// </summary>
        /// <returns></returns>
        public List<long> GetExternalIdProducts(int clientId)
        {
            return Using(connection => connection.Query<long>(@"
                                  SELECT
                                  pem.ExternalId
                                  FROM ProductExternalMap pem
                                  INNER JOIN Products p ON p.id = pem.productid
                                  WHERE
                                  pem.clientid=@clientId
                                 ",
                new
                {
                    clientId = clientId
                }).ToList());
        }

        public int GetExportedCount(int clientId)
        {
            return Using(connection =>
            {
                var sql = $@"
                                  SELECT
                                  Count(pem.ExternalId)
                                  FROM ProductExternalMap pem
                                  INNER JOIN Products p ON p.id = pem.productid
                                  WHERE
                                  pem.clientid=@clientId
                                 ";

                var reader = connection.QueryMultiple($"{sql};", new { clientid = clientId }, commandTimeout: 0);

                var totalCount = reader.Read<int>().SingleOrDefault();
                return totalCount;
            });
        }
        /// <summary>
        /// Create a relation between a products and external system
        /// </summary>
        /// <param name="mappings"></param>
        /// <param name="deleted"></param>
        public void MapProductsWithExternalProvider(List<ProductExternalMapping> mappings)
        {
            if (!mappings.IsNullOrHasZeroElements())
            {
                ///next we insert or update the other ones that are matched
                Using(connection =>
                {
                    var bulk = new BulkOperations();
                    using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromMinutes(10)))
                    {
                        bulk.Setup<ProductExternalMapping>()
                            .ForCollection(mappings)
                            .WithTable("ProductExternalMap")
                            .AddAllColumns()
                            .BulkInsertOrUpdate()
                            .MatchTargetOn(x => x.ExternalId)
                            .SetIdentityColumn(x => x.Id, ColumnDirectionType.Input)
                            .Commit(connection as SqlConnection);

                        trans.Complete();
                    }

                });
            }


        }

        /// <summary>
        /// Maps single webhook response to database
        /// </summary>
        /// <param name="map"></param>
        public void MapWebhookResponse(ProductExternalMapping map)
        {
                ///next we insert mapping
                Using(connection =>
                {
                    connection.Query(@"INSERT INTO [dbo].[ProductExternalMap]
                                                        (
                                                     [ProductId]
                                                    ,[ExternalId]
                                                    ,[CreatedOn]
                                                    ,[LastUpdatedOn]
                                                    ,[AppName]
                                                    ,[ClientId]
                                                          )
                                                     (
                                                     SELECT
                                                     @ProductId,
                                                     @ExternalId,
                                                     @CreatedOn,
                                                     @LastUpdatedOn,
                                                     @AppName,
                                                     @ClientId
                                                     )
                                                          ",
                        new
                        {
                            ProductId = map.ProductId,
                            ExternalId = map.ExternalId,
                            CreatedOn = map.CreatedOn,
                            LastUpdatedOn = map.LastUpdatedOn,
                            AppName = map.AppName,
                            ClientId = map.ClientId

                        });
                });


        }
        /// <summary>
        /// Deleting external products and the mapping
        /// </summary>
        /// <param name="deleted"></param>
        public void DeleteExternalProductAndMapping(List<ProductExternalMapping> deleted)
        {
            // TODO: implement bulk insert/update/delete
            if (!deleted.IsNullOrHasZeroElements())
            {
                //first we delete the not matched ones
                Using(connection =>
                {
                    int pageSize = 1000;
                    for (int page = 0; ; page++)
                    {
                        var ids = deleted.Skip(page * pageSize).Take(pageSize).Select(x => x.ExternalId).ToList();
                        connection.Execute(@"DELETE FROM ProductExternalMap WHERE ExternalId IN @Ids", new { Ids = ids }, commandTimeout:0);
                        if (ids.Count < pageSize) break;
                    }
                });

            }
        }

        /// <summary>
        /// We use this to get the clients credentials with his id
        /// </summary>
        /// <param name="clientId"></param>
        /// <returns></returns>
        public ClientShopifyCredentials GetCredentials(int clientId)
        {


            var cscConvertedtemp = Using(connection => connection.Query<ClientShopifyCredentials>(@"
                                   SELECT
                                   Id
                                  ,ClientId
                                  ,ShopifyStoreURL
                                  ,ShopifyStorePassword
                                  FROM ClientShopifyCredentials
                                  WHERE ClientId = @clientId;
                                 ",
                new
                {
                    ClientId = clientId
                }).SingleOrDefault());

            return cscConvertedtemp;

        }

        /// <summary>
        /// We set the credentials for the shopify access here or we insert new record
        /// </summary>
        /// <param name="obj"></param>
        public void SetCredentials(ClientShopifyCredentials obj)
        {

            Using(connection => connection.Query(@"
                IF @ClientId
                IN (SELECT DISTINCT clientid FROM dbo.clientshopifycredentials)
                BEGIN
                UPDATE [dbo].[ClientShopifyCredentials]
                SET ShopifyStoreURL = @ShopifyStoreURL, ShopifyStorePassword = @ShopifyStorePassword
                WHERE clientId = @ClientId
                END
                ELSE
                INSERT INTO
                [dbo].[ClientShopifyCredentials]
                (
                 [ClientId]
                ,[ShopifyStoreURL]
                ,[ShopifyStorePassword])

                (
				SELECT
                 @ClientId
                ,@ShopifyStoreURL
                ,@ShopifyStorePassword
                );
                                                ",
                new
                {
                    ClientId = obj.ClientId,
                    ShopifyStoreURL = obj.ShopifyStoreURL,
                    ShopifyStorePassword = obj.ShopifyStorePassword
                }));

        }

        /// <summary>
        /// We get all the products for grid here
        /// </summary>
        /// <param name="queryOptions"></param>
        /// <param name="clientId"></param>
        /// <returns></returns>
        public ExportGridToShopify GetAllProductsForGrid(int clientId, AdvancedQueryOptions queryOptions)
        {
            return Using(connection =>
            {
                var queryBuilder = new StringBuilder(queryOptions.Filters.Count > 0 ? string.Empty : "1 = 1");
                var currentIndex = 0;
                var shouldAppendOrOp = true;
                dynamic executionData = new ExpandoObject();
                foreach (var filter in queryOptions.Filters)
                {
                    if (currentIndex + 1 == queryOptions.Filters.Count) shouldAppendOrOp = false;

                    var query = string.Empty;
                    switch (filter.Name)
                    {
                        case @"Id":
                            queryBuilder.AppendLine(@" p.Id LIKE @Id ");
                            executionData.Id = $@"%{filter.Value}%";
                            break;
                        case @"Title":
                            queryBuilder.AppendLine(@" p.Title LIKE @Title ");
                            executionData.Title = $@"%{filter.Value}%";
                            break;
                        case @"Created":
                            queryBuilder.AppendLine(@" cast(p.Created as Date) = @Created ");
                            executionData.Created = filter.Value;
                            break;
                        case @"Category":
                            queryBuilder.AppendLine(@" c.Name LIKE @Category ");
                            executionData.Category = $@"%{filter.Value}%";
                            break;
                        case @"RetailerName":
                            queryBuilder.AppendLine(@" r.Name LIKE @RetailerName ");
                            executionData.RetailerName = $@"%{filter.Value}%";
                            break;
                        case @"Brand":
                            queryBuilder.AppendLine(@" p.Brand LIKE @Brand ");
                            executionData.Brand = $@"%{filter.Value}%";
                            break;
                        case @"StatusId":
                            queryBuilder.AppendLine(@" p.StatusId = @StatusId ");
                            executionData.StatusId = filter.Value;
                            break;
                        default:
                            currentIndex++;
                            continue;
                    }

                    queryBuilder.AppendLine(shouldAppendOrOp ? $@"{query} AND " : query);
                    currentIndex++;
                }
                var orderByBuilder = new StringBuilder();
                if (InstanceHelper.IsNullOrEmpty(queryOptions.OrderBy))
                {
                    queryOptions.OrderBy = @"p.Created";
                    queryOptions.Order = OrderBy.Desc;
                }

                var orderbyField = queryOptions.OrderBy;
                var orderby = queryOptions.Order.ToString();
                switch (queryOptions.OrderBy)
                {
                    case @"Id":
                        orderbyField = @"p.Id";
                        break;
                    case @"Title":
                        orderbyField = @"p.Title";
                        break;
                    case @"Created":
                        orderbyField = @"p.Created";
                        break;
                    case @"Category":
                        orderbyField = @"c.Name";
                        break;
                    case @"RetailerName":
                        orderbyField = @"r.Name";
                        break;
                    case @"Brand":
                        orderbyField = @"p.Brand";
                        break;
                    case @"StatusId":
                        orderbyField = @"p.StatusId";
                        break;
                }

                var orderbyStatement = $@"{orderbyField} {orderby}";
                orderByBuilder.AppendLine(orderbyStatement);

                var sql =
                    $@"
                    ;WITH
                    CTEResults AS
                   (
                   SELECT
                        p.ID,
                        p.Created,
			            p.StatusId,
						p.Title,
						c.Name AS Category,
						r.Name AS RetailerName,
                        p.Brand,
                   ROW_NUMBER() OVER (ORDER BY {orderByBuilder},p.id) AS RowNum
                   FROM products p
                   LEFT JOIN categories c ON c.id = p.categoryid
                   LEFT JOIN retailers r ON r.id = p.retailerid
                   LEFT JOIN productexternalmap pem ON pem.productid = p.id AND (pem.clientid = {clientId} or pem.clientid is null)
                   WHERE
                   {queryBuilder}
                   AND p.statusID=1
                   AND ((pem.productid is null and p.retailerid in(SELECT retailerid FROM retailer_clients rc WHERE clientid={clientId}  AND enabled=1
                   AND EXISTS(select id from retailers where id=rc.retailerId and statusid=1)) AND p.clientId=0 ))
                   OR (pem.ProductId IS NOT NULL AND p.updated > pem.LastUpdatedOn)


                   )
                   SELECT
                        p.ID,
                        p.Created,
			            p.StatusId,
                        (select top 1 src from product_images where p.id=productId) AS Image,
						p.Title,
						p.Category,
						p.RetailerName,
                        p.Brand

                   FROM (
                   SELECT *
                   FROM CTEResults
                   WHERE RowNum BETWEEN (@Page - 1) * @PageSize AND @Page * @PageSize
                   ) p
					                         ";

                var countSql =
                    $@"
					   SELECT
                       count (distinct p.id)
                        FROM Products P
                        LEFT JOIN ProductExternalMap pem ON pem.ProductId =p.id AND (pem.clientid = {clientId} or pem.clientid is null)
						LEFT JOIN Retailers r on r.id=p.retailerId
						LEFT JOIN categories c on c.id=p.categoryId
                        WHERE
                        {queryBuilder} AND
                        (pem.productid IS NOT NULL AND p.updated > pem.LastUpdatedOn )
                        AND p.statusId=1 AND (p.clientId={clientId}
                        OR ((p.retailerid in(select retailerid from retailer_clients rc where clientid={clientId} and enabled=1
                        AND EXISTS(select id from retailers where id=rc.retailerId and statusid=1)) and p.clientId=0 )))

                    ";

                var createSql =
                 $@"
					     SELECT
                       count (distinct p.id)
                        FROM Products P
                        LEFT JOIN ProductExternalMap pem ON pem.ProductId =p.id  AND (pem.clientid = {clientId} or pem.clientid is null)
						LEFT JOIN Retailers r on r.id=p.retailerId
						LEFT JOIN categories c on c.id=p.categoryId
                        WHERE  pem.productid IS NULL AND
                        p.statusId=1 AND (p.clientId={clientId}
                        OR ((p.retailerid in(select retailerid from retailer_clients rc where clientid={clientId} and enabled=1
                        AND EXISTS(select id from retailers where id=rc.retailerId and statusid=1)) and p.clientId=0 )))

                    ";
                var updateSql =
                 $@"
					    SELECT
                        count (distinct p.id)
                        FROM Products P
                        INNER JOIN ProductExternalMap pem ON pem.ProductId =p.id AND pem.clientid = {clientId}
						LEFT JOIN Retailers r on r.id=p.retailerId
						LEFT JOIN categories c on c.id=p.categoryId
                        WHERE
                        (pem.LastUpdatedOn IS NOT NULL AND p.updated > pem.LastUpdatedOn)
					    AND p.statusId=1 AND (p.clientId={clientId}
                        OR ((p.retailerid in(select retailerid from retailer_clients rc where clientid={clientId} and enabled=1
                        AND EXISTS(select id from retailers where id=rc.retailerId and statusid=1)) and p.clientId=0 )))
                    ";
                var exported =
                 $@"
                        SELECT
                        COUNT(Pem.productid)
                        FROM ProductExternalMap PEM
                        WHERE
                        Pem.ClientId={clientId}
                     ";

                var totalProducts =
               $@"
                     SELECT
                        COUNT(P.Id)
                        FROM Products P
                        WHERE
					    p.statusId=1 AND (p.clientId={clientId}
                        OR ((p.retailerid in(select retailerid from retailer_clients rc where clientid={clientId} and enabled=1
                        AND EXISTS(select id from retailers where id=rc.retailerId and statusid=1)) and p.clientId=0 )))

                     ";

                executionData.PageSize = queryOptions.PageSize;
                executionData.Page = queryOptions.Page;

                var str = connection.GetProfilingSql(sql, (object)executionData);

                var reader = connection.QueryMultiple($"{sql}; {countSql}; {exported}; {totalProducts}; {updateSql}; {createSql};", (object)executionData,commandTimeout:0);
                var values = reader.Read<ProductGrid>().ToList();
                var totalCount = reader.Read<int>().SingleOrDefault();
                var totalExported = reader.Read<int>().SingleOrDefault();
                var totalProductsEver = reader.Read<int>().SingleOrDefault();
                var totalUpdate = reader.Read<int>().SingleOrDefault();
                var totalCreate = reader.Read<int>().SingleOrDefault();

                return new ExportGridToShopify(values, totalCount) { ExportCount = totalExported, TotalProducts = totalProductsEver, ForUpdate =totalUpdate, ForCreating = totalCreate};
            });
        }

        /// <summary>
        /// Getting the deleted products
        /// </summary>
        /// <param name="clientId"></param>
        /// <returns></returns>
        public List<ProductExternalMapping> GetProductsWithStatusDeleted(int clientId)
        {
            return Using(connection => connection.Query<ProductExternalMapping>(@"
                               ;WITH CTE AS
                              (
                                SELECT pem.*
                                FROM PRODUCTS P
                                INNER JOIN ProductExternalMap pem ON pem.ProductId = p.id
                                WHERE
                                pem.ClientId=@clientid and p.statusId=3 or(EXISTS(select id from retailers r where r.id = p.retailerId and r.statusid=0)
								OR  EXISTS(select id from retailer_clients rc where rc.retailerId = p.retailerId AND rc.clientid=@clientid and rc.enabled=0))

                              )
                        SELECT *
                        FROM CTE
                                  ",
                                 new
                                 {
                                     ClientId = clientId
                                 }).ToList());
        }

        /// <summary>
        /// With this we get the categories in a list so we can use them later
        /// </summary>
        /// <returns></returns>
        public List<Category> GetCategories(int clientId)
        {
            return Using(connection => connection.Query<Category>(@"
                                 SELECT *
                                 FROM Categories c
                                 WHERE
								 c.id IN (SELECT DISTINCT P.CategoryId FROM Products P
                                 LEFT JOIN product_categories PC ON PC.productId = P.ID
                                 WHERE  p.statusID=1 AND
                   ((p.clientId=@clientId ) or (p.retailerid in(select retailerid from retailer_clients rc where clientid=@clientId and enabled=1
                   AND EXISTS(select id from retailers where id=rc.retailerId and statusid=1)) and p.clientId=0 )))
                                  ",
                                  new
                                  {
                                      ClientId = clientId
                                  }).ToList());
        }

        /// <summary>
        /// Get Categories
        /// </summary>
        /// <param name="clientId"></param>
        /// <returns></returns>
        public List<CategoryExternalMapping> GetMappedCategories(int clientId)
        {
            return Using(connection => connection.Query<CategoryExternalMapping>(@"
                                  ;WITH CTE as (SELECT DISTINCT P.CategoryId FROM Products P
                                 LEFT JOIN product_categories PC ON PC.productId = P.ID
                                 WHERE p.statusID=1 AND
                                 ((p.clientId=@clientId ) or (p.retailerid in(select retailerid from retailer_clients rc where clientid=@clientId and enabled=1
                                 AND EXISTS(select id from retailers where id=rc.retailerId and statusid=1)) and p.clientId=0 )))
								 SELECT *
                                  FROM CategoryExternalMap cem
                                 INNER JOIN cte c ON c.categoryId=cem.CategoryId AND cem.clientid=@clientId",
                                  new
                                  {
                                      ClientId = clientId
                                  }).ToList());
        }

        /// <summary>
        /// Map created categories from shopify
        /// </summary>
        /// <param name="mapping"></param>
        public void MapExternalCategories(CategoryExternalMapping mapping)
        {
            ///next we insert mapping
            Using(connection =>
            {
                connection.Query(@"INSERT INTO [dbo].[CategoryExternalMap]
                                                        (
                                                     [CategoryId]
                                                    ,[ExternalId]
                                                    ,[CreatedOn]
                                                    ,[LastUpdatedOn]
                                                    ,[AppName]
                                                    ,[ClientId]
                                                          )
                                                        (
                                                        SELECT
                                                        @CategoryId,
                                                        @ExternalId,
                                                        @CreatedOn,
                                                        @LastUpdatedOn,
                                                        @AppName,
                                                        @ClientId
                                                        )
                                                          ",
                    new
                    {
                        CategoryId = mapping.CategoryId,
                        ExternalId = mapping.ExternalId,
                        CreatedOn = mapping.CreatedOn,
                        LastUpdatedOn = mapping.LastUpdatedOn,
                        AppName = mapping.AppName,
                        ClientId = mapping.ClientId

                    });
            });
        }

        /// <summary>
        /// Saving backgorund service status, so we can read later
        /// </summary>
        /// <param name="status"></param>
        /// <param name="clientId"></param>
        public void BackgroundServiceStatus(string status, int clientId)
        {
            ///next we insert mapping
            Using(connection =>
            {
                connection.Query(@"INSERT INTO [dbo].[ShopifyProcessingReports]
                                                        (
                                                     [ClientId]
                                                    ,[StatusMessage]
                                                    ,[ProcessTimeStamp]
                                                          )
                                                        (
                                                        SELECT
                                                        @ClientId,
                                                        @StatusMessage,
                                                        GetDate()
                                                        )
                                                          ",
                    new
                    {
                        ClientId = clientId,
                        StatusMessage = status
                    });
            });
        }
    }
}