using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AdminDAL.DTO.Allocation;
using AdminDAL.Query.Allocation;
using Azure;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using GB5Shared.Validation;
using AdminDAL.DTO.Contact;




namespace AdminDAL.CustomCode.Allocation
{
    public class AllocationDAL : IAllocationDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public AllocationDAL(IQueryExecutor IQueryExecutor, IValidation validation)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = validation;
        }

        public async Task<string> GetAllocation(int MAllocationId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = AllocationQB.GET_ALLOCATION;
                var parameters = new { mallocationid = MAllocationId };
                MAllocationDTO AllocationDTOs = await _QueryExecutor.QuerySingleAsync<MAllocationDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(AllocationDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }
        public async Task<string> GetSelectListAllocation(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = AllocationQB.GET_SELECTLIST_ALLOCATION;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    var Result = await _QueryExecutor.QueryAsync<MAllocationPicklistDTO>(LoginDTO, SQL, parameter);
                    Json = JsonConvert.SerializeObject(Result);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveAllocation(MAllocationDTO MAllocationDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = AllocationQB.SAVE_ALLOCATION;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, MAllocationDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateAllocation(MAllocationDTO MAllocationDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string sql = AllocationQB.UPDATE_ALLOCATION;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, MAllocationDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<IEnumerable<MAllocationSplitPickListDTO>> GetSelectListMAllocationSplit(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string allocationId      = "";
                string allocationSplitId = "";
                string code              = "";
                string name              = "";

                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            string field = attr.FieldName.ToLower();
                            if (field == "allocationid")       allocationId      = Convert.ToString(attr.FieldValue);
                            else if (field == "id")            allocationSplitId = Convert.ToString(attr.FieldValue);
                            else if (field == "code")          code              = Convert.ToString(attr.FieldValue);
                            else if (field == "name")          name              = Convert.ToString(attr.FieldValue);
                        }
                    }
                }

                var sql = new System.Text.StringBuilder(AllocationQB.GET_SELECTLIST_MALLOCATION_SPLIT_BASE);
                if (!string.IsNullOrEmpty(allocationId))      sql.Append($"\r\n AND SP.ALLOCATIONID IN ({allocationId})");
                if (!string.IsNullOrEmpty(allocationSplitId)) sql.Append($"\r\n AND SP.ALLOCATIONSPLITID IN ({allocationSplitId})");
                if (!string.IsNullOrEmpty(code))              sql.Append($"\r\n AND SP.SPLITCODE IN ({code})");
                if (!string.IsNullOrEmpty(name))              sql.Append($"\r\n AND SP.SPLITNAME IN ({name})");

                var pagedSql = $@"
                    WITH AllocationSplit AS (
                        SELECT Id, Code, Name,
                               ROW_NUMBER() OVER (ORDER BY Id) AS RowNum
                        FROM ({sql}) AS BaseQuery
                    )
                    SELECT Id, Code, Name
                    FROM   AllocationSplit
                    WHERE  (@firstnumber = -1 AND @maxresult = -1)
                       OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

                var parameters = new { firstnumber = FirstNumber, maxresult = MaxResult };
                return await _QueryExecutor.QueryAsync<MAllocationSplitPickListDTO>(LoginDTO, pagedSql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> DeleteAllocation(int MAllocationId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = AllocationQB.DELETE_ALLOCATION;
                var parameter = new { mallocationid = MAllocationId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameter);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }
    }
}