using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GB5Shared.QueryExecutor;
using FrameworkDAL.DTO.Lov;
using FrameworkDAL.Query.Lov;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace FrameworkDAL.CustomCode.Lov
{
    public class LovDAL : ILovDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<LovDTO> GetLov(int LovId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LovQB.GET_LOV;
                var parameters = new { lovid = LovId };
                LovDTO LovDTOs = await _QueryExecutor.QuerySingleAsync<LovDTO>(LoginDTO, sql, parameters);
                return LovDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListLov(int FirstNumber,int MaxResult,CriteriaDTO CriteriaDTO,LoginDTO LoginDTO)
        {
            try
            {
                string lovTypeId = "";
                string code = "";
                string name = "";

                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        if (section.AttributesCriteriaList == null)
                            continue;

                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            string field = attr.FieldName?.ToLower() ?? "";

                            switch (field)
                            {
                                case "lovtype.id":
                                    lovTypeId = Convert.ToString(attr.FieldValue);
                                    break;

                                case "code":
                                    code = Convert.ToString(attr.FieldValue);
                                    break;

                                case "name":
                                    name = Convert.ToString(attr.FieldValue);
                                    break;
                            }
                        }
                    }
                }

                var sql = new StringBuilder(LovQB.GET_SELECTLIST_LOV);

                if (!string.IsNullOrWhiteSpace(lovTypeId))
                    sql.Append("\n AND LT.LOVTYPEID IN (" + lovTypeId + ")");

                if (!string.IsNullOrWhiteSpace(code))
                    sql.Append("\n AND L.LOVCODE LIKE @Code");

                if (!string.IsNullOrWhiteSpace(name))
                    sql.Append("\n AND L.LOVNAME LIKE @Name");

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

                var parameter = new
                {
                    firstnumber = FirstNumber,
                    maxresult = MaxResult,
                    Code = "%" + code + "%",
                    Name = "%" + name + "%"
                };

                var result = await _QueryExecutor.QueryAsync<LovPicklistDTO>(
                    LoginDTO,
                    finalSql,
                    parameter);

                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }

        public async Task<int> SaveLov(LovDTO LovDTO, LoginDTO LoginDTO)
        {
            try
            {

                const string Sql = LovQB.SAVE_LOV;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, LovDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateLov(LovDTO LovDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = LovQB.UPDATE_LOV;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, LovDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }



        public async Task<string> DeleteLov(int LovId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = LovQB.DELETE_LOV;
                var parameter = new { lovid = LovId };
                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);
            }
        }
    }
}