using FrameworkDAL.DTO.Gcm;
using FrameworkDAL.Query.GCM;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.PickListGenerator;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.GCM
{
    public class GCMDAL : IGCMDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public GCMDAL(IQueryExecutor IQueryExecutor, IValidation Validation)
        { 
            _QueryExecutor = IQueryExecutor;
            _Validation = Validation;
        }
        public async Task<int> GetSelectListGCMCount(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string sql = GCMQB.PICKLIST_GCMSELECTLIST_COUNT;
            sql = await ApplyGCMPickList(sql, CriteriaDTO!, LoginDTO).ConfigureAwait(false);
            sql = await ApplyGCMPickList(sql, LoginDTO.UserCriteriaDTO, LoginDTO).ConfigureAwait(false);

            var (filterSql, param) = BuildGCMFilters(CriteriaDTO);
            sql += filterSql;

            return await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, param).ConfigureAwait(false);
        }

        public async Task<IEnumerable<GcmPicklistDTO>> GetSelectListGCM(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            string sql = GCMQB.PICKLIST_GCMSELECTLIST_SORT_ORDER;
            sql = await ApplyGCMPickList(sql, CriteriaDTO!, LoginDTO).ConfigureAwait(false);
            sql = await ApplyGCMPickList(sql, LoginDTO.UserCriteriaDTO, LoginDTO).ConfigureAwait(false);

            var (filterSql, param) = BuildGCMFilters(CriteriaDTO);
            sql += filterSql;
            sql += "\r\n ORDER BY gcm.SORTORDER";

            return await _QueryExecutor.QueryAsync<GcmPicklistDTO>(LoginDTO, sql, param).ConfigureAwait(false);
        }

        private static (string Sql, object? Param) BuildGCMFilters(CriteriaDTO criteriaDTO)
        {
            string id      = "";
            string code    = "";
            string name    = "";
            string remarks = "";

            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 ?? "";
                        string value = Convert.ToString(attr?.FieldValue) ?? "";
                        if (string.Equals(field, "id",      StringComparison.OrdinalIgnoreCase)) id      = value;
                        if (string.Equals(field, "code",    StringComparison.OrdinalIgnoreCase)) code    = value;
                        if (string.Equals(field, "name",    StringComparison.OrdinalIgnoreCase)) name    = value;
                        if (string.Equals(field, "remarks", StringComparison.OrdinalIgnoreCase)) remarks = value;
                    }
                }
            }

            var sql    = new System.Text.StringBuilder();
            var param  = new System.Dynamic.ExpandoObject() as IDictionary<string, object>;

            if (!string.IsNullOrWhiteSpace(id) && int.TryParse(id, out int gcmId))
            {
                sql.Append("\r\n AND gcm.GCMID = @IdFilter");
                param["IdFilter"] = gcmId;
            }
            if (!string.IsNullOrWhiteSpace(code))
            {
                sql.Append("\r\n AND gcm.GCMCODE LIKE @CodeFilter");
                param["CodeFilter"] = $"%{code}%";
            }
            if (!string.IsNullOrWhiteSpace(name))
            {
                sql.Append("\r\n AND gcm.GCMNAME LIKE @NameFilter");
                param["NameFilter"] = $"%{name}%";
            }
            if (!string.IsNullOrWhiteSpace(remarks))
            {
                sql.Append("\r\n AND gcm.REMARKS LIKE @RemarksFilter");
                param["RemarksFilter"] = $"%{remarks}%";
            }

            return (sql.ToString(), param.Count > 0 ? param : null);
        }

        public async Task<string> ApplyGCMPickList(String Sql, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            IPickListExecutor pickListExecutor = new PickListExecutor();
            try
            {
                if (CriteriaDTO != null)
                {
                    string[,] CriteriaWithQueryField = new string[,]
                {

                    {"GcmType.Id", "gcm.GcmTypeId"} 
                 };

                    Sql = await pickListExecutor.ApplyCriteria(Sql, CriteriaWithQueryField, CriteriaDTO, LoginDTO);
                }
                return Sql;
            }
            catch (Exception  )
            {
                throw  ;
            } 
        }
        public async Task<string> GetGCM(int GcmId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = GCMQB.Get_GCM;
                var parameters = new { gcmid = GcmId };
                GcmDTO GcmDTOs = await _QueryExecutor.QuerySingleAsync<GcmDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(GcmDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

        public async Task<int> SaveGCM(GcmDTO GcmDTO, LoginDTO LoginDTO)
        {
            try
            {

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

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


        public async Task<string> DeleteGCM(int GCMId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = GCMQB.DELETE_GCM;
                var parameter = new { GCMid = GCMId };
                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);
            }
        }
    }
}